Recipe 21.9 Converting a ResultSet to a Result Object

Problem

You want to convert a java.sql.ResultSet to a javax.servlet.jsp.jstl.sql.Result object so that the object can be used with the JSTL.

Solution

Use the javax.servlet.jsp.jstl.sql.ResultSupport.toResult( ) method.

Discussion

The Result interface allows code to work with ResultSets in the form of Java arrays or java.util.Maps. The JSTL tags often use arrays or Maps to iterate through values (which is why they included the Result interface in the JSTL specification). Therefore, you might want to convert a ResultSet to a Result, then hand the Result to a JSP that uses the JSTL tags.

Example 21-12 is a servlet that:

Creates a ResultSet by querying a database.
Converts the ResultSet to a Result.
Forwards the Result to a JSP by storing the Result as a session attribute.

Example 21-12. A servlet converts a ResultSet to a Result

package com.jspservletcookbook;           

import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.*;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

import javax.servlet.*;
import javax.servlet.http.*;

public class DbServletResult extends HttpServlet {

   DataSource pool;
   
 
  public void init( ) throws ServletException {
        
    Context env = null;
        
    try{
           
        env = (Context) new InitialContext( ).lookup("java:comp/env");

        pool  = (DataSource) env.lookup("jdbc/oracle-8i-athletes");

        if (pool == null)
            throw new ServletException(
              "'oracle-8i-athletes' is an unknown DataSource");
             
    } catch (NamingException ne) { 
        
        throw new ServletException(ne);

    }
      
  }//init

  public void doGet(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException,
      java.io.IOException {
    
      String sql = "select * from athlete";
        
      try{
        
          //Get a Result object that represents the return value of the SQL
          //statement 'select * from athlete' 
          Result jspResult = select(sql);
        
          HttpSession session = request.getSession( );
        
          //store the Result in a session attribute, 
          //where it can be passed to
          //a JSP and used with the JSTL tags
          session.setAttribute(
            "javax.servlet.jsp.jstl.sql.Result",jspResult);
        
          RequestDispatcher dispatcher = request.getRequestDispatcher(
            "/useResult.jsp");
        
          dispatcher.forward(request,response);
        
       } catch (SQLException sqle){ 
           throw new ServletException(sqle.getMessage( ));}
 
  } //doGet

  private Result select(String sql) throws SQLException{

      if (sql == null || sql.equals(""))
          throw new SQLException("Invalid  parameter in select method");
        
      ResultSet rs = null;

      Connection conn = null;

      Result res = null;
    
      //Get a Connection from the pool
      conn = pool.getConnection( );
    
      if (conn == null )
          throw new SQLException("Invalid Connection in select method");
        
      PreparedStatement stmt = conn.prepareStatement(sql);
    
      //Create the ResultSet
      rs = stmt.executeQuery( );
    
      //Convert the ResultSet to a 
      //Result object that can be used with JSTL tags
      res=ResultSupport.toResult(rs);
    
      stmt.close( );//this will close any associated ResultSets

      conn.close( );//return Connection to pool

      return res;//return Result object

  }//select
}

Example 21-12 imports the necessary Java classes including the Result and ResultSupport classes:

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

The select( ) method does the important work: creating the ResultSet, converting this object to a Result, and returning the Result. Here is the code that performs the conversion:

res=ResultSupport.toResult(rs);

The ResultSupport class's static toResult( ) method takes a ResultSet as an argument and returns a Result.

The servlet's doGet( ) method then creates a session attribute from the Result and uses a RequestDispatcher to forward the request to a JSP. The JSP is named useResult.jsp.

The user initially requests the servlet in his browser, and the servlet passes the request to the JSP. The user then sees the JSP's output in their browser.

The RequestDispatcher code looks like this:

RequestDispatcher dispatcher = request.getRequestDispatcher(
            "/useResult.jsp");
dispatcher.forward(request,response);

Example 21-13 uses the JSTL core tags (with the "c" prefix). The c:set tag gains access to the session attribute and stores the attribute's value in a resultObj variable. The c:forEach and c:out tags then display the database values in the JSP.

Example 21-13. The JSP that uses a Result object stored as a session attribute

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<html>
<HEAD>
      <TITLE>Using a Result object</TITLE>
     </HEAD>
<body bgcolor="white">
<h2>View Database Data</h2>

<%--store a session attribute (the Result object) in a variable named 'resultObj'--%>
<c:set var="resultObj" value=
  "${sessionScope[\"javax.servlet.jsp.jstl.sql.Result\"]}" />

<table border="1" cellspacing="2">
<%-- for every row in the Result ...--%>
<c:forEach items="${resultObj.rows}" var="row">

  <%-- for every column in the row ...--%>
  <c:forEach items="${row}" var="column">

    <tr>
     <td align="right">
       <b> <c:out value="${column.key}" /> </b>
       </td>
       <td>
         <c:out value="${column.value}" />
         </td></tr>
    </c:forEach>

</c:forEach>
      </table>
</body>
</html>

The syntax "${sessionScope[\"javax.servlet.jsp.jstl.sql.Result\"]}" is necessary, because the session attribute name contains periods (.). Otherwise, the EL can acccess a scoped attribute, if the attribute is named myAttribute, using this simpler syntax:

${myAttribute}

Figure 21-6 shows how a web browser displays the JSP's output.

Figure 21-6. The JSP page output in a web browser