Many-to-many relationship update/delete question

leewill · **Posted:** Thu Aug 17, 2006 5:58 pm

Hibernate version: 3.1
Name and version of the database you are using: MySql 5

In my *:* relationship of User and Department, a join table (member_dept) is used. In java class, a HashSet is used in both side to represent this bi-directional relationship

Since the "inverse=true" can only be on one side, I set it on the Department side and hope to use User object to manage CRUD. However the requirement need Department GUI do the CRUD, too.

This cause problem: when use DepartmentDao to delete a user,

Code:

public void deleteMember(User member) {
....
    department.getMembers().remove(member);
    member.getDepartments().remove(this);
    session.saveOrUpdate(department);
}

it has exception of violating the foreign key contraint in table member_dept;
when use DepartmentDao to add and save a user:

Code:

public void addMember(User member) {
....
    department.getMembers().add(member);
    member.getDepartments().add(this);
    session.saveOrUpdate(department);
}

, it throws NonUniqueObjectException.

My understanding is: since only one object can manage the update of join table, and since inverse=true on Department, this object won't update that table. Therefore delete a user from Department object will of course cause fk contraint exception;

similar reason when use department object to add/save User object, "an object w/ same ID already exists in session", so that throw NonUniqueObjectException

Am I right?
My solution is (haven't tried yet): in department object
get Set of Users;
iterate through each user, call userDao.removeDepartment(dept) for delete member, or userDao.addDept(dept) for adding member;
save this user;

But I believe even if it sucess, it's a bad solution. anyone has a solution to deal with this?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You have set invsere="true" on Department, therefore it would be more correct to use saveOrUpdate on the user entity in the methods you posted. It doesn't really make a difference though, as both entities will be saved when you flush.

To avoid the constraint vioations, use not-null="true" on the <key> elements describing the columns that are causing the violations. This gives hibernate enough information to determine what order the deletes must be done in. If you put not-null="true" on both <key> mappings, the join table row will be deleted first, and that's what you need.

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

setting not-null=true which is the default by the way did not work.

Does somebody have any other solution

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

not-null's default is not always true. not-null defaults to true if the column is part of the primary key, otherwise it defaults to false. As the mapping wasn't provided, I had no way of knowing if the foreign key was the primary key or not.

leewill · **Posted:** Fri Aug 18, 2006 10:11 am

Both Department and User use a Long id as primary key. This is the mapping between the 2 entities (using xDoclet)
In User.java:

Code:

Set departments = new HashSet();

/**
   *  @hibernate.set name="departments" table="depts_users" lazy="false"
   *  @hibernate.key column="user_id"
   *  @hibernate.many-to-many class="model.Department" column="dept_id"
*/
public Set getDepartments() {
   return departments;
}

In Department.java:

Code:

Set members= new HashSet();

/**
   *  @hibernate.set name="members" table="depts_users" lazy="true" inverse="true"
   *  @hibernate.key column="dept_id"
   *  @hibernate.many-to-many class="model.User" column="user_id"
*/
public Set getUsers() {
   return users;
}

So I should set "not-null=true" on both side of @hibernate.key ?

This is part of the generated User.hbm.xml file

Code:

<set     name="departments"
            table="depts_users"
            lazy="false"
            inverse="true">

            <key  column="user_id"   not-null="true"/>         

            <many-to-many
                class="model.Department"
                column="dept_id">

             </many-to-many>

        </set>

leewill · **Posted:** Fri Aug 18, 2006 12:20 pm

I set not-null="true" on both side (on both side of hbm.xml, <key column="..." not-null="true"/>
Unfortunately it still didn't work and throw the same exception w/out not-null="true":

Quote:

Caused by: org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not delete: [model.User#4]; SQL [delete from app_user where id=? and version=?]; Can not delete or update a parent row: a foreign key constraint fails (`trakit/depts_users`, CONSTRAINT `FK344AB324576B830F` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`));

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I'd have thought that the code you provided would have worked. What if you try the less efficient option:

Code:

public void deleteMember(User member) {
....
    member.getDepartments().remove(this);
    session.saveOrUpdate(department);
    session.refresh(department);
}

This modifies the non-inverse collection, then refreshes the inverse collection from the DB, so it should be good. If this works, then I don't know what's up with your original mapping. Try removing the lazy="false", perhaps that's affecting things.

The not-null thing won't help because you're joining on primary keys, so not-null="true" is the default, like scarface said.

Actually, I have noticed one thing. In your code, and in the code that I've modified above, "this" and "department" refer to the same thing. But maybe they don't? Is there some bad delegation going on? I'd have thought that one of these two methods might be right, but the one you posted looks strange to me..

Code:

// Alternative method #1
public void deleteMember(User member) {
....
    department.getMembers().remove(member);
    // Remove this department from the member parameter
    member.getDepartments().remove(department);
    session.saveOrUpdate(department);
} 

// Alternative method #2
public void deleteMember(User member) {
....
    // Remove the member parameter from this department
    getMembers().remove(member);
    member.getDepartments().remove(this);
    session.saveOrUpdate(this);
}

It just seems unlikely to me that "this" and "department" are the same thing, unless you typoed your post..