Using Hibernate sequence generators manually

Question

Basically, I want a way to access sequence values in a database-neutral way. The use case is that I have a field on an entity that I want to set based on an incrementing value (other than the id).

For instance, say I have a Shipment entity. At some point after the shipment gets created, it gets shipped. Once it gets shipped, a manifest number is generated for it and assigned. The manifest number looks something like M000009 (Where the stuff after the 'M' is a left-padded value from a sequence).

Something similar was asked here: http://stackoverflow.com/questions/277630/hibernate-jpa-sequence-non-id, but I'm not a fan of the solution since it requires another table to maintain and seems like a weird relationship to have.

Does anyone know if it is possible to use something like hibernate's MultipleHiLoPerTableGenerator as something other than an ID generator?

If that's not possible, does anyone know of any libraries that handle this (either using hibernate or even just pure JDBC). I'd prefer not to have to write this myself (and have to deal with prefetching values, locking and synchronization).

Thanks.

Answer 1

I didn't read over the linked similar solution, but sounds like something I wound up doing. I created a table just for sequences. I added a row to the table for each sequence type I needed.

I then had a sequence generator class that would do the necessary sql query to fetch and update the sequence value for a particular named sequence.

I used hibernate's Dialect class to do it in a db neutral way.

I also would 'cache' the sequences. I would bump the stored sequence value by a large number, and then dole those out those allocated sequences from my generator class. If the class was destroyed (ie. app shutdown), a new instance of the sequence generator would start up at the stored value. (having a gap in my sequence numbers did not matter)

Answer 2

Her is a code samnple. I would like to caveat this with - I have not comiled this and it reuires spring code. Having said this it should still provide the bones of what you want to do.

public Long getManifestNumber() {
     final Object result = getHibernateTemplate().execute(new HibernateCallback() {
      public Object doInHibernate(Session sess) throws HibernateException, SQLException { 
       SQLQuery sqlQuery = sess.createSQLQuery("select MY_SEQUENCE.NEXTVAL from dual");
       sqlQuery.uniqueResult();
      }
     });
     Long toReturn;
     if (result instanceof BigDecimal) {
      toReturn = ((BigDecimal)result).longValue();
     }
     return toReturn;
    }

Answer 3

I think the complexity of your task depends on whether or not you manifest number needs to be sequential:

• If you don't need sequential manifest numbers then it's happy days and can use a sequence.
• If you do need sequential manifest numbers (or your database doesn't support sequences) then use an id table with the appropriate locking so that each transaction gets a unique sequential value.

Then you've got 2 options that I can think of:

• write the necessary JDBC code on your client, ensuring (if the manifest number is sequential) that the transaction being used is the same as that for the database update.
• use a trigger to create the manifest number when the appropriate update occurs.

I think my preference would be the trigger because the transaction side of things would be taken care of although it would mean the object would need refreshing on the client.