Hibernate @SQLInsert and On Duplicate Key

Question

In Hibernate, you can use @SQLInsert to define a custom insert query. Hibernate is using prepared statements, so you just need to provide the ? as placeholder, like this: INSERT INTO table (colA, colB) VALUES (?,?)

On a duplicate key for colA, I want colB to be updated with the new value, so i tried INSERT INTO table (colA, colB) VALUES (?,?) ON DUPLICATE KEY UPDATE colB = ? - This however throws the error that no third parameter is given. (No value specified for parameter 3)

How is the correct way to write this Query for hibernate? Any non-data-dependent update like INSERT INTO table (colA, colB) VALUES (?,?) ON DUPLICATE KEY UPDATE colB = colB +1 is working - but I need to set the actual value of colB that has been passed with the insert-call.

dognose · Accepted Answer · 2015-02-18 14:31:20Z

1

The correct answer would be to use the following statement:

INSERT INTO table (colA, colB) VALUES (?,?) ON DUPLICATE KEY 
UPDATE colB = VALUES(colB);

However then the problem appears that hibernate did not receive an auto-increment value back, once the update statement has been performed.

I found the following blog post (http://www.jroller.com/mmatthews/entry/getting_hibernate_and_mysql_s) and modifed the query to the following:

INSERT INTO table (colA, colB) VALUES (?,?) ON DUPLICATE KEY 
UPDATE colB = VALUES(colB), id = LAST_INSERT_ID(id);

which finally works.

An unsolvable Problem with this approach is, that the insertion of two entities that are equal within the same transaction does not work. Even if the second insertion would cause the correct update, the em would end up with 2 entity-instances representing the same database row - which is not allowed.

To solve this, one just needs to ensure that you don't insert 2 entities which are rendered equal due to their constraints. (I used the same logic for equals/hashcode as the composite-unique-key constraint, so i am able to eliminate such duplicates when performing batch-inserts)

answered Feb 18, 2015 at 14:31

dognose

21k9 gold badges67 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

burm87 Over a year ago

this is not working for me unfortunately, if you want to try to help me this is the link to my question, thanks. stackoverflow.com/questions/57593265/…

Collectives™ on Stack Overflow

Hibernate @SQLInsert and On Duplicate Key

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related