Require your help : Primary key generation

mac2006 · **Joined:** Tue Jul 04, 2006 9:40 am **Posts:** 4

Hibernate version: 3.1.3

We have a distributed system with operations in different locations. All of them use their local servers and database (MySQL). All these different systems need to synchronize their data with the central database every night. This means that the primary keys generation cannot be a simple identity strategy as it would result in conflicts. We are considering what would be the best option to take.

One of the strong candidates is providing each location with a unique id which would be prefixed to the id. Sounded good, so we got down to implementing it.. and immediately ran into problems.

I tried to create a custom IdentityGenerator which will do the job of gettingm the latest data and concatenating with its unique id - it somehow doesnt work :(

Code:

public class PrefixedNumericIdentityGenerator extends IdentityGenerator {
...
....
   public Serializable getResult(SessionImplementor session, ResultSet rs, Object object, PostInsertIdentityPersister persister) 
      throws SQLException 
   {
      String result = rs.getString(1);

      String strVal = prefix + result;
      
      if ( idClass==Long.class ) {
         Long longVal = new Long(strVal);
         return longVal;
      }
      else if ( idClass==Integer.class ) {
         Integer intVal = new Integer(strVal);
         return intVal;
      }

      else if ( idClass==Short.class ) {
         Short shortVal = new Short(strVal);
         return shortVal;
      }
      else
         throw new IdentifierGenerationException("this id generator generates long, integer or short ");

}

Config :

Code:

   <class name="Client" table="Client" >
      <id name="id" type="integer" unsaved-value="null" >
         <column name="id" not-null="true"/>
         <generator class="com.test.PrefixedNumericIdentityGenerator" >
            <param name="prefix">2005</param>
         </generator>
      </id>

x
Any inputs on how we could bring this off ?

Thanks[/code]

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Why not use a hilo generator, with the index-generating table on the central server?

Holy Joe · **Joined:** Wed Aug 23, 2006 10:11 am **Posts:** 9

Try a composite primary key, composed of the real key and another, constant column that contains the site id. I don't know if you can use auto key generation on the real key when you do that, but it's worth a try.

mac2006 · **Joined:** Tue Jul 04, 2006 9:40 am **Posts:** 4

tenwit wrote:

Why not use a hilo generator, with the index-generating table on the central server?

We cant do this because the central server is at a geographically different location. This raises two concerns :

1) potential security problems
2) such a call (which is bound to consume some time) for every entity that is getting persisted may not be such a great idea - from perspectives of scalability and performance.

Pl correct me if I am wrong.

Regards.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Fair enough, can't use hilo. The composite key solution is the best alternative. You can't use <generator> with composite-ids, but there are options. For example, if the central server is read-only (doesn't create new rows), then you can use two mapping files:

On the normal servers, use a normal id, one column, identity type. Use a separate column, normal property, for the other column that distinguishes which server the row was created on. Mark this column insert="false" update="false", and have the DB give it a constant value (e.g. the name of the server).
On the central server, use a composite-id, where both columns make up the id.

You don't need to do anytthing different in java code, except (ideally) cripple the app on the central server so that it never, ever creates rows on this sort of table.

Also, if MySQL supports it, there's the option of setting the identity range for each server to something non-overlapping, and not use a composite key. So server A has ID 1 to 1000000000, server B has 1000000001 to 2000000000, etc. SQLServer allows you to do this, maybe MySQL does too. Much easier, much easier to document, but a little icky.