What's the best way to get the last inserted id using sqlite from Java?

Question

What's the best way to get the last inserted id using sqlite from Java? Google is giving me different answers--some say select the last-insert-rowid; others say call statement.getGeneratedKeys(). What's the best route to take? (I just want to return the id, not use it for other inserts or anything.)

Answer 1

Use getGeneratedKeys() if your JDBC driver supports it. You don't want to muck around trying to get the key yourself after the insert. If your driver does not support getGeneratedKeys() then I would get the next value from the key before the insert.

Answer 2

Either approach executes the same exact SQL statement, but java.sql.Statement.getGeneratedKeys() is more portable to different underlying databases.

Using either the sqlite3_last_insert_rowid() C API function or the last_insert_rowid() SQL function is the correct way to retrieve the rowid generated during the last insert.

SQLite supports the SQL scalar function syntax as:

SELECT function-name();

Therefore, if you do not have access to the C API directly, you can invoke the scalar function last_insert_rowid() via a SELECT statement.

If you look at the source code for the SQLiteJDBC implementation (is this what you're using?) you will see that this is exactly what the author of that JDBC wrapper has done:

ResultSet getGeneratedKeys() throws SQLException {
    if (getGeneratedKeys == null) getGeneratedKeys = conn.prepareStatement(
        "select last_insert_rowid();");
    return getGeneratedKeys.executeQuery();
}