NewInstance

Have you ever thought about why almost every DB provides sequence functionality ? That's because sequences are not so trivial to implement when facing concurrent transactions.

Today I faced the problem of having a "sub-sequence" for some data: I had the need to generate sequence codes for groups of records in the same table. In input I had the main (common) code and I had to generate subcodes, for new records, based on the main code.

This is the wanted behavior:

mainCode = "001"
generateCode(mainCode)
will produce:
"001-001"
"001-002"
"001-003"

...then...
mainCode = "002"
generateCode(mainCode)
will produce:
"002-001"
"002-002"
"002-003"

...then...
mainCode = "001"
generateCode(mainCode)
will produce:
"001-004"
"001-005"
"001-006"

Easy, but I needed a separate sequence for each possible main code.

Here I describe how I solved the problem using hibernate 3.

This is how the code using my custom sequence looks like (version simplified):

public insertMyData(String mainCode) {
    Session session = getSession();
    Transaction tx = begin(session);
    try {
        Sequence sequence = getSequence(session, mainCode);
        for (int i = 0; i < ...; i++) {
            String subCode = mainCode + "-" + sequence.nextValue(); 
            session.save(new MyData(subCode));
        }
        session.flush(); // #4. flushes my data to db and updates the sequence into the DB
        // ...some other statements here...
        commit(session, tx);
    } catch (SomethingWentWrong ex) {
        rollback(session, tx);
        // ...log or rethrow...
    } finally {
        close(session);
    }
}

Notice the getSequence(Session, String) defined above. I'll show the code later, and it's there where the sequence is dinamically created or - if already existent - retrieved from the database. A sort of Singleton pattern (with some magic inside).

Now, let's see how looks like the Sequence object:

public class Sequence implements Serializable {
   private static final int MAX_VALUE = 9999999;
   public static final Integer LOCK_RECORD = new Integer(0);
   private Integer id; // PK
   private String mainCode;
   private int value; // sequence value

   // accessor methods here (get/set)
   // I usually define get/set accessor as 
   // private (as they are required by Hibernate)
   // when they're not needed to be public

   public int nextValue() {
      if (value == MAX_VALUE) value = 0; // cycling sequence 
      return ++value;
   }
}

And finally the little magic code, that does the real stuff: create the sequence (in a thread safe way):

private Sequence getSequence(Session session, String mainCode) {
    Object lock = session.load(Sequence.class, Sequence.LOCK_RECORD, LockMode.UPGRADE); // #1
    Sequence result = (Sequence)session.createCriteria(Sequence.class) // #2
         .add(Restrictions.eq("mainCode", mainCode)).uniqueResult();
    if (result == null) {
        result = new Sequence();
        result.setMainCode(mainCode);
        session.saveOrUpdate(result); // #3
    }
    return result;
}

Notes:

1. Here I lock the record with ID=0 (that I've inserted before) and, as I always lock this record before using any Sequence object, concurrent threads will have to wait until the transaction owning the lock commits or rollbacks. It's like having a lock on the entire table (this can be optimized if you lock on the result "#2" record, but this complicates the code, maybe in a next post I'll show the optimized version).
2. The sequence for the mainCode is retrieved from the db.
3. Here the Session object is stored (with initial value 0) in the database. When session.flush() will be called (#4, see previous snippet), also Session object will be flushed into the DB, updating it to the new value. If the transaction is rolled back then also the sequence won't be updated (wow! better than Oracle!).