SQL: Get value of auto incremented column from executeUpdate

Question

I'm trying to get the automatically incremented column after I input a new row. I have the following columns in my table:

+----+---------+---------+-------+------------+--------------+------------+---------------+-----------+
| ID | Minkita | Maxkita | Meser | MetodasNum | MainQuestion | MetodaData | RequiresZiood | Nispachim |
+----+---------+---------+-------+------------+--------------+------------+-----

Where ID is an auto-incremented, not null mediumint.

My JSP code for this:

PreparedStatement s = connection.prepareStatement(
            "INSERT INTO Threads"
            +"(`MinKita`, `MaxKita`, `Meser`, `MetodasNum`, `MainQuestion`, `MetodaData`, `RequiresZiood`, `Nispachim`)"
            +" VALUES(?, ?, ?, ?, ?, ? ,? ,?)"
);
//Blah blah, setting strings and values for the prepared statement... Then:
s.executeUpdate();

Now, I understand that executeUpdate returns the row number of my new row, but what I want to do is to get the ID from my row number.

The thing is, the IDs aren't consistent and they may be in an order like so: 1, 2, 5, 7, 13... as rows may be deleted.

So, how do I get the data in the ID column from the row number?

Thanks in advance,

~ NonameSL

Answer 1

You can get the generated ID when doing an INSERT with the following:

First create a PreparedStatement which does return generated values of auto-increment columns:

PreparedStatement s = connection.prepareStatement(
    "INSERT INTO <your sql>",
    Statement.RETURN_GENERATED_KEYS);

After you have execute the statement you can retrieve the ID as follows:

s.executeUpdate();
ResultSet result = stmt.getGeneratedKeys();
if (result.next())
     id = result.getObject(1);

If you have multiple AUTO_INCREMENT columns in the table you can also specify the columns which should be returned:

PreparedStatement s = connection.prepareStatement(
    "INSERT INTO <your sql>",
    new String[] { "ID" });  // the ID column only