Hibernate, Oracle, Sequences, Triggers

有困难，找猪八戒
请看原文：
http://www.juliesoft.com/blog/jon/index.php/2007/02/19/hibernate-oracle-sequences-triggers-and-off-by-one%E2%80%99s/



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.
有困难，找猪八戒