-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Bidirectional many-to-many relationships not ordering
PostPosted: Fri Aug 05, 2005 6:53 pm 
Regular
Regular

Joined: Wed May 11, 2005 11:57 pm
Posts: 80
I have a many-to-many relationship between students and courses. The relationship from courses back to students is marked with inverse true. Both relationships are modeled as ordered lists, with a sort order field for each side of othe relationship. The problem is that when I add new records to the join table, only one of the sort order fields is set. This leads to all sorts of problems with duplicate sort order fields.

If you look at the red section of the generated SQL, you'll see that only three fields are being inserted into the join table: There should be a fourth field, which is 'sortOrderForcourses'.

Here is the list of MySQL data. Notice that the 'sortOrderForcourses' is mostly 0, even though there are 4 entries for the same course.

Code:
mysql> select * from joincoursestudent;
+-----------+------------+---------------------+----------------------+
| coursesId | studentsId | sortOrderForcourses | sortOrderForstudents |
+-----------+------------+---------------------+----------------------+
|         1 |          1 |                   0 |                    0 |
|         1 |          2 |                   0 |                    0 |
|         1 |          2 |                   0 |                    1 |
|         1 |          3 |                   1 |                    0 |
+-----------+------------+---------------------+----------------------+


Hibernate version:
3.05

Mapping documents:
Code:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.test">
   <class lazy="true" name="Student">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="firstName" type="java.lang.String"/>
      <property name="lastName" type="java.lang.String"/>
      <property name="dormitory" type="java.lang.String"/>
      <property name="enrollmentDate" type="java.util.Date"/>
      <list name="courses" lazy="true" table="JoinCourseStudent" >
         <key column="studentsId"/>
         <index column="sortOrderForstudents"/>
         <many-to-many column="coursesId" class="Course"/>
      </list>
   </class>
</hibernate-mapping>


Code:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.prosc.test">
   <class lazy="true" name="Course">
      <id name="id" type="java.lang.Long" unsaved-value="null">
         <generator class="native"/>
      </id>
      <property name="name" type="java.lang.String"/>
      <property name="courseNumber" type="java.lang.String"/>
      <property name="courseDescription" type="java.lang.String"/>
      <many-to-one name="instructor" column="instructorId"/>
      <list name="students" lazy="true" table="JoinCourseStudent" cascade="lock" inverse="true" >
         <key column="coursesId"/>
         <index column="sortOrderForcourses"/>
         <many-to-many column="studentsId" class="Student" />
      </list>
   </class>
</hibernate-mapping>




Code between sessionFactory.openSession() and session.close():
Code:
Transaction tx = session.beginTransaction();
Student theStudent = (Student)session.load( Student.class, new Long(1) );
Course theCourse = (Course)session.load( Course.class, new Long(1) );

theStudent.getCourses().add( theCourse );
theCourse.getStudents().add( theStudent );
tx.commit();


Full stack trace of any exception that occurs:
No exception occurs

Name and version of the database you are using:
MySQL 4.10

The generated SQL (show_sql=true):
Hibernate: select student0_.id as id0_, student0_.firstName as firstName2_0_, student0_.lastName as lastName2_0_, student0_.dormitory as dormitory2_0_, student0_.enrollmentDate as enrollme5_2_0_ from Student student0_ where student0_.id=?
Hibernate: select courses0_.studentsId as studentsId1_, courses0_.coursesId as coursesId1_, courses0_.sortOrderForstudents as sortOrde4_1_, course1_.id as id0_, course1_.name as name0_0_, course1_.courseNumber as courseNu3_0_0_, course1_.courseDescription as courseDe4_0_0_, course1_.instructorId as instruct5_0_0_ from JoinCourseStudent courses0_ inner join Course course1_ on courses0_.coursesId=course1_.id where courses0_.studentsId=?
Hibernate: select course0_.id as id0_, course0_.name as name0_0_, course0_.courseNumber as courseNu3_0_0_, course0_.courseDescription as courseDe4_0_0_, course0_.instructorId as instruct5_0_0_ from Course course0_ where course0_.id=?
Hibernate: insert into JoinCourseStudent (studentsId, sortOrderForstudents, coursesId) values (?, ?, ?)

Debug level Hibernate log excerpt:
N/A


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 06, 2005 4:29 pm 
Newbie

Joined: Fri Jan 07, 2005 5:46 pm
Posts: 19
Try using an order-by or sort attribute (see 7.3.1. Sorted collections).


Top
 Profile  
 
 Post subject:
PostPosted: Sat Aug 06, 2005 4:51 pm 
Newbie

Joined: Fri Jan 07, 2005 5:46 pm
Posts: 19
Correction: [from HiA p.229]
Indexed collections (lists and maps) can't be used, since Hibernate won't initialize or maintain the index column if inverse="true".


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 10, 2005 6:57 pm 
Regular
Regular

Joined: Wed May 11, 2005 11:57 pm
Posts: 80
a5sk4s wrote:
Correction: [from HiA p.229]
Indexed collections (lists and maps) can't be used, since Hibernate won't initialize or maintain the index column if inverse="true".


But if I set inverse to "false" on both ends, I get an exception because Hibernate uses the sort order fields as part of the primary key, and when the objects are saved, they only update the sort order for their 'half' of the relationship, so I get a not-null constraint exception.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 10, 2005 7:59 pm 
Newbie

Joined: Fri Jan 07, 2005 5:46 pm
Posts: 19
I don't have a solution. Explicitly modelling the association class with 2 many-to-one relations might work, but it's not ideal.

Maybe someone else will chime in and shed some light on it.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.