?
Solved

Generating sequential ID in java

Posted on 2003-03-24
22
Medium Priority
?
432 Views
Last Modified: 2013-12-14
Hi All,
I am developing a product for OrderProcessing using jsp,servlet & jdbc with MS Access as backend.I want to generate unique Sequential ids for each order logged.Each person who will try to access the application will have a login id & password.
I wanted to get information on how can i generate new id for each indivisual request in java .I have to store each generated id in the database.The new id generated should be sequential.
Can anyone of you please help me on this please???

Thanks in advance

Regards,
Rashmi
0
Comment
Question by:gcr2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +3
22 Comments
 

Expert Comment

by:bhayzone
ID: 8200576
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
0
 

Expert Comment

by:bhayzone
ID: 8200587
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
0
 
LVL 2

Expert Comment

by:bkrahmer
ID: 8200606
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:k_suchdeva
ID: 8200629
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

0
 
LVL 2

Expert Comment

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

brian
0
 

Author Comment

by:gcr2002
ID: 8201512
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..
               
0
 

Author Comment

by:gcr2002
ID: 8201536
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..
               
0
 

Expert Comment

by:bhayzone
ID: 8201571
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.
0
 

Author Comment

by:gcr2002
ID: 8201780
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..
               
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8201847
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.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8201858
>> 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.
 
0
 

Expert Comment

by:bhayzone
ID: 8201859
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++;
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8201911
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.
 
0
 
LVL 1

Expert Comment

by:howesd
ID: 8201951
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
0
 

Expert Comment

by:bhayzone
ID: 8201989
A very good point Dave. I never thought of the concurrent access part. Thanks for pointing it out.
0
 

Author Comment

by:gcr2002
ID: 8202127
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??
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 8202164
Did you check the type??

Mayank.
0
 
LVL 1

Expert Comment

by:howesd
ID: 8202178
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
0
 

Author Comment

by:gcr2002
ID: 8202203
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??
0
 

Author Comment

by:gcr2002
ID: 8202559
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?
0
 
LVL 1

Accepted Solution

by:
howesd earned 200 total points
ID: 8202609
Someting like ...

con =pool.getConnection();
con.setAutoCommit(false);
===>  query="Update IdCount set lastidcnt = lastidcnt + 1";
stmt.executeUpdate(query);
===> query="Select lastcnt from IdCount";
ResultSet Rs = stmnt.execute(query);
int NewId;
if (Rs.next())
   NewId = Rs.getInt("lastcnt");
else
   throw Exception("Can't allocate next ID");

con.commit();
con.setAutoCommit(true);


You'll also need a bit of tidy up code to close the statement and ResultSet objects

Dave
0
 

Author Comment

by:gcr2002
ID: 8208711
Thanxs a lot Dave for helping me out with my problem.Thanxs once again..
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses
Course of the Month12 days, 10 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question