1 Reply Latest reply on Jun 28, 2011 12:07 PM by adamw

    Hibernate Annotatoin - How to Join Three Tables

    jemarjemarjemar
    jemarjemarjemar Jun 28, 2011 6:18 AM

       

      Hello Experts,

       

      Can you please help me how to join three tables with one common join table?

      I have USER, APPLICATION, and ROLE tables.

      And I want thier IDs to be joined in a table named USER_APP_ROLE(user.ID, application.ID, role.ID).

       

      When I remove either Application or Role tables in Join Many to Many my code is working.

       

      I have done the following codes:

       

      User.java

      @ManyToMany (targetEntity=Role.class)

      @JoinTable(name="USER_APPLICATION_ROLE",

      joinColumns=@JoinColumn(name="USER_ID"),

      inverseJoinColumns=@JoinColumn(name="ROLE_ID"))

      private Collection<Role> roles;

         

      @ManyToMany (targetEntity=Application.class)

      @JoinTable(name="USER_APPLICATION_ROLE",

      joinColumns=@JoinColumn(name="USER_ID"),

      inverseJoinColumns=@JoinColumn(name="APPLICATION_ID"))

      private Collection<Application> applications;

       

      Role.java

      @ManyToMany(mappedBy="roles", targetEntity=User.class)

      private Collection<User> users = new ArrayList<User>();

       

      Application.java

      @ManyToMany(mappedBy="applications", targetEntity=User.class)

      private Collection<User> users = new ArrayList<User>();

       

      When I tried to run the following test:

       

              user.getRoles().add(role1);

              user.getRoles().add(role2);

             

              role1.getUsers().add(user);

              role1.getUsers().add(user);

             

              role2.getUsers().add(user);

              role2.getUsers().add(user);

             

              user.getApplications().add(app1);

              user.getApplications().add(app2);

             

              app1.getUsers().add(user);

              app2.getUsers().add(user);

       

                ......

       

                session.beginTransaction();

                session.save(user);

                session.save(role1);

                session.save(role2);

                session.save(app1);

                session.save(app2);

       

      I get the following error:

       

      Hibernate: select seq_cm_user.nextval from dual

      Hibernate: select seq_role.nextval from dual

      Hibernate: select seq_role.nextval from dual

      Hibernate: select seq_application.nextval from dual

      Hibernate: select seq_application.nextval from dual

      Hibernate: insert into CM_USER (EMAIL, FIRST_NAME, LAST_NAME, MIDDLE_NAME, USERNAME, ID) values (?, ?, ?, ?, ?, ?)

      Hibernate: insert into CM_ROLE (DESCRIPTION, ID) values (?, ?)

      Hibernate: insert into CM_ROLE (DESCRIPTION, ID) values (?, ?)

      Hibernate: insert into CM_APPLICATION (CODE, DESCRIPTION, ID) values (?, ?, ?)

      Hibernate: insert into CM_APPLICATION (CODE, DESCRIPTION, ID) values (?, ?, ?)

      Hibernate: insert into USER_APPLICATION_ROLE (USER_ID, APPLICATION_ID) values (?, ?)

      Hibernate: insert into USER_APPLICATION_ROLE (USER_ID, APPLICATION_ID) values (?, ?)

      Exception in thread "main" org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)

          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

          at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)

          at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)

          at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)

          at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)

          at org.hibernate.persister.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:1179)

          at org.hibernate.action.CollectionRecreateAction.execute(CollectionRecreateAction.java:58)

          at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)

          at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)

          at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:188)

          at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)

          at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)

          at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)

          at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)

          at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)

          at com.hp.gdas.capman.HibernateTest.main(HibernateTest.java:73)

      Caused by: java.sql.BatchUpdateException: ORA-01400: cannot insert NULL into ("SYSTEM"."USER_APPLICATION_ROLE"."ROLE_ID")

       

          at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)

          at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10657)

          at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

          at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)

          ... 14 more

       

      Thank you very much!