Recipe10.2.Displaying Relational Data

Recipe 10.2. Displaying Relational Data

Problem

You want to display data from a relational database, but you don't know the structure of the data.

Solution

Use the RowSetDynaClass class (org.apache.commons.beanutils.RowSetDynaClass) provided by the Jakarta Commons BeanUtils project.

The JAR files for BeanUtils are included with the Struts distribution so no additional download is needed.

Start by creating a data access object, like the one shown in Example 10-1, which performs the database query and returns a BeanUtils RowSetDynaClass created from a JDBC result set.

Example 10-1. RowSetDynaClass-based data access object

package com.oreilly.strutsckbk.ch05; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; import org.apache.commons.beanutils.RowSetDynaClass; public class UserDao {     public RowSetDynaClass getUsersRowSet( ) throws Exception {         Connection conn = null;         Statement stmt = null;         ResultSet rs = null;         RowSetDynaClass rowSet = null;         try {             conn = getConnection( );             stmt = conn.createStatement( );             rs = stmt.executeQuery("select * from users");             rowSet = new RowSetDynaClass(rs);                     }         finally {             if (conn != null) conn.close( );         }         return rowSet;     }     private Connection getConnection( ) throws Exception {         Class.forName("com.mysql.jdbc.Driver");         return DriverManager.getConnection("jdbc:mysql://localhost/test");     } }

Create an Action that retrieves the RowSetDynaClass from the data access object and stores it in the servlet request. The Action shown in Example 10-2 retrieves a row set from the data access object of Example 10-1.

Example 10-2. Retrieving a RowSetDynaClass from an Action

package com.oreilly.strutsckbk.ch05; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.RowSetDynaClass; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; public class ViewUsersAction extends Action {     public ActionForward execute(ActionMapping mapping,                                   ActionForm form,                                  HttpServletRequest request,                                   HttpServletResponse response)                           throws Exception {         UserDao dao = new UserDao( );         RowSetDynaClass rowSet = dao.getUsersRowSet( );         request.setAttribute("rowSet", rowSet);         return mapping.findForward("success");     } }

Then create a JSP page, such as the one shown in Example 10-3, that iterates through the RowSetDynaClass stored in the request, first retrieving the column names, and then the data itself.

Example 10-3. JSP page that renders a RowSetDynaClass

<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ page import="org.apache.commons.beanutils.*" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-bean" prefix="bean" %> <%@ taglib uri="http://jakarta.apache.org/struts/tags-logic" prefix="logic" %> <html> <head>   <title>Struts Cookbook - Chapter 5 : Viewing Row Sets</title> </head> <body>   <h2>Viewing Row Sets</h2>   <bean:define  name="rowSet" property="dynaProperties"/>   <table border="2">     <tr>       <logic:iterate  name="cols">         <th><bean:write name="col" property="name"/></th>       </logic:iterate>     </tr>     <logic:iterate  name="rowSet" property="rows">       <tr>         <logic:iterate  name="cols">           <td>             <bean:write name="row" property="<%=((DynaProperty)col).                                                       getName( )%>"/>           </td>         </logic:iterate>       </tr>     </logic:iterate>   </table> </body> </html>

Of course, you tie the Action to the JSP using an action element in the struts-config.xml:

<action    path="/ViewUsers"            type="com.oreilly.strutsckbk.ch05.ViewUsersAction">     <forward name="success" path="/view_users.jsp"/> </action>

Discussion

A sizeable percentage of web applications use a relational database for storing data. In an architecture that emphasizes separation of concerns, controller actions interact with the model through business objects, data transfer objects, and service-oriented interfaces. But sometimes it makes more sense to provide direct views of data in the database.

The RowSetDynaClass provides an effective mechanism for accessing and retrieving data from JDBC result sets. This class, provided in the Jakarta Commons BeanUtils package, maps a JDBC result setrows and columns retrieved from a relational databaseto a JavaBean which can be accessed on a JSP page.

Figure 10-3 shows the results of executing the "select * from users" query from Example 10-1 against a MySQL test database.

Figure 10-3. Query results from MySQL

You construct a RowSetDynaClass by passing the JDBC ResultSet to the RowSetDynaClass's constructor. Because the data is copied, you can safely close the ResultSet and JDBC Connection without losing data. The RowSetDynaClass includes the data and contains the name and type of each column. In Example 10-3, the dynaProperties array property is used to render the table column headers. Then, the rowSet property is used to access a List of DynaBeans. Each DynaBean represents a row. Each field of the row is retrieved by name from each DynaBean.

Figure 10-4 shows the generated page for the /ViewUsers action.