How to Create Database IDs from Java Code?

Question

I am manually incrementing my ID number in my database as I can't get it to work automatically. It is for a project and doesn't matter if it doesn't work automatically - I can try and solve that later.

I am creating a method which checks for the lowest free ID number in a resultSet. The lowest will be set as an integer called availableId which users will be given upon registration.

This is what I have got so far from reading:

http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html http://wiki.netbeans.org/GetStartedwithJavaDB

As an example, these are my IDs in my database:

/,2,/,4,5,6,/,8,9

1, 3 and 7 are missing as those users deleted their accounts. I want to fill number 1 before I fill any others and then the next user will register as number 3 for example.

public int getAvailableId() {
    int availableId = 1;
    try {
        String stmt = "select PID from APP.PERSON";
        PreparedStatement ps = Main.getPreparedStatement(stmt);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int n = rs.getInt(1);
            int n2 = rs.next().getInt(1);

            if (!(n == 1)) {
                return availableId;
            } else if (!(n2-- == n)) {
                availableId = n++;
            }
        }
        rs.close();
        ps.close();
    } catch (Exception e) {
        System.out.println(e);
    }
    return availableId;
}

Two problems here:

rs.next() returns a boolean, but I want n2 to hold the value of the integer in the next row - I can't find a method that does this from here - http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html
I can't figure out how to loop through everything to find the lowest possible free id number.

Please excuse the rubbish code! I am learning a lot as I go along and I understand this is bad practice for one and two the code isn't great.

How can I go about doing this?

If you're really doing this, make sure to use transactions to avoid double IDs (imagine two programs look for the next ID at the same time and then use the same). This is horribly bad practice anyway and auto-incrementing the ID is easy in all RDBMS I can think of, better solve *this* problem (and possibly ask a question about it), will save you much hassle now and while maintaining your software. — Jens Erat, Feb 15 '13 at 15:53
Also, be aware that "select PID from APP.PERSON" does not guarantee and order. if you must, use: "select PID from APP.PERSON ORDER BY PID". Mostly, I've found that trying to reuse IDs that have been deleted is a bad idea. Just get the max and be done. — Darius X., Feb 15 '13 at 15:56
Thank you for the Edit Jens Erat, I couldn't think of a title so wrote that and forgot to complete it before submitting. Thanks for your response too. — John Vasiliou, Feb 15 '13 at 15:57

cowls · Accepted Answer · 2013-02-15T15:55:41.347

Your code is slightly wrong. This needs to be changed:

rs.next().getInt(1);

To just:

rs.getInt("PID");

rs.next() does two things. Returns true if there is a next row, and then moves the resultset cursor to point to the next row.

When there is no next row it will return false and hence break out the loop.

When you call getInt it will try to retrieve the int from the current row. As you can see the rs.next() in the loop condition will move it to the next row each time.

Also in this case you will need to declare

int n outside of the loop.

and then assign n = n2 at the end of each loop iteration.

Something like this:

    int n = 0;
    while (rs.next()) {

        int n2 = rs.getInt(1);

        if (!(n == 1)) {
            return availableId;
        } else if (!(n2-- == n)) {
            availableId = n++;
        }

        n = n2;
    }

You may need to play around with it to get it doing exactly what you want.

My final comment, dont bother trying to get the lowest possible free id number, just get the highest number from the database usign a SQL MAX() or something and then add 1.

How to Create Database IDs from Java Code?

1 Answers1