Hibernate TableGenerator

by Dejan Bosanac

On one project, I had to build standalone Java server application that will occasionally save some objects into existing database. I've chosen to use Hibernate for all database related work (O/R mapping, sessions handling, etc.) and I found it very reliable and easy to use. One thing that I didn't manage to do straightforward was the key generation.

This was a little piece of a large project with database structure defined a few years ago and one thing that was fixed is the way to obtain unique keys. A database table with name-value pairs was defined and all counters were supposed to be in there. This has proven to be a solid solution. Project had to be easy to port to any client's choice of database vendor and it was not Java-only project, so idea was to avoid usage of vendor specific sequences. Also, we used this structure not only for unique database keys, but for all other counters that we needed in the project (such as invoice numbers, which are client specific and usually has to be reset yearly).

For the key generation from the database table, Hibernate provides net.sf.hibernate.id.TableGenerator which is using a table that has only one column with the 'next key value' in it. So you have to have as many tables as many different generators you want to use. This method is surely a bit faster than one described above, but it is not convenient to use it when you need more then few counters in your system (and even if I want to use it was not possible at this moment).

TableGenerator class is used to execute SQL queries in a separate transaction and TableHiLoGenerator is used to generate the key value using HiLo algorithm (using the value returned from TableGenerator). Also, you can't use TableGenerator directly. I'm not sure why, I suppose because it is not efficient, but it makes it impossible to use this key generator when Hibernate is not the only tool you're using to persist data in the database. So idea was to extend TableGenerator to collect appropriate configuration parameters and issue queries that match table structure that I had. But this part of Hibernate wasn't looking like it was made for any customisation. Instead of writing my own class from the beginning, I've reorganized class structure so that it can be easily adapted to any SQL queries that you want to perform to get the next key value. Also, I wanted to provide simple way to apply any high level algorithm to it. For now that would be HiLo algorithm and "incremental" algorithm that will only return the next key value from the database (it's all I wanted to do in the first place).

For the unique key generation, you need to issue two queries. One to get next key value from the database and one to update the table with the new 'next value'. Original TableGenerator class, defines these two queries in the configure method. This is fine, because these definitions depends on the configuration parameters that are submitted (such as table name) and SQL dialect. Prepared statements are created from these queries in the generate method and if you want to execute query that has different number of parameters than one in the original TableGenerator you have to override this method. I wanted to avoid that, so I defined two methods prepareQuery and prepareUpdate, which you can override to match your queries. The logic of the generate method remains the same. Also, I changed TableGenerator so that it can be used directly.

One more improvement comes to my mind, but I left it out because it would break the compatibility with current Hibernate features. High level algorithms, such as HiLo, are implemented with subclassing and you can see that TableHiLoGenerator and SequenceHiLoGenerator looks the same (just extends different parent class). I was tempted to decouple this using Strategy pattern, and to make a new interface Algorithm, that will implement this high level behaviour. Later generator could be configured with whatever algorithm we choose, independent of the way we are getting the keys from the database. Currently there is only HiLo algorithm and maybe this refactoring is not justified, but it would enable quick implementation and configuration of new algorithms. Example configuration could be something like this



<id name="id" type="long" column="cat_id">
<generator class="table">
<param name="table">hi_value</param>
<param name="column">next_value</param>
<algorithm class="hilo">
<param name="max_lo">100</param>
</algorithm>
</generator>
</id>



This is just an idea, that could be easily implemented and is left for future improvements.

Here you can find the source code and instructions how to configure net.nighttale.hibernate.TableGenerator and net.nighttale.hibernate.MultipleTableGenerator (one that works with name-value tables). It works for me, and I hope it can work for you too. If you find any bugs, inconsistencies or a way to improve it, please contact me.

1 Comments

angeloni
2004-08-21 06:12:25
composite key
Hi Dejan!


Im very worry about a problem with HBN.


See:
i would like that HBN to generate next code to NUSEQ:


TBL1
cdsystem <-- pk
nmsystem


TBL2
cdsystem <-- pk (fk)
NUSEQ <-- pk
nmForm


Its possible? how?


thanks
Fernando.