Customize ID generator to work on different legacy database

simonzee · **Joined:** Tue Apr 16, 2013 6:34 pm **Posts:** 5

Hello Hibernate Experts,

Here is my situation.

I have a new web application working with two different types of legacy database, SQLServer2008 and Oracle11g. Hibernate 4.1.11 (and 4.0) is the ORM I am using.
I have entity defined as

@Table(name = "foo")
public class Foo {
private Integer id;
private String name;

@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
public Integer getId() {
return id;
}
public void setId(final Integer i) {
id = i;
}

@Basic
@Column(name = "name")
public String getName() {
return name;
}
public void setName(final String s) {
Name = s;
}
}

In Oracle, the table is created as

CREATE TABLE foo(
id NUMBER(10, 0) NOT NULL,
name varchar2( 255 char) NULL
);
CREATE SEQUENCE foo_SEQ START WITH 1 INCREMENT BY 1;

The foo_SEQ is used by a trigger to assign id value upon insertion.

In SQLServer, the table is created as

CREATE TABLE foo (
Id int IDENTITY(1,1) NOT NULL,
Name nvarchar(255) NULL
)

The code works on fine both SQLServe and Oracle except while working with Oracle, it is using ‘hibernate_sequence’ vs foo_SEQ. ‘hibenate_sequence’ is the hardcoded sequence used in Hibernate SequenceGenerator.

Since both are legacy databases, I can’t change Oracle database to use ‘hibernate_sequence’ as sequence name. Neither can I change how id is generated in SQLServer.

Currently my workround is to modify SequenceGenerator and build my own Hibernate from its source code. It works but not my preferred way as I have to remember this when upgrading to new Hibernate version.

Ideally, I would like to be able to register ‘SequenceGenerator’ with my own implemention and let the framework run as is. But I could not find a way to overwrite the default SequenceGeneator

Or I could write a customized MyIdentifierGenerator and annotated my entities with it, something like

Public MyIndentifierGenerator implements IndentifierGenrator {
@Override
public Serializable generate(final SessionImplementor session,final Object obj) {
if (this is SQLServer) {
use Hibernate IdentityGenerato to generate
} else {
Use MySequenceGenerator to generate
}
}
}

I did lots research and couldn’t figure a way to make a working class like this. There are errors thrown out from Hibernate framework which is not customizable (to me).

I am wondering if anyone could shed some lights on this.

Thanks,
Simon

Akash Mittal · **Joined:** Wed Apr 10, 2013 1:02 am **Posts:** 50

Hi,

What exactly is your question

simonzee · **Joined:** Tue Apr 16, 2013 6:34 pm **Posts:** 5

Sorry for the unclearness. My questions are:

1. Is it possible to replace Hibernate's default SequenceGenerator with my customized implementation (via configuration vs what I did to modify source code)?

2. How to implement a IndentifierGenerator that behaves like Hibernate's IdentityGenerator while underlying database is SQLServer and like SequenceGenerator (with customized behavior) while underlying database is Oracle. This IndentifierGenerator will be annoated as @GenericGenerator to entities.

Thanks,
Simon

Akash Mittal wrote:

Hi,

What exactly is your question

Akash Mittal · **Joined:** Wed Apr 10, 2013 1:02 am **Posts:** 50

Hey man i have not tried the custom ID generator.
A quick suggestion .. why cant you create a sequence/identity in oracle/mysql and provide name to hibernate... what say...

Let me know if it works else i try and implement custom ID .....

simonzee · **Joined:** Tue Apr 16, 2013 6:34 pm **Posts:** 5

Akash,

As I mentioned earlier, the databases are legacy. The Oracle one has its own sequence names and an trigger is used for creating IDs with those sequences. The SQLServer one has ID defined as identity. I can't change this.

I prefer one application code base for both databases, which means I either
1) define ID generation strategy as AUTO to let hibernate decide which ID generator to use. Hibernate did pickup SequenceGenerator correctly. The issue with me is that Hibernate is using default "hibernate_sequence" as sequence name while no sequence name is given in Entity annotation.
As my example code indicate, while using AUTO strategy, there is no sequence name is necessary (BTW, I didn't try to add @SequenceGenerator while defining AUTO as strategy which I will try shortly). Thus I have to customize the SequenceGenerator to use the legacy sequence names.
However, i couldn't fine a way to plug in my customization implementation besides changing Hibernate source code and build the whole core package.
2) define ID generation as GenericGenerator with my own implemntation strategy. In the implementation, the ID generation logic would be different depending on the which database it is dealing with. But I could only make it work for Sequence but not Identity as it is postInsert by framework. I would preferred this way if possible since it can live with my own application instead of Hiberante source.

Thanks,
Simon

Akash Mittal wrote:

Hey man i have not tried the custom ID generator.
A quick suggestion .. why cant you create a sequence/identity in oracle/mysql and provide name to hibernate... what say...

Let me know if it works else i try and implement custom ID .....

Akash Mittal · **Joined:** Wed Apr 10, 2013 1:02 am **Posts:** 50

OK.... No i am getting the flavor

1st Issue : Is to pass the name of sequence/identity already created in DB to hibernate
2nd Issue : Hibernate should switch bw seq/idenity as application hits any of the D/B
(pls correct me if m getting it wrong...:))

Hmmmm first i tried creating a sequence in oracle

Code:

CREATE SEQUENCE AKASH.SAMPLE_SEQ
  START WITH 21
  MAXVALUE 99999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

and gave the name to hibernate

Code:

  <id name="clockID" type="java.lang.Integer">
   <column name="CLOCKID"/>
   <generator class="sequence">
   <param name="sequence">sample_Seq</param>
   </generator>
  </id>

hibernate picked the sequence and worked as expected

Code:

INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select sample_Seq.nextval from dual
Hibernate: select sample_Seq.nextval from dual
Hibernate: select sample_Seq.nextval from dual
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
--------------------Fetching Values From the Database---------------------------
Hibernate: select clock0_.CLOCKID as CLOCKID0_, clock0_.CLOCKNAME as CLOCKNAME0_, clock0_.ISINDIGLO as ISINDIGLO0_ from CLOCK clock0_
Clock [clockID=4, clockName=Timex WR30M, isIndiglo=false]
Clock [clockID=5, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=6, clockName=Timex TR61M, isIndiglo=true]

I think i was able to simulate the 1st issue ... I am trying to work on 2nd issue in the meantime please pass onto your comments...

Akash Mittal · **Joined:** Wed Apr 10, 2013 1:02 am **Posts:** 50

Well i here is the solution to the second issue...

Code:

/** @author Akash Mittal
    @version 1.0
   Project:Hibernate4-XML-Oracle-Custom-Generator-Classes
   Package:com.akash.ID.generator.seqhilo 
   File Nam:MyIDGenerator.java
    Last Updated 19-Apr-2013
    Purpose: 
 */

package com.akash.ID.generator.seqhilo;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.IdentifierGenerator;




public class MyIDGenerator implements IdentifierGenerator{

   @Override
   public Serializable generate(SessionImplementor sessionImplementor, Object object)
         throws HibernateException {
      
      Connection connection=    sessionImplementor.connection();
      String databaseName ="";
      try {
         databaseName = connection.getMetaData().getDatabaseProductName();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      System.out.println("Hitting Database "+databaseName);
      if(databaseName.equals("Oracle")){
         // Code for Sequence
         try {
            PreparedStatement ps = connection
                    .prepareStatement("select sample_Seq.nextval as next_value from dual");
            ResultSet rs = ps.executeQuery();
               if (rs.next()) {
                   int id = rs.getInt("next_value");
                   System.err.println("Generated sequence nect val : " + id);
                   return id;
               }
         } catch (SQLException e) {
            e.printStackTrace();
            return null;
         }
      }
      if(databaseName.equals("Sql")){
         // Code for Identity
      }
      return null;
   }
}

and the configuration file

Code:

 <id name="clockID" type="java.lang.Integer">
   <column name="CLOCKID"/>
   <generator class="com.akash.ID.generator.seqhilo.MyIDGenerator">
   </generator>
  </id>

hope you can annotate it...:)

and output seems no less than perfect

Code:

INFO: HHH000397: Using ASTQueryTranslatorFactory
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 21
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 22
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 23
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
--------------------Fetching Values From the Database---------------------------
Hibernate: select clock0_.CLOCKID as CLOCKID0_, clock0_.CLOCKNAME as CLOCKNAME0_, clock0_.ISINDIGLO as ISINDIGLO0_ from CLOCK clock0_
Clock [clockID=22, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=23, clockName=Timex TR61M, isIndiglo=true]
Clock [clockID=18, clockName=Timex WR30M, isIndiglo=false]
Clock [clockID=19, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=20, clockName=Timex TR61M, isIndiglo=true]
Clock [clockID=21, clockName=Timex WR30M, isIndiglo=false]

simonzee · **Joined:** Tue Apr 16, 2013 6:34 pm **Posts:** 5

Akash,

Thanks for the reply. The solution you have here only works for Oracle. The same code/configuration won't work for SQLServer because the entity is associated with sequence generator not identity generator. My goal is to use one set of annoation for both database.

I only need to past name of sequence (concatenateion of tablename and '_SEQ') to Hibernate when it is Oracle database. There is no need to pass anything for SQLServer database. Hibernate's existing implementation works just fine.

Thanks,
Simon

Akash Mittal wrote:

OK.... No i am getting the flavor

1st Issue : Is to pass the name of sequence/identity already created in DB to hibernate
2nd Issue : Hibernate should switch bw seq/idenity as application hits any of the D/B
(pls correct me if m getting it wrong...:))

Hmmmm first i tried creating a sequence in oracle

Code:

CREATE SEQUENCE AKASH.SAMPLE_SEQ
  START WITH 21
  MAXVALUE 99999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

and gave the name to hibernate

Code:

  <id name="clockID" type="java.lang.Integer">
   <column name="CLOCKID"/>
   <generator class="sequence">
   <param name="sequence">sample_Seq</param>
   </generator>
  </id>

hibernate picked the sequence and worked as expected

Code:

INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select sample_Seq.nextval from dual
Hibernate: select sample_Seq.nextval from dual
Hibernate: select sample_Seq.nextval from dual
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
--------------------Fetching Values From the Database---------------------------
Hibernate: select clock0_.CLOCKID as CLOCKID0_, clock0_.CLOCKNAME as CLOCKNAME0_, clock0_.ISINDIGLO as ISINDIGLO0_ from CLOCK clock0_
Clock [clockID=4, clockName=Timex WR30M, isIndiglo=false]
Clock [clockID=5, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=6, clockName=Timex TR61M, isIndiglo=true]

I think i was able to simulate the 1st issue ... I am trying to work on 2nd issue in the meantime please pass onto your comments...

simonzee · **Joined:** Tue Apr 16, 2013 6:34 pm **Posts:** 5

Akash,

As I mentioned in previous post, I already make the sequence works in the customized IdentifierGenerator implementation. The part hard to achieve is how to make it work for SQLServer which happens to be the missing puzzle in your example code. The IndentityGenerator for SQLServer is post-Insert which is different from SequenceGenerator. I am not able to make it work, probably due to that I don't quite understand how Hibernate handles this case. If you can provide some thoughts/codes on that part, it will be greatly appreciated.

Thanks,
Simon

Akash Mittal wrote:

Well i here is the solution to the second issue...

Code:

/** @author Akash Mittal
    @version 1.0
   Project:Hibernate4-XML-Oracle-Custom-Generator-Classes
   Package:com.akash.ID.generator.seqhilo 
   File Nam:MyIDGenerator.java
    Last Updated 19-Apr-2013
    Purpose: 
 */

package com.akash.ID.generator.seqhilo;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.id.IdentifierGenerator;




public class MyIDGenerator implements IdentifierGenerator{

   @Override
   public Serializable generate(SessionImplementor sessionImplementor, Object object)
         throws HibernateException {
      
      Connection connection=    sessionImplementor.connection();
      String databaseName ="";
      try {
         databaseName = connection.getMetaData().getDatabaseProductName();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      System.out.println("Hitting Database "+databaseName);
      if(databaseName.equals("Oracle")){
         // Code for Sequence
         try {
            PreparedStatement ps = connection
                    .prepareStatement("select sample_Seq.nextval as next_value from dual");
            ResultSet rs = ps.executeQuery();
               if (rs.next()) {
                   int id = rs.getInt("next_value");
                   System.err.println("Generated sequence nect val : " + id);
                   return id;
               }
         } catch (SQLException e) {
            e.printStackTrace();
            return null;
         }
      }
      if(databaseName.equals("Sql")){
         // Code for Identity
      }
      return null;
   }
}

and the configuration file

Code:

 <id name="clockID" type="java.lang.Integer">
   <column name="CLOCKID"/>
   <generator class="com.akash.ID.generator.seqhilo.MyIDGenerator">
   </generator>
  </id>

hope you can annotate it...:)

and output seems no less than perfect

Code:

INFO: HHH000397: Using ASTQueryTranslatorFactory
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 21
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 22
Hitting Database Oracle
Hibernate: select sample_Seq.nextval as next_value from dual
Generated sequence nect val : 23
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
Hibernate: insert into CLOCK (CLOCKNAME, ISINDIGLO, CLOCKID) values (?, ?, ?)
--------------------Fetching Values From the Database---------------------------
Hibernate: select clock0_.CLOCKID as CLOCKID0_, clock0_.CLOCKNAME as CLOCKNAME0_, clock0_.ISINDIGLO as ISINDIGLO0_ from CLOCK clock0_
Clock [clockID=22, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=23, clockName=Timex TR61M, isIndiglo=true]
Clock [clockID=18, clockName=Timex WR30M, isIndiglo=false]
Clock [clockID=19, clockName=Timex SR51M, isIndiglo=true]
Clock [clockID=20, clockName=Timex TR61M, isIndiglo=true]
Clock [clockID=21, clockName=Timex WR30M, isIndiglo=false]