24.4 Using a Generic Database Bean

Some consider using the JSTL database access actions in JSP pages a bad idea because so much business logic ends up in the presentation layer (the View). For a very simple application, it's no big deal, but for a more complex application it's better to move the SQL statements to some other component type. You have basically two options: move it to a Controller servlet (or an action class that the servlet delegates to), as in the Chapter 19 example, or encapsulate it in a custom action. In both cases it makes sense to add yet another abstraction layer in the form of a bean that encapsulates the SQL statements and let the servlet or tag handler access the data in a purer form. One example of such a bean is the EmployeeRegistryBean used in Chapter 19 for authentication as well as for retrieving and saving information about an employee.

When you develop this type of database access components, you can of course use the JDBC API directly. I find it handy to use a generic JDBC bean, such as the com.ora.jsp.beans.sql.SQLCommandBean described in this section. Besides taking care of a lot of the grunt work, it also converts a query result into an instance of the same class that the JSTL <sql:query> action uses to expose the result. This makes it easy to use in a JSP page that renders the result.

The SQLCommandBean has three write-only properties. Example 24-4 shows the beginning of the class file with the setter methods.

Example 24-4. SQLCommandBean property setter methods

package com.ora.jsp.beans.sql;
  
import java.util.*;
import java.sql.*;
import javax.servlet.jsp.jstl.sql.*;
  
public class SQLCommandBean {
    private Connection conn;
    private String sqlValue;
    private List values;
  
    public void setConnection(Connection conn) {
        this.conn = conn;
    }
    
    public void setSqlValue(String sqlValue) {
        this.sqlValue = sqlValue;
    }
    
    public void setValues(List values) {
        this.values = values;
    }
    ...

The connection property holds the Connection to use, and the sqlValue property is set to the SQL statement to execute, with question marks as placeholders for variable values, if any. The application provides the values for the placeholders through the values property, a List with one object per placeholder.

Two methods in the SQLCommandBean execute the SQL statement: the executeQuery( ) method for a SELECT statement and the executeUpdate( ) method for all other types of statements. Example 24-5 shows the executeQuery( ) method.

Example 24-5. The SQLCommandBean's executeQuery( ) method

    public Result executeQuery(  ) throws SQLException {
        Result result = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        try {
            if (values != null && values.size(  ) > 0) {
                // Use a PreparedStatement and set all values
                pstmt = conn.prepareStatement(sqlValue);
                setValues(pstmt, values);
                rs = pstmt.executeQuery(  );
            }
            else {
                // Use a regular Statement
                stmt = conn.createStatement(  );
                rs = stmt.executeQuery(sqlValue);
            }
            result = ResultSupport.toResult(rs);
        }
        finally {
            if (rs != null) {
                try {rs.close(  );} catch (SQLException e) {}
            }
            if (stmt != null) {
                try {stmt.close(  );} catch (SQLException e) {}
            }
            if (pstmt != null) {
                try {pstmt.close(  );} catch (SQLException e) {}
            }
        }
        return result;
    }

If the values property is set, a JDBC PreparedStatement is needed to associate the values with the placeholders in the SQL statement. A private method named setValues( ) takes care of setting all values using the PreparedStatement setObject( ) method. If the values property isn't set, a regular JDBC Statement is created instead. In both cases, the JDBC driver is asked to execute the statement, and the resulting ResultSet is turned into a javax.servlet.jsp.jstl.sql.Result, which is returned to the caller. The conversion is performed by a static method in the javax.servlet.jsp.jstl.sql.ResultSupport support class defined by the JSTL specification. Besides the toResult( ) method used in Example 24-5, this class also provides a toResult( ) method that takes the maximum number of rows to include in the Result object as an argument.

You may wonder why a Result object is created and returned instead of returning the ResultSet directly. The reason is that a ResultSet is tied to the Connection that was used to generate it. When the Connection is closed or executes a new SQL statement, all open ResultSet objects for the Connection are released. You must therefore make sure you save the information from the ResultSet in a new data structure before reusing the Connection or return it to the pool.

The code for the creation of the PreparedStatement or Statement object and the execution of the statement is enclosed in a try/finally block. This is important, because if something fails (due to an invalid SQL statement, for instance), the JDBC methods throw an SQLException. The exception should be handled by the application using the SQLCommandBean, but first, all JDBC resources must be released, and the Connection object returned to the pool. Using a try block with a finally clause but no catch clause provides this behavior. If an exception is thrown, the finally clause is executed, and the exception is automatically thrown to the object that called the executeQuery( ) method. In the finally clause, the ResultSet object and either the PreparedStatement or Statement object are closed. It should be enough to close the statement object according to the JDBC specification (closing the statement should also close the ResultSet associated with the statement) but closing all resources used by the statement explicitly doesn't hurt and makes the code work even with a buggy JDBC driver. Each resource is closed within its own try/catch block, since the close( ) method can also throw an exception.

Example 24-6 shows the private setValues( ) method.

Example 24-6. The SQLCommandBean's setValues( ) method

private void setValues(PreparedStatement pstmt, List values)
    throws SQLException {
    for (int i = 0; i < values.size(  ); i++) {
        Object v = values.get(i);
        // Set the value using the method corresponding to the type.
        // Note! Set methods are indexed from 1, so we add 1 to i
        pstmt.setObject(i + 1, v);
    }
}

The setValues( ) method loops through all elements in the List with values. For each element, it uses the setObject( ) method to set the value of the corresponding placeholders in the PreparedStatement. You may wonder why a PreparedStatement is used here, since it's used only once. It's true that a PreparedStatement is intended to be reused over and over again to execute the same SQL statement with new values. However, it offers a convenient solution to the problem with different literal value syntax for date/time and number column values. When a PreparedStatement is used, the placeholders in the SQL statement can be set using the appropriate Java types instead, without worrying about what literal representation a certain JDBC driver supports. So even though it's only used once here, a PreparedStatement still has an advantage over a regular Statement.

The executeUpdate( ) method, shown in Example 24-7, is very similar to the executeQuery( ) method.

Example 24-7. The SQLCommandBean's executeUpdate( ) method

    public int executeUpdate(  ) throws SQLException {
        int noOfRows = 0;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        try {
            if (values != null && values.size(  ) > 0) {
                // Use a PreparedStatement and set all values
                pstmt = conn.prepareStatement(sqlValue);
                setValues(pstmt, values);
                noOfRows = pstmt.executeUpdate(  );
            }
            else {
                // Use a regular Statement
                stmt = conn.createStatement(  );
                noOfRows = stmt.executeUpdate(sqlValue);
            }
        }
        finally {
            if (rs != null) {
                try {rs.close(  );} catch (SQLException e) {}
            }
            if (stmt != null) {
                try {stmt.close(  );} catch (SQLException e) {}
            }
            if (pstmt != null) {
                try {pstmt.close(  );} catch (SQLException e) {}
            }
        }
        return noOfRows;
    }

The main difference is that the executeUpdate( ) method executes SQL statements that don't return rows, only the number of rows affected by the statement. Examples of such statements are UPDATE, INSERT, and DELETE. As for the executeQuery( ) method, a PreparedStatement is created and initialized with the values defined by the values property, if set. Otherwise a regular Statement is used. The statement is executed, and the number of affected rows is returned to the caller.