MySQL and Java - get last inserted value identifier (JDBC)

Question

Possible duplicate:
How to get insert ID in JDBC?

Hi, I am using JDBC to connect to a database through Java.

Now, I make some insert request, and I need to get the identifier of the last inserted value (so, after stmt.executeUpdate ).

I do not need something like SELECT id FROM table ORDER BY id DESC LIMIT 1 , because I may have problems with concurrency.

I just need to get the id associated with the last insertion (about my instance of Statement).

I tried this, but it doesn't seem to 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, each 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(). 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 :)

Answer 1

Could you just change:

 numero = stmt.executeUpdate(query); 

at

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

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

Refresh . There seems to be a lot of confusion about this answer, but I assume that people who are embarrassed don't read it in the context of the question asked. If you take the code provided by OP in your question and replace one line (line 6) that I suggest, everything will work. The variable numero completely irrelevant, and its value is never read after setting it.

Answer 2

Alternatively, you can:

 Statement stmt = db.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); numero = stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()){ risultato=rs.getInt(1); } 

Instead, use the Shawn Yar script instead of your script.