CODEWITHSUNDEEP
javasqlmysqljdbc
Amir Rachum
Amir Rachum

Reputation: 79685

Getting index of inserted rows from a MySQL database

I'm using Java (jdbc) to interact with a MySQL database. I have table with a primary index which is AUTO INCREMENT. When I insert a row, I need to get the index it just received. How do I do that?

Upvotes: 8

Views: 4164

Answers (3)

Jellicle
Jellicle

Reputation: 30256

Thanks to John Boker for his excellent response.

If you wish to use a PreparedStatement, you can still use RETURN_GENERATED_KEYS, but you have to apply the commands differently:

PreparedStatement ps = mysql.prepareStatement(
    "INSERT INTO myTable (colA, colB, colC) VALUES (?, ?, ?)",
     Statement.RETURN_GENERATED_KEYS );
ps.setString(1, "My text");
ps.setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime()););
ps.setInt(3, 5150);
ps.executeUpdate();
ResultSet results = ps.getGeneratedKeys();
results.next(); // Assume just one auto-generated key; otherwise, use a while loop here
System.out.println(results.getInt(1)); // there should only be 1 column in your results: the value of the auto-generated key
  1. Add the RETURN_GENERATED_KEYS param in the prepareStatement() function.
  2. Get results not from statement.executeUpdate() but from statement.getGeneratedKeys().

Upvotes: 3

barti_ddu
barti_ddu

Reputation: 10309

Alternatively, using Spring JDBC it would look like:

 Map<String, Object> map = new HashMap<String, Object>();
 map.put("column1", "test");
 map.put("column2", Boolean.TRUE);

 SimpleJdbcInsert insert = new SimpleJdbcInsert(template).withTableName("table").usingGeneratedKeyColumns("id");
 int id = insert.executeAndReturnKey(map).intValue();

Upvotes: 0

John Boker
John Boker

Reputation: 83729

From: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-basic.html#connector-j-usagenotes-last-insert-id

stmt.executeUpdate(
        "INSERT INTO autoIncTutorial (dataField) "
        + "values ('Can I Get the Auto Increment Field?')",
        Statement.RETURN_GENERATED_KEYS);

//
// Example of using Statement.getGeneratedKeys()
// to retrieve the value of an auto-increment
// value
//

int autoIncKeyFromApi = -1;

rs = stmt.getGeneratedKeys();

if (rs.next()) {
    autoIncKeyFromApi = rs.getInt(1);
} else {

    // throw an exception from here
}

rs.close();

rs = null;

Upvotes: 8

Related Questions