by Dejan Bosanac
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.TableGeneratorwhich 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).
TableGeneratorclass is used to execute SQL queries in a separate transaction and
TableHiLoGeneratoris used to generate the key value using HiLo algorithm (using the value returned from TableGenerator). Also, you can't use
TableGeneratordirectly. 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
TableGeneratorto 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
TableGeneratorclass, defines these two queries in the
configuremethod. 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
generatemethod and if you want to execute query that has different number of parameters than one in the original
TableGeneratoryou have to override this method. I wanted to avoid that, so I defined two methods
prepareUpdate, which you can override to match your queries. The logic of the
generatemethod remains the same. Also, I changed
TableGeneratorso 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
SequenceHiLoGeneratorlooks the same (just extends different parent class). I was tempted to decouple this using
Strategypattern, 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">
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.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.