How to generate unique ID's for database table?

[Former Member]

Hello,

I need functionality like "number ranges" in ABAP or "sequences" in Oracle. I have table with integer primary key field. I need to generate sequence numbers: 1, 2, 3, 4... Because they should be short then I can't use long integers like GUID.

How to do it?

Thanks.

Best regards,

Eugeny

[Former Member]

If only one application is running agaist your database, simply create a primary key generator indicated by the following code:

 class KeyGenerator
    {
        long nextValue_;

         public synchronized long nextValue()
        {
           return nextValue++;
         }
    }

If there are more than one applications using the same database, the implementation related to database maybe a good idea.

For instance, with jdbc, you can get the next unique value by calling SQL statement "SELECT sequenceName.NEXTVALUE FROM DUAL;" for oracle or the similiar sql statement for other databases.

Dennis

[Former Member]

This is no good idea because this object must be always syncronized with maximum key value in the database.

Second your suggestion also can not be used because our application should be database independed.

[Former Member]

Database independance doesn't mean you can not use vendor SQL. You can use or borrow the idea from ORM tool to make it work.

For instance, you have a class named KeyGenerator which simply delegate the key generation to the low level. At the low level you have the special generator for particular database accordingly.

You just need to bind your generator against the particular database. This would be a very efficient way.

Dennis

[Former Member]

Better idea is:

I have created table with two fields:

- number range name (string)

- last value (integer)

I have created Java class KeyGenerator with one static method

public static long getNextId(String name);

This methdod works by following algorithm:

1. Set lock on the table line for specified number range

2. Select last value for specified number range

3. Add 1 to last value

4. Update last value in the table for specified number range

5. Remove lock on the table line for specified number range

It works fine.

[Former Member]

It works on any database and guarantees that next ID will be always unique.

[Former Member]

Is this a transparent table in SAP? If so, then you could create a custom number range object using transaction SNRO in SAP, then create a custom function module that is remote enabled that wraps the standard FM NUMBER_GET_NEXT.

If this is not a SAP managed table, then you could create a plain old java object to manage your own custom number range, which would basically retrieve the current primary key field for your table, then return an incremented value.

[Former Member]

Hello,

Thanks for answer, but this is not possible because our production server have no ABAP part.

I hoped that SAP Java App. server has same functionality for generation unique values.