Saturday, April 29, 2006
MySql Getting LastInsertId
LastInsertId refers to the unique identifier that is generated for tuple/row of the table when you do a insert into the table. You should have "AUTO_INCREMENT" column in the table to get the last insert id. MySql needs this column to be UNIQUE. The value generated for such column will start from 1.
A simple table for our discussion would be.
create table autotable (id int auto_increment unique, primary key (id));
How do you get the last insert id when you insert a tuple?
This function will try to get the last insert id information given the prepared statement (for MySql JDBC driver). If the information is not available, the -1 value is returned.
Let us execute the prepared statement:
The lastInsertId value will be 0 in this case, though MySql inserts value 1 for the auto_increment field (id) of the table.
How do we fix this mismatch?
You have to make sure you will not insert values to auto_increment field, in which case MySql generates the value and you can get the generated value using the getLastInsertId function.
So the above piece of code should be written like this:
lastInsertId value will now be 1 (auto generated value for this insert).
A simple table for our discussion would be.
create table autotable (id int auto_increment unique, primary key (id));
How do you get the last insert id when you insert a tuple?
This function will try to get the last insert id information given the prepared statement (for MySql JDBC driver). If the information is not available, the -1 value is returned.
public Integer getLastInsertId(PreparedStatement ps) {
int lastInsertId = -1;
PreparedStatement lastInsertIdPs =
ps.getConnection().prepareStatement("SELECT LAST_INSERT_ID()");
ResultSet rs = lastInsertIdPs.executeQuery();
while(rs.next()) {
lastInsertId = rs.getInt(1);
}
return new Integer(lastInsertId);
}
Let us execute the prepared statement:
PreparedStatement preparedStatement =
connection.prepareStatement("insert into autotable (id) values (?)");
preparedStatement.setObject(1, new Integer(1));
preparedStatement.execute();
Integer lastInsertId = getLastInsertId(ps);
The lastInsertId value will be 0 in this case, though MySql inserts value 1 for the auto_increment field (id) of the table.
How do we fix this mismatch?
You have to make sure you will not insert values to auto_increment field, in which case MySql generates the value and you can get the generated value using the getLastInsertId function.
So the above piece of code should be written like this:
PreparedStatement preparedStatement =
connection.prepareStatement("insert into autotable (id) values (?)");
preparedStatement.setObject(1, null); // Let MySql generate the value for this field
preparedStatement.execute();
Integer lastInsertId = getLastInsertId(ps);
lastInsertId value will now be 1 (auto generated value for this insert).
