Discussion of conflicts with hibernate when using ORACLE trigger to generate a primary key

ORACLE trigger conflicts with hibernate

Capitain write:

Our database definition mapping uses

<id name="id" column="ID" type="long">    <generator class="sequence">        <param name="sequence">seq_somename</param>    </generator></id>

But in this way, I found that hibernate will get a sequence, and a database triger will get a sequence, so that the actual sequence will be added with 2 !! In addition, the primary key ID of the object returned by create is also incorrect, because it is also added with 1 by triger, and all records in the database are double numbers, but the primary key returned by create is, 5. 7. How can this problem be solved? Because triger cannot be deleted, can hibernate use the value generated by triger?

I checked the hibernate documentation and source code and found that when generator is Sequence

It will finally call this method

In oracle9dialect under dialect

    public String getSequenceNextValString(String sequenceName) {        return "select " + sequenceName + ".nextval from dual";    }

Triger goes this way again. sequence must be added twice.

I used Native and found that the work result is the same as sequence. Is there no solution?

Robbin write:

I have the simplest solution:

View the net. SF. hibernate. dialect. oracle9dialect 78th line in the source code of hibernate2.0.3:

public String getSequenceNextValString(String sequenceName) {  return "select " + sequenceName + ".nextval from dual";}

Just change it to the following format:

public String getSequenceNextValString(String sequenceName) {  return "select " + sequenceName + ".currval + 1 from dual";}

That is to say, when hibernate tries to get the sequence value, it does not allow the sequence to add 1. It only takes the current value and then adds 1 to return the result. When it is inserted, it allows the trigger to add 1 to the sequence.

Therefore, you can write a new Dialect:

package net.sf.hibernate.dialect;public class OracleMyDialect extends OracleDialect {  public String getSequenceNextValString(String sequenceName) {    return "select " + sequenceName + ".currval + 1 from dual";  }}

After compilation, put it in classpath and configure hibernate

hibernate.dialect net.sf.hibernate.dialect.OracleMyDialect

This will solve your problem.

Capitain write:

Thank you. I am not familiar with Oracle. I don't know. If I add a record to the database through multiple connections, will there be any problems,

In other words, I don't know if there is any problem during the time when the getsequencenextvalstring () and triger occur in hibernate? I don't know if the Oracle driver can make sure it is an atomic operation?

Robbin write:

It cannot guarantee atomicity, and there may be conflicts of concurrent access. However, you can only do this for the time being. To tell the truth, this method can only be a matter of expediency.

Since session. Save () needs to return the primary key, Hibernate cannot know what the primary key is currently created by the trigger when you use the trigger to create an ID.

select sequ.currval from dual;

Or

select max(id) from table_name;

When you insert data to obtain the primary key, there is a possibility of concurrent conflicts. The only way to avoid concurrency conflicts is:

insert into table_name ...   returning :id;

Use the SQL statement unique to Oracle to insert data and return the primary key to let hibernate know the value of the primary key. However, Hibernate does not support this SQL statement currently. If you want hibernate to support this SQL statement, you need to make considerable changes to the source code. See if it will be added later.

This is not the first time I have discussed it with others. There is indeed no good solution unless Hibernate is modified in a large area. However, I have been using Oracle for so many years and have never used trigger to implement the auto increment field. I personally think this method is not good. Oracle has freed sequence from the field, and you need to put it back. You can generate a primary key without using a trigger. See two examples:

Insert a single data entry:

insert table_name(id, name,...) values(sequ.nextval, ....);

Batch insert data:

insert table_name(id, name,...)  select sequ.nextval, name,...  from table2_name;

Richard write:

In this case, it is best to use the nvl () function (which comes with Oracle), and only one Oracle-related class can be modified. If other databases are used, are there any similar problems?

Robbin write:

Oldma wrote: Well, who can explain it?

See the following PL/SQL:

create trigger y before insert on bob  for each row  when (new.a is null)  begin    select x.nextval into :new.a from dual;  end;

Use trigger to read sequence, insert the table's primary key field, and simulate the auto increment field in SQL Server

Capitain write:

So what are the standard methods for Oracle to implement the auto increment field? Our current program is inserted according to the auto increment field, that is, the ID field is not written for database operations. Can other methods be used?

Mikeho write:

Yes, Oracle does not need to write sp any more. We used to write SP in SQL Server to generate seq. It does not use auto-growth because it is troublesome to return ID after method call. Now we all use the simulated UUID (only 50 bits, theoretically there is a risk of repeated build ).