[ Team LiB ] Previous Section Next Section

Recipe 21.9 Converting a ResultSet to a Result Object


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.


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


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:

  1. Creates a ResultSet by querying a database.

  2. Converts the ResultSet to a Result.

  3. 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;
        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);


  public void doGet(HttpServletRequest request, 
    HttpServletResponse response) throws ServletException,
      java.io.IOException {
      String sql = "select * from athlete";
          //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
          RequestDispatcher dispatcher = request.getRequestDispatcher(
       } 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
      stmt.close( );//this will close any associated ResultSets

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

      return res;//return Result object


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:


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(

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

      <TITLE>Using a Result object</TITLE>
<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">

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


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:


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

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

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Chapter 23 on the JSTL; Chapter 16 on using session attributes; Recipe 21.1 on accessing a database from a servlet without a connection pool; Recipe 21.2 and Recipe 21.3 on using a DataSource on Tomcat; Recipe 21.5 and Recipe 21.6 on using DataSources with servlets and JSPs on WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section