Is there a way to get the auto increment id from a prepared statement

Question

Is there a way to get an automatically generated key from a database query when using a Java query with prepared statements.

For example, I know that 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 make a tab 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 javadoc that PreparedStatements cannot return an Auto Generated ID.

Answer 1

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

Answer 2

There are several ways, and it seems that different jdbc drivers handle things a little differently or not at all in some cases (some of them will give only automatically generated primary keys, not other columns), but the main forms

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

Or use this form:

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

Answer 3

Yes, there is a way. I just found this hiding in a java document.

They must pass the AutoGeneratedKeys identifier as follows

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

Answer 4

I am one of those who looked through several threads looking for a solution to this problem ... and finally it worked. FOR THESE USES of jdbc: oracle: thin: with ojdbc6.jar PLEASE ACCEPT NOTE: You can use both 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 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 a real value of col Id System.out.println(id); } } catch (SQLException e) { // } 

Basically, try not to use Method1, if you just need the value SEQ.Nextval, b'cse will just return a RowID ref so you can hack your search in your head to use it, which is also not suitable for all data types you tried casting! This may work fine (return actual val) in MySQL, DB2, but not in Oracle.

And, disable your SQL Developer, Toad, or any client that uses the same INSERT login session when you are debugging. It MAY NOT affect you every time (debugging call) ... until you find that your applications are frozen without any exceptions for some time. Yes ... without exception!

Answer 5

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