How to write Custom class for Primary Key Generation.

Krishn · **Joined:** Sun Sep 07, 2008 10:14 am **Posts:** 1

Hi,

Please help me how to write a custom class for primary key generation?

I have only one column called "Id" (I don't have composite key).

Please help me with small example(if possible).

thank you.

riccardob · **Posted:** Mon Sep 08, 2008 9:48 am

Hi, I've write a custom Pk generator,
Simply following and customizing the source of Hibernate

Code:

org.hibernate.id.MultipleHiLoPerTableGenerator

I've just done it because I need a composite primary key generator.
below the code I've customized.

Code:

public class MyHiLoIdGenerator 
   extends TransactionHelper
   implements PersistentIdentifierGenerator, Configurable {
   
   private static final Logger log = LoggerFactory.getLogger(SigeproHiLoIdGenerator.class);
   
   public static final String ID_TABLE = "table";
   public static final String PK_COLUMN_NAME = "primary_key_column";
   public static final String PK2_COLUMN = "primary_key_column2";
   public static final String PK_VALUE_NAME = "primary_key_value";
   public static final String VALUE_COLUMN_NAME = "value_column";
   public static final String PK_LENGTH_NAME = "primary_key_length";

   private static final int DEFAULT_PK_LENGTH = 255;
   public static final String DEFAULT_TABLE = "hibernate_sequences";
   private static final String DEFAULT_PK_COLUMN = "sequence_name";
   private static final String DEFAULT_PK2_COLUMN = "business_key_2";
   private static final String DEFAULT_VALUE_COLUMN = "sequence_next_hi_value";
   
   private String tableName;
   private String pkColumnName;
   private String business_key_2;
   private String valueColumnName;
   private String query;
   private String insert;
   private String update;

   //hilo params
   public static final String MAX_LO = "max_lo";

   private long hi;
   private int lo;
   private int maxLo;
   private Class returnClass;
   private int keySize;


   public String[] sqlCreateStrings(Dialect dialect) throws HibernateException {
      return new String[] {
         new StringBuffer( dialect.getCreateTableString() )
               .append( ' ' )
               .append( tableName )
               .append( " ( " )
               .append( pkColumnName )
               .append( ' ' )
               .append( dialect.getTypeName( Types.VARCHAR, keySize, 0, 0 ) )
               .append( ",  " )
               
               .append( business_key_2 )
               .append( ' ' )
               .append( dialect.getTypeName( Types.VARCHAR, keySize, 0, 0 ) )
               .append( ",  " )
               
               .append( valueColumnName )
               .append( ' ' )
               .append( dialect.getTypeName( Types.INTEGER ) )
               .append( " ) " )
               .toString()
      };
   }

   public String[] sqlDropStrings(Dialect dialect) throws HibernateException {
      StringBuffer sqlDropString = new StringBuffer( "drop table " );
      if ( dialect.supportsIfExistsBeforeTableName() ) {
         sqlDropString.append( "if exists " );
      }
      sqlDropString.append( tableName ).append( dialect.getCascadeConstraintsString() );
      if ( dialect.supportsIfExistsAfterTableName() ) {
         sqlDropString.append( " if exists" );
      }
      return new String[] { sqlDropString.toString() };
   }

   public Object generatorKey() {
      return tableName;
   }

   public Serializable doWorkInCurrentTransaction(Connection conn, String sql) throws SQLException {
      int result;
      int rows;
      do {
         // The loop ensures atomicity of the
         // select + update even for no transaction
         // or read committed isolation level

         //sql = query;
         //SQL_STATEMENT_LOGGER.logStatement( sql, FormatStyle.BASIC );
         PreparedStatement qps = conn.prepareStatement(query);
         PreparedStatement ips = null;
         try {
            //qps.setString(1, ORMHelper.getProperties().getProperty("business_key_2"));
            qps.setString(1, ORMHelper.getBusiness_key_2());
            ResultSet rs = qps.executeQuery();
            boolean isInitialized = rs.next();
            if ( !isInitialized ) {
               result = 0;
               ips = conn.prepareStatement(insert);
               //ips.setString(1, key);
               ips.setInt(1, result);
               //ips.setString(2, ORMHelper.getProperties().getProperty("business_key_2"));
               ips.setString(2, ORMHelper.getBusiness_key_2());
               ips.execute();
            }
            else {
               result = rs.getInt(1);
            }
            rs.close();
         }
         catch (SQLException sqle) {
            log.error("could not read or init a hi value", sqle);
            throw sqle;
         }
         finally {
            if (ips != null) {
               ips.close();
            }
            qps.close();
         }

         //sql = update;
         PreparedStatement ups = conn.prepareStatement(update);
         try {
            ups.setInt( 1, result + 1 );
            ups.setInt( 2, result );
            ups.setString( 3, ORMHelper.getBusiness_key_2() );
            rows = ups.executeUpdate();
         }
         catch (SQLException sqle) {
            log.error("could not update hi value in: " + tableName, sqle);
            throw sqle;
         }
         finally {
            ups.close();
         }
      }
      while (rows==0);
      return new Integer(result);
   }

   public synchronized Serializable generate(SessionImplementor session, Object obj)
      throws HibernateException {
      PkId genericId = new PkId();
      
      if (maxLo < 1) {
         //keep the behavior consistent even for boundary usages
         int val = ( (Integer) doWorkInNewTransaction(session) ).intValue();
         if (val == 0) val = ( (Integer) doWorkInNewTransaction(session) ).intValue();
         genericId.setCodice((Integer)IdentifierGeneratorFactory.createNumber( hi + lo++, Integer.class ));
         genericId.setIdcomune(ORMHelper.getBusiness_key_2());
         return genericId;
      }
      if (lo>maxLo) {
         int hival = ( (Integer) doWorkInNewTransaction(session) ).intValue();
         lo = (hival == 0) ? 1 : 0;
         hi = hival * (maxLo+1);
         log.debug("new hi value: " + hival);
      }
      
      
      genericId.setCodice((Integer)IdentifierGeneratorFactory.createNumber( hi + lo++, Integer.class ));
      //genericId.setIdcomune(ORMHelper.getProperties().getProperty("business_key_2"));
      genericId.setIdcomune(ORMHelper.getBusiness_key_2());
      return genericId;
      
   }

   public void configure(Type type, Properties params, Dialect dialect) throws MappingException {
      tableName = PropertiesHelper.getString(ID_TABLE, params, DEFAULT_TABLE);
      pkColumnName = PropertiesHelper.getString(PK_COLUMN_NAME, params, DEFAULT_PK_COLUMN);
      business_key_2 = PropertiesHelper.getString(PK2_COLUMN, params, DEFAULT_PK2_COLUMN);
      valueColumnName = PropertiesHelper.getString(VALUE_COLUMN_NAME, params, DEFAULT_VALUE_COLUMN);
      String schemaName = params.getProperty(SCHEMA);
      String catalogName = params.getProperty(CATALOG);
      keySize = PropertiesHelper.getInt(PK_LENGTH_NAME, params, DEFAULT_PK_LENGTH);
      String keyValue = PropertiesHelper.getString(PK_VALUE_NAME, params, params.getProperty(TABLE) );

      System.out.println(sqlCreateStrings(dialect)[0]);
      
      if ( tableName.indexOf( '.' )<0 ) {
         tableName = Table.qualify( catalogName, schemaName, tableName );
      }

      query = "select " +
         valueColumnName +
         " from " +
         dialect.appendLockHint(LockMode.UPGRADE, tableName) +
         " where " + pkColumnName + " = '" + keyValue + "'" +
         " and " + business_key_2 + " = ? " +
         dialect.getForUpdateString();

      update = "update " +
         tableName +
         " set " +
         valueColumnName +
         " = ? where " +
         valueColumnName +
         " = ? and " +
         pkColumnName +
         " = '" + 
         keyValue 
         + "'" +
         " and " + business_key_2 + " = ? ";
      
      insert = "insert into " + tableName +
         "(" + pkColumnName + ", " +   valueColumnName + " , "+business_key_2 +") " +
         "values('"+ keyValue +"', ?, ? )";


      //hilo config
      maxLo = PropertiesHelper.getInt(MAX_LO, params, Short.MAX_VALUE);
      lo = maxLo + 1; // so we "clock over" on the first invocation
      returnClass = type.getReturnedClass();
   }
}

As a result I'have a table (hibernate_sequences)so defined

Code:

SEQUENCE_NAME                           VARCHAR2(255 CHAR)                                                                                                                                                                            
PRIMARY_KEY_COLUMN2                                VARCHAR2(255 CHAR)                                                                                                                                                                            
SEQUENCE_NEXT_HI_VALUE                  NUMBER(10)

I think, that starting from the source of the IdGenerator in the package
org.hibernate.id.* you'll be able to create one of your own, instead of using the defaults.

hope this help.

Cameron McKenzie · **Posted:** Tue Sep 09, 2008 8:18 pm

It's a great exercise, but is there a reason why you're not using the standard key generation tool of your database? Writing your own key generator is a challenging and fun exercise, but most databases provide great primary key generation support. The @GeneratedValue options is also very portable across databases.

Tutorial: Using Primary Key Generators with Hibernate and JPA Annotations

Quote:

@GeneratedValue JPA Annotation

Quite often in these tutorials, we have used the @GeneratedValue annotation to have the database generate a unique primary key for us. We have used the default Generation Type in each of our examples, but there are actually four different strategies for having the primary key generated by the database. Those four options are:

* AUTO
* IDENTITY
* TABLE
* SEQUENCE

javax.persistence.GenerationType.AUTO

The AUTO generation strategy is the default, and this setting simply chooses the primary key generation strategy that is the default for the database in question, which quite typically is IDENTITY, although it might be TABLE or SEQUENCE depending upon how the database is configured. The AUTO strategy is typically recommended, as it makes your code and your applications most portable.
javax.persistence.GenerationType.IDENTITY

The IDENTITY option simply allows the database to generate a unique primary key for your application. No sequence or table is used to maintain the primary key information, but instead, the database will just pick an appropriate, unique number for Hibernate to assign to the primary key of the entity. With MySQL, the first lowest numbered primary key available in the table in question is chosen, although this behavior may differ from database to database.
javax.persistence.GenerationType.Sequence

Some database vendors support the use of a database sequence object for maintaining primary keys. To use a sequence, you set the GenerationType strategy to SEQUENCE, specify the name of the generator annotation, and then provide the @SequenceGenerator annotation that has attributes for defining both the name of the sequence annotation, and the name of the actual sequence object in the database.
Here's what the getId() method of the Snafu class would look like if we used a SEQUENCE GenerationType:

@Id
@SequenceGenerator(name="s1", sequenceName="SEQ")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="s1")
public long getId() {return id;}

javax.persistence.GenerationType.TABLE

The TABLE GenerationType allocates a separate database table to keep track of the generation of unique ids. To facilitate the description of the table to be used, the TABLE strategy works hand in hand with the @TableGenerator annotation. So, if our Snafu class was to use a separate table for managing primary keys, the getId() method annotation would look like this:

@Id
@TableGenerator(name="tg", table="pk_table",
pkColumnName="name", valueColumnName="value",
allocationSize=10
)@GeneratedValue(strategy=GenerationType.TABLE, generator="tg")
public long getId() {return id;}

With this @TableGenerator annotation, a separate table in the database named pk_table would be created with two columns, one called name, and the other called value. The name will simply be Snafu, the name of the class using the table, indicating the class for which the key is being maintained. The pkColumnValue will maintain the current iteration of key generation. Furthermore, the allocationSize determines the increment size of the generated primary keys for a new thread.
Using the @TableGenerator Annotation

So, if we recreated the database using the @TableGenerator annotation,and then added three Snafu objects to the database in separate threads, a pk_table would maintain a database column with the name of Snafu, indicating through the value column that three iterations of primary key generations would have occurred. Furthermore, the Snafu table would have three records, with the primary keys all incrementing to the nearest hundred value.

aappddeevv · **Joined:** Wed May 31, 2006 7:47 am **Posts:** 6

Perhaps JPA is not being used. Also, the table generator described may not match the key sequence structures in the db. I think the original poster could also look at http://www.oreillynet.com/onjava/blog/2 ... rator.html for ideas on key generation to fit your needs. My legacy database had a pk table defined. Each row had the name of the table and the next value. This was slightly different than the id generators in hibernate at the time.

jaalex · **Joined:** Mon May 19, 2008 12:42 am **Posts:** 10

riccardob wrote:

Hi, I've write a custom Pk generator,
Simply following and customizing the source of Hibernate

Code:

org.hibernate.id.MultipleHiLoPerTableGenerator

I've just done it because I need a composite primary key generator.

Hi riccardob,
Can you please provide the hbm file too where this generator is linked to the composite PK? . In the Generator, are you generating the values of each column of the composite PK , or is it just one of them?