Is there a way to retrieve the autoincrement ID from a prepared statement

Question

Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements.

For example, I know AutoGeneratedKeys can work as follows.

stmt = conn.createStatement();

stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
if(returnLastInsertId) {
    ResultSet rs = stmt.getGeneratedKeys();
    rs.next();
    auto_id = rs.getInt(1);
}

However. What if I want to do an insert with a prepared Statement.

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql);

//this is an error
stmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
if(returnLastInsertId) {
    //this is an error since the above is an error
    ResultSet rs = stmt.getGeneratedKeys();
    rs.next();
    auto_id = rs.getInt(1);
}

Is there a way to do this that I don't know about. It seems from the javadoc that PreparedStatements can't return the Auto Generated ID.

returnLastInsertId where are you getting this variable – dedpo Aug 16 '17 at 06:17 — dedpo, Aug 16 '17 at 06:17

score 103 · Accepted Answer · edited Jun 02 '16 at 16:40

103

Yes. See here. Section 7.1.9. Change your code to:

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);


stmt.executeUpdate();
if(returnLastInsertId) {
   ResultSet rs = stmt.getGeneratedKeys();
    rs.next();
   auto_id = rs.getInt(1);
}

edited Jun 02 '16 at 16:40

Lukas Eder

211,314
129
689
1,509

answered Sep 03 '09 at 22:12

Yishai

90,445
31
189
263

9

@JafarAli, this question is about mysql. In oracle you have to specify [the generated column](https://community.oracle.com/thread/338591) or use the row id. – Yishai Oct 01 '14 at 20:30
1

Did you forget to `rs.close()` ? Is it necessary ? – Winter Jan 12 '17 at 15:15
2

@Winter, this code is focused on solving the problem in the question, not dealing with resource and exception handling. Calling close is necessary like any other statement and result set. – Yishai Jan 12 '17 at 15:58
@Winter, if your keys are longs, then call rs.getLong(1) at the end there. – Yishai Jan 12 '17 at 15:59

score 4 · Answer 2 · answered Sep 03 '09 at 22:23

There's a couple of ways, and it seems different jdbc drivers handles things a bit different, or not at all in some cases(some will only give you autogenerated primary keys, not other columns) but the basic forms are

stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Or use this form:

String autogenColumns[] = {"column1","column2"};
stmt = conn.prepareStatement(sql, autogenColumns)

score 2 · Answer 3 · answered Sep 03 '09 at 22:16

Yes, There is a way. I just found this hiding in the java doc.

They way is to pass the AutoGeneratedKeys id as follows

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

score 0 · Answer 4 · edited Jun 09 '14 at 12:11

I'm one of those that surfed through a few threads looking for solution of this issue ... and finally get it to work. FOR THOSE USING jdbc:oracle:thin: with ojdbc6.jar PLEASE TAKE NOTE: You can use either methods: (Method 1)

Try{
    String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
    myPrepStatement = <Connection>.prepareStatement(yourSQL, Statement.RETURN_GENERATED_KEYS);
    myPrepStatement.setInt(1, 123); 
    myPrepStatement.setInt(2, 123); 

    myPrepStatement.executeUpdate();
    ResultSet rs = getGeneratedKeys;
    if(rs.next()) {
      java.sql.RowId rid=rs.getRowId(1); 
      //what you get is only a RowId ref, try make use of it anyway U could think of
      System.out.println(rid);
    }
} catch (SQLException e) {
  //
}

(Method 2)

Try{
    String yourSQL="insert into Table1(Id,Col2,Col3) values(SEQ.nextval,?,?)";
    //IMPORTANT: here's where other threads don tell U, you need to list ALL cols 
    //mentioned in your query in the array
    myPrepStatement = <Connection>.prepareStatement(yourSQL, new String[]{"Id","Col2","Col3"});
    myPrepStatement.setInt(1, 123); 
    myPrepStatement.setInt(2, 123); 
    myPrepStatement.executeUpdate();
    ResultSet rs = getGeneratedKeys;
    if(rs.next()) {
    //In this exp, the autoKey val is in 1st col
    int id=rs.getLong(1);
    //now this's a real value of col Id
    System.out.println(id);
    }
} catch (SQLException e) {
  //
}

Basically, try not used Method1 if you just want the value of SEQ.Nextval, b'cse it just return the RowID ref that you may cracked your head finding way to make use of it, which also don fit all data type you tried casting it to! This may works fine (return actual val) in MySQL, DB2 but not in Oracle.

AND, turn off your SQL Developer, Toad or any client which use the same login session to do INSERT when you're debugging. It MAY not affect you every time (debugging call) ... until you find your apps freeze without exception for some time. Yes ... halt without exception!

`executeUpdate()` does not return a `ResultSet`, it returns an `int`, which is the update count. You have to retrieve the generated keys separately. See the existing answers, and the Javadoc for heaven's sake. -1. [Again.](http://stackoverflow.com/questions/14844864/jdbc-preparedstatement-always-returns-1-as-auto-generated-key) Please stop posting your untested guesswork here. — user207421, Jun 24 '13 at 08:21

score -1 · Answer 5 · answered Mar 12 '14 at 11:32

    Connection connection=null;
    int generatedkey=0;
    PreparedStatement pstmt=connection.prepareStatement("Your insert query");
    ResultSet rs=pstmt.getGeneratedKeys();
    if (rs.next()) {
       generatedkey=rs.getInt(1);   
               System.out.println("Auto Generated Primary Key " + generatedkey); 
    }

Is there a way to retrieve the autoincrement ID from a prepared statement

5 Answers5

Linked

Related