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?

Saltcellar

See also this investigation: #56454190 – Establishmentarian

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 view_count = view_count + 1" )
public class StoryCount

Culpa

What if you want to use an auto increment id field (say in mysql)? I'm getting weird errors about the database not returning an ID – Permissive

what would a on duplicate key update some_value = ? look like, where ? is the actual new value of some_value that should be updated? – Oration

Was this solution actually tested? I tried it and ran into difficulties due to the id field being out of sync in a referencing object, leading to foreign-key constraint error when the other object is saved. I guess this can work in specific scenarios. – Pompidou

Note that the column ordering used by Hibernate is not the same as order in which they are defined in Entity class. Here is how to account for this. – Stewart

What if I don't want to increment the view_count by 1, but by another number passed in as parameter? – Cleveland

Answer 2

This is an old question, but I was having a similar issue and figured I would add to this topic. I needed to add a log to an existing StatelessSession audit log writer. The existing implementation was using a StatelessSession because the caching behavior of the standard session implementation was unnecessary overhead and we did not want our hibernate listeners to fire for audit log writing. This implementation was about achieving as high a write performance as possible with no interactions.

However, the new log type needed to use an insert-else-update type of behavior, where we intend to update existing log entries with a transaction time as a "flagging" type of behavior. In a StatelessSession, saveOrUpdate() is not offered so we needed to implement the insert-else-update manually.

In light of these requirements:

You can use the mysql "insert ... on duplicate key update" behavior via a custom sql-insert for the hibernate persistent object. You can define the custom sql-insert clause either via annotation (as in the above answer) or via a sql-insert entity a hibernate xml mapping, e.g.:

<class name="SearchAuditLog" table="search_audit_log" persister="com.marin.msdb.vo.SearchAuditLog$UpsertEntityPersister">

    <composite-id name="LogKey" class="SearchAuditLog$LogKey">
        <key-property
            name="clientId"
            column="client_id"
            type="long"
        />
        <key-property
            name="objectType"
            column="object_type"
            type="int"
        />
        <key-property
            name="objectId"
            column="object_id"
        />
    </composite-id>
    <property
        name="transactionTime"
        column="transaction_time"
        type="timestamp"
        not-null="true"
    />
    <!--  the ordering of the properties is intentional and explicit in the upsert sql below  -->
    <sql-insert><![CDATA[
        insert into search_audit_log (transaction_time, client_id, object_type, object_id)
        values (?,?,?,?) ON DUPLICATE KEY UPDATE transaction_time=now()
        ]]>
    </sql-insert>

The original poster asks about MySQL specifically. When I implemented the insert-else-update behavior with mysql I was getting exceptions when the 'update path' of the sql exectued. Specifically, mysql was reporting 2 rows were changed when only 1 row was updated (ostensibly because the existing row is delete and the new row is inserted). See this issue for more detail on that particular feature.

So when the update returned 2x the number of rows affected to hibernate, hibernate was throwing a BatchedTooManyRowsAffectedException, would roll back the transaction, and propogate the exception. Even if you were to catch the exception and handle it, the transaction had already been rolled back by that point.

After some digging I found that this was an issue with the entity persister that hibernate was using. In my case hibernate was using SingleTableEntityPersister, which defines an Expectation that the number of rows updated should match the number of rows defined in the batch operation.

The final tweak necessary to get this behavior to work was to define a custom persister (as shown in the above xml mapping). In this instance all we had to do was extend the SingleTableEntityPersister and 'override' the insert Expectation. E.g. I just tacked this static class onto the persistence object and define it as the custom persister in the hibernate mapping:

public static class UpsertEntityPersister extends SingleTableEntityPersister {

    public UpsertEntityPersister(PersistentClass arg0, EntityRegionAccessStrategy arg1, SessionFactoryImplementor arg2, Mapping arg3) throws HibernateException {
        super(arg0, arg1, arg2, arg3);
        this.insertResultCheckStyles[0] = ExecuteUpdateResultCheckStyle.NONE;
    }

}

It took quite a while digging through hibernate code to find this - I wasn't able to find any topics on the net with a solution to this.

Repression

Can't you accomplish the same thing by specifying the "check" attribute? See docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/… Eg: <sql-insert check="none">... or @SQLInsert(sql="...", check="none") – Saccharide

Correction of above, annotation should look like: @SQLInsert(sql="...", check=ResultCheckStyle.NONE) – Saccharide

Answer 3

If you are using Grails, I found this solution which did not require moving your Domain class into the JAVA world and using @SQLInsert annotations:

1. Create a custom Hibernate Configuration
2. Override the PersistentClass Map
3. Add your custom INSERT sql to the Persistent Classes you want using the ON DUPLICATE KEY.

For example, if you have a Domain object called Person and you want to INSERTS to be INSERT ON DUPLICATE KEY UPDATE you would create a configuration like so:

public class MyCustomConfiguration extends GrailsAnnotationConfiguration {

    public MyCustomConfiguration() {
        super();

        classes = new HashMap<String, PersistentClass>() {
            @Override
            public PersistentClass put(String key, PersistentClass value) {
                if (Person.class.getName().equalsIgnoreCase(key)) {
                    value.setCustomSQLInsert("insert into person (version, created_by_id, date_created, last_updated, name) values (?, ?, ?, ?, ?) on duplicate key update id=LAST_INSERT_ID(id)", true, ExecuteUpdateResultCheckStyle.COUNT);
                }
                return super.put(key, value);
            }
        };
    }

and add this as your Hibernate Configuration in DataSource.groovy:

dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
    configClass = 'MyCustomConfiguration'
}

Just a note to be careful using LAST_INSERT_ID, as this will NOT be set correctly if the UPDATE is executed instead of the INSERT unless you set it explicitly in the statement, e.g. id=LAST_INSERT_ID(id). I haven't checked where GORM gets the ID from, but I'm assuming somewhere it is using LAST_INSERT_ID.

Hope this helps.

Counsel