Search code examples
javaoracle-databaseauto-generate

Auto generate ID in java

I am developing a web application using JSP + Servlets and Oracle10 as back-end.

I have a table for storing information related to Customers:

|ID        |Name            |City          |
|N0001     |ABC             |NASIK         |
|N0002     |PQR             |NASIK         |
|N....     |...             |NASIK         |
|N9999     |XYZ             |NASIK         |
|N10000    |LMN             |NASIK         |
|N10001    |MNO             |NASIK         |

In above table ID is a primary key, which is auto-generated depending upon the City (first Character of City + Number(Number must be minimum 4 character long, so for first ID, three leading zeroes will be added to number))

For Generating ID:
I am using following query for getting Largest ID from table, and then some java code Auto Generate Next ID.

query = "select ID from CUST where CITY='NASIK' order by ID desc";

Then getting the first ID from ResultSet which is as expected till the ID reach to the N9999, but when ID is N10000 or above then query is giving me N9999 as Largest ID.

Output after N9999: 

ID
----------
N9999
N10001
N10000
N0002
N0001

Output Expecting

ID
----------
N10001
N10000
N9999
N0002
N0001

So my question is that is there anything wrong in above query? Or is there any better way to Auto Generate ID which contains String.

Edit1 My requirement is to auto generate ID which will contain character at starting position.

Solution

  • use this query 

    select ID from CUST where CITY='NASIK' order by to_number(substr(ID,2)) desc;