How to get the Generated insert ID in JDBC?

Question

My source code has the following structure:

SourceFolder
AddProduct.jsp
Source Packages
-Controller(Servlets)
SaveProduct.java
-Model(Db Operations)
ProductDbOperations.java

I am inserting a new product into the product table and at the same time I am inserting an entry into product_collection table (product_id | collection_id).

To insert an entry into the product_collection table i need to get generated id from product table. After that a new entry is inserted into the product_collection table.

Also, I am not using any Framework and am using Netbeans 7.3.

Problem:

A new entry is inserted into the product table with this piece of code

IN: ProductDbOperations.java

try
{
    this.initConnection(); // Db connection 
    pst = cn.prepareStatement("INSERT INTO product values('"+name+"', "+quantity+", "+price+")");
    rs = pst.executeUpdate();
}
catch(SQLException ex)
{   

}

I Also used the solution at following link which doesn't works for me. I didn't got any SQL exception

How to get the insert ID in JDBC?

so help me find out why this code not working for me . Thanks a million.

How do you know you don't get an SQLException, your catch block is empty? Also did you try the getGeneratedKeys() method? — Kayaman, Sep 19 '13 at 09:52
What does "doesn't work for me" mean? What happens? At least print the stack trace in your catch block: `catch (SQLException ex) { ex.printStackTrace(); }` — Jesper, Sep 19 '13 at 09:52
@Kayaman Actually The code is very large so let me explain .. i m using a status variable of type String to get exception as string and function return a string like status = ex.toString(); after try catch block return status; — Surinder ツ, Sep 19 '13 at 09:56

score 8 · Accepted Answer · answered Sep 19 '13 at 09:56

Not all drivers support the version of getGeneratedKeys() as shown in the linked answer. But when preparing the statement, you can also pass the list of columns that should be returned instead of the "flag" Statement.RETURN_GENERATED_KEYS (and passing the column names works more reliably in my experience)

Additionally: as javaBeginner pointed out correctly, your usage of a prepared statement is wrong. The way you do it, will still leave you wide open to SQL injection.

// run the INSERT
String sql = "INSERT INTO product values(?,?,?)";
pst = cn.prepareStatement(sql, new String[] {"PRODUCT_ID"} );
pst.setString(1, name);
pst.setInt(2, quantity);
pst.setInt(3, price);
pst.executeUpdate();

// now get the ID:
ResultSet rs = pst.getGeneratedKeys();
if (rs.next()) {
   long productId = rs.getLong(1);
}

Note that the column name passed to the call is case-sensitive. For Oracle the column names are usually uppercase. If you are using e.g. Postgres you would most probably need to pass new String[] {"product_id"}

Please explain why you used this new String[] {"PRODUCT_ID"} The array of String — Surinder ツ, Sep 19 '13 at 10:00
pls tell me why System.out.println(); not working for me. Any Idea? — Surinder ツ, Sep 19 '13 at 10:13

score 2 · Answer 2 · answered Sep 19 '13 at 09:50

2

The way you are using is not the proper way of using preparedstatement

use the following way

    pst = cn.prepareStatement("INSERT INTO product values(?,?,?)");
pst.setString(1,name);
pst.setInt(2,quantity);
pst.setInt(3,price);
pst.executeUpdate();

answered Sep 19 '13 at 09:50

SpringLearner

13,738
20
78
116

@javaBegginer I need the generated id from the product table. – Surinder ツ Sep 19 '13 at 09:57
1

While that is a very good advice it does not cover the real problem: how to get the generated key. – Sep 19 '13 at 09:58
Can you tell me why using (?,?,?) for values is good for inserting or updating. – Surinder ツ Sep 19 '13 at 09:59
@Surinderツ It prevents SQL injection and typos for example. – Kayaman Sep 19 '13 at 10:00

score 0 · Answer 3 · edited Sep 19 '13 at 10:19

0

Yes there is a way to retrieve the key inserted by SQL. You can do it by: Using Statement.RETURN_GENERATED_KEYS in your previous insert and get the key which can be used in further insert

e.g:

String query = "INSERT INTO Table (Col2, Col3) VALUES ('S', 50)";
Statement stmt = con.createStatement();
int count = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

edited Sep 19 '13 at 10:19

Mark Rotteveel

100,966
191
140
197

answered Sep 19 '13 at 09:59

Vihar

3,626
2
24
47

What does "SQL u" mean? – Sep 19 '13 at 10:10
Just a typo mistake...... Yes there is a way to retrieve the key inserted by SQL ,u can do it by..... – Vihar Sep 19 '13 at 10:19
1

I have edited your answer, please use actual words and do not abbreviate to single letters like u (you) and b (be), it makes your answer harder to read. – Mark Rotteveel Sep 19 '13 at 10:20

How to get the Generated insert ID in JDBC?

3 Answers3