13

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?

Daddy Warbox
  • 4,500
  • 9
  • 41
  • 56
  • FYI, nearly duplicate Question: [Is there a way to retrieve the autoincrement ID from a prepared statement](http://stackoverflow.com/q/1376218/642706) – Basil Bourque Jul 17 '15 at 03:38

2 Answers2

31

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?
}
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • 2
    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. – BalusC Apr 15 '10 at 17:48
  • Okay great it works! Is this better than IDENTITY(), though? – Daddy Warbox Apr 15 '10 at 17:49
  • 2
    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. – BalusC Apr 15 '10 at 17:50
  • 2
    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(). – Dave Moten Jun 06 '15 at 23:55
7

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

Sean
  • 4,365
  • 1
  • 27
  • 31