使用 Oracle Trigger 从序列生成 id 的 HIbernate 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8002119/
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
HIbernate issue with Oracle Trigger for generating id from a sequence
提问by RKodakandla
We have a before insert trigger that gets the next value from sequence. When the object is persisted with save() method, hibernate gets the value from the sequence and adds it to the object. and when the transaction is committed from Spring's service layer, the ID value is again increased on the database. how do I avoid getting nextval() if the object already has an id..
我们有一个 before insert 触发器，它从序列中获取下一个值。当使用 save() 方法持久化对象时，hibernate 从序列中获取值并将其添加到对象中。当事务从 Spring 的服务层提交时，数据库上的 ID 值再次增加。如果对象已经有一个 id，我如何避免获取 nextval() 。
Here is what I ma trying to do..
这是我想要做的..
UserDao
用户道
public User saveUser(User user){
session.getCurrentSession.save(user);//line2
return user;//line3
}
UserService
用户服务
public void saveUserAndWriteToAudit(User user, UserAudit userAudit){
userDao.saveUser(user);//line1
userAudit.setUserId(user.getId);//line4
userAudit.saveUserAudit(userAudit);//line5
}
And the User Class
和用户类
@Entity
public class User{
@Id
@GeneratedValue(strategy=GenerationType.AUTO, generator="a1")
@SequenceGenerator(name="a1", sequenceName="usersequence")
private Long id;
/////////////////
}
When the cursor reaches line1 and line2 user object has null in id attribute. after line2, it has nextval from sequence - lets say 1. on line4, i have added user's id=1 to useraudit object.. when transaction is committed after line 5, 2 is inserted into User's id column and 1 into UserAudit's userId column. This serves no purpose to me :( How do I avoid this issue? Thanks!
当光标到达 line1 和 line2 时，用户对象的 id 属性为 null。在第 2 行之后，它具有序列中的 nextval - 比如说 1。在第 4 行上，我已将用户的 id=1 添加到 useraudit 对象中。当在第 5 行之后提交事务时，2 被插入到用户的 id 列中，1 被插入到 UserAudit 的 userId 列中。这对我没有任何意义:(我该如何避免这个问题？谢谢！
回答by Matthew Watson
Just update your trigger to only fire when not given an id.
只需将您的触发器更新为仅在未指定 id 时触发。
create or replace
trigger sa.my_trigger
before insert on sa.my_table
for each row
when (new.id is null)
begin
select sa.my_sequence.nextval
into :new.id
from dual;
end;
回答by Christopher Yang
The above solution is great, it's saved me a lot of headaches on this problem.
上面的解决方案很棒，它为我省去了很多关于这个问题的麻烦。
My only gripe is it opens the door to user/codes to insert any ID value without actually inquiring the sequence.
我唯一的抱怨是它为用户/代码打开了大门，可以在不实际查询序列的情况下插入任何 ID 值。
I've found the following solution also works. It lets Hibernate find the max ID and have it incremented every time an insert statement is executed. But when it hits the database, the ID is ignored and replaced by the one generated by the trigger, so there's no uniqueness in a cluster problem:
我发现以下解决方案也有效。它让 Hibernate 找到最大 ID 并在每次执行插入语句时增加它。但是当它访问数据库时，ID 被忽略并替换为触发器生成的 ID，因此集群问题中没有唯一性：
@Id
@GeneratedValue(generator="increment")
@GenericGenerator(name="increment", strategy = "increment")
private Long id;
The biggest drawback is @GenericGenerator is a Hibernate annotation, so you lose JPA's portability. It's also not clear to the programmers that this ID is actually linked to a sequence while in fact, it's one of the tightest coupled solutions that use a sequence.
最大的缺点是@GenericGenerator 是一个Hibernate 注释，所以你失去了JPA 的可移植性。程序员也不清楚这个 ID 实际上是链接到一个序列，而事实上，它是使用序列的最紧密耦合的解决方案之一。
回答by Praveen
create or replace
trigger sa.my_trigger
before insert on sa.my_table
for each row
when (new.id is null)
begin
select sa.my_sequence.nextval
into :new.id
from dual;
end;
this is something really wrong from database world.
这在数据库世界中确实是错误的。
Considering above solution, say sa.my_sequence.nextval is 51 and your hibernate framework will work without any issues. But if someone makes direct jdbc insert with your primary key value as 66 overriding the sequence (say, current value as 52), trigger will just insert.
考虑到上述解决方案，假设 sa.my_sequence.nextval 是 51，并且您的休眠框架将正常运行。但是，如果有人使用您的主键值作为 66 覆盖序列进行直接 jdbc 插入（例如，当前值为 52），触发器将仅插入。
The real issue is when sequence value incremented to 66, which will raise an exception in the trigger ending in resetting the sequence value. This really end in bad structure of schema design from database side.
真正的问题是当序列值增加到 66 时，这将在触发器中引发异常，以重置序列值结束。这确实以数据库端模式设计的不良结构告终。
回答by FelixM
Ideally you would remove the BEFORE INSERT TRIGGER. If you don't, Hibernate has no way of knowing the primary key, and it really needs that information. If you don't care about the object after the INSERT that might be okay (2nd level cache is still an issue), but if you need to use it right away it's a real problem. In the latter case, you could try this nasty approach:
理想情况下，您应该删除 BEFORE INSERT TRIGGER。如果不这样做，Hibernate 无法知道主键，它确实需要该信息。如果您不关心 INSERT 之后的对象，那可能没问题（二级缓存仍然是一个问题），但是如果您需要立即使用它，那就是一个真正的问题。在后一种情况下，您可以尝试这种讨厌的方法：
- Tell Hibernate that you manage the primary key yourself.
- Create the new object and put an arbitrary value into the primary key.
- Flush your Hibernate session and SELECT sequence.CURRVAL (assuming there is only one INSERT).
- Load the object using the obtained current sequence value and don't use the above instance.
- 告诉 Hibernate 你自己管理主键。
- 创建新对象并将任意值放入主键。
- 刷新您的 Hibernate 会话和 SELECT sequence.CURRVAL（假设只有一个 INSERT）。
- 使用获取的当前序列值加载对象，不要使用上面的实例。
