Hibernate 可以与 MySQL 的“ON DUPLICATE KEY UPDATE"一起使用吗?句法? [英] Can Hibernate work with MySQL's "ON DUPLICATE KEY UPDATE" syntax?

问题描述

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

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.

我们如何使用 Hibernate 做同样的事情，或者完成同样的目标?

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

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

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 将 SQL 限制为仅选择.如果您尝试调用 session.createSQLQuery("sql").executeUpdate()，Hibernate 将抛出异常.

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

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

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.

我真的要颠覆 Hibernate 吗?

Do I really have to subvert Hibernate?

推荐答案

你看过 Hibernate @SQLInsert 注解?

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

这篇关于Hibernate 可以与 MySQL 的“ON DUPLICATE KEY UPDATE"一起使用吗?句法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！