16.2 Displaying a Result Set

Java Servlet Programming, 2nd Edition > 16. Element Construction Set > 16.2 Displaying a Result Set

 
< BACKCONTINUE >

16.2 Displaying a Result Set

The use of ECS for full page creation has fallen out of favor as servlet-based templating technologies have improved. It's simply too much work to dynamically create what tends to be mostly static page content. However, ECS still has its place. ECS works well for those sections of a page that are extremely dynamic, where you need the full power of Java to determine what content to produce. As Jon Stevens, one of its creators, said, "Use ECS when you would otherwise have to use out.println( )."

For example, picture having a web application that lets clients execute ad hoc queries against a database. Perhaps you've implemented an online bug-tracking system and want a servlet that gives power users the option of running their own queries against the data (using a connection with read-only privileges). ECS works well for writing the page to display the database results, programmatically generating a table customized for the data. Example 16-2 demonstrates a simple ResultSet view component. It's reminiscent of the HtmlSQLResult class from Chapter 9, that used out.println( ). Using ECS instead of out.println( ) allows the code to be simpler and, as we'll see in the next section, more customizable.

Example 16-2. An Improved ResultSet Table
import java.io.*; import java.sql.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class SimpleResultSetTable extends Table {   public SimpleResultSetTable(ResultSet rs) throws SQLException {     setBorder(1);     ResultSetMetaData rsmd = rs.getMetaData();     int colCount = rsmd.getColumnCount();     TR row = new TR();     for (int i = 1; i <= colCount; i++) {       row.addElement(new TH().addElement(rsmd.getColumnName(i)));     }     addElement(row);     while (rs.next()) {       row = new TR();       for (int i = 1; i <= colCount; i++) {         row.addElement(new TD().addElement(rs.getString(i)));       }       addElement(row);     }   } }

This class extends org.apache.ecs.html.Table, thus representing an HTML <TABLE> element. It performs all its work in its constructor, reading the ResultSet and its ResultSetMetaData to generate a simple table of results. The constructor first calls setBorder(1) to set the border attribute of the table. Then it creates a row (TR) full of table header elements (TH) with each table head displaying the column name retrieved from the meta data. Finally the constructor loops over the result set and creates a row for each entry, filling the row with table data elements (TD) containing the simple String representation of the result set data.

The SimpleResultSetTable class can be used directly within a servlet using the snippet of code shown in Example 16-3.

Example 16-3. Using SimpleResultSetTable
Statement stmt = con.createStatement(); boolean gotResultSet = stmt.execute(sql);  // SQL from user if (!gotResultSet) {   out.println(stmt.getUpdateCount() + " rows updated."); } else {   out.println(new SimpleResultSetTable(stmt.getResultSet())); }

It generates a table as shown in Figure 16-1.

Figure 16-1. A raw result set veiw

The class can be used in conjunction with templating technologies. There's no reason a template needs to produce all the HTML content itself; if some page section requires the power of ECS, the template can include the ECS component's output into the page at the appropriate location.

What happens if the text returned by the ResultSet contains characters that HTML treats as special, like <, >, and &? By default, they're included directly and can potentially break the structure of the HTML. To solve this problem, ECS includes an org.apache.ecs.filter.CharacterFilter class that converts special HTML characters into their corresponding character entities. Every element uses this filter as its default filter, but all filtering is turned off by default for speed. You can turn on filtering on a per-element basis by calling setFilterState(true), or you can turn it on for the entire system by editing ecs.properties and setting the filter_state and filter_attribute_state values to true. Then all special characters in any text output will be automatically converted to character entities.

16.2.1 Customizing the Display

ECS uses the object-oriented nature of Java to provide highly customizable data display. With a little Java coding we can build on the SimpleResultSetTable class and create a less "simplistic" ResultSetTable class. This new table class will accept an array of TableCustomizer objects to control the content added to the table. Example 16-4 shows the TableCustomizer interface.

Example 16-4. The TableCustomizer Class
import java.sql.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public interface TableCustomizer {   public boolean accept(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException;   public Element display(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException; }

For customizers that implement this interface, the accept( ) method should return true if the customizer has an interest in handling the current table cell and false if not. The accept( ) method has access to the column type, column type name, column name, ResultSet object, and column index to help it decide. The display( ) method gets called if accept( ) returns true; it creates and returns an Element containing the table cell data to be added to the table. All HTML components implement the Element interface, so by returning Element the display( ) method allows for any type of component to be returned.

We can write customizers to better display null values, dates, and numbers, as shown in Example 16-5, Example 16-6, and Example 16-7. Creating HTML component objects instead of strings keeps the code simpler and more extensible. It would also be possible for the table to programmatically limit the sort of elements that could be returned.

Example 16-5. A Customizer for Null Values
import java.sql.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class NullCustomizer implements TableCustomizer {   public boolean accept(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     rs.getObject(index);     return rs.wasNull();   }   public Element display(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     // Print an "N/A" for null entries     return new StringElement("N/A");   } }
Example 16-6. A Customizer for Date Values
import java.sql.*; import java.text.*; import java.util.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class DateCustomizer implements TableCustomizer {   DateFormat fmt;   public DateCustomizer(Locale loc) {     fmt = DateFormat.getDateTimeInstance(                      DateFormat.SHORT, DateFormat.SHORT, loc);   }   public boolean accept(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     return (columnType == Types.DATE || columnType == Types.TIMESTAMP);   }   public Element display(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     // Print a short date and time using the specified locale     return new StringElement(fmt.format(rs.getDate(index)));   } }
Example 16-7. A Customizer for Number Values
import java.sql.*; import java.text.*; import java.util.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class NumberCustomizer implements TableCustomizer {   NumberFormat fmt;   public NumberCustomizer(Locale loc) {     fmt = NumberFormat.getNumberInstance(loc);   }   public boolean accept(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     return (columnType == Types.TINYINT ||             columnType == Types.SMALLINT ||             columnType == Types.INTEGER ||             columnType == Types.BIGINT ||             columnType == Types.REAL ||             columnType == Types.FLOAT ||             columnType == Types.DOUBLE);   }   public Element display(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     // Print the number using the specified locale     if (columnType == Types.TINYINT ||         columnType == Types.SMALLINT ||         columnType == Types.INTEGER ||         columnType == Types.BIGINT) {       return new StringElement(fmt.format(rs.getLong(index)));     }     else {       return new StringElement(fmt.format(rs.getDouble(index)));     }   } }

We can also write a more advanced customizer to make all bug IDs in the table into hyperlinks to a "bug view" servlet. Then no matter what query a user might enter, bug IDs will automatically be created as hyperlinks. Example 16-8 shows this customizer.

Example 16-8. A Customizer for Bug IDs
import java.sql.*; import java.text.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class BugIdCustomizer implements TableCustomizer {   String bugViewServlet;   public BugIdCustomizer(String bugViewServlet) {     this.bugViewServlet = bugViewServlet;   }   public boolean accept(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     return ((columnType == Types.CHAR ||              columnType == Types.VARCHAR ||              columnType == Types.LONGVARCHAR) &&             "bugid".equalsIgnoreCase(columnName));   }   public Element display(int columnType, String columnTypeName,                         String columnName, ResultSet rs, int index)                                                  throws SQLException {     // Create a link to a servlet to display this bug     String bugid = rs.getString(index);     return new A(bugViewServlet + "?bugmonofont">ResultSetTable  class  accepts in its constructor an array of TableCustomizer objects. For each cell the customizers in the array are given the opportunity to control the creation of each table cell. The customizers will be called in the order they are placed in the array, and the first customizer to accept the table cell wins. The code for the ResultSetTable class can be found in Example 16-9.

Example 16-9. A Customizable Result Set Table
import java.io.*; import java.sql.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; import org.apache.ecs.*; import org.apache.ecs.html.*; public class ResultSetTable extends Table {   public ResultSetTable(ResultSet rs) throws SQLException {     this(rs, null);   }   public ResultSetTable(ResultSet rs, TableCustomizer[] customizers)                                       throws SQLException {     setBorder(1);     if (customizers == null) {       customizers = new TableCustomizer[0];     }     ResultSetMetaData rsmd = rs.getMetaData();     int colCount = rsmd.getColumnCount();     TR row = new TR();     for (int i = 1; i <= colCount; i++) {       row.addElement(new TH().addElement(rsmd.getColumnName(i)));     }     addElement(row);     while (rs.next()) {       row = new TR();       for (int i = 1; i <= colCount; i++) {         TD td = new TD();         int columnType = rsmd.getColumnType(i);         String columnTypeName = rsmd.getColumnTypeName(i);         String columnName = rsmd.getColumnName(i);         // Give each customizer a chance to control output         boolean customized = false;         for (int c = 0; c < customizers.length; c++) {           TableCustomizer customizer = customizers[c];           if (customizer.accept(columnType, columnTypeName,                                 columnName, rs, i)) {             td.addElement(customizer.display(columnType, columnTypeName,                                              columnName, rs, i));             customized = true;             break;           }         }         // If no customizer wanted the job, display the value as a String         if (!customized) {           td.addElement(rs.getString(i));         }         row.addElement(td);       }       addElement(row);     }   } }

The outer while loop iterates through table rows, the outer for loop iterates over table columns, and the inner for loop manages the new customizer logic. The first customizer that "accepts" the cell handles the display for the cell. If no customizer accepts the cell, the table displays a simple String value. The servlet to call ResultSetTable is shown in Example 16-10.

Example 16-10. A Servlet to Exercise the ResultSetTable
import java.io.*; import java.sql.*; import java.text.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; import org.apache.ecs.*; import org.apache.ecs.html.*; import com.oreilly.servlet.*; public class ResultSetServlet extends HttpServlet {   public void doPost(HttpServletRequest req, HttpServletResponse res)                                 throws ServletException, IOException {     res.setContentType("text/html");     PrintWriter out = res.getWriter();     String url = req.getParameter("url");     String driver = req.getParameter("driver");     String sql = req.getParameter("sql");     // Quickly verify url/driver/sql exist     ParameterParser parser = new ParameterParser(req);     String[] required = { "url", "driver", "sql" };     String[] missing = parser.getMissingParameters(required);     if (missing != null && missing.length > 0) {       res.sendError(res.SC_BAD_REQUEST,                     "URL, Driver, and SQL string must all be provided");       return;     }     String param1 = req.getParameter("param1");     String param2 = req.getParameter("param2");     String param3 = req.getParameter("param3");     String param4 = req.getParameter("param4");     String param5 = req.getParameter("param5");     String param6 = req.getParameter("param6");     String val1 = req.getParameter("val1");     String val2 = req.getParameter("val2");     String val3 = req.getParameter("val3");     String val4 = req.getParameter("val4");     String val5 = req.getParameter("val5");     String val6 = req.getParameter("val6");     Properties props = new Properties();     if (param1 != null && val1 != null) { props.put(param1, val1); }     if (param2 != null && val2 != null) { props.put(param2, val2); }     if (param3 != null && val3 != null) { props.put(param3, val3); }     if (param4 != null && val4 != null) { props.put(param4, val4); }     if (param5 != null && val5 != null) { props.put(param5, val5); }     if (param6 != null && val6 != null) { props.put(param6, val6); }     Connection con = null;     try {       Class.forName(driver);       con = DriverManager.getConnection(url, props);       Statement stmt = con.createStatement();       boolean gotResultSet = stmt.execute(sql);       if (!gotResultSet) {         out.println(stmt.getUpdateCount() + " rows updated.");       }       else {         TableCustomizer[] customizers = {           new NullCustomizer(),           new DateCustomizer(req.getLocale()),           new BugIdCustomizer(req.getContextPath() + "/servlet/BugView"),           new NumberCustomizer(req.getLocale()),         };         out.println(new ResultSetTable(stmt.getResultSet(), customizers));       }     }     catch (Exception e) {       throw new ServletException(e);     }   } }

Figure 16-2 shows sample output. Notice now the bug ID is a hyperlink, the date is nicely formatted, and the null description displays "N/A."

Figure 16-2. A customized result set view (European date)

Example 16-11 gives a simple HTML frontend for the servlet.

Example 16-11. A Simple HTML Frontend for ResultSetServlet
<HTML><HEAD><TITLE>SQL Query</TITLE></HEAD><BODY> <P>   This application executes a SQL query or update   against any database on the public Internet. </P> <FORM METHOD="POST" ACTION="servlet/ResultSetServlet">   <TABLE WIDTH="75%" BORDER="1">     <TR>       <TD WIDTH="35%">Database URL:</TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="url" SIZE="60"></TD>     </TR>     <TR>       <TD WIDTH="35%">Database Driver:</TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="driver" SIZE="60"></TD>     </TR>     <TR>       <TD COLSPAN="2"><P ALIGN=CENTER> -- Database Properties -- </p></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop1" VALUE="username"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val1"></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop2" VALUE="password"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val2"></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop3" VALUE="cacherows"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val3"></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop4"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val4"></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop5"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val5"></TD>     </TR>     <TR>       <TD WIDTH="35%"><INPUT TYPE=TEXT NAME="prop6"></TD>       <TD WIDTH="65%"><INPUT TYPE=TEXT NAME="val6"></TD>     </TR>   </TABLE>   <P>     <TEXTAREA NAME="sql" COLS="80" ROWS="5"></TEXTAREA>     <BR>     <INPUT TYPE=SUBMIT VALUE="Submit SQL">   </P> </FORM>

This demonstration of ECS has only scratched the surface of what's possible. With the full power of Java at your disposal, you can create pages or sections of pages that are customized perfectly for the data, and by taking advantage of the object-oriented nature of ECS, you can use inheritance, polymorphism, and strong type checking to make your job easier.


Last updated on 3/20/2003
Java Servlet Programming, 2nd Edition, © 2001 O'Reilly

< BACKCONTINUE >


Java servlet programming
Java Servlet Programming (Java Series)
ISBN: 0596000405
EAN: 2147483647
Year: 2000
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net