last_insert_id()

[Andreas Bystrom]

Hi all!
If I add a record that has a id field with auto_increment, how do I get the id created for this record?
I'm using mySQL and there is this method latest_insert_id() but I don�t get how I could invoke it using java.sql library. Has someone got a clue?
// Andreas

[Juanjo Bazan]

The mySQL functions do not affect your java code.
You have to use the java.sql package as usual, just modify your sql statement:

String updateTable="INSERT INTO table VALUES('1','2',3,null)";
String delete="DELETE FROM table WHERE id=LAST_INSERT_ID()";
...
Statement sta=null;
try{
   sta=c.createStatement();
   int results=sta.executeUpdate(updateTable);
   sta.executeUpdate(deletePrecios);
}catch(SQLException ex){//manage ex}
...

HTH

[Andreas Bystrom]

I actually wanted the id that was last inserted, and it was not to be used in a new sql statement. I finally found a way:

 
PreparedStatement getLastInsertId = con.prepareStatement("SELECT LAST_INSERT_ID()");
ResultSet rs = getLastInsertId.executeQuery();
if (rs.next())
{
 insertId = rs.getLong("last_insert_id()");            
}

Then the long value of the id last inserted is in the variable insertId
// Andreas

[Bojan Tomic]

I was just looking into the exact same issue.
Here's the preferred solution: http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys

In short:

t_pstmt = a_connection.prepareStatement("YOUR INSERT GOES HERE", PreparedStatement.RETURN_GENERATED_KEYS);
int rownum = t_pstmt.executeUpdate();
t_resultset = t_pstmt.getGeneratedKeys();
if( rownum != 0 && !t_resultset.next()) {
      t_iVersion = t_resultset.getInt(1);
}