Inserting without specifing the ID

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

Hibernate version: 3.1
Database: Oracle 10g

Hello,

I am trying to configure in the table map file that the ID will be generated/inserted by the database.

I have in the database a trigger which fires while inserting and fills the ID column with a value read from a sequence.

Hibernate should not send the ID attribute, when trying to insert. But it should read it by selecting.

This one is trying to read the sequence itself (I don't need that the database trigger should do it!)

Code:

    
                    .
                    .
<class name="xx.yy.SeExampl" table="SEEXAMPL" schema="SESM">
        <id name="seId" type="long">
            <column name="SE_ID" length="13"/>
            <generator class="native" />
        </id>   
                    .
                    .
                    .

I have tried many combinations

I managed to get Hibernate not putting the ID in the insert query , by defining the attribute dynamic-insert="true" in the </class> tag. + using the generator identity (Identity is not meant to be used with Oracle). But it then try to read the just created ID with getLong (for DB2, MySQL, ...) which the Oracle driver doesn't support and then breaks with an exception.

To say it in few words, what I would need is a configuration which has the effect like the attribute insert="false", but for the </id> tag.

Thanks.

MmarcoM · **Joined:** Thu Apr 29, 2004 4:31 am **Posts:** 42

have you tried

generator="select" ?

hth
marco

terron · **Joined:** Wed Apr 25, 2007 6:50 am **Posts:** 9

yes, with select, it is causing an exception:

Code:

java.sql.SQLException: Invalid column type: getLong not implemented for class oracle.jdbc.driver.T4CRowidAccessor

I have the feeling that select is not meant to work with oracle

brianbeech2 · **Joined:** Wed May 30, 2007 4:14 pm **Posts:** 15

Has a solution for this been found? I'm running into the same problem and stumbled upon this. Please post your solution if you've figured out the prob. Thanks...

max · **Posted:** Sat Jun 02, 2007 3:32 pm

"select" is the right way to solve this.

Works fine on oracle in general; you must be doing something specific - e.g. accessing the special rowid column which sounds not like the thing you want.

guru_i · **Joined:** Wed Jan 05, 2011 3:22 pm **Posts:** 13

May be its too late but I had same problem and I saw that the property hibernate.jdbc.use_get_generated_keys was set to true.

I set it to false then I got different error saying "Caused by: org.hibernate.MappingException: Dialect does not support identity key generation" but may be I was using "select" as generator class.

still working on it!

guru_i · **Joined:** Wed Jan 05, 2011 3:22 pm **Posts:** 13

forgot to add the solution which I later found out.

If you are using 2 DB (e.g. Oracle, MS SQL Server) and if you are using ID Generator for MS SQL and sequence for Oracle then the best way to use "native" generator class in HB mapping files and for Oracle specify sequence name inside. e.g.

<id name="instanceId" column="id" type="long">
<generator class="native">
<param name="sequence">seq_item</param>
</generator>

this way HB will figure out which to use when

f6o · **Joined:** Tue Jun 28, 2011 11:12 am **Posts:** 2

I found myself into a similar problem. Finally, I solved it by using this HBM:

Code:

<hibernate-mapping package="mypackage" auto-import="false">
    <import class="MyClass" rename="my" />
    <class name="MyClass" table="my" lazy="false" select-before-update="true">

        <id name="id" type="java.lang.String" unsaved-value="null">
            <column name="ssh_id" not-null="true" />
            <generator class="sequence-identity">
                <param name="sequence">schema_owner.seq_my_id</param>
            </generator>
        </id>
        ...
    </class>
</hibernate-mapping>

Interestingly, I didn't find any documentation about the param required by sequece-identity, and had to do the hard working try-and-error approach gathering hints here and there.

Notice also the use of select-before-update="true". Without it, HB makes a mess with the sequence nextval.

Just my 2 cents.
Cheers

f6o · **Joined:** Tue Jun 28, 2011 11:12 am **Posts:** 2

I just discovered that my previous solution had a small problem. With that configuration, Hibernate issues a nextval on the sequence during insertion. This value is then discarded because the table has a trigger on insert which selects again a nextval. In this way, the ids genereated will be always even (or odd, depending on the value you start from), skipping one every other value.
In order to solve this, I have created a dummy sequence, which is called during Hibernate insertions, but the value is discarded by the table's trigger which sets the correct nextval from the correct sequence.
The mapping is then:

Code:

<hibernate-mapping package="mypackage" auto-import="false">
    <import class="MyClass" rename="my" />
    <class name="MyClass" table="my" lazy="false" select-before-update="true">

        <id name="id" type="java.lang.String" unsaved-value="null">
            <column name="ssh_id" not-null="true" />
            <generator class="sequence-identity">
                <param name="sequence">schema_owner.dummy_sequence</param>
            </generator>
        </id>
        ...
    </class>
</hibernate-mapping>

and the table definition:

Code:

CREATE SEQUENCE dummy_sequence  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE TABLE my (
  my_id NUMBER(10) NOT NULL PRIMARY KEY,
  ...
);

CREATE SEQUENCE  seq_my_id  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

CREATE OR REPLACE TRIGGER trg_my_id BEFORE INSERT ON my FOR EACH ROW
BEGIN
    SELECT  seq_my_id.nextval INTO :new.my_id FROM DUAL;
END;
/

In this way, you can still insert into 'my' table from sqlplus or standard JDBC in the usual way, without specifying the 'my_id' value, while allowing Hibernate to work with this kind of relational model.

Hope this helps someone.
Cheers