Wednesday 15 February 2012

Making a decoupled sequence generator, in JPA with MySQL.

I faced this problem recently and there is no good or easy solutions out there. I have made a research looking for a JPA annotation that provides this, with no luck.

After a while, I decided to solve the problem by my self, doing it manually. I usually have not enough time to analyze problems to come up with an academic solution, so I will tell you what I have done to move forward, and you can post alternative solutions.

What I did was to create a table where I'm going to store all the independent sequences, giving them a name and the actual value.


So, as you may notice, there will be a row per sequence where the value will be modified by an update sql query. The best way to avoid concurrence problems updating the sequence is to create a function we can select when we need a new sequenced number.

Designed like that you can create so many functions as sequences you need. All stored in a single table and you just need to execute an SQL query selecting the proper function to get you sequence number.

Assuming you are using Hibernate as your JPA implementation framework, you can not use an HQL query to get this sequence, because you are invoking a function and as far as I know this is not possible using HQL. The workaround here is to create a native query to execute.

I hope you find this useful!!