Hi -

I am using EJB3 + JPA {Hibernate 3} on Weblogic10 App server and have been facing the following problem: -

A)Table User
---------------
UserID PK
UserName

B)Table Role
---------------
RoleID PK
RoleName


C)Table UserRole {composite PK}
--------------
UserID (PK)
RoleID (PK)
UserRoleCreateDate


I have 3 Entities corresponding to these 3 tables. And their relationship is as follows: -

A) User
@OneToMany(fetch = FetchType.EAGER, mappedBy="user", cascade=CascadeType.ALL)
private Set<UserRole> userRoleCollection;


B) Role
@OneToMany(fetch = FetchType.EAGER, mappedBy="role", cascade=CascadeType.ALL)
private Set<UserRole> userRoleCollection;

C) UserRole

has a UserRolePK class with userID and roleID.
and has these relation declaration

@ManyToOne
@JoinColumn(name="ROLE_ID", insertable=false, updatable=false)
private Role role;

@ManyToOne
@JoinColumn(name="USER_ID", insertable=false, updatable=false)
private User user;


Now I have a business requirement that I should be able to add and delete user+role asscociation in UserRole table. In order to accomplish the same I have the following business methods in my EJB3 implementation class: -


Add Role to User method

public User addRole(User user, long id) {
Query q = em.createQuery("select e from Role e " +
"where e.roleId in (:roleIDs)");
q.setParameter("roleIDs", id);
List<Role> rolesTobeAdded = q.getResultList();

for(Role role : rolesTobeAdded) {
UserRolePK pk = new UserRolePK();
pk.setUserId(user.getUserId());
pk.setRoleId(role.getRoleId());

UserRole ur = new UserRole();
ur.setPk(pk);
ur.setCreatedDate(new Date());
ur.setUser(user);
ur.setRole(role);
//Add user role.
user.getTempSecUserRoleCollection().add(ur);
}
return em.merge(user);
}

This method works fine and it addes the new user role association into the UserRole table. Now come the part with the problem: -


Delete Role to User method

public User deleteRole(User user, long id) {
Set <UserRole> roleList =
user.getUserRoleCollection();
for(UserRole role: roleList) {
UserRolePK pk = new UserRolePK();
pk.setUserId(user.getUserId());
pk.setRoleId(id);

//NOTE: I have implement equals and hascode for PK class
if(role.getPk().equals(pk)) {
user.getUserRoleCollection().remove(role);
break;
}
}//end of for
return em.merge(user);
}

Now for delete from the association table, I do not want any data deleted from Role or User table. But when I inkove my delete method I do see Hibernate select queries getting executed, but it does not perform any delete on the association table. I am confused as to why insert into the association table works and not delete? I will would be grateful if someone can provide me an answer to this question.

Best Regards,
Dece

After going through tons of documentation and wasting couple of days, I figured out that, JPA 1.0 specification does not support "Delete Orphan" cascade type, as found in hibernate.

Therefore, in scenarios where there is a parent {@OneToMany}+ child{@ManyToOne}relationship, as explained in the question, and if you use JPA, when you issue a delete / disassociate children from parent by removing the children from collection in parent {as implemented in the service class} and issuing a merge on parent following action happens: -

The return Parent object after merge, would NOT contain the children in the collection BUT the children record would NOT get deleted from the association table.

This problem only manifests with delete children operation, updates and inserts works correctly. Point to be noted out here is that JPA 2.0 bundled in JEE 1.6 would support this feature. Till that time we have to do manually delete the children and then issue a merge on the parent.

I have seen this question go unanswered in many forums and if at all they are answered the correct reason isn't mentioned. Therefore I hope my answer saves time for other who might use JPA1.0 and get into the same issue.

BR