-->
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 Previous  1, 2, 3, 4
Author Message
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Tue Jun 21, 2011 8:45 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
akud wrote:
I add small fixes to quoted identifiers, because Oracle driver throws exception on quoted identifier with this generator.

That's not specific to this generator! You're supposed to use backticks for quoted identifiers in Hibernate: http://docs.jboss.org/hibernate/core/3. ... dentifiers


Top
 Profile  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Fri Jun 01, 2012 7:31 am 
Newbie

Joined: Fri Jun 01, 2012 6:53 am
Posts: 1
Hi

I have BEFORE INSERT trigger in DB using Oracle sequence (sequnce is set if ID passed is NULL)
Now with this customer trigger generated class, things works fine (ID is getting aut-generated when I am calling session.persist(..) and when ID passed is NULL.

But there is another case... where have to update anothe data source with the same object, so calling session.replicate(...) and ID already generated. But here again new ID is been generated.

Can you please let you know, how to avoid generating new ID when ID is already passed.
Thanks in advance.


Top
 Profile  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Sun Dec 16, 2012 10:39 am 
Newbie

Joined: Sun Dec 16, 2012 10:37 am
Posts: 1
Is this can work for version 4.17.final?


Top
 Profile  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Tue Mar 19, 2013 12:07 pm 
Newbie

Joined: Tue Sep 04, 2012 9:00 am
Posts: 3
Hello,

I'm trying to update this class to be hibenate 4 compatible. However, I'm faced to the following problems :

Code:
return session.getBatcher().prepareStatement(insertSQL, keyColumns);


The getBatcher() is no more available from session, but apparently we have to take it from BuilderBatcher (https://community.jboss.org/wiki/HibernateCoreMigrationGuide40)
However how can I construct a BuilderBatcher ?

Code:
IdentifierGeneratorHelper.getGeneratedIdentity(insert.getGeneratedKeys(), getPersister().getIdentifierType());

the getGeneratedIdentity now need the resultset as the first parameter, but how can I provide it ?

Here is my class used
Code:
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.jdbc.batch.spi.BatchBuilder;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.AbstractPostInsertGenerator;

import org.hibernate.id.IdentifierGeneratorHelper;
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;


/**
* 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 {
           BatchBuilder bb =
            return session.getBatcher().prepareStatement(insertSQL, keyColumns);
        }

        protected Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
            insert.executeUpdate();
            return IdentifierGeneratorHelper.getGeneratedIdentity(insert.getGeneratedKeys(), getPersister().getIdentifierType());
        }
    }


Top
 Profile  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Thu Jul 25, 2013 12:34 pm 
Newbie

Joined: Thu Jul 25, 2013 11:01 am
Posts: 1
The following works fine for me with Hibernate 4.2.3.Final (hibernate-core, hibernate-entitymanager) and Oracle database 10 and ojdbc 11.1.0.7.0

Code:
/* ... */
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.AbstractPostInsertGenerator;
import org.hibernate.id.IdentifierGeneratorHelper;
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;

/* ... */
public class TriggerAssignedIdentityGenerator extends AbstractPostInsertGenerator {

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

   public static class Delegate extends AbstractReturningDelegate {

      private Dialect dialect;
      private 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");
         }
      }

      /**
       * {@inheritDoc}
       */
      @Override
      public IdentifierGeneratingInsert prepareIdentifierGeneratingInsert() {
         return new NoCommentsInsert(dialect);
      }

      /**
       * {@inheritDoc}
       */
      @Override
      protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException {
         return session.connection().prepareStatement(insertSQL, keyColumns);
      }

      /**
       * {@inheritDoc}
       */
      @Override
      protected Serializable executeAndExtract(PreparedStatement insert, SessionImplementor session)
         throws SQLException {
         insert.executeUpdate();
         ResultSet generatedKeys = insert.getGeneratedKeys();
         try {
            return IdentifierGeneratorHelper.getGeneratedIdentity(generatedKeys, keyColumns[0], getPersister()
               .getIdentifierType());
         } finally {
            generatedKeys.close();
         }
      }
   }
}


Top
 Profile  
 
 Post subject: Re: JPA, Hibernate 3.6.8, ojdbc14
PostPosted: Sun Oct 20, 2013 8:21 pm 
Newbie

Joined: Sun Oct 20, 2013 7:50 pm
Posts: 1
Thanks!

This appears to work fine with JPA 2, Hibernate 3.6.8, ojdbc14-14, Oracle 10g.

JPA annotations:
@Id
@GeneratedValue(generator = "triggerAssigned")
@GenericGenerator(name = "triggerAssigned", strategy = "com.mycompany.myproject.util.TriggerAssignedIdentityGenerator")
private long id;


I can now successfully call entityManager.persist on a new transient entity object.


Top
 Profile  
 
 Post subject: Re: JPA, Hibernate 3.6.8, ojdbc14
PostPosted: Wed Nov 20, 2013 6:08 am 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
jmslick wrote:
Thanks!

This appears to work fine with JPA 2, Hibernate 3.6.8, ojdbc14-14, Oracle 10g.
[...]


Did you notice this post a few pages above where I explain how to declare it globally for a java package ? http://forum.hibernate.org/viewtopic.php?f=1&t=973262&hilit=triggerassigned&start=30#p2432521

BTW thanks everyone for your contributions. It's really cool.


Top
 Profile  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Tue Aug 16, 2016 6:49 am 
3 years later ...
The code
protected PreparedStatement prepare(
...
return session.connection().prepareStatement(insertSQL, keyColumns);

will generate SQL ending by "... RETURNING my_id into ?"
and ipso facto the Oracle error:
ORA-22816: unsupported feature with RETURNING clause.
Oracle driver is ojdbc7-12.1.0.2

Is there a workaround or another way to reuse an existing trigger?


Top
  
 
 Post subject: Re: Before Insert Trigger and ID generator
PostPosted: Tue Feb 14, 2017 12:19 pm 
Beginner
Beginner

Joined: Thu Dec 23, 2004 5:24 am
Posts: 40
Location: Luxembourg
okilele wrote:
3 years later ...
The code
protected PreparedStatement prepare(
...
return session.connection().prepareStatement(insertSQL, keyColumns);

will generate SQL ending by "... RETURNING my_id into ?"
and ipso facto the Oracle error:
ORA-22816: unsupported feature with RETURNING clause.
Oracle driver is ojdbc7-12.1.0.2

Is there a workaround or another way to reuse an existing trigger?


Please, could you check these restrictions on the SQL generated by Hibernate:
Quote:
Restrictions

The following restrictions apply to the RETURNING clause:

The expr is restricted as follows:
o Each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause.
o Single-set aggregate function expressions cannot include the DISTINCT keyword.
o You cannot specify the returning_clause for a multitable insert.
o You cannot use this clause with parallel DML or with remote objects.
o You cannot retrieve LONG types with this clause.
o You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.


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 Previous  1, 2, 3, 4

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.