MySQL 如果主键由数据库生成，如何使用 em.merge() 为 jpa 实体插入 OR 更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3998064/
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 use em.merge() to insert OR update for jpa entities if primary key is generated by database?
提问by Bobo
I have an JPA entity like this:
我有一个这样的 JPA 实体：
@Entity
@Table(name = "category")
public class Category implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@Column(name = "name")
private String name;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "category")
private Collection<ItemCategory> itemCategoryCollection;
//...
}
Use Mysql as the underlying database. "name" is designed as a unique key. Use Hibernate as JPA provider.
使用Mysql 作为底层数据库。“名称”被设计为唯一键。使用 Hibernate 作为 JPA 提供者。
The problem with using merge method is that because pk is generated by db, so if the record already exist (the name is already there) then Hibernate will trying inserting it to db and I will get an unique key constrain violation exception and not doing the update . Does any one have a good practice to handle that? Thank you!
使用合并方法的问题是，因为 pk 是由 db 生成的，所以如果记录已经存在（名称已经存在），那么 Hibernate 将尝试将其插入到 db 中，我将得到一个唯一键约束冲突异常，而不是执行更新 。有没有人有很好的做法来处理这个问题？谢谢！
P.S: my workaround is like this:
PS：我的解决方法是这样的：
public void save(Category entity) {
Category existingEntity = this.find(entity.getName());
if (existingEntity == null) {
em.persist(entity);
//code to commit ...
} else {
entity.setId(existingEntity.getId());
em.merge(entity);
//code to commit ...
}
}
public Category find(String categoryName) {
try {
return (Category) getEm().createNamedQuery("Category.findByName").
setParameter("name", categoryName).getSingleResult();
} catch (NoResultException e) {
return null;
}
}
回答by Pascal Thivent
How to use em.merge() to insert OR update for jpa entities if primary key is generated by database?
如果主键由数据库生成，如何使用 em.merge() 为 jpa 实体插入 OR 更新？
Whether you're using generated identifiers or not is IMO irrelevant. The problem here is that you want to implement an "upsert" on some unique key other than the PK and JPA doesn't really provide support for that (mergerelies on database identity).
您是否使用生成的标识符与 IMO 无关。这里的问题是您想在 PK 之外的某个唯一键上实现“upsert”，而 JPA 并没有真正提供支持（merge依赖于数据库标识）。
So you have AFAIK 2 options.
所以你有 AFAIK 2 选项。
Either perform an INSERT first and implement some retry mechanism in case of failure because of a unique constraint violation and then find and update the existing record (using a new entity manager).
首先执行 INSERT 并在由于唯一约束违反而失败的情况下实现一些重试机制，然后查找并更新现有记录（使用新的实体管理器）。
Or, perform a SELECT first and then insert or update depending on the outcome of the SELECT (this is what you did). This works but is not 100% guaranteed as you can have a race condition between two concurrent threads (they might not find a record for a given categoryName and try to insert in parallel; the slowest thread will fail). If this is unlikely, it might be an acceptable solution.
或者，先执行 SELECT，然后根据 SELECT 的结果插入或更新（这就是您所做的）。这有效，但不能 100% 保证，因为您可以在两个并发线程之间存在竞争条件（它们可能找不到给定 categoryName 的记录并尝试并行插入；最慢的线程将失败）。如果这不太可能，它可能是一个可接受的解决方案。
Update:There might be a 3rd bonus option if you don't mind using a MySQL proprietary feature, see 12.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax. Never tested with JPA though.
更新：如果您不介意使用 MySQL 专有功能，可能会有第三个奖励选项，请参阅12.2.5.3。INSERT ... ON DUPLICATE KEY UPDATE 语法。虽然从未用 JPA 测试过。
回答by Stijn de Witt
I haven't seen this mentioned before so I just would like to add a possible solution that avoids making multiple queries. Versioning.
我之前没有看到过这个，所以我只想添加一个可能的解决方案来避免进行多次查询。版本控制。
Normally used as a simple way to check whether a record being updated has gone stale in optimistic lockingscenario's, columns annotated with @Versioncan also be used to check whether a record is persistent (present in the db) or not.
通常用作检查正在更新的记录在乐观锁定场景中是否过时的简单方法，用@Version注释的列也可用于检查记录是否持久（存在于数据库中）。
This all may sound complicated, but it really isn't. What it boils down to is an extra column on the record whose value changes on every update. We define an extra column version in our database like this:
这一切听起来可能很复杂，但实际上并非如此。它归结为记录上的一个额外列，其值在每次更新时都会发生变化。我们在我们的数据库中定义一个额外的列版本，如下所示：
CREATE TABLE example
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
version INT, -- <== It really is that simple!
value VARCHAR(255)
);
And mark the corresponding field in our Java class with @Versionlike this:
并在我们的 Java 类中标记相应的字段，@Version如下所示：
@Entity
public class Example {
@Id
@GeneratedValue
private Integer id;
@Version // <-- that's the trick!
private Integer version;
@Column(length=255)
private String value;
}
The @Version annotation will make JPA use this column with optimistic locking by including it as a condition in any update statements, like this:
@Version 批注将使 JPA 通过乐观锁定将此列作为条件包含在任何更新语句中，如下所示：
UPDATE example
SET value = 'Hello, World!'
WHERE id = 23
AND version = 2 -- <-- if version has changed, update won't happen
(JPA does this automatically, no need to write it yourself)
（JPA自动做这个，不需要自己写）
Then afterwards it checks whether one record was updated (as expected) or not (in which case the object was stale).
然后它会检查一条记录是否已更新（如预期）（在这种情况下，该对象已过时）。
We must make sure nobody can set the version field or it would mess up optimistic locking, but we can make a getter on versionif we want. We can also use the version field in a method isPersistentthat will check whether the record is in the DB already or not without ever making a query:
我们必须确保没有人可以设置 version 字段，否则它会搞乱乐观锁定，但是version如果我们愿意，我们可以创建一个 getter 。我们还可以在一个方法isPersistent中使用 version 字段，该方法将检查记录是否已经在数据库中，而无需进行查询：
@Entity
public class Example {
// ...
/** Indicates whether this entity is present in the database. */
public boolean isPersistent() {
return version != null;
}
}
Finally, we can use this method in our insertOrUpdatemethod:
最后，我们可以在我们的insertOrUpdate方法中使用这个方法：
public insertOrUpdate(Example example) {
if (example.isPersistent()) {
// record is already present in the db
// update it here
}
else {
// record is not present in the db
// insert it here
}
}
