What's the best way to get the last inserted id using sqlite from Java?
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();}