Java PreparedStatement retrieving last inserted ID

Question

This answer to this question done this way seems to be very difficult to find on the internet. Basically I am inserting values into a MySQL database using PreparedStatement. I use the PreparedStatement to escape the data to prevent SQL Injection attacks. The problem is, there is now way retreving those keys.

String query="Insert INTO Table_A(name, age) (?, ?)";
//String query="Insert INTO Table_A(name, age) ('abc','123' )";//Doesn't escape
PreparedStatement prest;
prest = con.prepareStatement(query);
prest.setString(1,"abc");
prest.setInt(2,123);
prest.executeUpdate();
//prest.executeUpdate(query, PreparedStatement.RETURN_GENERATED_KEYS); Throws an error
//prest.executeQuery(); Throws an error

So how can I escape input and use PreparedStatements in Java?

Pass the `RETURN_GENERATED_KEYS` to the `prepareStatement()` call and use `executeUpdate()` to exeute the insert. If that doesn't work, please provide the exception. — ZeissS, Apr 01 '11 at 12:14
@BalusC, that is exactly what I was looking for. To bad its not an answer! — Devin Dixon, Apr 01 '11 at 12:53

score 73 · Answer 1 · answered Jun 18 '13 at 12:29

pass Statement.RETURN_GENERATED_KEYS in prepareStatement() along with your query. And then use getGeneratedKeys() of PreparedStatement to get the ResultSet containing your inserted auto_incremented_id.

String query="Insert INTO Table_A(name, age) (?, ?)";
                //String query="Insert INTO Table_A(name, age) ('abc','123' )";//Doesn't escape
                PreparedStatement prest;
                prest = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                prest.setString(1,"abc");
                prest.setInt(2,123);
                prest.executeUpdate();
                //prest.executeUpdate(query, PreparedStatement.RETURN_GENERATED_KEYS); Throws an error
                //prest.executeQuery(); Throws an error
                ResultSet rs = prest.getGeneratedKeys();
                if(rs.next())
                {
                    int last_inserted_id = rs.getInt(1);
                }

i want to add this query before my insert statement to allow emojis in my table -> SET NAMES utf8mb4; but now i am unable to get last insert id from my insert query in prepared statement. what should i do — Onkar Musale, May 29 '19 at 07:07

score 4 · Answer 2 · answered Apr 01 '11 at 12:16

4

Use java.sql.ResultSet java.sql.Statement.getGeneratedKeys() to retrieve your generated keys.

answered Apr 01 '11 at 12:16

Dave G

9,639
36
41

score 0 · Answer 3 · answered Apr 01 '11 at 12:14

0

If you can't use RETURN_GENERATED_KEYS, use another statement (a query this time) to do

SELECT last_insert_id()

Make sure that you definitely have an auto-increment primary key column in that table.

answered Apr 01 '11 at 12:14

Jonathan

13,354
4
36
32

Does this also work for the situation that there were multiple inserts to the same table that you're trying to get id for one of the inserts? – user917099 May 10 '16 at 22:37

Java PreparedStatement retrieving last inserted ID

3 Answers3

Linked

Related