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.)
Add a comment |
2 Answers
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(); } 
Comments
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.
5 Comments
lmat - Reinstate Monica
In what way is "the next value from the key before the insert" any better than "the largest value after the insert"? Both have the same type of race condition, and both rely on monotonic, single increments of the key.
Brian
The assumption is that the key retrieved before the insert would not be based on any query of the target table but from some key provider. In Oracle terms it would be Select sequence_name.nextval from dual. I don't know the particulars for SQL lite but some api call that returns a key in its own autonomous transaction completely independent of the insert. That said...use getGeneratedKeys().
Martin Pfeffer
row = stmt.getGeneratedKeys().getInt(1);
Donal Fellows
You don't know that the next key issued will be one greater than the current max key; it's possible that the sequence might've moved on since then (e.g., because of deleted rows). You want to get the key that the DB issues, whatever that is, and for it to do all the worrying about keeping things unique. That's part of its job.
Sam Ginrich
@Martin Pfeffer ... which seems to be the only valid answer in case of concurrency