How to get last inserted id into jdbc in jsp

Question

How can I get ID from just inserted row in jsp?

        PreparedStatement ps = con.prepareStatement("INSERT INTO Recipients(CustomerID,Name, Street, City, ZipCode, PhoneNumber, EmailAddress,"
            + " ContactPersonName, ContactPersonSurname, ContactPersonPhoneNumber,ContactPersonEmailAddress) "
            + "values(?,?,?,?,?,?,?,?,?,?,?)");

And I need use id from Recipients to insert it into Parcels

PreparedStatement ps2 = con.prepareStatement("INSERT INTO Parcels(TransportTypeID,CustomerID,RecipientID,ParcelAmount, DimLength, DimHeight, DimWidth, ParcelWeigth, Content,"
            + " AdditionalDescription, IsCustomParcel, ReceiptFee, InsuranceAmount, OwnPick, DispatchDate) "
            + "values((?,?,?,?,?,?,?,?,?,?,?,?,?,?,?");

    ps2.setString(1,transportTypeID);
    ps2.setString(2,customerID);
    ps2.setInt(3,recipientID); ...

I was trying to use ResultSet:

int recipientID=0;
    ResultSet rs2=st.executeQuery("SELECT * FROM Recipients");
    if(rs2.last())
           {
            recipientID = rs2.getInt("RecipientID");
           }

but it didn't work.

Which rdbms are you using? For example, if you are using Oracle, you can use a CallableStatement rather than a PreparedStatement and then use the "RETURNING id INTO ?" to get the return value. — Glenn, Aug 17 '12 at 23:26

Glenn · Accepted Answer · 2012-08-18T11:14:45.993

2

Try something like this:

String query = "BEGIN " +
               "  INSERT INTO Recipients( CustomerID, Name, Street, City, ZipCode, PhoneNumber" + 
               "                         ,EmailAddress, ContactPersonName, ContactPersonSurname" +
               "                         ,ContactPersonPhoneNumber, ContactPersonEmailAddress) " +
               "    VALUES(?,?,?,?,?,?,?,?,?,?,?) RETURNING id into ?; " +
               "END;";

CallableStatement cs = conn.prepareCall(query);

cs.setString(1,transportTypeID);
cs.setString(2,customerID);
cs.setInt(3,recipientID);
...
cs.registerOutParameter(12, java.sql.Types.INTEGER);

cs.execute();

int id = cs.getInt(12);

Then use the resultant id for your next insert. Or you could also put it all into a proc.

edited Aug 18 '12 at 11:14

answered Aug 18 '12 at 00:08

Glenn

8,932
2
41
54

I'm using mysql-connector-java. I can't use "OracleTypes" – Paul Aug 18 '12 at 09:34
@Paul I removed the Oracle specific type. It was not necessary. – Glenn Aug 18 '12 at 11:16
now I'm getting error: java.sql.SQLException: Parameter number 12 is not an OUT parameter – Paul Aug 18 '12 at 11:59
@Paul Probably a counting issue? Count the number of question marks and see that things line up. But it may be easier to start with a simple test, a two column table, insert one return the other. Make sure it works in the simple case. Also, take a look at this and other samples: http://stackoverflow.com/questions/5942203/callablestatement-registeroutparameter-multiple-row-result – Glenn Aug 18 '12 at 12:42
Same question actually answered here as well: http://stackoverflow.com/questions/3552260/plsql-jdbc-how-to-get-last-row-id – Glenn Aug 18 '12 at 12:44

score 0 · Answer 2 · answered Sep 06 '12 at 17:18

0

I've found solution: ResultSet rs2=st.executeQuery("SELECT * FROM Recipients ORDER BY RecipientID DESC Limit 1;"); while(rs2.next()) { recipientID = rs2.getInt(1); }

answered Sep 06 '12 at 17:18

Paul

25
1
5

How to get last inserted id into jdbc in jsp

2 Answers2

Linked