In practice, we often run into all sorts of weird requirements, and this one is no exception. There is a table in the database with an auto-incrementing id column; after inserting a record, we need to return the id of the just-inserted row.

In a single-threaded environment, this is naturally a very simple problem: first get the database's current maximum id, assign MAX + 1 to the id in the insert statement, and then insert.

It's also easy to implement in Oracle: just create a sequence for the table and fetch the next value from it every time. The efficiency is solid and fair.

But returning the id of a just-inserted row in a multi-threaded MySQL environment is a bit tricky. The first idea that comes to mind is to add a lock around the insert, query the MAX(id) after insertion, and then release the lock. But obviously that's not very elegant.

After some searching online, there are many ways to solve this, but I ended up choosing the getGeneratedKeys method.

getGeneratedKeys is a method under Statement; its description is as follows:

Retrieves any auto-generated keys created as a result of executing this Statement object. If this Statement object did not generate any keys, an empty ResultSet object is returned.

Note:If the columns which represent the auto-generated keys were not specified,the JDBC driver implementation will determine the columns which best represent the auto-generated keys. Return: a ResultSet object containing the auto-generated key(s) generated by the execution of this Statement object Exception:

  1. SQLException if a database access error occurs or this method is called on a closed Statement
  2. SQLFeatureNotSupportedException if the JDBC driver does not support this method since 1.4

In short, this method can return the auto-generated value. Since each insert is performed using an instance created from a Connection, there are no thread-safety concerns and no need for locking.

It sounds a bit abstract, so let's demonstrate with a couple of lines of code:

Assume we have a table with an auto_increment id and a name column of type varchar(255). We only pass in name, and output the id or an error message.

Java
public int insert(String name) {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	int retNum = -1;
	try {
		conn = iDBPool.tryGetConnection();// get Connection
		if (conn == null) return -1;
		String sql = "insert into example_table(name) values(?)";
		ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); // 
		ps.setString(1,name);
		ps.executeUpdate();

		rs = ps.getGeneratedKeys(); 
		if(rs.next()){
			retNum = rs.getInt(1);
		}else {
			retNum = -1;
		}

	} catch (Exception e) {
		Log.getLog().info(e.getMessage());
		e.printStackTrace();
	} finally {
		iDBPool.release(conn);
		try {
			if (null != ps) ps.close();
			if (null != rs) rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	return retNum;
}
That's it.