Org.hibernate.exception.ConstraintViolationException: Failed to perform JDBC batch update [due to unique constraint]
Error: org.hibernate.exception.ConstraintViolationException: JDBC batch update failed
java.sql.BatchUpdateException: Duplicate entry '24 -0-es_reservation_detail 'for key' questionId_referenceId_referenceType '
I'm going to save the reservation object. This reservation object contains a collection of reserveaitonDetails objects, and each reservation detail object contains a collection of questionAnswers Objects.
The main problem is the unique constraint on the question. Answer table
Unqiue Constraint: question_id, reference_id, reference_type.
When the system saves a reservation: The system first saves the reservation, then collects the reservation data, and then all the answers to the questions using 0 (reference_id). By adding a question with a reference ID of 0, the system throws an exception because the 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 keep the collection of booking data
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 the system executes point (5) script. The system will be through the exclusion of violation of restrictions.
Is there a way that hibernate update the reference_id in question_answer (table) just before making the next insert request.
Script 8 should work after 4th script
Script 9 should run after the 5th script
source to share
The Hiberate option cascade
inserts foreign key child objects if you mapped the parent class correctly when mapping with children. If you haven't mapped the parent class in the child mapping, but how Integer
reference_id
, then the foreign key will be updated using a separate update request:
update question_answer set reference_id = ? where question_answer_id = ?
If you cannot specify the Parent object in the Child mapping, you can use a workaround. You can set reference_id
as null
allowed (in a table) and you need to set referenceId=null;
in a child. Then the Hibernate cascade will inject the child objects into the null
foreign key and the next update query to set the referenceId
generated foreign key.
Note: Values null
in a unique column are not considered duplicate values if they appear more than once.
source to share
Most likely, you did not define in the QuestionAnswerDTO object a reference to the main entity, which is ReservationDetails. You have to configure @MAnyToOne in QuestionAnswerDTO which has @JoinColumn "reference_id" something like this
@ManyToOne
@JoinColumn(name="persistence_id")
private ReservationDetail reservationDetail;
and then set the ReservationDetail property to the actual ReservationDetail to which it belongs.
The JPA will then know that it needs to populate the reference_id with a reference to REservationDetails. Otherwise, it treats it like any other field
source to share