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