ProblemYou 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. SolutionUse the javax.servlet.jsp.jstl.sql.ResultSupport.toResult( ) method. DiscussionThe 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:
Example 21-12. A servlet converts a ResultSet to a Resultpackage 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 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>
Figure 21-6 shows how a web browser displays the JSP's output. Figure 21-6. The JSP page output in a web browserSee AlsoThe 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 . |