102

Possible Duplicate:
How to get the insert ID in JDBC?

Hi, I'm using JDBC to connect on database through out Java.

Now, I do some insert query, and I need to get the id of last inserted value (so, after a stmt.executeUpdate).

I don't need something like SELECT id FROM table ORDER BY id DESC LIMIT 1, because I may have concurrency problems.

I Just need to retrieve the id associated to the last insertion (about my instance of the Statement).

I tried this, but seems it doesn't work on JDBC :

public Integer insertQueryGetId(String query) {
    Integer numero=0;
    Integer risultato=-1;
    try {
        Statement stmt = db.createStatement();
        numero = stmt.executeUpdate(query);

        ResultSet rs = stmt.getGeneratedKeys();
        if (rs.next()){
            risultato=rs.getInt(1);
        }
        rs.close();

        stmt.close();
    } catch (Exception e) {
        e.printStackTrace();
        errore = e.getMessage();
        risultato=-1;
    }
  return risultato;
}

In fact, every time risultato = -1, and I get java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().

How can I fix this problem? Thanks Stackoverflow People :)

Community
  • 1
  • 1
markzzz
  • 47,390
  • 120
  • 299
  • 507
  • See my question: http://stackoverflow.com/questions/4224228/preparedstatement-with-statement-return-generated-keys – Buhake Sindi Nov 22 '10 at 14:55
  • Duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – BalusC Nov 22 '10 at 14:59

2 Answers2

183

Wouldn't you just change:

numero = stmt.executeUpdate(query);

to:

numero = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

Take a look at the documentation for the JDBC Statement interface.

Update: Apparently there is a lot of confusion about this answer, but my guess is that the people that are confused are not reading it in the context of the question that was asked. If you take the code that the OP provided in his question and replace the single line (line 6) that I am suggesting, everything will work. The numero variable is completely irrelevant and its value is never read after it is set.

Sean Bright
  • 118,630
  • 17
  • 138
  • 146
  • 1
    You Rock!!! Heheh, im not so able to read this warning message :) – markzzz Nov 22 '10 at 15:00
  • 1
    +1 for `RETURN_GENERATED_KEYS` – bizzr3 Oct 08 '14 at 11:27
  • yeah, this does not really work. Try inserting a couple of records, delete all of them (NOT TRUNCATE) and see if the correct ID is retreived ;) – Pierre Mar 29 '15 at 00:54
  • The referenced manual page states that the returned value is _the number of records changed_ and not the _index value(s)_ (it can't do that with a single integer, there might be several). So this won't work! See Buhake Sindi's answer for a way which should actually work. – wu-lee Jun 17 '15 at 11:39
  • 3
    @wu-lee The return value is irrelevant (in OP's question, `numero` is not used). You'll notice that he iterates over the `ResultSet` returned by `Statement.getGeneratedKeys()`. Let me know if you have other questions. – Sean Bright Jun 17 '15 at 11:42
  • 2
    That is just wrong... Method executeUpdate returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing. You have to create resultset and get the last ID like this: rs = st.getGeneratedKeys(); if(rs.next()) { insertId = rs.getInt(1); } – slodeveloper Apr 08 '16 at 20:30
  • I see now, ... Then the varible "numero" is irrelevant as you said. – slodeveloper Apr 26 '16 at 21:53
  • thanks it helped me alot ! – danny Apr 15 '17 at 14:17
  • a hidden gem :) – Davor Hrg Nov 15 '17 at 14:50
146

Alternatively you can do:

Statement stmt = db.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
numero = stmt.executeUpdate();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()){
    risultato=rs.getString(1);
}

But use Sean Bright's answer instead for your scenario.

Ferhat KOÇER
  • 3,890
  • 1
  • 26
  • 26
Buhake Sindi
  • 87,898
  • 29
  • 167
  • 228