Posts tagged ‘auto-increment’


In this post I’m going to describe how to use hibernate id generator in order to produce portable solution for genrating prefix sequences with user-defined – part of an issue tracking system (very simple for the sake of brewity).


As you might have guessed it – the requirement towards it are not that new and they resemble what we all know from other systems in the field. Each ticket is created within its own workspace and prefix which might be defined per workspace. Ticket number should start from 0 and increase. In case user decided to chanage prefix:

  • the old issues should keep it,
  • while new ones should be named after it
  • all numbering should not be influenced by prefix change

Solution idea

The entity hierarchy below reflects part of business with Client entity used as root for all the rest of nodes and having references to Projects (yes for simplicity of this post there’s nothing else :))) which you can see depicted in diagram #1.

Diagram #1

From technical point of view this dit look much like a sequence sequence where project-id would be used as primary key and increased value in second column would be used as ticket number.


Technical details

MySQL is the choice of RDBMS so it felt natural to use some of its built-in functonality and just wrap it around with Hibernate. So my idea was to use a composite primary-key with auto-increment column which would allow to set index per project and restart counter each project is inserted.

An DDL sql for such a table looks fairly simple:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)

And with simple inserts:

INSERT INTO animals (grp,name) VALUES


| grp    | id | name    |
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |

Copied from mysql docs

This is exactly the behaviour I wanted so you might ask why not use it and stop wasting other’s time with this post. Well there’s a little detail hidden in the DDL statement above. Careful reader might have noticed that it requires you to use MyISAM engine/table type which among all other things is non-transactional. If you try to use it with InnoDB engine you end up with global auto-increment despite the id column.

Hibernate generators

From the very beginning all of this sql was supposed to be wrapped inside hibernate generator but after finding out that pure MySQL solution was out of the table I decided to analyse what exactly are generator and what they have to offer. This result in reading generator discussion after which I found out that hibernate team has almost solved my problem…


If you’re like me – than you have probably never heard of  MultipleHiLoPerTableGenerator which can generate sequence per table and solves some issues related to:

  • creating table with dialect-specific ddl
  • creating initial sequence entry if non-existent
  • using locking on row-level for concurrent access
  • uses separate table for sequences which means that read-only queries are not affected
  • doing retry in case sequence was updated in-between
  • and last but not least – its portable

Everything comes with it’s price of course – this generator’s code is barely readable not to mention someone trying to debug what is actually happening.


So I decided to follow path showed by hibernate team and do some copy-pasting of their generator and start my own so here’s step-by-step:

  1. An artificial entity was created called TaskNumber.
  2. It contains embeddable id consiting of Project and number
  3. Embedded id is translated into database composite key.
  4. Composite key ensures that there are no two tickets with the same number in the same project.
  5. Id field is annotated with generator annotation.
  6. Generator takes care of creating table for storing sequences
  7. Before each insert of TaskNumber it increments per-project sequence
  8. Newly updated sequence is applied to TaskNumber and persisted


As one might expect the code is complex and hard to debug – the only positive side is that all the bumps have been handled already by hibernate team and it should produce less headaches in future when real concurrency and silmultaneous updates occur.

See it for yourself 🙂