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

问题描述

MySQL支持 INSERT ... ON DUPLICATE KEY UPDATE ... 语法，它允许您盲目地插入到数据库中，并回退到更新当存在快速事务隔离并且您想要更新的值取决于数据库中已存在的值时，这会很有帮助。

>

作为一个人为的例子，假设您想要统计博客上查看故事的次数。使用这种语法做到这一点的一种方式可能是：

pre $ INSERT INTO story_count（id，view_count）VALUES（12345，1）
ON DUPLICATE KEY UPDATE set view_count = view_count + 1

这会更有效率和更有效而不是开始一个交易，并处理新的故事发生在首页时发生的不可避免的异常。

我们如何做到这一点，或完成相同的目标，Hibernate ？首先，Hibernate的HQL解析器会抛出一个异常，因为它不理解数据库特定的关键字。事实上，除非是 INSERT ... SELECT .... ，否则HQL不喜欢任何显式插入。第二，Hibernate限制SQL只能选择。如果您尝试调用 session.createSQLQuery（sql）。executeUpdate（）。

第三，在这种情况下，Hibernate的 saveOrUpdate 不适合账单。你的测试会通过，但如果你每秒有超过一个访问者，你就会失败。

我真的必须颠覆Hibernate吗？

解决方案

您是否看过Hibernate @ SQLInsert Annotation？

 @SQLInsert（sql =INSERT INTO story_count（id，view_count） ON DUPLICATE KEY UPDATE view_count = view_count + 1）
 public class StoryCount 
  

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?

解决方案

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屋！