How to mimic upsert behavior using Hibernate?

Question

I'm writing an application that sync's entities from a third party datasource into our own schema, with a transformation/mapping step in between. I'm using Hibernate to represent and persist the entities in our own schema. A problem I'm running into is that I have a unique multi-column key on one of my tables. The behavior I would like to see is analogous to an upsert: when Hibernate goes to persist an entity and detects a unique constraint violation, it does an update instead. We are using MySQL, which provides an INSERT ... ON DUPLICATE KEY UPDATE syntax, but I'm not sure how or if Hibernate can be made to make use of it?

I suppose I could always try the insert, and if I catch an exception do an update, but that seems hacky and suboptimal. Any tips on a clean way to do this?

Answer 1

We are using MySQL, which provides an INSERT ... ON DUPLICATE KEY UPDATE syntax, but I'm not sure how or if Hibernate can be made to make use of it?

It looks like someone did it by overriding the sql-insert statement used by Hibernate for this entity. If you don't mind not being portable (and probably using a stored procedure), have a look.

I suppose I could always try the insert, and if I catch an exception do an update, but that seems hacky and suboptimal. Any tips on a clean way to do this?

Another option would be to:

1. perform a select on the unique key
2. if you find a record, update it
3. if you don't find a record, create it

But unless you lock the whole table(s) during the process, you can face some race condition in a multi-threaded and distributed environment and step #3 can potentially fail. Imagine two concurrent threads:

Thread 1:

• begin trans
• performs a select on a key
• no record found
• create a record
• commit

Thread 2:

• begin trans
• performs a select on the same key
• no record found
• create a record
• commit (FAIL! because thread 1 was faster and a record with the same unique key now exists)

So you would have to implement some kind of retry mechanism anyway (locking the whole table(s) is not a good option IMO).