如何使用Hibernate模仿upsert行为？ [英] How to mimic upsert behavior using Hibernate?

问题描述

我正在编写一个应用程序，将来自第三方数据源的实体同步到我们自己的模式中，其间有转换/映射步骤。我使用Hibernate来表示并坚持我们自己的模式中的实体。我遇到的一个问题是我的桌子上有一个唯一的多列键。我希望看到的行为类似于upsert：当Hibernate持久存在一个实体并检测到一个唯一的约束冲突时，它会进行更新。我们正在使用MySQL，它提供了一个INSERT ... ON DUPLICATE KEY UPDATE语法，但是我不确定Hibernate如何以及如何使用它？

<我想我总是可以尝试插入，如果我发现一个异常做了一个更新，但这看起来很拙劣，并且不是最理想的。任何提示干净的方式来做到这一点？

解决方案

我们使用MySQL，它提供INSERT ... ON DUPLICATE KEY UPDATE语法，但我不确定如何以及如果Hibernate可以使用它？

<看起来有人通过重写 Hibernate使用这个实体的sql-insert 语句。如果你不介意不能移植（也可能使用存储过程），请看看。

我想我总是可以尝试插入，如果我发现异常做了一个更新，但这似乎很拙劣和不理想。任何提示干净的方式来做到这一点？

另一种选择是：

$ b $如果您找到记录，则

对唯一密钥

执行选择找不到记录，然后创建它

但是，除非您在整个表中锁定该过程中，您可能会面临多线程和分布式环境中的一些竞争条件，并且步骤＃3可能会失败。想象一下两个并发线程：

线程1：

• begin trans


• 对某个键执行选择


• 未找到记录


• 创建记录

线程2：

• 开始转换


• 在同一个键上执行选择
  
• 未找到记录


• 创建记录


• 提交（ FAIL！），因为线索1速度更快，现在存在具有相同唯一关键字的记录）
  
  

  所以你不得不实施某种重试机制（锁定整个表并不是一个好选择IMO）。

  
  

  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?

  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?

  解决方案

  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?

  It looks like someone did it by overriding the sql-insert statement used by Hibernate for this entity. If you don't mind not being portable (and probably using a stored procedure), have a look.

  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:

  1. perform a select on the unique key
  2. if you find a record, update it
  3. if you don't find a record, create it

  But unless you lock the whole table(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:

  Thread 1:

  • begin trans
  • performs a select on a key
  • no record found
  • create a record
  • commit

  Thread 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)

  So you would have to implement some kind of retry mechanism anyway (locking the whole table(s) is not a good option IMO).

  这篇关于如何使用Hibernate模仿upsert行为？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！