如何获得最后插入的ID到JSP中的JDBC(How to get last inserted id

我怎样才能从刚插入的行ID在jsp中？

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

我需要使用的ID从收件人将其插入包裹

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); ...

我试图用的ResultSet：

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

但没有奏效。

Answer 1:

尝试是这样的：

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

然后用得到的ID为你的下一个插件。 或者你也可以把它全部变成PROC。

Answer 2:

我找到了解决办法： ResultSet rs2=st.executeQuery("SELECT * FROM Recipients ORDER BY RecipientID DESC Limit 1;"); while(rs2.next()) { recipientID = rs2.getInt(1); } ResultSet rs2=st.executeQuery("SELECT * FROM Recipients ORDER BY RecipientID DESC Limit 1;"); while(rs2.next()) { recipientID = rs2.getInt(1); }

文章来源: How to get last inserted id into jdbc in jsp