How to prevent Hibernate from inserting ID automatically?

huckfinn19 · **Joined:** Fri Sep 29, 2006 3:47 pm **Posts:** 5

Hi all,
I am using Oracle 9i with a table named EVENT that has a primary key column ID and a string column NAME. I created a sequence (SEQ_EVENT_ID) and a trigger to generate Ids automatically in this column whenever an item is inserted to simulate the "identity" feature supported by other databases.

I am using the following:
Hibernate version: 3.1.3
Database: Oracle9i

Definition of my primary key in mapping doc:
<hibernate-mapping package="devdb">
<class name="Event" table="EVENT">
<id name="Id" type="integer" column="ID" >
<generator class="sequence">
<param name="sequence">SEQ_EVENT_ID</param>
</generator>
</id>
<property name="Name" column="NAME" type="string" not-null="false" length="50" />
</class>
</hibernate-mapping>

The problem is that whenever I insert a new EVENT, hibernate queries the sequence (as expected, I must admit) and then my DB trigger queries the sequence as well which increments my Ids by two at every insertion.

Code used:

Code:

        Client oNewEvent = new Client();
        oNewEvent.setName("new test name...");

        oSession.save(oNewEvent);
        oSession.getTransaction().commit();

Logs by hibernate:
Hibernate: select SEQ_CLIENT_ID.nextval from dual
Hibernate: insert into CLIENT (NAME, ID) values (?, ?)

Basically, I need to be able to define my ID column in the mapping file, but I don't want hibernate to use it when I insert a new event. I want to enter the name of the event, to commit it and to let the database trigger take care of the ID column.

I have tried the <generator class="identity"/> tag, but since I am using Oracle, hibernate won't let me because Oracle does not really support idendity columns.

So how should I configure the <id...> tag in the mapping file so that Hibernate ignores it on insertion?

Thanks,

Huck

HamsterofDeath · **Joined:** Fri Sep 01, 2006 3:35 pm **Posts:** 21

have you tried setting the id yourself before calling save()?

EdC · **Joined:** Tue Sep 26, 2006 11:46 pm **Posts:** 33

The sequence generator is meant to be used without the trigger and "automagical" assignment. What you need is an ID Generator that is a subclass of AbstractPostInsertGenerator.

I'm not aware of one that will do exactly what you want, though the SelectGenerator may do the job.

huckfinn19 · **Joined:** Fri Sep 29, 2006 3:47 pm **Posts:** 5

Thanks for your replies.

HamsterofDeath: I tried what you suggested. Here is what I get:

Code:

        oSession.beginTransaction();
        Event oNewEvent = new Event();
        oNewEvent.setId(-1); // Fake id. Let the DB sequence generate a real one.
        oNewEvent.setTitle(sTitle);
        oNewEvent.setStartDate(tDate);
        oSession.save(oNewEvent);
        oSession.getTransaction().commit();
        System.out.println("New Id: " + oNewEvent.getId());

The output of the last command is "New Id: -1", meaning that the insert went fine, the sequence was used by the trigger, but my object in my application is not persisent, because hibernate thinks the Id that was stored in the DB is -1.

Can you see any was around this?

Thanks again.

huckfinn19 · **Joined:** Fri Sep 29, 2006 3:47 pm **Posts:** 5

EdC: I also tried your suggestion. Here are my results:

The "select" generator class is used to do a select after the insert has been performed. For example, the following mapping definition

Code:

<generator class="select">
   <param name="key">Id</param>
</generator>

will lead to the following Hibernate executions (with the same code as above):

Hibernate: insert into EVENT (STARTDT, TITLE) values (?, ?)
Hibernate: select ID from EVENT where ID =?
org.hibernate.HibernateException: Unable to resolve property: Id
...

I believe I am getting this exception because the Id property in my object is never set (I want it to be set automatically by Hibernate). And a SELECT ID WHERE ID = ? is not the best SQL statement ever. So for fun, I pretended that the date column was unique (far from reality) :

Code:

<generator class="select">
    <param name="key">StartDate</param>
</generator>

This led to the following Hibernate actions:

Hibernate: insert into EVENT (STARTDT, TITLE) values (?, ?)
Hibernate: select ID from EVENT where STARTDT =?

which works fine is my date column is unique, but it is not the case.

So I believe this won't work. Any more suggestions?

huckfinn19 · **Joined:** Fri Sep 29, 2006 3:47 pm **Posts:** 5

It seams like the Hibernate team may have produced something that would do something close to what I am looking for. The class would be called:

org.hibernate.id.SequenceIdentityGenerator

Here is the link:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-1179?page=all

ahmedferdous · **Joined:** Wed Oct 04, 2006 4:27 am **Posts:** 1

hi huckfinn19,

did you find any workaround to this ?
we are also facing the same problem.
And we just cant drop the triggers cause they are also used by other applications.

huckfinn19 · **Joined:** Fri Sep 29, 2006 3:47 pm **Posts:** 5

Sorry, still no luck. I am sure that there MUST be a workaround for this since the TRIGGER/SEQUENCE pair in Oracle is the way to go to generate primary keys automatically.

I did not have the chance to try the hibernate fix suggested at the link I mentionned earlier in this thread, it seems like it may be the solution.

If you find a solution on your side, please let me know on this forum, it would be greatly appreciated!

Good luck,

H.

max · **Posted:** Fri Oct 06, 2006 5:45 pm

why is neither the "select" nor "sequence" built in support not enough for you ?

fuzebest · **Joined:** Fri Dec 17, 2004 10:38 am **Posts:** 54

Maybe not very nice solution, but it seems does the thing

Code:

public class TriggerGenerator extends AbstractPostInsertGenerator {

   public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(PostInsertIdentityPersister persister, Dialect dialect,
           boolean isGetGeneratedKeysEnabled) throws HibernateException {
      if (!isGetGeneratedKeysEnabled) {
         throw new IdentifierGenerationException("trigger generator requires getGeneratedKeys() JDBC3 functionality enabled");
      }
      return new TriggerGeneratorDelegate(persister, dialect);
   }

   private static class TriggerGeneratorDelegate extends AbstractReturningDelegate {

      private final Dialect dialect;

      public TriggerGeneratorDelegate(PostInsertIdentityPersister persister, Dialect dialect) {
         super(persister);
         this.dialect = dialect;
      }

      @Override
      protected Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
         insert.executeUpdate();
         return IdentifierGeneratorFactory.getGeneratedIdentity(NamedGeneratedKeysHelper.getGeneratedKey(insert), getPersister().getIdentifierType());
      }

      @Override
      protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException {
         return session.getBatcher().prepareStatement(insertSQL, getPersister().getRootTableKeyColumnNames());
      }

      public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
         return new SequenceIdentityGenerator.NoCommentsInsert(dialect);
      }

   }

}

xserty · **Posted:** Thu Jan 11, 2007 8:03 am

fuzebest wrote:

Maybe not very nice solution, but it seems does the thing

Code:

[snip...snip/]

fuzebest, thanks for sharing: works like a charm! :)
Xserty

Nicols · **Joined:** Fri Feb 23, 2007 7:42 am **Posts:** 1

I use the following generator class to deal with primary keys generated by trigger in an Oracle database.
It works by selecting the current value of the sequence (ex : "select mySequence.currval from dual;") directly after the insert has been processed (must be in the same database transaction).

Hope it helps.

Nicols

Sample mapping extract :

Code:

<id name="id" column="address_id">
     <generator class="be.dms.TriggerGenerator">
           <param name="sequence">address_sequence</param>
           <param name="schema">dms</param>
    </generator>
</id>

Generator class :

Code:

package be.dms;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.id.AbstractPostInsertGenerator;
import org.hibernate.id.Configurable;
import org.hibernate.id.IdentifierGenerationException;
import org.hibernate.id.PostInsertIdentityPersister;
import org.hibernate.id.insert.AbstractSelectingDelegate;
import org.hibernate.id.insert.IdentifierGeneratingInsert;
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate;
import org.hibernate.type.Type;

public class TriggerGenerator extends AbstractPostInsertGenerator implements
        Configurable {

    private String sequenceName;
    private String schemaName;

    public void configure(Type type, Properties params, Dialect d)
            throws MappingException {
        sequenceName = params.getProperty("sequence");
        schemaName = params.getProperty("schema");
    }

    public InsertGeneratedIdentifierDelegate getInsertGeneratedIdentifierDelegate(
            PostInsertIdentityPersister persister, Dialect dialect,
            boolean isGetGeneratedKeysEnabled) throws HibernateException {
        return new TriggerGeneratorDelegate(persister, dialect, sequenceName,
                schemaName);
    }

    protected static class TriggerGeneratorDelegate extends
            AbstractSelectingDelegate implements
            InsertGeneratedIdentifierDelegate {

        private final Dialect dialect;
        private final Type idType;
        private final String idSelectString;

        TriggerGeneratorDelegate(PostInsertIdentityPersister persister,
                Dialect dialect, String sequenceName, String schemaName) {

            super(persister);

            this.dialect = dialect;

            idSelectString = "SELECT "
                    + sequenceName
                    + ".currval"
                    + (StringUtils.isEmpty(schemaName) ? ""
                            : ("@" + schemaName)) + " as id FROM DUAL";

            idType = persister.getIdentifierType();
        }

        public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
            return new IdentifierGeneratingInsert(dialect);
        }

        protected String getSelectSQL() {
            return idSelectString;
        }

        protected Serializable getResult(SessionImplementor session,
                ResultSet rs, Object entity) throws SQLException {
            if (!rs.next()) {
                throw new IdentifierGenerationException(
                        "Unable to get the generated key with : "
                                + idSelectString);
            }
            return (Serializable) idType.nullSafeGet(rs, "id", session, entity);
        }
    }
}

landrain · **Posted:** Fri Apr 13, 2007 8:06 am

Nicols wrote:

I use the following generator class to deal with primary keys generated by trigger in an Oracle database. [...]

If you don't want the reselect the sequence, you can maybe use this one too: http://forum.hibernate.org/viewtopic.php?t=973262

landrain · **Posted:** Fri Apr 13, 2007 8:29 am