By jW.


2009-09-03 22:04:26 8 Comments

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.

5 comments

@Yishai 2009-09-03 22:12:34

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);
}

@Yishai 2014-10-01 20:30:57

@JafarAli, this question is about mysql. In oracle you have to specify the generated column or use the row id.

@Winter 2017-01-12 15:15:06

Did you forget to rs.close() ? Is it necessary ?

@Yishai 2017-01-12 15:58:59

@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 2017-01-12 15:59:29

@Winter, if your keys are longs, then call rs.getLong(1) at the end there.

@peterong 2013-06-24 07:39:22

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!

@user207421 2013-06-24 08:21:36

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. Please stop posting your untested guesswork here.

@peterong 2013-06-24 08:59:30

Sorry, my mistake. I've corrected them.

@JDGuide 2014-03-12 11:32:08

    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); 
    }

@nos 2009-09-03 22:23:34

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)

@jW. 2009-09-03 22:16:05

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);

Related Questions

Sponsored Content

33 Answered Questions

[SOLVED] What's the simplest way to print a Java array?

  • 2009-01-03 20:39:39
  • Alex Spurling
  • 2153769 View
  • 1841 Score
  • 33 Answer
  • Tags:   java arrays printing

34 Answered Questions

[SOLVED] Create ArrayList from array

21 Answered Questions

[SOLVED] How do I call one constructor from another in Java?

  • 2008-11-12 20:10:19
  • ashokgelal
  • 806784 View
  • 2134 Score
  • 21 Answer
  • Tags:   java constructor

21 Answered Questions

[SOLVED] PDO Prepared Inserts multiple rows in single query

63 Answered Questions

[SOLVED] Avoiding != null statements

10 Answered Questions

[SOLVED] PostgreSQL Autoincrement

13 Answered Questions

[SOLVED] java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

  • 2012-08-30 08:13:15
  • Kanagavelu Sugumar
  • 305077 View
  • 109 Score
  • 13 Answer
  • Tags:   java jdbc

2 Answered Questions

[SOLVED] MySQL Query result is very slow

  • 2014-07-26 01:23:55
  • user3822347
  • 1731 View
  • 4 Score
  • 2 Answer
  • Tags:   java mysql sql jdbc

1 Answered Questions

[SOLVED] Java Prepared-Statement getGeneratedKeys() error

2 Answered Questions

[SOLVED] Where is the mysqli prepared statement query error?

Sponsored Content