SQL (Java, h2): What's the best way to retrieve the unique ID of the single item I just inserted into my database?

Question

My current method is this:

SELECT TOP 1 ID FROM DATAENTRY ORDER BY ID DESC

This assumes the latest inserted item always has the highest unique ID (primary key, autoincrementing). Something smells wrong here.

Alternatives?

Answer 1

If using MySQL you can do

select last_insert_id();

If using MS SQL

select scope_identity();

For H2, I believe it's

CALL SCOPE_IDENTITY();

but I don't have any experience with that DB

Answer 2

If the JDBC driver supports it, you can also just use Statement#getGeneratedKeys() for that.

String sql = "INSERT INTO tbl (col) VALUES (?)";
preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, col);
preparedStatement.executeUpdate();
generatedKeys = preparedStatement.getGeneratedKeys();
if (generatedKeys.next()) {
    long id = generatedKeys.getLong(1);
} else {
    // Throw exception?
}