将resultset列值分配给变量以在另一个SQL语句中使用？？JAVA

Question

我正在用Java创建一个以数据为中心的Web服务，用于部署到Glassfish。到目前为止，我所有的方法都正常工作，只有一种除外

我试图根据下面的代码，将结果集中的一个值分配给另一个SQL语句中使用的变量。我不确定这是否可能，或者我的SQL是否错了，但如果有任何想法，我将不胜感激

ResultSet rset1 = stmt1.executeQuery("SELECT * 
                                        FROM WorkOrder 
                                       WHERE WorkOrderID = '"+workOrderID+"'");
Integer custID = rset1.getInt(3);

ResultSet rset2 = stmt2.executeQuery("SELECT * 
                                        FROM Customer 
                                       WHERE CustID = '"+custID+"'");
Integer quoteID = rset1.getInt(2);

ResultSet rset3 = stmt3.executeQuery("SELECT * 
                                        FROM Quote 
                                       WHERE QuoteID = '"+quoteID+"'");

Answer 1

OMG Ponies提供的注释非常重要，值得注意，但并不能真正回答这个问题。AVD也是正确的。我把它整理了一下，包括准备好的声明。请使用事先准备好的声明。它们会帮助你晚上睡觉

PreparedStatement pstmt1 = con.prepareStatement(
    "SELECT * FROM WorkOrder WHERE WorkOrderID = ?");
PreparedStatement pstmt2 = con.prepareStatement(
    "SELECT * FROM Customer WHERE CustID = ?");
PreparedStatement pstmt3 = con.prepareStatement(
    "SELECT * FROM Quote WHERE QuoteID = ?");

pstmt1.setInt(1, workOrderId)
ResultSet rset1 = pstmt1.executeQuery();

// test validity of rset1 
if(rset1.next()) {
    pstmt2.setInt(1, rset1.getInt(3))
    ResultSet rset2 = pstmt2.executeQuery();

    // test validity of rset2
    if(rset2.next()) {
        pstmt3.setInt(1, rset1.getInt(2))
        ResultSet rset3 = pstmt3.executeQuery();
    }
}