org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update [Due to Unique Constraint]

Question

Error: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

java.sql.BatchUpdateException: Duplicate entry '24-0-es_reservation_detail' for key 'questionId_referenceId_referenceType'

I am going to save reservation object. This reservation object contains collection of reservaitonDetails objects and each reservation detail object contains collection of questionAnswers Objects.

The main problem is unique constraint on questionAnswer table

Unqiue Constraint: question_id, reference_id, reference_type.

When system save reservation object: system first save reservation, then collection of reservation details and then all its question answers with 0 (reference_id). While adding question with 0 reference id, system throws exception because unique constraint is violated.

ReservationDetail.hbm.xml

.............
.............
<set name="questionAnswers" lazy="true" cascade="all-delete-orphan" where="reference_type = 'es_reservation_detail'">
        <key column="reference_id"/>
        <one-to-many class=".....QuestionAnswerDTO" />
    </set>
.............
.............

Example:

If we save collection on reservation details

1. insert into reservation......... (reservation id = 1)

2. insert into reservation_detail.......   (reservation detail id = 1)
3. insert into reservation_detail.......   (reservation detail id = 2)

4. insert into question_answer..... (referece_type='RD' referece_id=0, question_id =1) - For Reservation Detail id =  1
5. insert into question_answer..... (referece_type='RD' referece_id=0, question_id =1) - For Reservation Detail id =  1

6. update reservation_detail set reservation_id = ? where reservation_detail_id = ? (reservation_id = 1, reservation_detail_id = 1)
7. update reservation_detail set reservation_id = ? where reservation_detail_id = ? (reservation_id = 1, reservation_detail_id = 2)

8. update question_answer set reference_id = ? where question_answer_id = ? (reference_id = 1 and question_answer_id =1)
9. update question_answer set reference_id = ? where question_answer_id = ? (reference_id = 2 and question_answer_id =2)

When system will execute point (5) script. System will through constraint violation exception.

Is there any way that hibernate update reference_id in question_answer (table) immediately before creating next insert query.

Script 8 must run after 4th script

Script 9 must run after 5th script

Answer 1

Hiberate cascade option insert child objects with foreign key if you have correctly mapped Parent Class in child mapping. If you have not mapped Parent Class in child mapping but as Integer reference_id, then foreign key will be updated using separate update query:

 update question_answer set reference_id = ? where question_answer_id = ?

If you cannot specify Parent class object in Child mapping, then you can use a workaround. You can set reference_id as null allowed (on table) and you need to set referenceId=null; in child object. Then Hibernate cascade will insert child objects will null foreign key, and next call update query to set referenceId generated foreign key.

Note: null values in unique column are not considered as duplicate values if appeared more than one time.

Answer 2

Most probably you did not define In QuestionAnswerDTO entity a reference back to master entity, which is ReservationDetails. You should configure a @MAnyToOne in QuestionAnswerDTO, that has as @JoinColumn "reference_id" something like that

@ManyToOne
 @JoinColumn(name="persistence_id")
 private ReservationDetail reservationDetail;

and then set reservationDetail property to the actual ReservationDetail object to which it belongs.

Then JPA will know that it needs to populate reference_id with a reference to REservationDetails. Otherwise it considers it as any other field