How do I get ID of row i just inserted in Java

Question

Ok so my code looks like this

        String sqlString = "INSERT into auditlog(report_name, username, start_datetime, resource_uri, resource_id) VALUES (?,?,?,?,?)";
        preparedStatement = connection.prepareStatement(sqlString);
        preparedStatement.setString(1, reportName);
        preparedStatement.setString(2, username);
        preparedStatement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
        preparedStatement.setString(4, uri);
        preparedStatement.setBigDecimal(5, new BigDecimal(0));
        preparedStatement.executeUpdate();

The table has one more field "ID", but that gets auto generated, how do I get that key ? I need to use it as a Foreign Key in the next bit of my code

@Abu so you directly ignore concurrence and get whatever ID you might retrieve. — Alfabravo, Jun 21 '13 at 13:46

dijkstra · Accepted Answer · 2013-06-21T13:50:34.993

6

ResultSet generatedKeys = preparedStatement.getGeneratedKeys();

if (generatedKeys.next()) {
    Long id = generatedKeys.getLong(1);
}

edited Jun 21 '13 at 13:50

answered Jun 21 '13 at 13:45

dijkstra

1,068
2
16
39

Will this work ? ____ String generatedKeys = preparedStatement.getGeneratedKeys().toString(); – Andre Jun 21 '13 at 13:48
No Andre, this returns a `ResultSet` object, you need to retrieve it from `ResultSet` – Abubakkar Jun 21 '13 at 13:50
I edited my answer. If your id is Long, you get it like this. – dijkstra Jun 21 '13 at 13:51

score 3 · Answer 2 · answered Jun 21 '13 at 13:46

3

Statement interface defines getGeneratedKeys() method. But it all depends on the driver you use. If the driver implements it, you can retrieve the generated IDs

answered Jun 21 '13 at 13:46

Santosh

17,667
4
54
79

score 2 · Answer 3 · answered Jun 21 '13 at 13:57

You need to pass PreparedStatement.RETURN_GENERATED_KEYS to your prepare, and then use getGeneratedKeys() to retrieve it;

preparedStatement = connection.prepareStatement(sqlString,      
    PreparedStatement.RETURN_GENERATED_KEYS);  

...

int rownum = preparedStatement.executeUpdate();  
ResultSet resultset = preparedStatement.getGeneratedKeys();  
if( rownum != 0 && !resultset.next()) {  
      int version = resultset.getInt(1);  
}

Paul · Answer 4 · 2013-06-21T13:57:17.343

0

In MySQL, "SELECT LAST_INSERT_ID()" should do it. Make sure you do it in the same transaction, of course.

Don't "SELECT MAX(id) FROM ...". That could easily work fine but get slower and slower as the table grows.

Also, since JDBC coding is nearly all boilerplate coding, write yourself a SqlUtils.getLastInsertId(Connection con) method somewhere, and save yourself some typing!

edited Jun 21 '13 at 13:57

answered Jun 21 '13 at 13:51

Paul

3,009
16
33

How do I get ID of row i just inserted in Java

4 Answers4