many to many relationship with junction table

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

I have user and role tables with a junction table that has the primary keys of the usr_id and role_id, along with some extra fields. This is very similar to what is described in http://forum.hibernate.org/viewtopic.php?t=959196&highlight=compositeelement and in section 6.3.2 of Hibernate in Action (p 229). I have used a composite element to map the extra fields, which are the username and date created.

User declares a Set of Roles:

Code:

private Set<Role> roles = new LinkedHashSet<Role>();

and has a public getter and setter for the Roles Set and also an addRole method which takes a Role object as its parameter.

The extra java class used in the composite-element is as follows:

Code:

public class UserRole  implements Serializable {
   private User user;
   private Role role;
   private String userIdCreate = Utils.getUsername();
   private Date dateCreate = new Date();

   public UserRole() {
   }

   public Date getDateCreate() {
      return dateCreate;
   }

   public Role getRole() {
      return role;
   }

   public User getUser() {
      return user;
   }

   public String getUserIdCreate() {
      return userIdCreate;
   }

   public void setUser(User user) {
      this.user = user;
   }

   public void setRole(Role role) {
      this.role = role;
   }
   
}

The mapping for User has this:

Code:

<hibernate-mapping auto-import="true" default-lazy="false">
   <class name="au.com.woolworths.hrportal.user.User" table="USR">
...
      <set name="roles" table="USR_ROLE_XREF" cascade="all" access="field" lazy="true">
         <key column="USR_ID" not-null="true"/>
         <composite-element class="au.com.woolworths.hrportal.user.component.UserRole">
            <many-to-one name="role" class="au.com.woolworths.hrportal.user.role.Role" column="ROLE_ID" access="field"/>
            <property name="userIdCreate" column="CRE_USR_ID" />
            <property name="dateCreate" column="CRE_DTETM"   />
         </composite-element>
      </set>
...

When I try to cerate a new User containing Roles, I get the following exception:

Code:

org.springframework.orm.hibernate3.HibernateSystemException: could not get a field value by reflection getter of au.com.woolworths.hrportal.user.component.UserRole.role; nested exception is org.hibernate.PropertyAccessException: could not get a field value by reflection getter of au.com.woolworths.hrportal.user.component.UserRole.role

I've been struggling with this all day and I don't know why it doesn't work.
Any suggestions would be appreciated
Thanks
Alan

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

You've told hibernate to use the role field, but you've made it private. Either give it public, protected or default access, or else get ride of the access="field" attribute.

Also, your mapping tells hibernate to look for specific methods that you haven't written. You need setUserIdCreate and setDateCreate.

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Thanks tenwit for your reply.
I corrected what you said, but I think my main problem was I wasn't referencing the UserRole class at all in the java code. After reading P229 of HIA, I thought the CategoorizedItem class in the book's example was automatically being instantiated by Hibermate. Silly me

I changed the code to when creating a user to this:

Code:

UserRole userRole = new UserRole(role);
user.addUserRole(userRole);  // add to set of userRoles

then persist it.

I changed the constructor to just take a role, as I didn't require a parent element in the mapping.

When I run the unit test now, I see a delete in the SQL and then another insert. Can't explain this:

Code:

Hibernate: select USR_SEQ.nextval from dual
Hibernate: insert into usr (row_ver_no, usr_nm, pwd_val, enbld_usr_fl, fst_nm, last_nm, cre_dtetm, upd_dtetm, cre_usr_id, upd_usr_id, usr_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update usr set row_ver_no=?, pwd_val=?, enbld_usr_fl=?, fst_nm=?, last_nm=?, cre_dtetm=?, upd_dtetm=?, cre_usr_id=?, upd_usr_id=? where usr_id=? and row_ver_no=?
Hibernate: insert into usr_role_xref (usr_id, role_id, cre_usr_id, cre_dtetm, row_ver_no) values (?, ?, ?, ?, ?)
Hibernate: update usr set row_ver_no=?, pwd_val=?, enbld_usr_fl=?, fst_nm=?, last_nm=?, cre_dtetm=?, upd_dtetm=?, cre_usr_id=?, upd_usr_id=? where usr_id=? and row_ver_no=?
Hibernate: delete from usr_role_xref where usr_id=? and role_id=? and cre_usr_id=? and cre_dtetm=? and row_ver_no=?
Hibernate: insert into usr_role_xref (usr_id, role_id, cre_usr_id, cre_dtetm, row_ver_no) values (?, ?, ?, ?, ?)
2006-05-30 16:21:16,353 [3756    ] [main] WARN  org.hibernate.util.JDBCExceptionReporter (logExceptions) - SQL Error: 1, SQLState: 23000
2006-05-30 16:21:16,353 [3756    ] [main] ERROR org.hibernate.util.JDBCExceptionReporter (logExceptions) - ORA-00001: unique constraint (WW_SDC.PK_USR_ROLE_XREF) violated

2006-05-30 16:21:16,353 [3756    ] [main] WARN  org.hibernate.util.JDBCExceptionReporter (logExceptions) - SQL Error: 1, SQLState: 23000
2006-05-30 16:21:16,353 [3756    ] [main] ERROR org.hibernate.util.JDBCExceptionReporter (logExceptions) - ORA-00001: unique constraint (WW_SDC.PK_USR_ROLE_XREF) violated

2006-05-30 16:21:16,353 [3756    ] [main] ERROR org.hibernate.event.def.AbstractFlushingEventListener (performExecutions) - Could not synchronize database state with session

I'm only attempting to persist one user with one role
Any ideas?
Thanks

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Further to my last post, my Junit test was extending org.springframework.test.AbstractDependencyInjectionSpringContextTests. When I changed it to extend org.springframework.test.AbstractTransactionalDataSourceSpringContextTests, I just see one insert to the USR table and an insert to the USR_ROLE_XREF table- and the test passes. Can't expalin this one either

Alan

alankstewart · **Joined:** Sat Mar 04, 2006 1:07 am **Posts:** 27

Solved it - I hadn't implemented equals() and hashCode() in the UserRole class