java Hibernate 可以使用 MySQL 的“ON DUPLICATE KEY UPDATE”语法吗？

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

提问by mtpettyp

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.

MySQL 支持“ INSERT ... ON DUPLICATE KEY UPDATE ...”语法，允许您“盲目”插入到数据库中，如果存在，则回退到更新现有记录。

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?

我们如何使用 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 ....".

首先，Hibernate 的 HQL 解析器会抛出异常，因为它不理解特定于数据库的关键字。事实上，HQL 不喜欢任何显式插入，除非它是一个“ INSERT ... SELECT ....”。

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

其次，Hibernate 将 SQL 限制为仅选择。如果您尝试调用 Hibernate 将抛出异常session.createSQLQuery("sql").executeUpdate()。

Third, Hibernate's saveOrUpdatedoes 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.

第三，saveOrUpdate在这种情况下，Hibernate不符合要求。您的测试会通过，但是如果每秒有一个以上的访问者，那么您将遇到生产故障。

Do I really have to subvert Hibernate?

我真的要颠覆 Hibernate 吗？

回答by mtpettyp

Have you looked at the Hibernate @SQLInsertAnnotation?

你看过 Hibernate @SQLInsertAnnotation 吗？

@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

回答by gary schulte

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 andwe 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.

这是一个老问题，但我遇到了类似的问题，并认为我会添加到这个主题中。我需要将日志添加到现有的 StatelessSession 审计日志编写器。现有的实现使用 StatelessSession，因为标准会话实现的缓存行为是不必要的开销，我们不希望我们的休眠侦听器为审计日志写入而触发。这种实现是为了在没有交互的情况下实现尽可能高的写入性能。

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.

然而，新的日志类型需要使用插入-其他-更新类型的行为，我们打算用事务时间更新现有的日志条目作为“标记”类型的行为。在 StatelessSession 中，不提供 saveOrUpdate()，因此我们需要手动实现 insert-else-update。

In light of these requirements:

鉴于这些要求：

You canuse 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.:

您可以通过 hibernate 持久对象的自定义 sql-insert 使用 mysql“插入...重复密钥更新”行为。您可以通过注释（如上面的答案）或通过 sql-insert 实体定义自定义 sql-insert 子句 hibernate xml 映射，例如：

<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 issuefor more detail on that particular feature.

原始海报专门询问了 MySQL。当我使用 mysql 实现 insert-else-update 行为时，当执行 sql 的“更新路径”时出现异常。具体来说，mysql 在仅更新 1 行时报告 2 行已更改（表面上是因为删除现有行并插入新行）。有关该特定功能的更多详细信息，请参阅此问题。

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.

因此，当更新返回 2 倍影响休眠的行数时，休眠将抛出 BatchedTooManyRowsAffectedException，将回滚事务并传播异常。即使您要捕获异常并对其进行处理，到那时该事务也已回滚。

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.

经过一番挖掘，我发现这是 hibernate 使用的实体持久器的问题。在我的例子中，hibernate 使用的是 SingleTableEntityPersister，它定义了一个期望，即更新的行数应该与批处理操作中定义的行数相匹配。

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:

使此行为起作用所需的最后调整是定义自定义持久器（如上面的 xml 映射所示）。在这种情况下，我们所要做的就是扩展 SingleTableEntityPersister 并“覆盖”插入期望。例如，我只是将这个静态类添加到持久对象上，并将其定义为休眠映射中的自定义持久器：

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.

挖掘休眠代码花了很长时间才找到这个 - 我无法在网上找到任何带有解决方案的主题。

回答by the_owl

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:

如果您使用的是 Grails，我发现这个解决方案不需要将您的域类移动到 JAVA 世界并使用 @SQLInsert 注释：

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.

1. 创建自定义休眠配置
2. 覆盖 PersistentClass 映射
3. 使用 ON DUPLICATE KEY 将您的自定义 INSERT sql 添加到您想要的持久类。

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:

例如，如果您有一个名为 Person 的域对象，并且您希望 INSERTS 为 INSERT ON DUPLICATE KEY UPDATE，您将创建如下配置：

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.groovy 中的 Hibernate 配置：

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.

请注意使用 LAST_INSERT_ID 时要小心，因为如果执行 UPDATE 而不是 INSERT，则不会正确设置，除非您在语句中明确设置它，例如 id=LAST_INSERT_ID(id)。我还没有检查 GORM 从哪里获取 ID，但我假设它在某个地方使用 LAST_INSERT_ID。

Hope this helps.

希望这可以帮助。