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 :)

Improve this question
2

2 Answers 2

Reset to default
184

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

You Rock!!! Heheh, im not so able to read this warning message :)
+1 for RETURN_GENERATED_KEYS
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 ;)
@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.
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); }
|
145

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.