java - 获取java.sql.SQLException:在尝试多个查询时ResultSet关闭ERROR后不允许进行操作

我正在尝试根据用户输入从数据库中获取数据，以便用户可以跟踪订单。该文件可以在IDE中正常编译，但是会在浏览器中引发以下错误：

javax.servlet.ServletException：java.sql.SQLException：ResultSet关闭后不允许进行操作

form.jsp

<form action="viewOrderHistory" method="Post" id="invoiceNumberLookup">
            Invoice Number:
            <BR><BR>
            <INPUT TYPE="TEXT" NAME="invoice_number">
            &nbsp;
            <INPUT TYPE="SUBMIT" value="View Order">
        </form>


            <form action="viewOrderHistory" method="Post" id="referenceNumberLookup">
            Reference Number:
            <BR><BR>
            <INPUT TYPE="TEXT" NAME="reference_number">
            &nbsp;
            <INPUT TYPE="SUBMIT" value="View Order">
        </form>

result.jsp

<%@ page import="java.sql.*" %>

<HTML>
    <HEAD>
        <TITLE>Fetching Data From a Database</TITLE>
    </HEAD>

    <BODY>
        <H1>Fetching Data From a Database</H1>

        <%
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost/app?user=root&password=password");

            Statement statement = connection.createStatement();

            String first_name = request.getParameter("first_name");
            String last_name= request.getParameter("last_name");

            String invoice_number = request.getParameter("invoice_number");
            String reference_number = request.getParameter("reference_number");
            String amount = request.getParameter("amount");
            String status= request.getParameter("status");
            String date_created = request.getParameter("date_created");
            String quantity = request.getParameter("quantity");
            String product_name = request.getParameter("product_name");
            String product_price = request.getParameter("product_price");

            ResultSet resultset =

                    statement.executeQuery("select * from customer where first_name = '" + first_name + "'") ;
            statement.executeQuery("select * from customer where last_name = '" + last_name + "'") ;



                statement.executeQuery("select * from customer_order where amount = '" + amount + "'") ;
            statement.executeQuery("select * from customer_order where date_created = '" + date_created + "'") ;

            statement.executeQuery("select * from customer_order where reference_number = '" + reference_number + "'") ;
            statement.executeQuery("select * from customer_order where invoice_number = '" + invoice_number + "'") ;
            statement.executeQuery("select * from customer_order where status = '" + status + "'") ;
            statement.executeQuery("select * from ordered_product where quantity = '" + quantity + "'") ;
statement.executeQuery("select * from ordered_product where product_name = '" + product_name + "'") ;
statement.executeQuery("select * from ordered_product where product_price = '" + product_price + "'") ;

            if(!resultset.next()) {
                out.println("Sorry, could not find that order. ");
            } else {
        %>

        <div class="banner">
       <%
           }
       %>
            </div>

        Invoice Number: <%= resultset.getString("invoice_number") %>
        Reference Number: <%= resultset.getString("reference_number") %>
        Created by: <%= resultset.getString("first_name") %> <%= resultset.getString("last_name") %>
        Date Created: <%= resultset.getString("date_created") %>
        Grand Total: <%= resultset.getString("amount") %>


        Order:
        <TABLE BORDER="1">
            <TR>
               <TH>Product Name</TH>
               <TH>Product Price</TH>
               <TH>Quantity</TH>
           </TR>
           <TR>
               <TD> <%= resultset.getString("product_name") %> </TD>
               <TD> <%= resultset.getString("product_price") %> </TD>
               <TD> <%= resultset.getString("quantity") %> </TD>
           </TR>
       </TABLE>
    </BODY>
</HTML>

最佳答案

注意Statement documentation说：

默认情况下，每个Statement对象只能同时打开一个ResultSet对象。

现在，您的程序中包含以下语句：

ResultSet resultset =

        statement.executeQuery("select * from customer where first_name = '" + first_name + "'") ;
        statement.executeQuery("select * from customer where last_name = '" + last_name + "'") ;

        statement.executeQuery("select * from customer_order where amount = '" + amount + "'") ;
        statement.executeQuery("select * from customer_order where date_created = '" + date_created + "'") ;

        statement.executeQuery("select * from customer_order where reference_number = '" + reference_number + "'") ;
        statement.executeQuery("select * from customer_order where invoice_number = '" + invoice_number + "'") ;
        statement.executeQuery("select * from customer_order where status = '" + status + "'") ;
        statement.executeQuery("select * from ordered_product where quantity = '" + quantity + "'") ;
        statement.executeQuery("select * from ordered_product where product_name = '" + product_name + "'") ;
        statement.executeQuery("select * from ordered_product where product_price = '" + product_price + "'") ;

这意味着您使用相同的executeQuery对象多次调用Statement。但是，您只将第一次执行的结果保存到resultset中。

也就是说，ResultSet所引用的resultset对象是该语句中的对象：

statement.executeQuery("select * from customer where first_name = '" + first_name + "'") ;

如果您认为自己正在执行此操作，则它后面的所有其他查询都是单独的查询，并且不会存储在同一变量中。他们只是创建ResultSet对象，然后将其丢弃，因为您没有将结果分配给任何变量。

调用第二个executeQuery时，由于只允许一个ResultSet对象，因此关闭了存储在ResultSet中的resultset对象，并打开了一个新对象。然后是另一个，然后是另一个。

无论如何，在完成所有这些语句之后，您将进入检查resultset.next()的部分。但是在这一点上，正如我所说，该对象已经关闭。

基本上，您应该确定要运行的查询，仅运行该特定查询，然后可以在ResultSet中实时打开resultset。然后您可以填写它。

此外，打印结果集中的值的部分应位于else块内。您将它们放在该else的花括号之后，这意味着当查询没有返回任何行时，您将遇到麻烦，因为无论如何您都将尝试打印它们。

关于java - 获取java.sql.SQLException:在尝试多个查询时ResultSet关闭ERROR后不允许进行操作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31169772/