24.5 Developing Application-Specific Database Components

The SQLCommandBean class described in this chapter can be used for application-specific components that access a database. The bean is used like this:

SQLCommandBean sqlCommandBean = new SQLCommandBean(  );
sqlCommandBean.setConnection(dataSource.getConnection(  ));
String sql = "SELECT * FROM Employee WHERE UserName = ?");
sqlCommandBean.setSqlValue(sql);
List values = new ArrayList(  );
values.add(userName);
sqlCommandBean.setValues(values);
Result result = sqlCommandBean.executeQuery(  );

Chapter 19 includes a more advanced example of an application-specific bean (the EmployeeRegisterBean) that uses the SQLCommandBean.

You can also use these classes in your application-specific custom actions. One example is the custom action that's mentioned in Chapter 12 as an alternative to the generic database actions for inserting or updating employee information:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="myLib" uri="mytaglib" %>
  
<myLib:saveEmployeeInfo dataSource="${appDataSource}" />
  
<%-- Get the new or updated data from the database --%>
<sql:query var="newEmpDbInfo"="${example}" scope="session">
  SELECT * FROM Employee 
    WHERE UserName = ?
  <sql:param value="${param.userName}" />
</sql:query>
  
<%-- Redirect to the confirmation page --%>
<c:redirect url="confirmation.jsp" />

Example 24-8 shows one way to implement this custom action.

Example 24-8. SaveEmployeeInfoTag class

package com.mycompany.tags;
  
import java.sql.*;
import java.text.*;
import java.util.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.*;
import javax.servlet.jsp.jstl.sql.Result;
import com.ora.jsp.beans.sql.SQLCommandBean;
import com.ora.jsp.util.*;
  
public class SaveEmployeeInfoTag extends SimpleTagSupport {
    private DataSource dataSource;
    
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public void doTag(  ) throws JspException {
        // Get all request parameters
        PageContext pageContext = (PageContext) jspContext;
        ServletRequest request = pageContext.getRequest(  );
        String userName = request.getParameter("userName");
        String password = request.getParameter("password");
        String firstName = request.getParameter("firstName");
        String lastName = request.getParameter("lastName");
        String dept = request.getParameter("dept");
        String empDateString = request.getParameter("empDate");
        String emailAddr = request.getParameter("emailAddr");
        if (userName == null || password == null || 
            firstName == null || lastName == null || 
            dept == null || empDateString == null ||
            emailAddr == null) {
            throw new JspException("Missing a mandatory parameter");
        }
  
        SQLCommandBean sqlCommandBean = new SQLCommandBean(  );
        if (dataSource == null) {
            throw new JspException("The data source cannot be found");
        }
  
        Connection conn = null;
        try {
            conn = dataSource.getConnection(  );
            sqlCommandBean.setConnection(conn);
            
            // Get the current info, if any
            String sqlValue =
                "SELECT * FROM Employee WHERE UserName = ?";
            List values = new ArrayList(  );
            values.add(userName);
            sqlCommandBean.setSqlValue(sqlValue);
            sqlCommandBean.setValues(values);
            Result result = sqlCommandBean.executeQuery(  );
            
            // Create values for insert/update
            values.clear(  );
            values.add(password);
            values.add(firstName);
            values.add(lastName);
            values.add(dept);
            // Must convert the String value to java.sql.Date
            java.util.Date empDate = 
                StringFormat.toDate(empDateString, "yyyy-MM-dd")
            java.sql.Date empSQLDate = 
                new java.sql.Date(empDate.getTime(  ));
            values.add(empSQLDate);
            values.add(emailAddr);
            values.add(new Timestamp(System.currentTimeMillis(  )));
            values.add(userName);
            
            if (result.getRowCount(  ) == 0) {
                // New user. Insert
                StringBuffer sb = new StringBuffer(  );
                sb.append("INSERT INTO Employee ").
                    append("(Password, FirstName, LastName, Dept, ").
                    append("EmpDate, EmailAddr, ModDate, UserName) ").
                    append("VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
                sqlCommandBean.setSqlValue(sb.toString(  ));
            }
            else {
                // Existing user. Update
                StringBuffer sb = new StringBuffer(  );
                sb.append("UPDATE Employee ").
                    append("SET Password = ?, FirstName = ?, ").
                    append("LastName = ?, Dept = ?, EmpDate = ?, ").
                    append("EmailAddr = ?, ModDate = ? ").
                    append("WHERE UserName = ?");
                sqlCommandBean.setSqlValue(sb.toString(  ));
            }
            sqlCommandBean.executeUpdate(  );
        }
        catch (SQLException e) {
            throw new JspException("SQL error: " + e.getMessage(  ));
        }
        catch (ParseException e) {
            throw new JspException("Invalid empDate format: " + 
                e.getMessage(  ));
        }
        finally {
            try {
                if (conn != null) {
                    conn.close(  );
                }
            }
            catch (SQLException e) {
                // Ignore
            }
        }
    }
}

This tag handler has one property, named dataSource. It's marked as required in the TLD for the tag so it must always be set. It's also declared to accept a request-time attribute value, so an EL expression can be used to assign it a reference to the data source:

  ...
  <tag>
    <name>saveEmployeeInfo</name>
    <tag-class>com.mycompany.tags.SaveEmployeeInfoTag</tag-class>
    <body-content>empty</body-content>
  
    <attribute>
      <name>dataSource</name>
      <required>true</required>
      <rtexprvalue>true</rtexprvalue>
    </attribute>
  </tag>
  ...

In the doTag( ) method, all request parameters with information about the employee are first retrieved. If a parameter is missing, an exception is thrown. An SQLCommandBean instance is then created and provided a Connection, retrieved from the DataSource.

The tag handler uses the bean to execute a SELECT statement to find out if the specified employee is already defined in the database. If not, the tag handler sets the bean's SQL statement to an INSERT statement and executes it with all the information provided through the request parameters; otherwise the tag handler uses the bean to execute an UPDATE statement.

The tag handler class described here is intended to show you how to use the database access classes to implement your own custom actions. The tag handler class can be improved in several ways. For instance, it can use the JSTL Config class (see Chapter 23) to get hold of a default DataSource if the dataSource attribute is omitted, and provide default values for missing parameters, such as the current date for a missing employment date and an email address based on the employee's first and last name if the email address is missing. You can also use a bean as input to the action instead of reading request parameters directly. This allows the bean to be used as described in Chapter 8 to capture and validate user input until all information is valid, and then pass it on to the custom action for permanent storage of the information in a database. Finally, it's a good idea to encapsulate all database access in a bean, such as the EmloyeeRegistryBean, and use this bean in the tag handler class instead of using the SQLCommandBean directly.