CODEWITHSUNDEEP
javamysql
amal
amal

Reputation: 3592

Auto Increment ID break the Sequence

My program has an auto incremented ID with the format of ITM001, ITM002.... But after ITM009, ITM0010 the sequence is broken and goes back to ITM001. Because of that I'm getting ITM0010 as next item ID instead of ITM0011. Please help me to understand how should I write the query to continue the sequence. 

ITM001
**ITM0010**
ITM002
ITM003
ITM004
ITM005
ITM006
ITM007
ITM008
ITM009
   Connection conn = DBConnection.conn();
      String sql = "select itemId from ItemMain";
       ResultSet res = DBHandller.getData(sql, conn);
        if (res.last()) {
              String lastid = res.getString("itemId");
               return lastid;
        }
        return null;   
/////////////////////////////////////////////////////////////////////////////


              String itemID = ItemController.getItemID();

              String[] split = itemID.split("ITM",0);
              int num = Integer.parseInt(split[1]);
              itemID = "ITM00"+ (num+1);
              txtitemID.setText(itemID);

Upvotes: 1

Views: 714

Answers (2)

Louis Ricci
Louis Ricci

Reputation: 21106

You are implicitly sorting on a VARCHAR field.

SELECT a.a FROM (
    SELECT 'a001' [a]
    UNION ALL
    SELECT 'a009' [a]
    UNION ALL
    SELECT 'a0010' [a]
) a ORDER BY a

a001
a0010
a009

The last id you pick up will always be 009 so the next one you create will always be 0010.

If you front padded the "10" with 1 "0" instead of two the implicit ascending sorting would be correct. "001 ... 009, 010"

Upvotes: 0

pcalcao
pcalcao

Reputation: 15990

The problem is that you're using split on 0, and there are several 0's in your String after you increment, for instance:

    String[] split = itemID.split("ITM",0);
    int num = Integer.parseInt(split[1]);
    String second = "ITM00" + (num + 1);
    System.out.println(second);
    System.out.println(Arrays.toString(second.split("0")));

This will output:

[ITM, , 1]

Since the last 0 will also be split.

Something like:

String itemID = "ITM009";
int num = Integer.parseInt(itemID.substring(itemID.indexOf("0")));
String second = "ITM0" + String.format("%02d", num + 1);
System.out.println(second);

Will give you what you want probably, but you need to figure out how many digits you want your key to have and adapt accordingly.

Upvotes: 1

Related Questions