java - 在 JSP 中使用多个结果集

Question

我对 JSP 还很陌生。我试图选择 2 个 sql 语句来生成 2 个单独的表。我可以成功选择 1 个表，但是当我尝试 2 个表时，我无法让它运行。我所做的是。

设置我的连接:

<%
String driverName = "com.mysql.jdbc.Driver";
String connectionUrl = "jdbc:mysql://localhost:3306/";
String dbName = "supr";
String userId = "root";
String password = "secret";

try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
ResultSet resultSet2 = null;
%>

连接并执行查询

<%
       try {
       connection = DriverManager.getConnection(
       connectionUrl + dbName, userId, password);
       statement = connection.createStatement();
       String sql = "SELECT con.container_id, con.con_location, con.con_status, concap.capacity FROM container con INNER JOIN con_capacity concap ON con.container_id = concap.container_id";

       String sql2 = "SELECT p.pipe_id, p.pipe_location, pd.PipeDis_date FROM pipe p JOIN pipe_disinfect pd ON p.pipe_id = pd.pipe_id ";
       resultSet = statement.executeQuery(sql);
       resultSet2 = statement.executeQuery(sql2);
       %>

第一个表单独工作效果很好。

<table class="table table-hover">
          <thead>
           <th>Container ID</th>
           <th>Location</th>
           <th>Status</th>
           <th>Capacity</th>
           <th></th>
          </thead>
          <tbody>
           <%
           while (resultSet.next()) { %>
           <tr>
            <td><%=resultSet.getString("Container_ID")%></td>
            <td><%=resultSet.getString("Con_Location")%></td>
            <td><%=resultSet.getString("Con_Status")%></td>
            <td><%=resultSet.getString("Capacity")%></td>
            <td><button class="btn btn-primary">Schedule</button></td>
           </tr>
          </tbody>
          <%
         }

        } catch (Exception e) {
        e.printStackTrace();
       }
       %>                                          
      </table>

但是当我尝试与下面的第二个表组合时，我无法让它运行。

<table class="table table-hover">
       <thead>
        <th>Pipe ID</th>
        <th>Location</th>
        <th>Last Disinfection</th>
        <th></th>
       </thead>
       <tbody>
        <%
        while (resultSet2.next()) { %>
        <tr>
         <td><%=resultSet2.getString("Pipe_ID")%></td>
         <td><%=resultSet2.getString("Pipe_Location")%></td>
         <td><%=resultSet2.getString("Pipe_LastDisinfect")%></td>
         <td><button class="btn btn-primary">Schedule</button></td>
        </tr>
       </tbody>
       <%  /**I'm getting an error on this line on eclipse****/
      }

     } catch (Exception e) {
     e.printStackTrace();
    }
    %>                                          
   </table>

我从 GlassFish 收到内部服务器错误

Answer 1

连接、语句和结果集必须是 close()d。因此重用变量是行不通的。

Class.forName 不再需要加载数据库供应商指定的库 jar。

<%
    String sql = "SELECT con.container_id, con.con_location, con.con_status, "
        + " concap.capacity "
        + "FROM container con "
        + "INNER JOIN con_capacity concap ON con.container_id = concap.container_id";
    try (Connection connection = DriverManager.getConnection(
            connectionUrl + dbName, userId, password);
        PreparedStatement statement = connection.prepareStatement(sql)) {
        try (ResultSet resultSet = statement.executeQuery()) {
%>
...
<%
        }
    }
%>

使用上面的 try-with-resources 语法，即使返回或抛出异常，也会自动完成关闭。

此外，这种变量的使用可以避免考虑不同的名称，例如statement2。