Generating sequential ID in java

fire a query to get the largest id in the table .... your query looks something like this

SELECT max(id) FROM <table-name>;

your remaining java code is simple
int nextId = rs.getInt(1)+1;


Hope this helps

fire a query to get the largest id in the table .... your query looks something like this

SELECT max(id) FROM <table-name>;

your remaining java code is simple
int nextId = rs.getInt(1)+1;


Hope this helps

If only one servlet engine could be modifying the database, it would probably be safe to use a singleton class with synchronized methods to control your IDs.  This would probably give you much better performance.  To protect data integrity, I would put a unique key on the table.  That should cause an Exception to be thrown if the new data has a duplicate key.

brian

The best approach is this case is to create Oracle sequences(if you are usign oracle as database).

After you create that you can use it like this.
e.g.
SELECT YOURSEQUENCE.nextval FROM dual

Everytime you call this you will get a number incremented by the unit that you define while creating the sequence.
So if your increment is 1 then then you will get sequential numbers.

Regards
Khem

He specifically mentioned Access, otherwise I would have suggested the same thing.  :)

brian

Hi,
I tried using the following query using jdbc::SELECT max(id) FROM <table-name>;

query="Select MAX(ID_Cnt) from OrderMaster";
rs=stmt.executeQuery(query);
nxtid=(rs.getInt("ID_Cnt"))+1;

where ID_Cnt is my field..

i am getting an SQLException stating Column not found.I have this field in my table.if i remove MAX then it works fine..so what do i do now??it's not accepting MAX command..

Any help on this please..
               

Hi,
I tried using the following query using jdbc::SELECT max(id) FROM <table-name>;

query="Select MAX(ID_Cnt) from OrderMaster";
rs=stmt.executeQuery(query);
nxtid=(rs.getInt("ID_Cnt"))+1;

where ID_Cnt is my field..

i am getting an SQLException stating Column not found.I have this field in my table.if i remove MAX then it works fine..so what do i do now??it's not accepting MAX command..

Any help on this please..
               

The exception is being thrown at the line
nxtid=(rs.getInt("ID_Cnt"))+1;

becoz ur querying for MAX(Id_Cnt) and trying to extract Id_Cnt in the above statement. You need to extract the MAX(iD_cNT) ...

to rectify ..... do this

nxtid=rs.getInt(1)+1; instead of using the column name.

If u need to use strings, u have to modify ur query as

rs= stmt.executeQuery ("SELECT MAX(Cnt_Id) AS myId FROM OrderMaster");
int next = rs.getInt("myId")+1

hope this helps.

hi,
I tried using it as u hv mentioned:

query="Select MAX(ID_Cnt) AS myId from OrderMaster";
     rsid=stmtid.executeQuery(query);
     nxtid=rsid.getInt("myId")+1;

it's again giving an exception:Data type mismatch in the criteria exception.

i tried giving the rsid.getInt(20) where ID_Cnt is the 20th field in my table..it's giving invalid descriptor exception...

how do i work around this problem...need ur help..
               

Maybe that the data-type defined for ID_Cnt in the table is 'long'.... in that case, you'll need a 'long' variable and you'll have to use the getLong () method instead of getInt ().

Mayank.

>> i tried giving the rsid.getInt(20) where ID_Cnt is the 20th field in my table..it's giving invalid descriptor exception...

It will, because rsid is not referring to the OrderMaster table but the table generated by the query (which has only one attribute - myId).

Mayank.
 

u cant use rs.getInt(20). The number that u use ..eg 20 is not the field number in your table, its the field number in your query (starting with 1) .. eg

SELECT name,age,tel,address FROM ADDRESSBOOK;

1 = name
2 = age;
3 = tel
4 = address

irrespective of their locations in the table and irrespective on the number of fields in the table

so just use

nextId = rs.getInt(1);
nextId++;

Bhayzone,

>> its the field number in your query

If you select only MAX (ID_Cnt), do you think that it'll allow you to specify getInt (20), because there is only one attribute selected and I guess the ResultSet has nothing to do with the original table OrderMaster but has only to do with the query, which yields only one attribute??

Ya, getInt (1) is ok here.... but I guess there might just be a problem with the data-type (int/long)??

Mayank.
 

Selecting Max(colname) from a large table will give poor performance and is not guarrenteed to give you a unique value in cases where there is concurrent access. Two threads which execute the query at the same time will get the same result, add one to it and you'll get a duplicate key on insert ( assumming that you've defined the key as unique ).

The singleton class suggested by bkrahmer will give good performance but does give you a problem if you've got multiple JVMs.

Another approach is to have a separate table which just contains the last id which was allocated. You can make this threadsafe by using the following locking strategy

start a transaction
update the register table by one
read back the new value and store it in your code
commit the change to the database
use the value you just retrieved as the key value.

This builds a performance bottleneck in to the allocation of the ids but as lock as you design the right database locking strategy you're guarrenteed unique ids no matter how many JVM's are started up.

Dave

A very good point Dave. I never thought of the concurrent access part. Thanks for pointing it out.

Hi Dave,
I didn't get what do u ean by locking strategy.How do i lock it??
As far as i hv understood:suppose my table is IdCount & my field is lastidcnt::
con =pool.getConnection();
con.setAutoCommit(false);
query="Select lastidcnt from IdCount";
get the lastidcnt(curid=rs.getInt(lastidcnt))
increment by 1 to get my current id into curid var
query="Update IdCount Set lastidcnt="+curid;
stmt.executeUpdate(query);
con.commit();
con.setAutoCommit(true);

Did i get it rite...what else i need to include in my code??

Did you check the type??

Mayank.

It all depends on what the databases default locking strategy is. On the asusmption that read locks are shared and update locks are exclusive within your database engine ( you'd have to check up on that ) then the code you've got above will work except you've got the select and update statements the wrong way round. In the code above, two threads can both read the IdCount at the same time, bioth would add one to it and then write it back. Both threads would generate the same id.

If you do the update first it takes an exclusive lock meaning that any other threads which are trying to also do the update must wait for "your" thread to commit. So, you update the row, taking an exclusive lock, read back the next value ( your existing exclusive lock is still in operation so no one else can do anything to the row ) and then commit your changes. Any threads which are waiting for the lock to be released can then get access to the changed value, meaning that you can't get duplicate values being generated.

The whole of this relies on the locking strategy that is on use. It needs update locks to be exclusive, read locks to be shared, and preferably ( although not vitally ) for dirty reads to be disallowed.

Dave

Hi Dave,
I didn't get what do u ean by locking strategy.How do i lock it??
As far as i hv understood:suppose my table is IdCount & my field is lastidcnt::
con =pool.getConnection();
con.setAutoCommit(false);
query="Select lastidcnt from IdCount";
get the lastidcnt(curid=rs.getInt(lastidcnt))
increment by 1 to get my current id into curid var
query="Update IdCount Set lastidcnt="+curid;
stmt.executeUpdate(query);
con.commit();
con.setAutoCommit(true);

Did i get it rite...what else i need to include in my code??

Hi Dave,
I still didn't get one thing,u asked me to update the table first..update the table with what value??First of all i need to get the last id which is stored in the database,then i can update..but without the last value from the database,how can i do it??If i update the table with one,ui will loose the old count...

may be i haven't got what u r trying to explain...
Can u please help me on this?

Thanxs a lot Dave for helping me out with my problem.Thanxs once again..