Previous
Implementing the DAO Pattern
The app15a application exemplifies the DAO pattern. In this application you can search customers, add, update, and delete customers. The CustomerDAO interface provides methods for manipulating CustomerTO objects. The class diagram is the same as the one in Figure 15.4. The CustomerDAO interface has one implementation, CustomerDAOMySQLImpl.
To discuss the application thoroughly, we split the applications into the following subsections.
Note
To run the app15a application, you need to have a MySQL database installed on your machine and run the MySQLScript.sql file included in the app15a application to create the Customers table in the test database.
The DAO Interface and the DAOBase Class
DAO is an interface that all DAO classes must implement, either directly or indirectly. There is only one method defined in the DAO interface, getConnection. The DAO interface is given in Listing 15.1.
Listing 15.1: The DAO interface
package app15a.dao;
import java.sql.Connection;
public interface DAO {
public Connection getConnection() throws DAOException;
}
The DAOBase class, given in Listing 15.2, provides an implementation of the getConnection method of the DAO interface.
Listing 15.2: The DAOBase Class
package app15a.dao;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DAOBase implements DAO {
public Connection getConnection() throws DAOException {
Connection connection = null;
Context context = null;
DataSource dataSource = null;
try {
context = new InitialContext();
dataSource = (DataSource)
context.lookup("java:/comp/env/jdbc/myDataSource");
}
catch (NamingException e) {
throw new DAOException();
}
if (dataSource!= null) {
try {
connection = dataSource.getConnection();
}
catch (SQLException e) {
throw new DAOException();
}
}
return connection;
}
}
The getConnection method returns a java.sql.Connection that can be used by the methods in a DAO object to access the database. In J2SE, you can obtian a Connection object through a java.sql.DriverManager. In J2EE however, scalability is very important and you want the feature of connection pooling, to return Connection objects quickly. The javax.sql.DataSource supports connection pooling and all J2EE containers must provide a DataSource object from which Connection objects can be obtained. Connection pooling is so important that you can even find this feature in Tomcat, even though Tomcat is not a J2EE container.
As you can see in the DAOBase class implementation, the DataSource object is returned by a JNDI lookup operation. JNDI lookup is an expensive operation, and as such, obtaining a DataSource is resource intensive. In Chapter 16 you will learn how to cache the DataSource object so that it will only be created once and reused afterwards.
The DataSource object is provided by the container. With Tomcat, you need the following Context element in the server.xml file in the conf directory of your Tomcat deployment directory.
<Context path="/app15a" docBase="app15a" reloadable="true"
debug="8">
<Loader checkInterval="7" reloadable="true"/>
<Resource name="jdbc/myDataSource" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/myDataSource">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>username</name>
<value>root</value>
</parameter>
<parameter>
<name>password</name>
<value></value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/test</value>
</parameter>
</ResourceParams>
</Context>
The Context element above facilitates the creation of a DataSource object from which you can get java.sql.Connection objects from a pool. The specifics of the DataSource object are given in the parameter elements of the ResourceParams element. The username and password parameters specify the user name and password used to access the database, the driverClassName parameter specifies the JDBC driver, and the url parameter specifies the database URL for accessing the MySQL database. The url parameter indicates that the database server resides in the same machine as Tomcat (the use of localhost in the URL) and the database the DataSource object references is the test database.
Warning
For Tomcat to create a DataSource object, you need the database JDBC driver in the common/lib directory of Tomcat’s deployment directory. The downloadable ZIP accompanying this book comes with the JDBC driver for MySQL. For other containers, consult the documentation on how to configure them to create a DataSource object.
Also, for your DAO implementation, you may want to extend the java.lang.Exception class to have your own DAO-specific exception. Methods in DAO objects can throw this specific exception so that you can provide code that deals with data access and data manipulation failures.
A simple DAO-specific exception class, named DAOException, is given in Listing 15.3.
Listing 15.3: The DAOException Class
package app15a.dao;
public class DAOException extends Exception {
}
The Config Class
The Config class is a utility class that provides application-specific values. See Chapter 14 for information on this Config class.
In the app15a you want to pass the dbType context parameter to your DAO objects. As you will see later, the dbType specifies the type of the relational database that stores your data, i.e. whether it is an Oracle, a MySQL, etc.
The Config class is given in Listing 15.4. Chapter 14 explained the importance of having a Config object and how to implement one.
Listing 15.4: The Config Class
package app15a.util;
import java.util.HashMap;
import java.util.Map;
public class Config {
private static Config configInstance;
private Map map = new HashMap();
static {
try {
configInstance = new Config();
}
catch (Exception e) {
}
}
public static Config getInstance() {
return configInstance;
}
public void addKeyValue(String key, Object value) {
map.put(key, value);
}
public Object getValue(String key) {
return map.get(key);
}
public Map getMap () {
return map;
}
}
The value of the following context-param element in the web.xml file is used to populate the Config object.
<context-param> <param-name>dbType</param-name> <param-value>mySQL</param-value> </context-param>
The CustomerDAO Interface
The application uses one DAO class, CustomerDAO. To support multiple databases, CustomerDAO is written as an interface that defines the methods for CustomerDAO objects. Listing 15.5 presents the CustomerDAO interface.
Listing 15.5: The CustomerDAO interface
package app15a.dao;
import app15a.to.CustomerTO;
import app15a.to.CustomerSearchCriteriaTO;
import java.util.ArrayList;
public interface CustomerDAO {
public void createCustomer(CustomerTO customer)
throws DAOException;
public void updateCustomer(CustomerTO customer)
throws DAOException;
public CustomerTO getCustomer(int customerId) throws DAOException;
public void deleteCustomer(int customerID) throws DAOException;
public ArrayList searchCustomers(
CustomerSearchCriteriaTO searchCriteria) throws DAOException;
}
Every method can throw a DAOException. The createCustomer and updateCustomer methods accept a CustomerTO object that needs to be inserted or updated. The getCustomer and deleteCustomer methods accept a customer identifier, and the searchCustomers method accepts a CustomerSearchCriteriaTO.
In the app15a application, the CustomerSearchCriteriaTO class is similar to the CustomerTO class, however in other applications the CustomerSearchCriteriaTO class can include search-related properties, such as sortOrder and maximumSearchResults, that do not exist in CustomerTO. Hence, the need for another class that encapsulates user search criteria.
The CustomerDAOMySQLImpl Class
The CustomerDAOMySQLImpl class, presented in Listing 15.6, is an implementation of the CustomerDAO interface. To support another database, you can create another CustomerDAO implementation, such as CustomerDAOOracleImpl, CustomerDAOSQLServerImpl, etc.
Listing 15.6: The CustomerDAOMySQLImpl Interface
package app15a.dao;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import app15a.dao.DAOException;
import app15a.dao.DBUtil;
import app15a.to.CustomerTO;
import app15a.to.CustomerSearchCriteriaTO;
public class CustomerDAOMySQLImpl extends DAOBase
implements CustomerDAO {
private static final String GET_CUSTOMER_SQL =
"SELECT name, address, phone FROM Customer WHERE id = ?";
private static final String CREATE_CUSTOMER_SQL =
"INSERT INTO Customer (name,address,phone) VALUES (?, ?, ?)";
private static final String DELETE_CUSTOMER_SQL =
"DELETE FROM Customer WHERE id = ?";
private static final String UPDATE_CUSTOMER_SQL =
"UPDATE Customer SET name=?, address=?, phone=? WHERE id = ?";
public void createCustomer(CustomerTO customer) throws DAOException {
Connection connection = null;
PreparedStatement pStatement = null;
try {
connection = getConnection();
//Prepare a statement to insert a record
pStatement = connection.prepareStatement(CREATE_CUSTOMER_SQL);
pStatement.setString(1, customer.getName());
pStatement.setString(2, customer.getAddress());
pStatement.setString(3, customer.getPhone());
pStatement.executeUpdate();
pStatement.close();
}
catch (SQLException ex) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
throw new DAOException();
}
}
}
public void updateCustomer(CustomerTO customer)
throws DAOException {
Connection connection = null;
PreparedStatement pStatement = null;
try {
connection = getConnection();
// Prepare a statement to insert a record
pStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL);
pStatement.setString(1, customer.getName());
pStatement.setString(2, customer.getAddress());
pStatement.setString(3, customer.getPhone());
pStatement.setInt(4, customer.getId());
pStatement.executeUpdate();
pStatement.close();
}
catch (SQLException e) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
}
}
}
public CustomerTO getCustomer(int customerId)
throws DAOException {
Connection connection = null;
PreparedStatement pStatement = null;
ResultSet rs = null;
CustomerTO customer = new CustomerTO();
try {
connection = getConnection();
// Prepare a statement to insert a record
pStatement = connection.prepareStatement(GET_CUSTOMER_SQL);
pStatement.setInt(1,customerId);
rs = pStatement.executeQuery();
if (rs.next()) {
customer.setName(rs.getString("name"));
customer.setAddress(rs.getString("address"));
customer.setPhone(rs.getString("phone"));
customer.setId(customerId);
}
rs.close();
pStatement.close();
}
catch (SQLException ex) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
}
}
return customer;
}
public void deleteCustomer(int customerId) throws DAOException {
Connection connection = null;
PreparedStatement pStatement = null;
try {
connection = getConnection();
pStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL);
pStatement.setInt(1, customerId);
pStatement.executeUpdate();
pStatement.close();
}
catch (SQLException e) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
}
}
}
public ArrayList searchCustomers(
CustomerSearchCriteriaTO searchCriteria) throws DAOException {
ArrayList arrayList = new ArrayList();
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
//Build the search criterias
StringBuffer criteriaSql = new StringBuffer(512);
criteriaSql.append(
"SELECT id, name, address, phone FROM Customer WHERE ");
if (searchCriteria.getName() != null) {
criteriaSql.append("name LIKE '%" +
DBUtil.fixSqlFieldValue(searchCriteria.getName()) + "%' AND ");
}
if (searchCriteria.getAddress() != null) {
criteriaSql.append("address LIKE '%" +
DBUtil.fixSqlFieldValue(searchCriteria.getAddress()) +
"%' AND ");
}
if (searchCriteria.getPhone() != null) {
criteriaSql.append("phone LIKE '%" + DBUtil.fixSqlFieldValue(searchCriteria.getPhone()) + "%'");
}
//Remove unused 'And' & 'WHERE'
if (criteriaSql.substring(criteriaSql.length()-5).
equals(" AND "))
criteriaSql.delete(criteriaSql.length()-5,
criteriaSql.length()-1);
if (criteriaSql.substring(criteriaSql.length()-7).
equals(" WHERE "))
criteriaSql.delete(criteriaSql.length()-7,
criteriaSql.length()-1);
try {
connection = getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(criteriaSql.toString());
while (rs.next()) {
CustomerTO customer = new CustomerTO();
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setAddress(rs.getString("address"));
customer.setPhone(rs.getString("phone"));
arrayList.add(customer);
}
rs.close();
statement.close();
}
catch (SQLException e) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
}
}
return arrayList;
}
}
The SQL statements for all the methods, except searchCustomers, are defined as static final strings because they never change. Making them static final avoids creating the same strings repeatedly. Also, all those methods use a PreparedStatement instead of a java.sql.Statement even though the PreparedStatement object is only executed once. The use of PreparedStatement saves you from having to check if one of the arguments contains a single quote. Using a Statement, you must escape any single quote in the argument.
The searchCustomers method, on the other hand, is based on a dynamic SQL statement. This necessitates us to use a Statement object. Consequently, you must check for single quotes in the arguments using the DbUtil class’s fixSqlFieldValue method. Listing 15.7 presents the fixSqlFieldValue method.
Listing 15.7: The fixSqlFieldValue method
public static String fixSqlFieldValue(String value) {
if (value==null)
return null;
int length = value.length();
StringBuffer fixedValue = new StringBuffer((int) (length * 1.1));
for (int i=0; i<length; i++) {
char c = value.charAt(i);
if (c=='\'')
fixedValue.append("''");
else
fixedValue.append(c);
}
return fixedValue.toString();
}
The DAOFactory Class
The DAOFactory class helps the client instantiate a DAO class. Also, the necessity for a DAOFactory class in the application stems from the fact that the implementation class name is not known at design time, e.g. whether it is CustomerDAOMySQLImpl or CustomerDAOOracleImpl. As such, the DAOFactory class hides the complexity of creating a DAO object.
The DAOFactory class is presented in Listing 15.8.
Listing 15.8: The DAOFactory Class
package app15a.dao;
import app15a.util.Config;
public class DAOFactory {
private String databaseType;
private static DAOFactory instance;
static {
instance = new DAOFactory();
}
private DAOFactory() {
databaseType = (String)
Config.getInstance().getValue("dbType") ;
}
public static DAOFactory getInstance() {
return instance;
}
public CustomerDAO getCustomerDAO() {
String className = null;
if (databaseType!=null) {
if (databaseType.equalsIgnoreCase("mysql")) {
return new CustomerDAOMySQLImpl();
}
//if (databaseType.equalsIgnoreCase("oracle")) {
// return new CustomerDAOOracleImpl();
//}
}
return null;
}
}
You can use the DAOFactory in Listing 15.9 if you know the implementation classes for all your DAOs when the application is written. This means, if you are thinking of only supporting two databases, MySQL and Oracle, you know beforehand the type for the CustomerDAO class is either CustomerDAOMySQLImpl or CustomerDAOOracleImpl. If in the future your application needs to support Microsoft SQL Server, you must rewrite the DAOFactory class, i.e. adds another if statement in the getCustomerDAO class.
You can add support of more databases without recompiling the DAOFactory class if you use reflection to create the DAO object. Instead of the dbType parameter in your web.xml file, you have customerDAOType. Then, you would have the following code in your DAOFactory class’s getCustomerDAO method.
String customerDAOType = Config.getCustomerDAOType(); Class customerDAOClass = Class.forName(customerDAOType); CustomerDAO customerDAO = customerDAOClass.newInstance();
The Clients
The clients of this application are the various Struts action classes: CreateCustomerAction, DeleteCustomerAction, GetCustomerAction, SearchCustomerAction, and UpdateCustomerAction. To obtain a CustomerDAO object, you use the DAOFactory class’s getCustomerDAO method. Once you have the object, you simply need to call the method you need.
Here is how you create a new customer.
CustomerDAO customerDAO = DAOFactory.getInstance().getCustomerDAO(); customerDAO.createCustomer(customerTO);
Looking at the code, you’ll find that the persistence layer (in this case the CustomerDAO class) hides the complexity of accessing the database. The client does not need to know anything but use the above two lines of code to persist a CustomerTO object.
Running the Application
The app15a application provides the action classes for creating a new customer, updating and deleting an existing a customer, and searching for customers. The main entry point is the displaySearchCustomerForm action. To invoke this action, use the following URL.
http://locahost:8080/app15a/displaySearchCustomerForm.do
You will see something similar to Figure 15.5.
Figure 15.5: The Search form
If you are running this application for the first time, you need to add some customers by clicking the Create New Customer link. Afterwards, you can search for customers and update and delete existing customers.