my dog learned polymorphism*
The moose likes JDBC and the fly likes Sequence generator Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sequence generator" Watch "Sequence generator" New topic
Author

Sequence generator

Iris Hoekstra
Greenhorn

Joined: Aug 10, 2005
Posts: 29
We're writing an application that needs to be able to insert records into a database with a unique id.

The DBMS we have to use is ancient and doesn't support auto-increment or any such nifty features. So far, we query the database for the maximum value, add 1 to that and insert the new record with the new number. This approach has to change, however, because theoretically two users could get the same max value and then we're in trouble. I should also add that the application will run on two servers simultaneously in the future.

I have been reading something about sequence generators but I am not sure I get the idea. I would rather not write a separate sequence generator (i.e. one that is not part of the application). From what I understand, I don't have to do that if I create a database table that holds... some value. That's where I get lost. Could someone provide some pointers?
Srikanth Basa
Ranch Hand

Joined: Jun 06, 2005
Posts: 241
This may give some idea for a potential implementation
-----------------------------------------------
TBL: MY_SEQ_TABLE
-----------------------------------------------
Columns : SEQUENCE_NAME , SEQUENCE_VALUE
-----------------------------------------------
Initial Val: DUMMY_SEQUENCE , 0
-----------------------------------------------

Your sequence implementation will be something like this

1. currentSequenceValue = Select SEQUENCE_VALUE from MY_SEQ_TABLE where SEQUENCE_NAME = 'DUMMY_SEQUENCE' FOR UPDATE (Note the "for update" option)

2. Update MY_SEQ_TABLE set SEQUENCE_VALUE = SEQUENCE_VALUE + increment WHERE SEQUENCE_NAME = 'DUMMY_SEQUENCE'

3. newSequenceValue = currentSequenceValue + increment

4. Commit
-----------------------------------------------
Note : You may have to consider many other performance related aspects while implementing it.
Iris Hoekstra
Greenhorn

Joined: Aug 10, 2005
Posts: 29
Thanks, but unfortunately 'FOR UPDATE' is not supported either.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30050
    
149

Iris,
What database are you using? Some database provide built in sequences or something else that may be of use to you.

"for update" is used an Oracle to show you need a write lock. In some other databases - like db2 - you get a write lock automatically. In those databases, you have to say "for fetch only" to get a read only lock.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Iris Hoekstra
Greenhorn

Joined: Aug 10, 2005
Posts: 29
Hi Jeanne,

We're using CA Datacom. We are approaching the database through FireXML, a piece of middleware which enables you to speak SQL to Datacom.

But I'll tell you what: 'for fetch only' seems to be supported! That brings the solution Srikanth offered back into the realm of possibilities. Thanks, both of you!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30050
    
149

Iris,
Just to clarify, you don't actually want to use "for fetch only" as that would make the query read only. It's absence is equivalent to "for update."
Tracy Nelson
Greenhorn

Joined: Sep 29, 2005
Posts: 12
Originally posted by Iris Hoekstra:
We're writing an application that needs to be able to insert records into a database with a unique id.


Is the only restriction that the ID must be unique? If so, then you can probably just use Date.getTime() to get the time in milliseconds, then prepend the user ID or some other per-user bit of data.

If the IDs have to be a monotonically increasing series, then you could write a simple sequence generator class that takes a value in its constructor and has a thread-safe getNextId() method that just increments the value and passes back the new value. Initialize it with the value returned from "SELECT MAX(id) FROM <whatever table you're using>". This only has to be done at application startup, so even if it takes a couple of seconds to get the value, it shouldn't be too bad.

If you have to have gapless sequences (e.g., check or invoice numbers), then your safest bet is to use the database sequence table already discussed. If possible, use a stored procedure to control access to the table. This way, you can prevent anyone from modifying the table directly, so nobody introduces gaps or overlaps.

For your two-database situation, I'd recommend using a stateful EJB to hand out new IDs, unless you need gapless numbers. Then I'd recommend you keep the sequence table on only one database, and make all transactions hit that database.


Protect me from the things I want!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sequence generator
 
Similar Threads
Hibernate: ORM Mapping "Could not get next sequence value" SQLCODE: -142
id value should be sequence or asssigned
Hibernate can access private fields and methods ? How come ?
Catching PK violations in JDBC
Problems using a database sequence in JPA