Hibernate 3.0.5, Spring 1.2.8, Oracle 9i.

In my app, I think I need two different kinds of id generators. I have a couple of tables where all the content is generated while the app is running. For these, I'll use a normal sequence. These two tables will continue to accumulate rows while the application is running.

However, I have several tables (most of them, actually), whose data is initialized into the database before the application starts. In the present script, I hardcode incrementing OID values into each row. After the application starts up, however, a small number of rows may be added to these tables. This will happen very rarely, but it will happen.

If I define a plain sequence for the latter tables, I would have to hardcode the starting value for the sequence for that table, to ensure that new rows added to the table(s) use a unique sequence value.

Would I be better off just defining a trigger to set the OID, so my initialization data wouldn't need to hardcode the OID, and I could use plain sequences all around?

I'm moving forward on the trigger/sequence implementation, but I have one issue, and one question.

The issue may be specific to Oracle or Jelly (my db init script is a Jelly script).

My trigger definition code looks like this:

<sql:update sql="CREATE OR REPLACE TRIGGER ${table}_SEQ_TRG
BEFORE INSERT ON ${table}
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT ${table}_SEQ.nextval into NEW.${seqpk} FROM dual;
END"/>

So far, I've only initialized the database, I haven't run my app using Hibernate yet. When I viewed the trigger in my db browser, the "DESCRIPTION" field of the trigger had all the text you see here (with the correct variable values substituted), but it ends with "FOR EACH ROW". The "BEGIN" part to the end isn't there. I'm not sure if that's just an anomaly with the db browser (SQuirreL) or whether the trigger body is corrupt.

The question is, if I do this, do I need to have a generator specified in the Hibernate mappings file for my "id" column? I don't know if I can leave it out.

I figured out my syntax error in the trigger definition (missing colon), so the trigger executes fine when I manually insert a row in my SQL browser.

However, I'm still unsure what to put into my mappings file. Can I just have the following, or do I need to specify some sort of generator that indicates the database creates the column value on its own (through the trigger)?

<id name="oid">
<column name="OID"/>
</id>

Well, I tried both without a generator, and with a "select" generator with "OID" as the key (and the column), and they both result in this exception:

Caused by: org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: [<fqcn>#0]

I'm guessing I have to use some sort of generator that retrieves the OID after the row is inserted.

Earlier, I had made a mistake in my mapping file, leaving off the "select" generator reference on the table I was testing (I managed to add it to all the tables but the one I was testing this for :) ). So, I don't get the NonUniqueObjectException anymore.

However, I'm just getting a different exception. Going down to the root cause, I see this:

------------------
Caused by: java.sql.SQLException: [BEA][Oracle JDBC Driver]Value can not be converted to requested type.
at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
at weblogic.jdbc.base.BaseData.getInteger(Unknown Source)
at weblogic.jdbc.base.BaseResultSet.getInt(Unknown Source)
at weblogic.jdbc.wrapper.ResultSet_weblogic_jdbc_base_BaseResultSet.getI
nt(Unknown Source)
at org.hibernate.id.IdentifierGeneratorFactory.get(IdentifierGeneratorFa
ctory.java:50)
at org.hibernate.id.IdentifierGeneratorFactory.getGeneratedIdentity(Iden
tifierGeneratorFactory.java:35)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntit
yPersister.java:1761)
------------------

Again, my generator spec looks like this:

<id name="oid">
<column name="OID"/>
<generator class="select">
<param name="key">OID</param>
</generator>
</id>

I would not use a trigger to set the key value on insert because Hibernate will know nothing about it and you could end up with two copies of the same object in your session with different keys.

What I would do is use a single sequence for your hibernate objects (or one per table if you like) and then use the sequence_name.nextval for the static inserts at setup time.

After much iteration through this, I certainly agree with your first conclusion. I've given up on triggers for this. In fact, I've given up on sequences also. I thought about all of my tables, and they all have reasonable natural keys, although they are all composite keys. This will work for both my dynamic tables and my static tables. I still have to get through my initial test of this, but I think this strategy will work.