Here's the situation:
I have a non-hibernate part of my application that does an insertion in to the RFQ_DATA table using straight SQL:

insert into rfq_data (filename, rfq_data_id, parent_po_id, date_generated, extension, status, type, username) values (?, rfq_data_id_seq.nextval, ?, sysdate, ?, 'PENDING', ?, ?)

As you can see, it sets the key field (rfq_data_id) to rfq_data_id_seq.nextval


I then have a hibernate part of my application that does an insertion into the RFQ_DATA table this way:

Integer lastRfqDataId = (Integer) session.createQuery("select MAX(rd.rfqDataId) from RfqData rd").uniqueResult();

if (lastRfqDataId == null) {

lastRfqDataId = new Integer(0);

}

Integer rfqDataId = new Integer(lastRfqDataId.intValue() + 1);

RfqData rd = new RfqData(rfqDataId, poId, supplierCode, extension, type, username, filename, dResponseDate);

session.save(rd)


As you can see, this code does not reference or update the rfq_data_id_seq, but instead manually finds the max key field and using the that +1 to get the next id to use.

So, besides the obvious synchronization issues that can result from doing it this way (i.e. using two separate statements, one to get the max and one to insert), here’s the main issue I am having.

I get a unique constraint violation whenever the non-hibernate SQL above tries to do an insertion into the rfq_data table whenever that table got previously updated using my hibernate code, since that hibernate code never updated the rfq_data_id_seq sequence.

What’s the easiest and quickest approach for this?

Thank you,

Alex

P.S. (I saw Pietro's custom sequences posting at hibernate.bluemars.net/296.html, and it may or may not be what I need. If it then I need more instructions.)

Dump the manual processing of key value generation as there is full support for defining sequences as your ID generator. See documentation.

thanks, david - can you point me to the right place in documentation

Thanks ctran.

thanks, guys

The link was very helpful, except what exactly does it mean when it says:

>> Both these strategies require two SQL queries to insert a new object.

Can you give me an example of a kind of two SQL queries it's talking about?

Refering to a select to get the sequence number and populate the id followed by an update to save the object.

could you give an example

Hibernate: select SEQ_DEFAULT.nextval from dual
Hibernate: insert into parent (id) values (?)

ok, so I am confused.

firstly, my dual table does not contain sequences. Secondly, am I not going to be writing this in HQL? If so, don't I need to then provide a mapping for the DUAL table? Do I really need to do all this?

Please elaborate?

Firstly, this is the output of Hibernate to the Oracle SQL engine. The result of a save, for example, Hibernate session call:

session.save(Object o);

Hibernate knows from the mapping that a sequence must be generated and the data stored. This just happens. Its hibernate's job - no need for us to worry about it.
The DUAL table in Oracle is a dummy table if you like. Always there and is mainly used by system like operations (such as getting a sequence number). You do not need to map it. Ignore the fact its there. Oracle docs will tell you more. Hope this clears it up for you.

Thanks for explanation, David. So you're saying what that documentation is talking about is the resultant generated sql, not something that I need to worry about when making my call, and that I can still use the regular session. save(o) to do this.

So does that mean that all I have to do is to set the properties of an object, LEAVE THE PRIMARY KEY PROPERTY NULL and call session.save(o) and it will know to use the nextval from that sequence (provided, of course that I have the appropriate mapping, such as this?

<id name="id" type="long" column="person_id">
<generator class="sequence">
<param name="sequence">person_id_sequence</param>
</generator>
</id>


Also, is this only a feature of h3 or is it supported by 2.8 as well?

Yes you understand it - Just save and hibernate will do the work for you. Yes it will work with H2. I am assuming you have created the sequence with the name you have defined in your mapping file.