-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 54 posts ]  Go to page 1, 2, 3, 4  Next
Author Message
 Post subject: Before Insert Trigger and ID generator
PostPosted: Fri Apr 13, 2007 6:45 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
EDIT (2011/01/21):
* See here for the latest version of the code (Hibernate 3.6.0.Final).
* See here for how to use with annotations.
* See here for how to declare it at package level when using annotations.
* See here for a bug in Oracle 10.2.0.0 to 11.1.0.7 that may prevent a correct working of this code.

I am using a legacy Oracle database where the values of the primary keys are assigned from a sequence using a "before insert" trigger.
However I couldn't find an id generator for this case: there is the SequenceIdentityGenerator in Hibernate (sequence-identity), but that one increments the sequence itself by doing "insert (sequence.nextval, ...) returning id". In my case, the sequence is incremented by the trigger. So I had to do "insert (...) returning id" instead.

Therefore I have built my own id generator, based on the code of the sequence-identity generator (not extending it, because I don't require to access the sequence; the "before insert" trigger does the job with the sequence and set the id in the data before it is saved in the db).

As I have seen a few people in this forum with a similar requirement, I'd like to share the code of this id generator with you.
You can copy it and modify freely, but please keep my name in it.

Note: like the sequence-identity generator, it requires JDK 1.4 and an Oracle 10 JDBC driver (however I have tested it from a 10g driver connected to a 9i db and it works). The minimum version of the JDBC driver that supports the required feature is 10.2.0.1 (minimum recommended version is 10.2.0.3).

Code:
package jpl.hibernate.util;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.id.AbstractPostInsertGenerator;
import org.hibernate.id.IdentifierGeneratorFactory;
import org.hibernate.id.PostInsertIdentityPersister;
import org.hibernate.id.SequenceIdentityGenerator.NoCommentsInsert;
import org.hibernate.id.insert.AbstractReturningDelegate;
import org.hibernate.id.insert.IdentifierGeneratingInsert;
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate;
import org.hibernate.util.NamedGeneratedKeysHelper;

/**
* A generator with immediate retrieval through JDBC3 {@link java.sql.Connection#prepareStatement(String, String[]) getGeneratedKeys}.
* The value of the identity column must be set from a "before insert trigger"
* <p/>
* This generator only known to work with newer Oracle drivers compiled for
* JDK 1.4 (JDBC3). The minimum version is 10.2.0.1
* <p/>
* Note: Due to a bug in Oracle drivers, sql comments on these insert statements
* are completely disabled.
*
* @author Jean-Pol Landrain
*/
public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {

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

    public static class Delegate extends AbstractReturningDelegate {
        private final Dialect dialect;

        private final String[] keyColumns;

        public Delegate(PostInsertIdentityPersister persister, Dialect dialect) {
            super(persister);
            this.dialect = dialect;
            this.keyColumns = getPersister().getRootTableKeyColumnNames();
            if (keyColumns.length > 1) {
                throw new HibernateException("trigger assigned identity generator cannot be used with multi-column keys");
            }
        }

        public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
            NoCommentsInsert insert = new NoCommentsInsert(dialect);
            return insert;
        }

        protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException {
            return session.getBatcher().prepareStatement(insertSQL, keyColumns);
        }

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


To use it, in your mapping files:

Code:
        <id name="id"
            column="MECA_UID">
                <generator class="jpl.hibernate.util.TriggerAssignedIdentityGenerator" />
        </id>


You can add to this forum in case you find a bug.
Please, also let me know if it has helped you.

Cheers,
Jean-Pol.


Last edited by landrain on Fri Jan 21, 2011 10:42 am, edited 4 times in total.

Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 12:13 am 
Newbie

Joined: Mon Sep 17, 2007 11:42 pm
Posts: 16
Location: Auckland, New Zealand
Thanks greatly! works like magic!

Perhaps this could be incorporated into Hibernate for real? From the posts on the forum seems like this is quite a common problem with Oracle.

Here's the posts that brought me here:
http://forum.hibernate.org/viewtopic.ph ... 76#2364976


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 4:45 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
astubbs wrote:
Thanks greatly! works like magic!

Thanks for your feedback.

astubbs wrote:
Perhaps this could be incorporated into Hibernate for real? From the posts on the forum seems like this is quite a common problem with Oracle.

Yes, I have that feeling too. In fact, the "sequence identity" generator, available in Hibernate, was initially developed for such cases. Unfortunately, the way it's been done you cannot configure it to tell you don't want Hibernate to increment the sequence. Maybe a modification to this id generator would be sufficient, allowing its use without a sequence parameter. I had seen a request for something like this in the Hibernate Jira. So, it may come in a future release. Wait and see.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 8:59 am 
Newbie

Joined: Mon Sep 17, 2007 11:42 pm
Posts: 16
Location: Auckland, New Zealand
actually, to get it to work with the Hibernate trunk I had to change a line
from
return IdentifierGeneratorFactory.getGeneratedIdentity(NamedGeneratedKeysHelper.getGeneratedKey(insert),
getPersister().getIdentifierType());
to
return IdentifierGeneratorFactory.getGeneratedIdentity(insert
.getGeneratedKeys(), getPersister().getIdentifierType());

as they've removed the NamedGeneratedKeysHelper class. instead you just use insert.getGeneratedKeys(). does that mean anything to you?
I'm still testing this stuff out...


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 18, 2007 9:10 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
It's possible. This id generator had been developed for the version 3.2.2 of Hibernate. I haven't tested it with a more recent version so far.
Thanks for your adaptation to the latest version.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 14, 2008 5:40 pm 
Newbie

Joined: Mon Nov 26, 2007 11:41 am
Posts: 6
Thanks for the code, It is exactly what I needed... but I have a problem:

I'm using hibernate 3.2.5ga, Oracle Database 10g Express Edition Release 10.2.0.1.0, Oracle JDBC driver, version: 10.1.0.4.2 and this generator seems to not work.

Code:
14 gen 2008 22:15:25,991  WARN [Portal Admin] JDBCExceptionReporter  - SQL Error: 17023, SQLState: null
14 gen 2008 22:15:25,992 ERROR [Portal Admin] JDBCExceptionReporter  - Function unsupported
org.hibernate.exception.GenericJDBCException: could not insert: ..............


this is my hbm

Code:
<id name="id" type="big_decimal">
    <column name="ID" precision="22" scale="0" />
    <generator class="TriggerAssignedIdentityGenerator" />
</id>


what's wrong?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 15, 2008 5:18 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
mario.casola wrote:
JDBCExceptionReporter - Function unsupported
what's wrong?


You need a version 10.2.x.x of the JDBC driver.
See http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/readme_10201.html:
Quote:
DML Returning
Can you believe it! After all these years of saying "next
release", DML returning is now supported in the Thin and OCI
drivers. DML returning is a SQL language feature that permits SQL
DMS statements (insert, update, delete) to return result
sets. This is especially advantageous to return columns modified
by triggers or autogenerated keys.


I recommend 10.2.0.3, as the previous versions have a long list of bugs (and more memory leaks than the 10.2.0.3)

Cheers,
Jean-Pol.

P.S.: I'm also using it with Hibernate 3.2.5ga now. No modification to the code has been necessary.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jan 15, 2008 7:25 am 
Newbie

Joined: Mon Nov 26, 2007 11:41 am
Posts: 6
Thanks for the answer, now works fine :-)

I had to make a change to the method IdentifierGeneratorFactory.get(ResultSet rs, Type type) because BigDecimal is not supported, obviously I rewrote my IdentifierGeneratorFactory.

thanks again
Mario


Top
 Profile  
 
 Post subject: populating surrogate key with trigger
PostPosted: Mon Feb 25, 2008 1:36 am 
Newbie

Joined: Mon Feb 25, 2008 1:27 am
Posts: 1
May I suggest that if you have the option, you would be much better off removing the trigger and populating on the insert with returning option.

Else for each row you have two context switches, one from the insert to pl/sql to execute the trigger and back to sql to get the sequence value.

If this is a low activity table it might not be worth the trouble, but this is a very expensive way to populate the primary key from a sequence.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 29, 2008 1:08 pm 
Newbie

Joined: Fri Feb 29, 2008 1:07 pm
Posts: 5
Maybe this http://sabbour.wordpress.com/2007/04/22 ... e-madness/
could solve the original problem too...


Top
 Profile  
 
 Post subject: Re: populating surrogate key with trigger
PostPosted: Mon Mar 10, 2008 6:10 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
Jim Schmidt wrote:
May I suggest that if you have the option [...]


Of course, that's the ideal solution. But here we are talking about a solution for legacy databases, meaning you usually don't have this kind of option (for example, because other applications are also using it and you don't want to impact them).

Cheers,
JP


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 10, 2008 6:16 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
maxxyme wrote:
Maybe this http://sabbour.wordpress.com/2007/04/22 ... e-madness/
could solve the original problem too...


Yes, probably. But, in this case, modifying the trigger is a workaround put in place because the Hibernate code doesn't use the correct identity generator. If you use the identity generator described here, you won't have to modify the trigger and everything will work fine.

JP


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 11, 2008 9:56 am 
Newbie

Joined: Tue Mar 11, 2008 9:51 am
Posts: 1
hy i wanted to try out the TriggerAssignedIdentityGenerator class. i'm not exactly sure how to use it.
can you give me an example how i can call this class with annotations?

where do i define the name of the trigger to use?

thanks


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 11, 2008 11:29 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
mcweba wrote:
can you give me an example how i can call this class with annotations?


I haven't tried it, because I don't use annotations, but I think this will work. Add this code on the methods to retrieve the unique identifiers in your classes:

Code:
@Id @GeneratedValue(generator="triggerAssigned")
@GenericGenerator(name="triggerAssigned",
  strategy = "jpl.hibernate.util.TriggerAssignedIdentityGenerator"
)
@Column(name="MECA_UID")
public Long getId() { ... }


Note: of course, the @GenericGenerator is a Hibernate annotation from the @org.hibernate.annotations package. It is not JPA.

mcweba wrote:
where do i define the name of the trigger to use?


You don't define it. The generator doesn't need to know the name of the trigger because it doesn't access it. It works in this way:

- Hibernate inserts the values of the persistent object into the database. To do this, it uses a special type of "insert" statement that is able to return the value of a specified column; thus this id generator will return the value of the id column (it can return only one value, so it won't work with "composite ids", that are ids made of multiple table columns).
Note that no additional sql statement other than the initial "insert" will be executed, but this requires a JDBC level 3 driver in order to work, because this special kind of "insert" is a JDBC 3 extension (only available in Oracle since the driver 10.2.0.1).
- Because of the "insert" statement, the trigger (which is of type "before insert") is executed. The trigger sets the value in the id column and then the data is stored in the database.
- The id generator gets that value (it gets it as result from the special insert statement) and updates the persistent object with it.

An example of a "before insert" trigger:

Code:
CREATE OR REPLACE TRIGGER "SCHEMA_NAME".TG_MEETING_CATEGORY
BEFORE INSERT
ON MEETING_CATEGORY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT SEQ_MEETING_CATEGORY.NEXTVAL INTO :NEW.MECA_UID FROM DUAL;
END TG_MEETING_CATEGORY;


That's it. I hope it helps.

Jean-Pol.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 20, 2008 11:29 am 
Newbie

Joined: Thu Mar 20, 2008 11:14 am
Posts: 2
Thx a lot landrain, this is what I looked for.
The problem was only, that is is required to work as schema owner with your solution.
This because {@link java.sql.Connection#prepareStatement(String, String[]) getGeneratedKeys} whants to get the returntype from the database meta.
I work with Oracle 10g and JDBC 10.2.0.3.

I modified your solution that it also works also without connection as schema owner.
The bad thing is that I had to work directly with java.sql and oracle.jdbc.
Maybe someone has a better solution?

Code:
package org.hibernate.id;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleTypes;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.id.SequenceIdentityGenerator.NoCommentsInsert;
import org.hibernate.id.insert.AbstractReturningDelegate;
import org.hibernate.id.insert.IdentifierGeneratingInsert;
import org.hibernate.id.insert.InsertGeneratedIdentifierDelegate;

/**
* A generator with immediate retrieval through JDBC3
* {@link java.sql.Connection#prepareStatement(String)}. The value of the identity column must be
* set from a "before insert trigger" <p/> This generator only known to work with newer Oracle
* drivers compiled for JDK 1.4 (JDBC3). The minimum version is 10.2.0.1 <p/> Note: Due to a bug in
* Oracle drivers, sql comments on these insert statements are completely disabled.
*
* This class dos not use the method
* {@link java.sql.Connection#prepareStatement(String, String[]) getGeneratedKeys} because with this
* method the driver wants get the return type form the database meta. If the application user is
* not the shemaowner, this is not possible. This is the reason why the ReturnParameter is hard set
* to Long.
*
* @author Jean-Pol Landrain
* @author Beat Sager
*/
public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {
    private static final String RETURN_PREFIX = " RETURNING ";
    private static final String RETURN_SUFFIX = " INTO ?";
    private static final String Q_MARK = "?";

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

    public static class Delegate extends AbstractReturningDelegate {
        private final Dialect dialect;
        private final String sqlSuffix;

        public Delegate(PostInsertIdentityPersister persister, Dialect dialect) {
            super(persister);
            this.dialect = dialect;
            String[] keyColumns = getPersister().getRootTableKeyColumnNames();
            if (keyColumns.length > 1) {
                throw new HibernateException(
                        "trigger assigned identity generator cannot be used with multi-column keys");
            }
            sqlSuffix = new StringBuffer(26).append(RETURN_PREFIX).append(keyColumns[0]).append(
                    RETURN_SUFFIX).toString();
        }

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

        protected PreparedStatement prepare(String insertSQL, SessionImplementor session)
                throws SQLException {
            insertSQL += sqlSuffix;
            OraclePreparedStatement stmt = (OraclePreparedStatement) session.getBatcher()
                    .prepareStatement(insertSQL);
            stmt.registerReturnParameter(StringUtils.countMatches(insertSQL, Q_MARK),
                    OracleTypes.NUMBER);
            return stmt;
        }

        protected Serializable executeAndExtract(PreparedStatement statement) throws SQLException {
            OraclePreparedStatement insert = (OraclePreparedStatement) statement;
            if (insert.executeUpdate() > 0) {
                ResultSet resultSet = insert.getReturnResultSet();
                if (resultSet.next()) {
                    return new Long(resultSet.getLong(1));
                }
            }
            throw new SQLException("Returned no id in resultset");
        }
    }
}


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 54 posts ]  Go to page 1, 2, 3, 4  Next

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.