Hopefully this is not a duplicate question. I checked the forum and didn't exactly find the answer.

We are using Oracle. For a given table we have a primary key which is set by a trigger before an insert is performed, for example. The trigger simply calls a sequence to get the nextval which is set as the primary key.

While the Hi/Lo algorithm would be the best solution, all our legacy code depends on the trigger to handle the key generation, so I can't just make the change without modifing the legacy.

What I need is a way to perform the following with hibernate:

insert into <table>(...) values(...);
select <sequence>.currval form dual;

Then apply the resulting value returned by the select into the hibernate object as the object key.

I know that the "select" generator was designed to do something similar but I haven't been able to get it to work. I believe it attempts to select the primary key then use the returned value as input to the insert, for instance. The trigger will subsequently call sequence.nextval which will effectively change the key and the hibernate object will be invalid (since the key it has is incorrect)

There is a post generator that I've been hacking at that I have sort of working but is buggy due to my lack of knowledge of hibernate internals and I can't be the only one with this issue.

To meet my deadlines here is what I've done as a work around but I'd appreciate any other ideas too:

Change the trigger:
...
if user !='<USER>' then
select <sequence>.nextval into :new.id from dual;
end if;
..

Then use <USER> for hibernate and different user for legacy. I use "sequence" generator class which calls the sequence and applies the resulting key to the hibernate object.

Any thoughts?

No, you missed the intent of the select generator. It is exactly what you are looking for. It assumes the id is generated/inserted within the database, so it does not attempt to generate/bind any value here. Instead, after the insert completes, it attempts to select the generated id value from the database.

In order for it it work, though, you need to tell it about a unique property by which it can locate the just inserted row.

http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-id-select

Ok. Actually what you stated is my understanding of the select generator. But what I haven't been able to figure out is how to configure the descriptor or more specifically, what to provide for the key.

<id name="id" type="long" column="person_id">
<generator class="select">
<param name="key">socialSecurityNumber</param>
</generator>
</id>

The only way, to my knowledge, to get the key that was generated through the trigger is to call the same sequence the trigger used and request the currval.

How do I configure the "key" param to call the sequence? I believe the select generator expects to be able to retrieve the newly inserted row by another unique field. But in my case the only unique field is the primary key which is generated through the trigger.

Please let me know if I'm still missing it.

thanks

you can try

define your generator
or
use interceptor

That is not safe. What happens when another connection calls nextval on that sequence in the interim between your insert and your select? Mayhem, that's what ;)


Well then you have problems, huh? You could use a custom type mapped to the oracle rowid; have a look at our testsuite in the org.hibernate.test.rowid package for an example.

Ok. I finally figured out what was wrong with the generator I've been working on. I kept getting an invalid column error and I finally thought to change the sql from:

select <sequence>.currval from dual

to:

select <sequence>.currval as id from dual

Since id is the name of my primary key field the 'as id' fixed the problem. I'll modify my code next to provide a column name parameter instead of hard coding the 'as' statement.

Just one more comment concerning earlier statements concerning a previous post:


That is not safe. What happens when another connection calls nextval on that sequence in the interim between your insert and your select? Mayhem, that's what ;)


With all due respect, this statement is not correct. Sequences are safe at a cursor level. If this were not true then the sequence generator provided in Hibernate would not work as it calls the specified sequence to get the nextval then applies that value to the insert. Using Oracle sequences in this manner is common. Of course it would be best to place the calls inside a transaction block too.

Thank you to everyone that posted replies.

Just another followup.

It was too late last night when I wrote my last reply. The rowid solution of course would not work...


I stand corrected. I just tried a test with currval and it does understand the current-value partitioned by the connection (not cursor) such that selecting the currval psuedo column actually returns the last value returned for nextval *within that connection*.


With all due respect, this statement is not correct ;) Hibernate issues a "select seq_name.nextval from dual". It then retrieves that selected value from the resulting result set and uses that in the insert...


Anyway, yet another option would be to somehow utilize the oracle "insert into ... RETURNING column_list" syntax.

Guys,

I'm working with the same issue on my Oracle database -- the insert has a trigger that assigns the next sequence value.

To summarize my understanding from the discussion:

<id name="id" type="long" column="person_id">
<generator class="select">
<param name="key">socialSecurityNumber</param>
</generator>
</id>

will not work unless socialSecurityNumber and the generated person_id are separate unique identifiers for the same row. So if I only have "person_id", which is generated by the insert trigger, I cannot use the "select" generator.

So as blynn did, I need to define my own generator that does this:

insert into <table>(...) values(...);
select <sequence>.currval form dual;

Hibernate folks, is this correct?

Blynn, would you mind posting your generator?

Thanks

Just for the sake of closure - this post seems to solve the issue for me:
http://forum.hibernate.org/viewtopic.ph ... 77#2364977