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

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?

Permeance

FYI, nearly duplicate Question: Is there a way to retrieve the autoincrement ID from a prepared statement – Contemplate

Answer 1

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?
}

Numbskull

To my experience and knowledge, all current JDBC driver versions of the major RDBMS servers like MySQL, MSSQL, PostgreSQL, Oracle and DB2 supports it (it took a while for Oracle and PostgreSQL, up to about one year ago they didn't support it). Unfortunately I don't have experience with H2, so I can't tell from top of head, but a quick glance on Google learns me that it does support it. – Numbskull

Okay great it works! Is this better than IDENTITY(), though? – Permeance

It does IDENTITY() "under the hoods", but now in a more abstract and DB-agnostic way using pure JDBC API. You have less maintenance headache whenever you'd like to switch of database. If you did it all the right and standard-SQL way, then all you basically need to do is to replace JDBC driver and URL/login. You can keep the coding intact. – Numbskull

a quick note that this only works in h2 for the last generated key in the case where the insert statement inserted multiple records. So h2 only partially supports getGeneratedKeys(). – Purposeless

Answer 2

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

Expulsive

Hmm. I wonder if there's an H2 equivalent... – Permeance

Sorry, didn't see the h2 tag initially. Updated my answer with what I believe to be the equivalent. – Expulsive

Well I will be inserting with PreparedStatement instances in Java, so I might need IDENTITY() instead. But yeah, that should do it. Thanks. – Permeance

H2's SCOPE_IDENTITY() won't work as expected. See groups.google.com/d/msg/h2-database/0xJsP993RHY/0LERpBvtLNUJ – Hoodoo

@Hoodoo IMHO it usually works as expected, because all you need is to use SELECT SCOPE_IDENTITY(); instead of SET. That's all. – Washko

H2 "SELECT SCOPE_IDENTITY() as LAST_ID;" then resultSet.getInt("LAST_ID"); – Zane

As of H2 2.0.202 SCOPE_IDENTITY() is onlz supported in MSSQL mode. – Deme