How can I get auto increment values in batch update query (Java,MySQL)?

Question

I am using Spring-Jdbc template(first timer) to create MySql repository. I have tables that use AutoIncrement columns as primary key.

I wonder if there a way to get newly generated Ids (autoInc) with each successful batch create statement?

Any pointers or sample would be a great help.

Thanks Panks

Answer 1

Use getGeneratedKeys() method from your Statement or PreparedStatement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

This call may throw java.sql.SQLFeatureNotSupportedException if the JDBC driver, that you are using, does not support this method.

Sample code snippet:

String sql_insert =  
    "insert into my_table ( non_auto_incrmnt_fld_names_,_separated ) " +  
                 " values ( record1 ), ( record2 )"; // append as many as required  
...  
int rowsAffected = stmtObject.executeUpdate( sql_insert, Statement.RETURN_GENERATED_KEYS );  
ResultSet rs = stmtObject.getGeneratedKeys();  

//******************************************************  
rs.last();  
int rows = rs.getRow();  
System.out.println( "Generated keys count: " + rows );  
int currentRow = 1;  
rs.beforeFirst();  
//******************************************************/  

while( rs.next() ) {  
    System.out.println( /**/( currentRow++ ) + " = " + /**/rs.getInt( 1 ) );  
} // while rs