Generating Sequential Transaction ID's in Java

Question

I want to generate Sequential Trnsaction ID's for employees to store in the Database ... I wrote the below jsp code(This ain't the code to insert Tx ID in the database) ..The code works fine .. When i use order by clause to see the latest transaction ..i'm not getting the expected transation ID...How can i make the transaction ID's unique , Sequential so that i can retrieve them in a particular sequence by using Order By clause?

  <%
         String url = "jdbc:mysql://localhost:3306/";

        String dbName = "ystem";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "";
        try {
            Class.forName(driver).newInstance();
            Connection conn = DriverManager.getConnection(url+dbName,userName,password);
            Statement stat=conn.createStatement();
            ResultSet rs=stat.executeQuery("select count(*) from `employee`");
            int x=0;
            UUID idOne = UUID.randomUUID();
            while(rs.next()) {                    
                x=Integer.parseInt(rs.getString(1))+1;
            }
            out.println(idOne.toString().toUpperCase()+"-"+String.valueOf(x));
                        stat.close(); conn.close();
        }
        catch(Exception x) {
            out.println(x.getMessage());
        }
    %>

Don't use scriptlets. http://stackoverflow.com/questions/3177733/how-to-avoid-java-code-in-jsp-files — Matt Ball, Mar 25 '12 at 16:50
What is your question and how does it relate to the sample code? Close. — usr, Mar 25 '12 at 16:55
I don't see how to add an `order by` clause anywhere in this code. Can you also explain why are you generating a UUID ? Is that part of your Tx ID? If so, why not use that only? Why append `count+1` ? — Diego, Mar 25 '12 at 16:56
String s= (idOne.toString().toUpperCase()+"-"+String.valueOf(x); This is how i generated unique ID ...And Count+1 is to make the string(ID) Sequential ... I don't wanna add Order by clause in the above code ... I executed the query with order by clause in command prompt/Shell to check if i get the required UID — Pramod, Mar 25 '12 at 17:04
No. You don't want to do this at all. You want the transaction ID to be generated by the database. They are really good at this, and the entire and immense concurrency problem, that you haven't even spotted yet, is completely eliminated. — user207421, Mar 25 '12 at 21:05

score 0 · Answer 1 · answered Mar 25 '12 at 18:07

Leaving aside the wisdom of this approach, appending the count to the UUID isn't going to give you something that will be meaningfully orderable by an order by clause because you are creating something in the form RANDOMSTUFF-2. That can't be ordered in any sequence because order by will sort a string column lexically starting with the first character.

So instead, put the counter at the beginning of your string. Then you'll have something order by can meaningfully sort:

String s = String.valueOf(x) + "-" + idOne.toString().toUpperCase();

(Though you'll probably want/need to zero-pad the output of String.valueOf(x) because otherwise "1000" will be sorted to be before "2". So you need "2" to output as "0002", for example.)

(and of course you should use StringBuilder.append() (or StringBuffer if your version of Java is old enough) rather than string concatenation).

Generating Sequential Transaction ID's in Java

1 Answers1