获取java.sql.SQLException：在尝试多个查询时，ResultSet关闭ERROR后不允许操作(Getting java.sql.SQLException: Operation not allowed after ResultSet closed ERROR while trying multiple queries)
我正在尝试根据用户输入从数据库中获取数据，以便用户可以跟踪订单。 该文件在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"> <INPUT TYPE="SUBMIT" value="View Order"> </form> <form action="viewOrderHistory" method="Post" id="referenceNumberLookup"> Reference Number: <BR><BR> <INPUT TYPE="TEXT" NAME="reference_number"> <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>I'm trying to fetch data from database based on user input so the user can track orders. The file compiles fine in the IDE but throws this error in the browser:
javax.servlet.ServletException: java.sql.SQLException: Operation not allowed after ResultSet closed
form.jsp
<form action="viewOrderHistory" method="Post" id="invoiceNumberLookup"> Invoice Number: <BR><BR> <INPUT TYPE="TEXT" NAME="invoice_number"> <INPUT TYPE="SUBMIT" value="View Order"> </form> <form action="viewOrderHistory" method="Post" id="referenceNumberLookup"> Reference Number: <BR><BR> <INPUT TYPE="TEXT" NAME="reference_number"> <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文档说：
默认情况下，每个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 + "'") ;这意味着您使用相同的Statement对象多次调用executeQuery 。 但是您只将第一次执行的结果保存到结果集中。
也就是说， resultset引用的ResultSet对象是此语句中的对象：
statement.executeQuery("select * from customer where first_name = '" + first_name + "'") ;跟随它的所有其他查询都是单独的查询，如果您认为自己正在做的事情，则不会存储在同一个变量中。 它们只是创建ResultSet对象，然后它被抛弃，因为您没有将结果分配给任何变量。
一旦调用第二个executeQuery ，因为只允许一个ResultSet对象，所以resultset集中存储的ResultSet对象将被关闭，并打开一个新对象。 然后另一个，然后另一个。
无论如何，在您完成所有这些语句之后，您将进入检查resultset.next() 。 但在这一点上，正如我所说，这个目标已经被关闭了。
基本上，您应该决定要运行哪些查询，仅运行该特定查询 ，然后在resultset集中有一个实时的打开ResultSet 。 然后你可以填写它。
此外，从结果集中打印值的部分应位于else块内。 你把它们放在else的大括号之后，这意味着当查询没有返回任何行时你会遇到麻烦，因为你无论如何都会尝试打印它们。
The code below works (implying you are using a servlet):
Form.jsp
<form method="post" action="viewPublishers"> Please enter the publisher ID you wish to view: <BR><br> <input type='text' name='pub_id' required> <BR><BR> <input type='submit' value="View"> </form>Response.jsp
<% Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/app?user=root&password=12345"); Statement statement = connection.createStatement(); String pub_id = request.getParameter("pub_id); ResultSet resultset = statement.executeQuery("select * from publishers where id = '" + pub_id+ "'") ; if(!resultset.next()) { out.println("Sorry, could not find that publisher.Please enter a valid ID. <a href='viewPublishers'>Try Again.</a> "); } else { %> <table> <thead> <tr> <th>Publisher ID</th> <th>First Name</th> <th>Last Name</th> <th>Books</th> </thead> <tbody style="font-size:small;"> <tr> <td data-label="Publiser ID"><%= resultset.getString("pub_id") %></td> <td data-label="First Name"><%= resultset.getString("first_name") %></td> <td data-label="Last Name"><%= resultset.getString("last_name") %></td> <td data-label="Books"><%= resultset.getString("books") %> </tr> </tbody> </table> <div> <% } %></div>更多推荐
发布评论