12

I am connecting to a Java DB database with JDBC and want to retrieve the id (which is on auto increment) of the last record inserted.

I see this is a common question, but I see solutions using for example MS SQL Server, what is the equivalent for Java DB?

Community
  • 1
  • 1
Kryptic
  • 1,922
  • 2
  • 21
  • 29

3 Answers3

26

No need to use a DBMS specific SQL for that.

That's what getGeneratedKeys() is for.

When preparing your statement you pass the name(s) of the auto-generated columns which you can then retrieve using getGeneratedKeys()

PreparedStatement pstmt = connection.prepareStatement(
     "insert into some_table (col1, col2, ..) values (....)", 
      new String[] { "ID_COLUMN"} ); 

pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys(); // will return the ID in ID_COLUMN

Note that column names are case sensitive in this case (in Derby and many other DBMS).
new String[] { "ID_COLUMN"} is something different than new String[] { "id_column"}

Alternatively you can also use:

connection.prepareStatement("INSERT ...", PreparedStatement.RETURN_GENERATED_KEYS);
  • I just wanna confirm this, thought I'm 99% sure, will this still return the intended auto-generated column value even if multiple insert statements have been executed in the time between inserting and fetching column value using `getGeneratedKeys()`? – Akash Agarwal Feb 21 '16 at 06:30
  • Doesn't work for me, it also looks like [JDBC doesn't fully support Derby's identity columns](https://groups.google.com/d/msg/activejdbc-group/aU1fRiRAffs/XMIE3OOvzRkJ)? I get an exception saying that my table doesn't have an auto-generated column named 'id', which it clearly does :) – Alex Jan 14 '19 at 20:40
  • 1
    Anyway I solved by using `PreparedStatement.RETURN_GENERATED_KEYS` instead of the string array as the second argument in the PreparedStatement. (I'm pretty sure my problem was not a typo if anyone was wondering). – Alex Jan 14 '19 at 20:56
  • 1
    @Alex: I am 100% the above code works with Apache Derby. Your link is for a group that deals with ActiveJDBC - which is an ORM (="Obfuscated Relational Model") framework **on top** of JDBC. "JDBC" itself doesn't support anything - it's just a specification that needs to be implemented by the vendor of the JDBC driver. That has nothing to do with the JDBC driver Derby is providing. If _that_ framework doesn't work with Derby, it's neither "JDBC's" fault nor Derby's fault. Maybe you didn't specify the column correctly, e.g. `new String[] { "ID"}` is something else than `new String[] { "id"}` –  Jan 14 '19 at 21:00
  • Oh I see, thanks for the clarification. And yes apparently column names are case sensitive, I was sure they were working both ways, thanks again @a_horse_with_no_name. I'll leave my comment anyways just to provide a more general way to get the columns. – Alex Jan 14 '19 at 21:32
3

You may be able to get what you're looking for using the IDENTITY_VAL_LOCAL function. (Derby Reference)

This function is supposed to return "the most recently assigned value of an identity column for a connection, where the assignment occurred as a result of a single row INSERT statement using a VALUES clause."

It's worth noting that this function will return DECIMAL(31,0), regardless of the actual data type of the corresponding identity column.

Also, this only works for single row inserts that contain a VALUES clause.

nybbler
  • 4,793
  • 28
  • 23
1

For those who have issues getting the generated autoincrement id like I used to for Java Derby, my answer can be of help.

stmt.executeUpdate("INSERT INTO xx(Name) VALUES ('Joe')", Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
  int autoKey = rs.getInt(1); //this is the auto-generated key for your use
}

Answer copied from here

CanCoder
  • 1,073
  • 14
  • 20