Can Hibernate work with MySQL's "ON DUPLICATE KEY UPDATE" syntax?

Question

MySQL supports an "INSERT ... ON DUPLICATE KEY UPDATE ..." syntax that allows you to "blindly" insert into the database, and fall back to updating the existing record if one exists.

This is helpful when you want quick transaction isolation and the values you want to update to depend on values already in the database.

As a contrived example, let's say you want to count the number of times a story is viewed on a blog. One way to do that with this syntax might be:

 INSERT INTO story_count (id, view_count) VALUES (12345, 1)
 ON DUPLICATE KEY UPDATE set view_count = view_count + 1

This will be more efficient and more effective than starting a transaction, and handling the inevitable exceptions that occur when new stories hit the front page.

How can we do the same, or accomplish the same goal, with Hibernate?

First, Hibernate's HQL parser will throw an exception because it does not understand the database-specific keywords. In fact, HQL doesn't like any explicit inserts unless it's an "INSERT ... SELECT ....".

Second, Hibernate limits SQL to selects only. Hibernate will throw an exception if you attempt to call session.createSQLQuery("sql").executeUpdate().

Third, Hibernate's saveOrUpdate does not fit the bill in this case. Your tests will pass, but then you'll get production failures if you have more than one visitor per second.

Do I really have to subvert Hibernate?

Answer 1

Have you looked at the Hibernate @SQLInsert Annotation?

@Entity
@Table(name="story_count")
@SQLInsert( sql="INSERT INTO story_count(id, view_count) VALUES (?, ?)
 ON DUPLICATE KEY UPDATE set view_count = view_count + 1")
public class StoryCount