java 如何使用 Hibernate 模仿 upsert 行为？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3671481/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to mimic upsert behavior using Hibernate?
提问by Jason
I'm writing an application that sync's entities from a third party datasource into our own schema, with a transformation/mapping step in between. I'm using Hibernate to represent and persist the entities in our own schema. A problem I'm running into is that I have a unique multi-column key on one of my tables. The behavior I would like to see is analogous to an upsert: when Hibernate goes to persist an entity and detects a unique constraint violation, it does an update instead. We are using MySQL, which provides an INSERT ... ON DUPLICATE KEY UPDATE syntax, but I'm not sure how or if Hibernate can be made to make use of it?
我正在编写一个应用程序，将来自第三方数据源的实体同步到我们自己的模式中，中间有一个转换/映射步骤。我正在使用 Hibernate 在我们自己的架构中表示和持久化实体。我遇到的一个问题是我的一个表上有一个唯一的多列键。我希望看到的行为类似于 upsert：当 Hibernate 去持久化一个实体并检测到一个唯一的约束违规时，它会进行更新。我们正在使用 MySQL，它提供了 INSERT ... ON DUPLICATE KEY UPDATE 语法，但我不确定如何或是否可以使 Hibernate 使用它？
I suppose I could always try the insert, and if I catch an exception do an update, but that seems hacky and suboptimal. Any tips on a clean way to do this?
我想我总是可以尝试插入，如果我捕捉到异常，则进行更新，但这似乎很笨拙且不理想。任何关于以干净的方式执行此操作的提示？
回答by Pascal Thivent
We are using MySQL, which provides an INSERT ... ON DUPLICATE KEY UPDATE syntax, but I'm not sure how or if Hibernate can be made to make use of it?
我们正在使用 MySQL，它提供了 INSERT ... ON DUPLICATE KEY UPDATE 语法，但我不确定如何或是否可以使 Hibernate 使用它？
It looks like someone did itby overriding the sql-insertstatement used by Hibernate for this entity. If you don't mind not being portable (and probably using a stored procedure), have a look.
看起来有人通过覆盖Hibernate 为此实体使用的语句来做到这sql-insert一点。如果您不介意不可移植（并且可能使用存储过程），请查看。
I suppose I could always try the insert, and if I catch an exception do an update, but that seems hacky and suboptimal. Any tips on a clean way to do this?
我想我总是可以尝试插入，如果我捕捉到异常，则进行更新，但这似乎很笨拙且不理想。任何关于以干净的方式执行此操作的提示？
Another option would be to:
另一种选择是：
- perform a select on the unique key
- if you find a record, update it
- if you don't find a record, create it
- 对唯一键执行选择
- 如果您找到记录，请更新它
- 如果没有找到记录，请创建它
But unless you lock the wholetable(s) during the process, you can face some race condition in a multi-threaded and distributed environment and step #3 can potentially fail. Imagine two concurrent threads:
但是，除非您在此过程中锁定整个表，否则您可能会在多线程和分布式环境中面临一些竞争条件，并且步骤 #3 可能会失败。想象两个并发线程：
Thread 1:
主题 1：
- begin trans
- performs a select on a key
- no record found
- create a record
- commit
- 开始翻译
- 对键执行选择
- 没有找到记录
- 创造记录
- 犯罪
Thread 2:
主题 2：
- begin trans
- performs a select on the same key
- no record found
- create a record
- commit (FAIL!because thread 1 was faster and a record with the same unique key now exists)
- 开始翻译
- 在同一个键上执行选择
- 没有找到记录
- 创造记录
- 提交（失败！因为线程 1 速度更快，并且现在存在具有相同唯一键的记录）
So you would have to implement some kind of retry mechanism anyway (locking the whole table(s) is not a good option IMO).
所以无论如何你都必须实现某种重试机制（锁定整个表不是一个好的选择 IMO）。
回答by Ken Jarrad
The race condition can be avoided by "select ... for update"
可以通过“select ... for update”避免竞争条件
