Sqlite JDBC driver not supporting RETURN_GENERATED_KEYS

Question

I am using Xerial latest jdbc driver for sqlite (version 3.7.2) It does not seem to have support for Statement RETURN_GENERATED_KEYS. I keep getting "not implemented by SQLite JDBC driver" in the SQLException.

I really do not want to make another select call (e.g select id from tasks order by 1 desc limit 1 ) in order to get the latest Id created by an auto-increment field. I need to use that id to populate a field in another table.

Are there any better ways of achieving this using sqlite-jdbc driver ?

Answer 1

If upgrading/replacing the JDBC driver is not an option (the SQLiteJDBC seem to support it), then you really need to fire a SELECT last_insert_rowid() query to obtain the generated key. To avoid race conditions with concurrent inserts, fire it in a transaction.

connection = database.getConnection();
connection.setAutoCommit(false); // Starts transaction.
preparedStatement = connection.prepareStatement(INSERT_SQL);
preparedStatement.setSomething(something);
// ...
preparedStatement.executeUpdate();
statement = connection.createStatement();
generatedKeys = statement.executeQuery("SELECT last_insert_rowid()");
if (generatedKeys.next()) {
    generatedKey = generatedKeys.getLong(1);
}
connection.commit(); // Commits transaction.

Answer 2

I'm also using sqlite-jdbc-3.7.2.jar and found that using RETURN_GENERATED_KEYS does fail, however merely doing a statement.execute(sql) followed by resultset = statement.getGeneratedKeys() and meta = resultset.getMetaData() shows that a column name last_insert_rowid() is available in the resultset. So resultset.getInt("last_insert_rowid()") does indeed return the newly inserted rowid without an additional select.