Hibernate, Oracle, Sequences, Triggers

本文探讨了Hibernate在使用Oracle数据库时如何与序列和触发器协同工作以设置主键值,解释了常见的off-by-one问题,并提供了解决方案。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

[url=http://www.zhubajie.com/task/?welcome=1523583]有困难,找猪八戒[/url]
请看原文:
[url]http://www.juliesoft.com/blog/jon/index.php/2007/02/19/hibernate-oracle-sequences-triggers-and-off-by-one%E2%80%99s/[/url]


Hibernate, Oracle, Sequences, Triggers, and Off-by-One’s

Posted by jonchase

Hibernate and Oracle triggers may both need to set the primary key value when inserting a row into the database - here’s how to make them play nice together.

Oracle users typically use a combination of a trigger and a sequence to achieve what many databases support natively - the concept of an Identity data type for use in creating primary keys.

Often this is accomplished with something like the following in Oracle:

/* create a simple table */
create table Person (
id number(9) not null,
name nvarchar2(10) );

/* primary key definition on Person.id */
create unique index pk_Person on Person (id);

/* generates a non-repeating (unique) numeric value */
create sequence seq_Person_id;

/* gets called every time a Person row is
inserted to set Person.id to the next value
of seq_Person_id */
create or replace trigger trig_Person_insert
before insert on Person
for each row
begin
select seq_Person_id.NEXTVAL into :new.id from dual;
end;

The above is all well and good - it works great when a client does something like the following:

/* insert a row into Person table */
insert into Person (name) values ("Jon");

The following row would be inserted into the database:

[1, Jon]

And in steps Hibernate to ruin everything.

Let’s assume we have mapped the above table definition to a Person class we have created, like so:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="example.Person" table="Person">
<id name="id">
<generator class="sequence">
<param name="sequence">seq_Person</param>
</generator>
</id>
<property name="name" />
</hibernate-mapping>

Here’s how to insert a new Person row using Hibernate (pseudo code):

Person person = new Person("Julie");
session.save(person);
System.out.println("person.id:" + person.id);

The above code creates a new Person object with a name of “Julie” and then inserts it into the database. Assuming the row with “Jon” has already been inserted into the database, here’s what the program would print out when run:

person.id:2

So you’d assume that the following two rows now exist in the database:

[1, Jon]
[2, Julie]

Right?

Wrong.

There are two rows in the database, that’s for sure, but they look like this:

[1, Jon]
[3, Julie]

3? Did we just discover a bug in Oracle? Are you telling me it can’t get the math right for 1 + 1? Thankfully, Oracle and Hibernate are operating exactly as planned - there’s no bug here. As usual, the devil is in the details.

What’s happening? Why are there two different id values for “Julie”? Why is there a [2] printed out, while the database holds a [3]?

For an explanation, look at the same code one more time, this time with some comments on the steps Hibernate and the Oracle database take when inserting the Person into the database:

Person person = new Person("Julie");
// 1st, Hibernate will make a call to get the next
// value of seq_Person:
// select seq_Person.nextval from dual; -- returns [2]
// 2nd, Oracle will call the trig_Person_insert
// trigger to set the value of id. Note that
// the trigger also uses the sequence to get
// the next value of id (3) and sets the id of
// the new Person row using that value (thus
// overriding the [2] Hibernate will set in the
// insert statement)
// 3rd, Hibernate will use the value it got from
// the sequence (2) when inserting the Person row:
// insert into Person (id, name) values (2, "Julie");
session.save(person);
System.out.println("person.id:" + person.id);

In the case above, even though Hibernate explicitly specified that [2] should be used for the id of the new row, the trigger defined in Oracle trumped Hibernate, and thus the row in the database has a value of [3]. However, Hibernate doesn’t know this, and so it sets the id of the Person object to [2].

Confused yet? Hopefully less that you were before.

Here’s how to fix the issue. Change the trigger definition to the following:

/* gets called every time a Person row is inserted,
but only sets the Person.id if it is not already
specified in the SQL insert statement */
create or replace trigger trig_Person_insert
before insert on Person for each row
begin
if :new.id is null
then select seq_Person_id.NEXTVAL into :new.id from dual;
end if;
end;

Notice that the trigger only sets the id if it’s not already set in the insert statement. After the long explanation of the problem, that’s all there is to the solution. If a user now issues an insert like the following:

insert into Person (name) values ("Marc");

The database will contain the following rows:

[1, Jon]
[3, Julie]
[4, Marc]

And if Hibernate is then used to insert another row:

Person person = new Person("Tony");
session.save(person);

Tthe database will contain the following rows, as expected:

[1, Jon]
[3, Julie]
[4, Marc]
[5, Tony]

One Note

Many relational databases, like SQL Server, MySql, and Db/2 (to name a few) natively support the concept of Identity Columns. That is, to get unique key generation on these platforms, simply declare the id column as an Identity type, and the database will take care of all the id generation for you - no muss no fuss.

Just change the Hibernate mapping we used above for the id column to this:

<id name="id"> <generator class="native">
<param name="sequence">seq_Person</param>
</generator>
</id>

…and Hibernate will pick the appropriate strategy for the database it’s running on - identity for those databases that support it, or the sequence strategies for databases like Oracle.
One More Note

Modification to the trigger isn’t necessary if the ONLY client that is going to be inserting data into the database is Hibernate. In that case, the trigger isn’t necessary at all. Hibernate will use the defined sequence to get a value for id and insert it. The above is useful for the situation where non-Hibernated based clients will be inserting data into the database. This is often common if, say, a web application built using Hibernate is inserting data into the database and a legacy COBOL application is also inserting data into the same database. A strategy like the above will allow both applications (and any others added in the future) to peacefully coexist. However, the peaceful coexistence of your Java and COBOL programmers is another issue altogether.
[url=http://www.zhubajie.com/task/?welcome=1523583]有困难,找猪八戒[/url]
内容概要:文章详细介绍了电梯门禁(梯控)系统的硬件安装与接线要点。首先强调了梯控板与楼层按键对接的重要性,包括遵循一一对应原则以避免错层、越层问题,允许空层存在以适应实际需求。接着阐述了不同接线方式(COM、NO、NC端口的不同组合)对用户权限的影响,如单层权限用户刷卡直达指定楼层,多层权限用户在特定接线方式下的操作限制。硬件安装方面,强调了无源干触点设计原则以确保电气隔离,防止系统间干扰,以及读卡器接入时的规范要求。文章还介绍了梯控系统的技术原理,如身份验证机制(二维码/IC卡/人脸识别)、消防联动功能(紧急情况下释放所有楼层权限),并指出该系统适用于小区、写字楼等场景,支持机器人乘梯SDK扩展。最后,根据不同场景需求提出了适用的接线方式选择,如严格管控场景下选择4.3接线以实现精准权限控制,限制多层用户手动选层场景下选择4.1接线并配合软件权限设置。; 适合人群:从事电梯安装维护的技术人员、楼宇自动化工程师及相关领域的管理人员。; 使用场景及目标:①指导技术人员正确安装和接线梯控系统,确保系统安全稳定运行;②帮助管理人员了解不同接线方式对用户权限的影响,以便根据实际需求选择合适的配置方案;③提升楼宇安全管理和服务质量,特别是在小区、写字楼等场所的应用。; 其他说明:梯控系统的正确安装和接线不仅关系到系统的正常运作,更直接影响到用户的安全和使用体验。因此,在实际操作中务必严格按照规范执行,同时关注最新的技术发展和应用场景变化,以确保系统始终处于最佳状态。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值