Using Multiple ResultSet in JSP

Question

I'm pretty new with JSP. I am trying to select 2 sql statements to produce 2 separate tables. I could successfully select 1 table,but when i tried 2 tables i can't get it to run. What I did was.

setting up my connection:

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

Connect and execute Query

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

This first table works well alone.

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

But when I tried combining with a second table as below, then I cant get it to run.

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

and I got internal server error from GlassFish

remove the end tag "" out of while loop and add it out of it and paste the error log if is shown again — Mohammad, Mar 21 '18 at 08:52

score 0 · Answer 1 · answered Mar 21 '18 at 09:07

Connections, statements and result sets must be close()d. Hence reusing variables will not do.

Class.forName no longer is needed to load the database vendor specif library 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()) {
%>
...
<%
        }
    }
%>

Using the try-with-resources syntax above, the closes are done automatically even when returning or an exception is thrown.

Also this variable usage prevents the need to think of different names like statement2.

Using Multiple ResultSet in JSP

1 Answers1