I want to be able to store and fetch a counter value from my database which starts at zero and goes to Integer.MAX_VALUE. I want to be able to get the latest value and then have the value incremented so that the next time it is fetched it will equal (previous value + 1). It's not clear to me how I would go about doing this using Hibernate, and whether or not it's possible with all databases, i.e. perhaps some databases won't support this sort of sequencing.

One idea I have is that I could create a Hibernate object with an Integer counter field, set the original counter value to zero, save the object to my database, and then each time I need the next counter value I would get the object via a DAO method, get the counter field to use as the value in my Java code, increment the counter field in the object, and then update the object with the new incremented value. However this seems like a real hack, and I'm assuming/hoping that there's a more elegant way of doing this using Hibernate. The closest thing I've found so far looks to be using a generated property in my POJO, but I haven't been able to make it work, maybe because I need to set up my own generation strategy and I haven't found any documentation which describes how you would do that. I'm afraid I'm barking up the wrong tree by taking that approach, but I can't find anything else which looks to be an appropriate solution. As an example below is what I've tried to do, but again it's not working as I'd like -- the counter field is always null rather than being a generated sequence value as I was hoping for:



I'm using Hibernate version 3.2.6.GA and Hibernate Annotations version 3.4.0.GA. I'm testing with an embedded HSQL database.

If anyone has done this before and can give me any ideas/advice I'd really appreciate the help. Thanks in advance.

--James

When you use the @Generated annotation you are telling Hibernate that your database is generating a value for this column without assistance of Hibernate.

The @GeneratedValue annotation is only used together with @Id properties and in many requires some kind of assistance by Hibernate to generate the value.

To solve the problem you need to configure the database to automatically assign a value to the column. The support for this varies from database to database. For example, with PostgreSQL you can do something like:



I have no experience with HSQL so I have no idea if something like that works.

Thanks for the clarification. The more I find out about this the more it's starting to look like I can't really do what I want to do, at least not in a straight-forward and database independent way using Hibernate. I typically test my data access code using an embedded HSQL database but in production we use MySQL. If I can't do this using some mechanism in Hibernate which works across all databases then it makes my automated testing much trickier, and this is one of the main reasons I was hoping to come up with a solution using Hibernate.

--James

I agree with you that using @Generated annotation is not so easy for cross-platform support. MySQL has no support for sequences and can have only one AUTO_INCREMENT column per table. And that column is already used for the ID property.

What is the use case for the counter? What makes it different from the auto-generated id? I am thinking that Hibernate has several options for customizing how your data is handled. Things to consider are for example:

* Custom user types: http://www.hibernate.org/hib_docs/v3/re ... pes-custom
* Custom property accessor: http://www.hibernate.org/hib_docs/v3/re ... n-property
* Interceptor: http://www.hibernate.org/hib_docs/v3/re ... vents.html

Or... maybe I have misunderstood you and it is the <version> property you are looking for: http://www.hibernate.org/hib_docs/v3/re ... on-version

The use case is that I want to have a counter to use for creating MAC addresses when provisioning virtual hosts. I have a base MAC address that I can increment when I need to get a new MAC address, so my thinking was to have a counter somewhere that I could use as a sequence value to add to my base MAC address in order to get a new MAC address.

For example let's say my starting/base address is AA:BB:CC:00:00:00. When I need a new MAC address I would like to do something like this:



My original idea was to have things configured in such a way that I could call a method on my DAO class which would return the current counter value and increment the counter value in the same step. The more I think about it the more it makes better sense to control the value myself and update the counter object after incrementing the value. If so then the questions are now how do I insure that there's always one and only one counter object in the database table, and how do I set the relevant transaction properties on the DAO to insure that no two threads ever read dirty data. I expect that these questions will be easier to figure out and the solution more platform independent than the approach I was first considering.

If anyone can suggest an easier or more elegant solution to the problem described above then please suggest it!

--James

A simple solution would be to have an entity with only an ID attribute. The ID is either generated by a sequence or an auto-increment column (depending on which database you are using). Then when you need a new value you simply create a new instance and save it to the database. Possibly in a separate transaction if you don't want this to interfere with the current transaction.

The good thing is that you'll never have to worry about threading issues since the database is doing that for you. The drawback is that the table fills up with more and more rows, but will that really be a problem? In any case, it should be possible for the CounterDAO to issue a batch delete statement that deletes old rows.

Thanks again, this is a great idea. I have implemented a solution using this approach and it appears to work well. I remove the new object as soon as I'm done with using its ID value so no objects are building up in the table. I really appreciate the help.

--James

If you are removing the new Id only object each time, I would be curious to know whether you can expect consistent behaviour from the various database systems that you may want your code to be able to operate with.


--
Stephen Souness

Me too. My preliminary testing has only been in JUnit tests which access an in-memory HSQL database. It doesn't seem to matter when I remove objects shortly after I create them, the subsequent new objects have the expected ID values. I'm eager to see whether or not there will be issues with this solution once I deploy it into a clustered MySQL environment, which is the planned production environment.

--James