Primary key generation

[dsnyckers]

Hi,

I have a question regarding the retrieval of unique primary keys. I use JBoss 3.0.2 with CMP EJBs.

At the moment I use a seperate table which stores the last added unique ids for my tables, and handles concurrency issues.

BUT, this all worked fine till the database got shared.
Now there are also other programs which also add data to the database and which use at this moment a get MAX + 1 query to retrieve the next unique id to use. So this obviously will lead to problems.

What's the best solution, approach to tackle down this problem ?

TIA,
Dave

[lfsoft]

Take a look at Floyd Marinescu's book "EJB Design Patterns".
You can download a free PDF from www.theserverside.com (Books & Articles). Source Code is also provided.

[sysuser1]

As the previous reply mentioned, I would advise you to check out the book, but to go one up, here is the code for one of the patterns in the book (my own adaptation, so look at it critically, I havent tested it in a production environment, although it is true to its original).
The code generates keys that are truly unique across clusters and can be generated in your java-code to then be inserted in the database when you choose to, and still be unique, heres the code:

/*
* UUIDGenerator.java
*
* Created on den 15 februari 2003, 22:43
*/

package net.infonatural.db;

import java.security.SecureRandom;
import java.net.InetAddress;

/**
* @author Wille Faler, Infonatural Technologies, www.infonatural.net
* Code HEAVILY inspired by Floyd Marinescus code in the book "EJB Design Patterns", although I have made some alterations
* 99% of credit should go to mr. Marinescu.
*
* Object that implements a Universally Unique Identifier for use in primary-key generation for example.
* The UUIDGenerators generated id:s have the following characteristics:
*1. Unique down to the millisecond.
*2. Unique across a cluster due to a portion generated on the basis of the underlying IP.
*3. Unique down to the objects within a JVM.
*4. Unique within an object within a millisecond due to randomization on each method-call.
*
*/
public class UUIDGenerator {

private static UUIDGenerator generator = null;
private SecureRandom seeder;

private String midValue;

/** Creates a new instance of UUIDGenerator */
private UUIDGenerator(){
try{
InetAddress inet = InetAddress.getLocalHost();
byte[] bytes = inet.getAddress();
String hexInetAddress = "";
for(int i = 0;i < bytes.length; i++){
hexInetAddress += new Byte(bytes).hashCode();
}
String thisHashCode = "" + System.identityHashCode(this);
seeder = new SecureRandom();
int node = seeder.nextInt();
this.midValue = hexInetAddress + thisHashCode;
}catch(Exception e){

}
}

/**
* gets an instance of the UUIDGenerator
*/
public static synchronized UUIDGenerator getInstance(){
if(generator == null){
generator = new UUIDGenerator();
}
return generator;
}

/**
* generates a new unique id.
*/
public String getUUID(){
long timeNow = System.currentTimeMillis();

int timeLow = (int) timeNow & 0xFFFFFFFF;
int node = seeder.nextInt();

return(timeLow + midValue + node);
}


}

[hezekiel]

Wow! some slow responses in this thread!

Here's another solution:

PreparedStatement ps = con.prepareStatement(someStatement, PreparedStatement.RETURN_GENERATED_KEYS);
ps.set....
ps.executeUpdate();
// 2. Get the generated key(s) as a result set after the insert is done.
ResultSet rs = ps.getGeneratedKeys();
if(!rs.next()) // should not happen...
throw new SQLException("ups!");
long id = rs.getLong(1);
ps.close();
return id;

Preconditions: You need a modern database which supports auto increment (most of them do) and you need a JDBC 3 level driver.

[marc.fleury]

> Wow! some slow responses in this thread!
>
> Here's another solution:
>

cmp one field mapped to incremented row with option b locking and select for update setting. Available since 2.4.x

next.

> PreparedStatement ps =
> con.prepareStatement(someStatement,
> PreparedStatement.RETURN_GENERATED_KEYS);
> ps.set....
> ps.executeUpdate();
> // 2. Get the generated key(s) as a result set
> set after the insert is done.
> ResultSet rs = ps.getGeneratedKeys();
> if(!rs.next()) // should not happen...
> throw new SQLException("ups!");
> long id = rs.getLong(1);
> ps.close();
> return id;
>
> Preconditions: You need a modern database which
> supports auto increment (most of them do) and you
> need a JDBC 3 level driver.

[cryokeeper]

I like to be more simple. I just use the following as a primary key in all my EJB projects:

<hashCode of object (the TO, VO, whatever)>-<2 byte random number>-

Kinda big and not so human readable but its unique and easy.

Basically you would have some utility class that your session facade would call, populate the PK of your TO and then populate the EJB. Real simple and strait forward.

[jocsch]

Hi,
in floyd manesc. j2ee design patterns book is a primary key pattern published, that catches whole sequence blocks from the database and holds them in a stateful session bean. so database access only occurs when the block is empty.
In the code listing of the book, both beans (the stateful session and the entity bean) are declared with the serializable isolation level (it's a weblogic example and there it's possible to declare the isolation level of each bean).
How can I do this with jboss. I haven't found a possibility to only declare two beans with a seperate isolation level.

The only possibility I see, is to use a singelton to serve the the keys. But if there is another solution (with jboss 3.0.7) I would love to here it.

[piobair]

If you want a Java UUID generator you can go use mine at www.iwombat.com. You'll find a white paper in the java resources and code in the opensource java sections, respectively. Its been in production in various forms languages and environments for over 10 years now. You can choose String or byte[] representations also. Couple of caviats with the Java version and the workarounds are mentioned in both the white-paper and the javadocs.

Enjoy.