0

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

Improve this question
3
  • ummm... SELECT ID FROM YourTable? Commented Jun 21, 2013 at 13:43
  • @Doorknob SELECT MAX(ID) FROM YourTable Commented Jun 21, 2013 at 13:44
  • 2
    @Abu so you directly ignore concurrence and get whatever ID you might retrieve. Commented Jun 21, 2013 at 13:46

4 Answers 4

Reset to default
6 
ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); if (generatedKeys.next()) { Long id = generatedKeys.getLong(1); }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Will this work ? ____ String generatedKeys = preparedStatement.getGeneratedKeys().toString();
No Andre, this returns a ResultSet object, you need to retrieve it from ResultSet
I edited my answer. If your id is Long, you get it like this.
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

Improve this answer

Comments

2

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); }
Improve this answer

Comments

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!

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.