Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1.3

Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.hibernate.test.studentcourse" default-lazy="false">

<class name="Student" table="student">
<id name="id" column="student_id" unsaved-value="null">
<generator class="sequence">
<param name="sequence">student_seq</param>
</generator>
</id>
<property name="studentName" column="student_name"/>
</class>

<class name="Course" table="course">
<id name="id" column="course_id" unsaved-value="null">
<generator class="sequence">
<param name="sequence">course_seq</param>
</generator>
</id>
<property name="courseName" column="course_name"/>
<list name="students" table="student_course" inverse="false" cascade="none">
<key column="course_id"/>
<list-index column="list_index"/>
<many-to-many column="student_id" class="Student"/>
</list>
</class>

</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

Course course = (Course) s.load(Course.class, new Long(100));

// Remove first student in List
course.getStudents().remove(0);

s.saveOrUpdate(course);
s.flush();



Full stack trace of any exception that occurs:
org.hibernate.exception.ConstraintViolationException: could not update collection rows: [org.hibernate.test.studentcourse.Course.students#100]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.collection.BasicCollectionPersister.doUpdateRows(BasicCollectionPersister.java:222)
at org.hibernate.persister.collection.AbstractCollectionPersister.updateRows(AbstractCollectionPersister.java:1337)
at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:55)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
at org.hibernate.test.studentcourse.StudentCourseTest.testBasicOperation(StudentCourseTest.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at org.hibernate.test.TestCase.runTest(TestCase.java:150)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 1;TITO.STUDENT_COURSE
at com.ibm.db2.jcc.a.co.d(co.java:1113)
at com.ibm.db2.jcc.b.bd.k(bd.java:314)
at com.ibm.db2.jcc.b.bd.a(bd.java:63)
at com.ibm.db2.jcc.b.r.a(r.java:64)
at com.ibm.db2.jcc.b.bq.c(bq.java:219)
at com.ibm.db2.jcc.a.cp.P(cp.java:1299)
at com.ibm.db2.jcc.a.cp.d(cp.java:1803)
at com.ibm.db2.jcc.a.cp.L(cp.java:348)
at com.ibm.db2.jcc.a.cp.executeUpdate(cp.java:331)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
at org.hibernate.persister.collection.BasicCollectionPersister.doUpdateRows(BasicCollectionPersister.java:209)
... 23 more



Name and version of the database you are using: DB2 version 8

The generated SQL (show_sql=true):

delete
from
student_course
where
course_id=?
and list_index=?

update
student_course
set
student_id=?
where
course_id=?
and list_index=?



Debug level Hibernate log excerpt:

Hibernate:
delete
from
student_course
where
course_id=?
and list_index=?
[2969ms,DEBUG,LongType,main] binding '100' to parameter: 1
[2984ms,DEBUG,IntegerType,main] binding '2' to parameter: 2
Hibernate:
update
student_course
set
student_id=?
where
course_id=?
and list_index=?
[2984ms,DEBUG,LongType,main] binding '2' to parameter: 1
[2984ms,DEBUG,LongType,main] binding '100' to parameter: 2
[2984ms,DEBUG,IntegerType,main] binding '0' to parameter: 3




Database schema:

CREATE TABLE COURSE(
COURSE_ID BIGINT NOT NULL,
COURSE_NAME VARCHAR(30) NOT NULL,
CONSTRAINT PK_COURSE PRIMARY KEY (COURSE_ID)
);

CREATE TABLE STUDENT(
STUDENT_ID BIGINT NOT NULL,
STUDENT_NAME VARCHAR(20) NOT NULL,
CONSTRAINT PK_STUDENT PRIMARY KEY (STUDENT_ID)
);

CREATE TABLE STUDENT_COURSE(
STUDENT_ID BIGINT NOT NULL,
COURSE_ID BIGINT NOT NULL,
LIST_INDEX INTEGER NOT NULL,
CONSTRAINT PK_SC PRIMARY KEY (STUDENT_ID, COURSE_ID)
);

ALTER TABLE STUDENT_COURSE ADD CONSTRAINT FK_SC_COURSE
FOREIGN KEY (COURSE_ID)
REFERENCES COURSE(COURSE_ID);

ALTER TABLE STUDENT_COURSE ADD CONSTRAINT FK_SC_STUDENT
FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENT(STUDENT_ID);



Test Data:

INSERT INTO COURSE(COURSE_ID, COURSE_NAME) VALUES (100, 'Math');
INSERT INTO COURSE(COURSE_ID, COURSE_NAME) VALUES (200, 'English');
INSERT INTO COURSE(COURSE_ID, COURSE_NAME) VALUES (300, 'Physics');

INSERT INTO STUDENT(STUDENT_ID, STUDENT_NAME) VALUES (1, 'Tom');
INSERT INTO STUDENT(STUDENT_ID, STUDENT_NAME) VALUES (2, 'Dick');
INSERT INTO STUDENT(STUDENT_ID, STUDENT_NAME) VALUES (3, 'Harry');

INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (100, 1, 0);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (100, 2, 1);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (100, 3, 2);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (200, 1, 0);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (200, 2, 1);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (300, 1, 0);
INSERT INTO STUDENT_COURSE(COURSE_ID, STUDENT_ID, LIST_INDEX) VALUES (300, 3, 1);



Entity classes:

public class Course
{
private Long id;
private String courseName;
private List students = new ArrayList();

// other code (get/set, etc)...
}

public class Student
{
private Long id;
private String studentName;

// other code (get/set, etc)...
}


--------------------------------------------------------------------------------

I caught between rock and a hard one. Rock is business layer developer and hard one is DBA. I caught in middle because I do persistence code and Hibernate mappings.

As you see from database schema above, we are having classic many-to-many between course and student. Course have many students. Student have many courses. You are knowing this one already for sure.

We have associative table STUDENT_COURSE. DBA defined PK on that table as COURSE_ID + STUDENT_ID. Of course this is correct because student cannot be in same course twice!

Now, as you see, business layer developer make a List to hold a Course's Students. This is because the ORDER of a Course's Students is significant in our problem domain. Business layer developer likes to use List for this because it has nice transparent handling of ordering. For example, he doesn't have to create artificial "sortOrder" property in objects. He just adds, removes and shuffles items in List and Hibernate takes care of setting the LIST_INDEX value in the database. So, is no hassle for him. He is liking this feature very much!

Unfortunately, as you see in sample test code above, when you try to remove a Student from a Course, and the Student is not the very last element in List, you violate the primary key constraint. This is because, IMHO, Hibernate uses strange SQL to update the List. Essentially, here is what Hibernate try to do:

1) delete from student_course where course_id=100 and list_index=2;
2) update student_course set student_id=2 where course_id=100 and list_index=0;
3) update student_course set student_id=3 where course_id=100 and list_index=1;

If the primary key were all three columns (COURSE_ID + STUDENT_ID + LIST_INDEX), net effect of these 3 statements would be correct. However, DBA refuses to modify the PK from current COURSE_ID + STUDENT_ID. This is because having all three columns in PK could lead to bad data because a student could be put in the same course multiple times (by either bad SQL script or another application).

DBA says that business layer developer should use Set instead. But that is not ideal either because you don't get transparent handling of order. So then business layer developer is not happy. I think both guys hate me now!

When I look at those 3 SQL statements, I think "why they doing this"? That SQL seems to assume that PK is COURSE_ID + LIST_INDEX instead of COURSE_ID + STUDENT_ID! I think is best if SQL does this instead:

1) delete from student_course where course_id=100 and student_id=1;
2) update student_course set list_index=0 where course_id=100 and student_id=2;
3) update student_course set list_index=1 where course_id=100 and student_id=3;

To me, these new SQL statements are easier to understand because they use the real PK. More importantly, they don't violate the PK constraint when they run.

Is there a way to tell Hibernate to use the real PK for updating this table? Or is there another solution that lets me keep List mapping and existing PK constraint?

The SQL statements that Hibernate generates do look a bit bizarre to me too. I wonder why they are that way? From what I can tell there is no performance gain from doing it that way -- in your example, there are three SQL statements whether the List is saved with Hibernate's generated statements or your recommended statements.

Actually, your statements would probably run faster because they would use the primary key index (of course, I guess your DBA could create an index on COURSE_ID + LIST_INDEX but you generally would get the primary key index for free when you create the table).

The other thing about your recommended statements is that they are easy to understand. I mean, if you tried to pseudocode the operation, that's the way I'd do it. That is:

"Delete the record corresponding to the item you removed. Then, update the list_index of the remaining records to correspond to their new index in the List."

Makes sense to me. Call me crazy...

Thanks you Cyboc for comment.



No, I think you do make sense.

Anyway, I was thinking, does anyone know if there some kind of "hook" for me to create workaround for this behaviour? For example, is there interface I can implement? Or perhaps I can extend PersistentList and override somethings in that one? I don't mind doing the work! I'm not lazy or looking for someone to do it for me. Just, please, give me hint for which class or interface to look at.

What you thinking about this one?

I think you're on the right track with that idea. I've actually been working quite a bit lately with the new UserCollectionType feature. I think you might be able to implement that interface and use the collection-type attribute of the list element to hook it up to your List that stores the students. For example, your modified list mapping might look like this:



I'm sure your next question is: How do you implement UserCollectionType? Unfortunately, it does not seem to be well documented...yet. However, just download the Hibernate source code and look at the classes in the "org.hibernate.test.usercollection" package in the test code tree (that's what I did). Those classes provide a pretty good example.

The only problem is, I don't think implementing UserCollectionType is enough. You will also need to change the behaviour of the underlying PersistentCollection. Perhaps you could extend PersistentList and put your new behaviour there (see the class PersistentMyList for an example). You will probably need to trace the code for a List update to see where the SQL gets created and see if you can override some method to change the SQL. That could get pretty dicey though, and might involve several classes. Good luck...

Thanks you for tips. I am now tracing the code. From what I see, I thinking it's maybe hard to change SQL generation. But I did see this interesting code in CollectionUpdateAction when tracing:



You see this line?: collection.needsRecreate(persister)

I thinking to myself if I extend PersistentList like you suggesting, maybe I can override needsRecreate() to return true (maybe make it always returns true?). Then I think this will automatically do like "one shot delete" pattern, except nice because transparent.

What you thinking about this one? Could this one be working?

I thinking this one could be working. ;-)

Why don't you try it and see?

Okay, I tried it but still some problems. Let me explain.

Okay, first I look at the classes in the "org.hibernate.test.usercollection" package in the test code tree as you suggest. Following that example, I make these two classes:





Then I change list mapping to this one:



I think "great", this one will be working. But when I try, I get this error:



I know what you thinking but I already put this one in hibernate-mapping element in xml file:



Maybe something else wrong with this one? What you thinking?

Try this:

collection-type="org.hibernate.test.studentcourse.ForceRecreateListUserCollectionType"

For whatever reason (perhaps it's a bug...), the collection-type attribute seems to ignore the package attribute of the hibernate-mapping element.

BTW, to the Hibernate guys, can you confirm if that is a bug?

Thanks you, that works!

And you know what? The UserTypeCollection worked too! I don't get primary key violation when I remove item from List and save List back to database. Here is SQL that get generated:



So, as you see, it sort of simulate "one shot delete" but automatically! This is nice workaround. However, I wish Hibernate would fix this issue and generate better SQL (see above message -- it should use real PK for locating list elements in associative tables) in the first place so that I don't have to use a workaround. But anyway, I think this workaround is working for me.

Hi,

I tried your approach with composite-elements and it works great!! I can see the delete clean up the existing rows and then the subsequent inserts happen.

This approach does not work however if I change the composite-element to a one-to-many association.

As an eg:

This works:

<list name="prefferedCommModes" table="TMP_WW_CUST_PREF_COMMN_MODE" cascade="all-delete-orphan" lazy="true"
collection-type="com.engg.util.orm.hibernate.ForceRecreateListUserCollectionType" >
<key column="CUSTOMER_ID" not-null="true" />
<list-index column="PRIORITY"/>
<composite-element class="CustomerPreferredCommunicationMode">
<property name="communicationModeId" column="COMMUNICATION_MODE_CD" >
<type name="com.engg.util.orm.hibernate.KeyValueCodeType">
<param name="identifier">COMMUNICATION_MODE</param>
</type>
</property>
</composite-element>
</list>


This does not work:

<list name="prefferedCommModes" cascade="all" lazy="true" inverse="false" collection-type="com.engg.util.orm.hibernate.ForceRecreateListUserCollectionType">
<key column="CUSTOMER_ID" not-null="true" />
<list-index column="PRIORITY"/>
<one-to-many class="CustomerPreferredCommunicationMode" />
</list>


Any ideas/pointers?

Yikes, it's been so long and I can't remember this. Sorry, I have no suggestions for one-to-many at this time. But I'm glad the many-to-many worked for you.

I put some debug statements in the ForceRecreateListUserCollectionType instantiate and in the ForceRecreatePersistentList needsRecreate and I see the debug statments getting printed in the one-to-many scenario.

So Hibernate is using these classes, but unfortunately, I'm pretty certain i'm missing something by which the DELETE does not get fired.

Anyway, composite-elements are not too bad either, so I'll go with those for now.

Thanks!!

I just wanted to find out if with Hibernate 3.2.0.ga there is now a solution to the above problem that does not involve using a custom collection type? Thanks.

I've been bitten by this same bug. Did anyone file an issue in JIRA?