DAO tutorial - the data layer

Fast menu
Introduction
The data layer
Preparing database
User model
Handling DAO exceptions
Properties file
Properties file loader
DAO factory
User DAO interface
User DAO implementation
DAO utility class
DAO test harness
Further exercises
How about connection pooling?

Introduction

In almost every application there is less or more interaction with a database or any other kind of datastore. In a well designed application the database logic is separated from the business and presentation logic. It must be able to run independently from the business and the presentation logic. This allows the developer much better maintainability, testability, reusability and replaceability.

Better maintainability, because the database logic is concentrated in a single layer, so there is no need to search for all related code in different classes with mixed code. Better testability, because the database logic can be run independently, so there is no need to run the business and/or presentation logic to test the database logic. Better reusability, because you only need to put the same database logic in the classpath of another applications (JSF, JSP, Swing, plain Java application, etcetera) to use it, so there is no need to copypaste it over all places. Better replaceability, because you can more easy provide interfaces to it and make use of the factory pattern, so there is no need to replace all code in the business and/or presentation logic when you want to switch from e.g. plain JDBC to Hibernate or JPA.

This article will show step by step how to create a data layer using basic JDBC.

Back to top

The data layer

The database logic is also called the 'data layer'. It exist of database access objects (DAO's). The DAO's can create, read, update and delete (CRUD) the model objects in/from the datastore. The model objects are just javabeans representing real world data, such as User, Product, Order, etcetera.

In this part of the DAO tutorial we'll create a data layer based on the basic JDBC API. The final goal is to map between the 'User' table in the database and the 'User' model in the Java code. This tutorial assumes that you're familiar with at least the Java basics, the JDBC API and the SQL language. This is essential to understand the code properly, although the code is well documented and almost self-explaining. If you're not familiar with one or more of them, then follow the links to learn about them first.

Back to top

Preparing database

In this tutorial we'll pick MySQL as database server and create a 'javabase' database with the 'User' table. Although, when the code is well written, you can use any SQL database server to your choice. You might need to change the CREATE statements slightly to suit what the specific database server understands. Here is how you could create the 'User' table in MySQL:

CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE javabase.User (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(60) NOT NULL,
    password VARCHAR(32) NOT NULL,
    firstname VARCHAR(40) NULL,
    lastname VARCHAR(40) NULL,
    birthdate DATE NULL,

    PRIMARY KEY (id),
    UNIQUE (email)
);

This 'User' table has an autogenerated ID field which indicate the unique user ID. The email and password fields are required (handy to have as a login). The password field is to be filled with a MD5 hash of the password (to improve security, so that one who hijacked the database still can't recover the password) so it needs to be exactly 32 chars. The firstname, lastname and birthdate are optional (NULL is allowed). The email address is unique in the whole table. For a good compatibility with all characters available on the world, it's a good practice to set the database to use the Unicode character encoding UTF-8 by default.

Also as a good practice -it is always better than using 'root'- also create a new database user who has privileges on the table. We will create a new user 'java' which is exclusively to be used by Java code. Here we assume that the user 'java' is operating at domain 'localhost' (when the JVM as well as the database server runs at the same machine, which might not always occur in production, but that's beyond the scope of this tutorial). The statements below are MySQL specific, you might need to change the statements depending on the database server used.

CREATE USER 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';
GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';

Last but not least, to access a database server using the JDBC API, you need a JDBC driver. The JDBC driver is a concrete implementation of the JDBC API (which exist of almost only interfaces). The JDBC driver is generally already provided by the database manfacturer itself. In case of MySQL you can download it here. Just place the JAR file in the runtime classpath.

Back to top

User model

Now we need to create a model class which represents a single row of the 'User' table of the 'javabase' database. It must be a class which follows the Javabean specifications. That is, having a (implicit) no-arg constructor, having private properties which are exposed by public getters and setters, having the Object#equals() and Object#hashCode() implemented and finally the class must also implement java.io.Serializable.

Also using wrapper datatype objects (Long, Integer, etc.) instead of primitive datatypes (long, int, etc.) is preferred. This among others because of the fact that any datatype in a database can be null, while a primitive in Java cannot be null at all. That would cause problems if you for example are using an integer field in the database which allows null, while you're mapping it against int in Java.

package com.example.model;

import java.io.Serializable;
import java.util.Date;

/**
 * This class represents the User model. This model class can be used thoroughout all
 * layers, the data layer, the controller layer and the view layer.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class User implements Serializable {

    // Constants ----------------------------------------------------------------------------------

    private static final long serialVersionUID = 1L;

    // Properties ---------------------------------------------------------------------------------

    private Long id;
    private String email;
    private String password;
    private String firstname;
    private String lastname;
    private Date birthdate;

    // Getters/setters ----------------------------------------------------------------------------

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public Date getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(Date birthdate) {
        this.birthdate = birthdate;
    }

    // Object overrides ---------------------------------------------------------------------------

    /**
     * The user ID is unique for each User. So this should compare User by ID only.
     * @see java.lang.Object#equals(java.lang.Object)
     */
    @Override
    public boolean equals(Object other) {
        return (other instanceof User) && (id != null)
             ? id.equals(((User) other).id)
             : (other == this);
    }

    /**
     * The user ID is unique for each User. So User with same ID should return same hashcode.
     * @see java.lang.Object#hashCode()
     */
    @Override
    public int hashCode() {
        return (id != null) 
             ? (this.getClass().hashCode() + id.hashCode()) 
             : super.hashCode();
    }

    /**
     * Returns the String representation of this User. Not required, it just pleases reading logs.
     * @see java.lang.Object#toString()
     */
    @Override
    public String toString() {
        return String.format("User[id=%d,email=%s,firstname=%s,lastname=%s,birthdate=%s]", 
            id, email, firstname, lastname, birthdate);
    }

}

Back to top

Handling DAO exceptions

To 'hide' the specific implementation code of the data layer (e.g. JDBC, Hibernate or JPA), it may be useful to wrap their (runtime) exceptions with DAO specific exception classes. So after extending java.lang.RuntimeException the usual way you should get something like as the following classes:

package com.example.dao;

/**
 * This class represents a generic DAO exception. It should wrap any exception of the underlying
 * code, such as SQLExceptions.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class DAOException extends RuntimeException {

    // Constants ----------------------------------------------------------------------------------

    private static final long serialVersionUID = 1L;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Constructs a DAOException with the given detail message.
     * @param message The detail message of the DAOException.
     */
    public DAOException(String message) {
        super(message);
    }

    /**
     * Constructs a DAOException with the given root cause.
     * @param cause The root cause of the DAOException.
     */
    public DAOException(Throwable cause) {
        super(cause);
    }

    /**
     * Constructs a DAOException with the given detail message and root cause.
     * @param message The detail message of the DAOException.
     * @param cause The root cause of the DAOException.
     */
    public DAOException(String message, Throwable cause) {
        super(message, cause);
    }

}

package com.example.dao;

/**
 * This class represents an exception in the DAO configuration which cannot be resolved at runtime,
 * such as a missing resource in the classpath, a missing property in the properties file, etcetera.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class DAOConfigurationException extends RuntimeException {

    // Constants ----------------------------------------------------------------------------------

    private static final long serialVersionUID = 1L;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Constructs a DAOConfigurationException with the given detail message.
     * @param message The detail message of the DAOConfigurationException.
     */
    public DAOConfigurationException(String message) {
        super(message);
    }

    /**
     * Constructs a DAOConfigurationException with the given root cause.
     * @param cause The root cause of the DAOConfigurationException.
     */
    public DAOConfigurationException(Throwable cause) {
        super(cause);
    }

    /**
     * Constructs a DAOConfigurationException with the given detail message and root cause.
     * @param message The detail message of the DAOConfigurationException.
     * @param cause The root cause of the DAOConfigurationException.
     */
    public DAOConfigurationException(String message, Throwable cause) {
        super(message, cause);
    }

}

Back to top

Properties file

To access a database using JDBC you need at least information about the JDBC URL of the database to be connected, the class name of the JDBC driver to be loaded and the username and password to login to the database. Instead of the JDBC URL and the JDBC driver class name, you can also use only the JNDI name of the container managed datasource to be used. Those parameters must be stored somewhere that the Java code can access it and that you can easily maintain it externally. A common practice is to put those kind of configuration settings in a properties file and to place the properties file in the classpath so that the Java code can access it.

It is useful to keep a single properties file for multiple database configuration settings. To separate the database specific settings it is useful to use a specific key prefix. In this case we'll take the database name as specific key prefix.

Here is how the file dao.properties look like:

javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
javabase.jdbc.driver = com.mysql.jdbc.Driver
javabase.jdbc.username = java
javabase.jdbc.password = d$7hF_r!9Y

Put it somewhere in the default classpath (e.g. the root package) or specify its path in the -classpath parameter when running the Java application. In case of a webapplication you may consider to place it outside the WAR (the webapp project) so that you can edit it without the need to redeploy (only an appserver/webapp restart is enough). In such case you need to add its path to the (shared) classpath of the application server in question. In for example Apache Tomcat 6.0, you can specify it in the shared.loader property of the catalina.properties file in the Tomcat/conf folder.

Back to top

Properties file loader

We can use the java.util.Properties API to load those properties. We only want to wrap it in another class so that it can take a specific key prefix in the constructor. We also want to wrap the getProperty() so that it takes a boolean parameter 'mandatory' so that it throws a DAOConfigurationException when the property value is null or empty while it is mandatory. Also, instead of loading the properties file from the disk everytime, we want to load it once and keep the properties file in the memory.

Note: it depends on how often you think that this file changes in your environment, if it changes only once per year, then it is really not worth that to load it from disk everytime, but if it changes for example every day, then it might be worth to add a static method which reloads the properties file and execute it by some (scheduled) background job.

package com.example.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * This class immediately loads the DAO properties file 'dao.properties' once in memory and provides
 * a constructor which takes the specific key which is to be used as property key prefix of the DAO
 * properties file. There is a property getter which only returns the property prefixed with
 * 'specificKey.' and provides the option to indicate whether the property is mandatory or not.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class DAOProperties {

    // Constants ----------------------------------------------------------------------------------

    private static final String PROPERTIES_FILE = "dao.properties";
    private static final Properties PROPERTIES = new Properties();

    static {
        ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
        InputStream propertiesFile = classLoader.getResourceAsStream(PROPERTIES_FILE);

        if (propertiesFile == null) {
            throw new DAOConfigurationException(
                "Properties file '" + PROPERTIES_FILE + "' is missing in classpath.");
        }

        try {
            PROPERTIES.load(propertiesFile);
        } catch (IOException e) {
            throw new DAOConfigurationException(
                "Cannot load properties file '" + PROPERTIES_FILE + "'.", e);
        }
    }

    // Vars ---------------------------------------------------------------------------------------

    private String specificKey;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Construct a DAOProperties instance for the given specific key which is to be used as property
     * key prefix of the DAO properties file.
     * @param specificKey The specific key which is to be used as property key prefix.
     * @throws DAOConfigurationException During class initialization if the DAO properties file is
     * missing in the classpath or cannot be loaded.
     */
    public DAOProperties(String specificKey) throws DAOConfigurationException {
        this.specificKey = specificKey;
    }

    // Actions ------------------------------------------------------------------------------------

    /**
     * Returns the DAOProperties instance specific property value associated with the given key with
     * the option to indicate whether the property is mandatory or not.
     * @param key The key to be associated with a DAOProperties instance specific value.
     * @param mandatory Sets whether the returned property value should not be null nor empty.
     * @return The DAOProperties instance specific property value associated with the given key.
     * @throws DAOConfigurationException If the returned property value is null or empty while
     * it is mandatory.
     */
    public String getProperty(String key, boolean mandatory) throws DAOConfigurationException {
        String fullKey = specificKey + "." + key;
        String property = PROPERTIES.getProperty(fullKey);

        if (property == null || property.trim().length() == 0) {
            if (mandatory) {
                throw new DAOConfigurationException("Required property '" + fullKey + "'"
                    + " is missing in properties file '" + PROPERTIES_FILE + "'.");
            } else {
                // Make empty value null. Empty Strings are evil.
                property = null;
            }
        }

        return property;
    }

}

Back to top

DAO factory

And now the 'main' class of the DAO package. You can use getInstance() to obtain an instance for the given database name, which on its turn is to be used as specific key for the properties file. The specific instance returned depends on the properties file configuration. Then you can obtain DAO's for the database instance using the DAO getters.

It has a package private getConnection() method which returns a connection to the database. The DAO classes in the same package can acquire the connection using that method. If you specified both the URL and the driver in the properties file, then it would load the driver and make use of a java.sql.DriverManager#getConnection() call to return the connection. If you specified only the URL in the properties file, it will be assumed as JNDI name of the datasource and make use of javax.sql.DataSource#getConnection() call to return the connection. With a DataSource you can easily make use of connection pooling to improve performance. If you're using for example Apache Tomcat as application server, then you can find here how to configure a JNDI datasource with connection pooling capabilities in Tomcat: Apache Tomcat 6.0 JNDI Datasource HOW-TO. You can find a complete example in the before-last chapter: How about connection pooling?.

package com.example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * This class represents a DAO factory for a SQL database. You can use {@link #getInstance(String)}
 * to obtain a new instance for the given database name. The specific instance returned depends on
 * the properties file configuration. You can obtain DAO's for the DAO factory instance using the 
 * DAO getters.
 * 

 * This class requires a properties file named 'dao.properties' in the classpath with among others
 * the following properties:
 * 

 * name.url *
 * name.driver
 * name.username
 * name.password
 * 
 * Those marked with * are required, others are optional and can be left away or empty. Only the
 * username is required when any password is specified.
 * 

 * 
The 'name' must represent the database name in {@link #getInstance(String)}.
 * 
The 'name.url' must represent either the JDBC URL or JNDI name of the database.
 * 
The 'name.driver' must represent the full qualified class name of the JDBC driver.
 * 
The 'name.username' must represent the username of the database login.
 * 
The 'name.password' must represent the password of the database login.
 * 
 * If you specify the driver property, then the url property will be assumed as JDBC URL. If you
 * omit the driver property, then the url property will be assumed as JNDI name. When using JNDI
 * with username/password preconfigured, you can omit the username and password properties as well.
 * 

 * Here are basic examples of valid properties for a database with the name 'javabase':
 * 

 * javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
 * javabase.jdbc.driver = com.mysql.jdbc.Driver
 * javabase.jdbc.username = java
 * javabase.jdbc.password = d$7hF_r!9Y
 * 
 * 

 * javabase.jndi.url = jdbc/javabase
 * 
 * Here is a basic use example:
 * 

 * DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
 * UserDAO userDAO = javabase.getUserDAO();
 * 
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public abstract class DAOFactory {

    // Constants ----------------------------------------------------------------------------------

    private static final String PROPERTY_URL = "url";
    private static final String PROPERTY_DRIVER = "driver";
    private static final String PROPERTY_USERNAME = "username";
    private static final String PROPERTY_PASSWORD = "password";

    // Actions ------------------------------------------------------------------------------------

    /**
     * Returns a new DAOFactory instance for the given database name.
     * @param name The database name to return a new DAOFactory instance for.
     * @return A new DAOFactory instance for the given database name.
     * @throws DAOConfigurationException If the database name is null, or if the properties file is
     * missing in the classpath or cannot be loaded, or if a required property is missing in the
     * properties file, or if either the driver cannot be loaded or the datasource cannot be found.
     */
    public static DAOFactory getInstance(String name) throws DAOConfigurationException {
        if (name == null) {
            throw new DAOConfigurationException("Database name is null.");
        }

        DAOProperties properties = new DAOProperties(name);
        String url = properties.getProperty(PROPERTY_URL, true);
        String driverClassName = properties.getProperty(PROPERTY_DRIVER, false);
        String password = properties.getProperty(PROPERTY_PASSWORD, false);
        String username = properties.getProperty(PROPERTY_USERNAME, password != null);
        DAOFactory instance;

        // If driver is specified, then load it to let it register itself with DriverManager.
        if (driverClassName != null) {
            try {
                Class.forName(driverClassName);
            } catch (ClassNotFoundException e) {
                throw new DAOConfigurationException(
                    "Driver class '" + driverClassName + "' is missing in classpath.", e);
            }
            instance = new DriverManagerDAOFactory(url, username, password);
        }

        // Else assume URL as DataSource URL and lookup it in the JNDI.
        else {
            DataSource dataSource;
            try {
                dataSource = (DataSource) new InitialContext().lookup(url);
            } catch (NamingException e) {
                throw new DAOConfigurationException(
                    "DataSource '" + url + "' is missing in JNDI.", e);
            }
            if (username != null) {
                instance = new DataSourceWithLoginDAOFactory(dataSource, username, password);
            } else {
                instance = new DataSourceDAOFactory(dataSource);
            }
        }

        return instance;
    }

    /**
     * Returns a connection to the database. Package private so that it can be used inside the DAO
     * package only.
     * @return A connection to the database.
     * @throws SQLException If acquiring the connection fails.
     */
    abstract Connection getConnection() throws SQLException;

    // DAO implementation getters -----------------------------------------------------------------

    /**
     * Returns the User DAO associated with the current DAOFactory.
     * @return The User DAO associated with the current DAOFactory.
     */
    public UserDAO getUserDAO() {
        return new UserDAOJDBC(this);
    }

    // You can add more DAO implementation getters here.

}

// Default DAOFactory implementations -------------------------------------------------------------

/**
 * The DriverManager based DAOFactory.
 */
class DriverManagerDAOFactory extends DAOFactory {
    private String url;
    private String username;
    private String password;

    DriverManagerDAOFactory(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
    }

    @Override
    Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
}

/**
 * The DataSource based DAOFactory.
 */
class DataSourceDAOFactory extends DAOFactory {
    private DataSource dataSource;

    DataSourceDAOFactory(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}

/**
 * The DataSource-with-Login based DAOFactory.
 */
class DataSourceWithLoginDAOFactory extends DAOFactory {
    private DataSource dataSource;
    private String username;
    private String password;

    DataSourceWithLoginDAOFactory(DataSource dataSource, String username, String password) {
        this.dataSource = dataSource;
        this.username = username;
        this.password = password;
    }

    @Override
    Connection getConnection() throws SQLException {
        return dataSource.getConnection(username, password);
    }
}

Back to top

User DAO interface

Now we need to define a DAO interface which defines all actions which needs to be performed on the User model class. All DAO implementations (JDBC, Hibernate, JPA, etc) should then adhere the contract as specified by this interface. A concrete implementation of the DAO interface should only be obtained from the DAO factory and you should use it as a central point for the mapping between the User model and a SQL database. When you want to add new methods, then you should specify them in the interface first.

package com.example.dao;

import java.util.List;

import com.example.model.User;

/**
 * This interface represents a contract for a DAO for the {@link User} model.
 * Note that all methods which returns the {@link User} from the DB, will not
 * fill the model with the password, due to security reasons.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public interface UserDAO {

    // Actions ------------------------------------------------------------------------------------

    /**
     * Returns the user from the database matching the given ID, otherwise null.
     * @param id The ID of the user to be returned.
     * @return The user from the database matching the given ID, otherwise null.
     * @throws DAOException If something fails at database level.
     */
    public User find(Long id) throws DAOException;

    /**
     * Returns the user from the database matching the given email and password, otherwise null.
     * @param email The email of the user to be returned.
     * @param password The password of the user to be returned.
     * @return The user from the database matching the given email and password, otherwise null.
     * @throws DAOException If something fails at database level.
     */
    public User find(String email, String password) throws DAOException;

    /**
     * Returns a list of all users from the database ordered by user ID. The list is never null and
     * is empty when the database does not contain any user.
     * @return A list of all users from the database ordered by user ID.
     * @throws DAOException If something fails at database level.
     */
    public List list() throws DAOException;

    /**
     * Create the given user in the database. The user ID must be null, otherwise it will throw
     * IllegalArgumentException. After creating, the DAO will set the obtained ID in the given user.
     * @param user The user to be created in the database.
     * @throws IllegalArgumentException If the user ID is not null.
     * @throws DAOException If something fails at database level.
     */
    public void create(User user) throws IllegalArgumentException, DAOException;

    /**
     * Update the given user in the database. The user ID must not be null, otherwise it will throw
     * IllegalArgumentException. Note: the password will NOT be updated. Use changePassword() instead.
     * @param user The user to be updated in the database.
     * @throws IllegalArgumentException If the user ID is null.
     * @throws DAOException If something fails at database level.
     */
    public void update(User user) throws IllegalArgumentException, DAOException;

    /**
     * Delete the given user from the database. After deleting, the DAO will set the ID of the given
     * user to null.
     * @param user The user to be deleted from the database.
     * @throws DAOException If something fails at database level.
     */
    public void delete(User user) throws DAOException;

    /**
     * Returns true if the given email address exist in the database.
     * @param email The email address which is to be checked in the database.
     * @return True if the given email address exist in the database.
     * @throws DAOException If something fails at database level.
     */
    public boolean existEmail(String email) throws DAOException;

    /**
     * Change the password of the given user. The user ID must not be null, otherwise it will throw
     * IllegalArgumentException.
     * @param user The user to change the password for.
     * @throws IllegalArgumentException If the user ID is null.
     * @throws DAOException If something fails at database level.
     */
    public void changePassword(User user) throws DAOException;

}

Back to top

User DAO implementation

And here is where it all happens. This is a concrete JDBC implementation of the User DAO interface. It has a package private constructor so that only the DAOFactory can construct it and associate the DAO class with itself. Another good practice is to acquire and close the Connection, (Prepared)Statement and ResultSet in the shortest possible scope. This DAO class shows how to do it properly, using the package private DAOFactory#getConnection() method. Closing should happen in the finally block in the reversed order as you have acquired them. Otherwise an (unexpected) exception would cause the resources left open. When you don't close the resources properly, they may be kept open for too long and the database would run out of them on long term (for example in a lifetime long running Java EE web application!) and the whole application would crash.

Also using PreparedStatement has strong preference over using Statement as it is faster, eases setting of Java objects (e.g. Date and InputStream) in a SQL query and, most importantingly, it saves your logic from SQL injection attacks, especially when the query data can be controlled by client input.

package com.example.dao;

import static com.example.dao.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.example.model.User;

/**
 * This class represents a concrete JDBC implementation of the {@link UserDAO} interface.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class UserDAOJDBC implements UserDAO {

    // Constants ----------------------------------------------------------------------------------

    private static final String SQL_FIND_BY_ID =
        "SELECT id, email, firstname, lastname, birthdate FROM User WHERE id = ?";
    private static final String SQL_FIND_BY_EMAIL_AND_PASSWORD =
        "SELECT id, email, firstname, lastname, birthdate FROM User WHERE email = ? AND password = MD5(?)";
    private static final String SQL_LIST_ORDER_BY_ID =
        "SELECT id, email, firstname, lastname, birthdate FROM User ORDER BY id";
    private static final String SQL_INSERT =
        "INSERT INTO User (email, password, firstname, lastname, birthdate) VALUES (?, MD5(?), ?, ?, ?)";
    private static final String SQL_UPDATE =
        "UPDATE User SET email = ?, firstname = ?, lastname = ?, birthdate = ? WHERE id = ?";
    private static final String SQL_DELETE =
        "DELETE FROM User WHERE id = ?";
    private static final String SQL_EXIST_EMAIL =
        "SELECT id FROM User WHERE email = ?";
    private static final String SQL_CHANGE_PASSWORD =
        "UPDATE User SET password = MD5(?) WHERE id = ?";

    // Vars ---------------------------------------------------------------------------------------

    private DAOFactory daoFactory;

    // Constructors -------------------------------------------------------------------------------

    /**
     * Construct an User DAO for the given DAOFactory. Package private so that it can be constructed
     * inside the DAO package only.
     * @param daoFactory The DAOFactory to construct this User DAO for.
     */
    UserDAOJDBC(DAOFactory daoFactory) {
        this.daoFactory = daoFactory;
    }

    // Actions ------------------------------------------------------------------------------------

    @Override
    public User find(Long id) throws DAOException {
        return find(SQL_FIND_BY_ID, id);
    }

    @Override
    public User find(String email, String password) throws DAOException {
        return find(SQL_FIND_BY_EMAIL_AND_PASSWORD, email, password);
    }

    /**
     * Returns the user from the database matching the given SQL query with the given values.
     * @param sql The SQL query to be executed in the database.
     * @param values The PreparedStatement values to be set.
     * @return The user from the database matching the given SQL query with the given values.
     * @throws DAOException If something fails at database level.
     */
    private User find(String sql, Object... values) throws DAOException {
        User user = null;

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, sql, false, values);
            ResultSet resultSet = statement.executeQuery();
        ) {
            if (resultSet.next()) {
                user = map(resultSet);
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }

        return user;
    }

    @Override
    public List list() throws DAOException {
        List users = new ArrayList<>();

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_LIST_ORDER_BY_ID);
            ResultSet resultSet = statement.executeQuery();
        ) {
            while (resultSet.next()) {
                users.add(map(resultSet));
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }

        return users;
    }

    @Override
    public void create(User user) throws IllegalArgumentException, DAOException {
        if (user.getId() != null) {
            throw new IllegalArgumentException("User is already created, the user ID is not null.");
        }

        Object[] values = {
            user.getEmail(),
            user.getPassword(),
            user.getFirstname(),
            user.getLastname(),
            toSqlDate(user.getBirthdate())
        };

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_INSERT, true, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Creating user failed, no rows affected.");
            }
            
            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    user.setId(generatedKeys.getLong(1));
                } else {
                    throw new DAOException("Creating user failed, no generated key obtained.");
                }
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    @Override
    public void update(User user) throws DAOException {
        if (user.getId() == null) {
            throw new IllegalArgumentException("User is not created yet, the user ID is null.");
        }

        Object[] values = {
            user.getEmail(),
            user.getFirstname(),
            user.getLastname(),
            toSqlDate(user.getBirthdate()),
            user.getId()
        };

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_UPDATE, false, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Updating user failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    @Override
    public void delete(User user) throws DAOException {
        Object[] values = { 
            user.getId()
        };

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_DELETE, false, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Deleting user failed, no rows affected.");
            } else {
                user.setId(null);
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    @Override
    public boolean existEmail(String email) throws DAOException {
        Object[] values = { 
            email
        };

        boolean exist = false;

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_EXIST_EMAIL, false, values);
            ResultSet resultSet = statement.executeQuery();
        ) {
            exist = resultSet.next();
        } catch (SQLException e) {
            throw new DAOException(e);
        }

        return exist;
    }

    @Override
    public void changePassword(User user) throws DAOException {
        if (user.getId() == null) {
            throw new IllegalArgumentException("User is not created yet, the user ID is null.");
        }

        Object[] values = {
            user.getPassword(),
            user.getId()
        };

        try (
            Connection connection = daoFactory.getConnection();
            PreparedStatement statement = prepareStatement(connection, SQL_CHANGE_PASSWORD, false, values);
        ) {
            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
                throw new DAOException("Changing password failed, no rows affected.");
            }
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    // Helpers ------------------------------------------------------------------------------------

    /**
     * Map the current row of the given ResultSet to an User.
     * @param resultSet The ResultSet of which the current row is to be mapped to an User.
     * @return The mapped User from the current row of the given ResultSet.
     * @throws SQLException If something fails at database level.
     */
    private static User map(ResultSet resultSet) throws SQLException {
        User user = new User();
        user.setId(resultSet.getLong("id"));
        user.setEmail(resultSet.getString("email"));
        user.setFirstname(resultSet.getString("firstname"));
        user.setLastname(resultSet.getString("lastname"));
        user.setBirthdate(resultSet.getDate("birthdate"));
        return user;
    }

}

Back to top

DAO utility class

Repeated and/or standardized code is best to be refactored into an utility class (a final and non-constructable class with only static methods). This also applies on DAO code for which we create the following class.

package com.example.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Utility class for DAO's. This class contains commonly used DAO logic which is been refactored in
 * single static methods. As far it contains a PreparedStatement values setter and a
 * java.util.Date to java.sql.Date converter.
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public final class DAOUtil {

    // Constructors -------------------------------------------------------------------------------

    private DAOUtil() {
        // Utility class, hide constructor.
    }

    // Actions ------------------------------------------------------------------------------------

    /**
     * Returns a PreparedStatement of the given connection, set with the given SQL query and the
     * given parameter values.
     * @param connection The Connection to create the PreparedStatement from.
     * @param sql The SQL query to construct the PreparedStatement with.
     * @param returnGeneratedKeys Set whether to return generated keys or not.
     * @param values The parameter values to be set in the created PreparedStatement.
     * @throws SQLException If something fails during creating the PreparedStatement.
     */
    public static PreparedStatement prepareStatement
        (Connection connection, String sql, boolean returnGeneratedKeys, Object... values)
            throws SQLException
    {
        PreparedStatement statement = connection.prepareStatement(sql,
            returnGeneratedKeys ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
        setValues(statement, values);
        return statement;
    }

    /**
     * Set the given parameter values in the given PreparedStatement.
     * @param connection The PreparedStatement to set the given parameter values in.
     * @param values The parameter values to be set in the created PreparedStatement.
     * @throws SQLException If something fails during setting the PreparedStatement values.
     */
    public static void setValues(PreparedStatement statement, Object... values)
        throws SQLException
    {
        for (int i = 0; i < values.length; i++) {
            statement.setObject(i + 1, values[i]);
        }
    }

    /**
     * Converts the given java.util.Date to java.sql.Date.
     * @param date The java.util.Date to be converted to java.sql.Date.
     * @return The converted java.sql.Date.
     */
    public static Date toSqlDate(java.util.Date date) {
     return (date != null) ? new Date(date.getTime()) : null;
    }

}

Back to top

DAO test harness

And now finally the test harness for all classes in the DAO package. This is just a simple class with a main method which can be runt as a Java application in the command console or in the IDE. Everytime when you change the data layer it is useful to use this test harness to test the data layer to verify if nothing has been broken or if newly added stuff (add it to the test harness code then) functions correctly.

package com.example;

import java.text.SimpleDateFormat;
import java.util.List;

import com.example.dao.DAOFactory;
import com.example.dao.UserDAO;
import com.example.model.User;

/**
 * Test harness for the com.example.dao package. This require the following preconditions:
 * 

 * 
A MySQL server running at localhost:3306 with a database named 'javabase'.
 * 
A 'user' table in the 'javabase' database which is created as follows:
 * 

 * CREATE TABLE javabase.user (
 *     id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 *     email VARCHAR(60) NOT NULL,
 *     password VARCHAR(32) NOT NULL,
 *     firstname VARCHAR(40) NULL,
 *     lastname VARCHAR(40) NULL,
 *     birthdate DATE NULL,
 *
 *     PRIMARY KEY (id),
 *     UNIQUE (email)
 * )
 * 
 * 
A MySQL user with the name 'java' and password 'd$7hF_r!9Y' which has sufficient rights on
 * the javabase.user table.
 * 
A MySQL JDBC Driver JAR file in the classpath.
 * 
A properties file 'dao.properties' in the classpath with the following entries:
 * 

 * javabase.jdbc.driver = com.mysql.jdbc.Driver
 * javabase.jdbc.url = jdbc:mysql://localhost:3306/javabase
 * javabase.jdbc.username = java
 * javabase.jdbc.password = d$7hF_r!9Y
 * 
 * 
 *
 * @author BalusC
 * @link http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html
 */
public class DAOTest {

    public static void main(String[] args) throws Exception {
        // Obtain DAOFactory.
        DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
        System.out.println("DAOFactory successfully obtained: " + javabase);

        // Obtain UserDAO.
        UserDAO userDAO = javabase.getUserDAO();
        System.out.println("UserDAO successfully obtained: " + userDAO);

        // Create user.
        User user = new User();
        user.setEmail("foo@bar.com");
        user.setPassword("password");
        userDAO.create(user);
        System.out.println("User successfully created: " + user);

        // Create another user.
        User anotherUser = new User();
        anotherUser.setEmail("bar@foo.com");
        anotherUser.setPassword("anotherPassword");
        anotherUser.setFirstname("Bar");
        anotherUser.setLastname("Foo");
        anotherUser.setBirthdate(new SimpleDateFormat("yyyy-MM-dd").parse("1978-03-26"));
        userDAO.create(anotherUser);
        System.out.println("Another user successfully created: " + anotherUser);

        // Update user.
        user.setFirstname("Foo");
        user.setLastname("Bar");
        userDAO.update(user);
        System.out.println("User successfully updated: " + user);

        // Update user.
        user.setFirstname("Foo");
        user.setLastname("Bar");
        userDAO.update(user);
        System.out.println("User successfully updated: " + user);

        // List all users.
        List users = userDAO.list();
        System.out.println("List of users successfully queried: " + users);
        System.out.println("Thus, amount of users in database is: " + users.size());

        // Delete user.
        userDAO.delete(user);
        System.out.println("User successfully deleted: " + user);

        // Check if email exists.
        boolean exist = userDAO.existEmail("foo@bar.com");
        System.out.println("This email should not exist anymore, so this should print false: " + exist);

        // Change password.
        anotherUser.setPassword("newAnotherPassword");
        userDAO.changePassword(anotherUser);
        System.out.println("Another user's password successfully changed: " + anotherUser);

        // Get another user by email and password.
        User foundAnotherUser = userDAO.find("bar@foo.com", "newAnotherPassword");
        System.out.println("Another user successfully queried with new password: " + foundAnotherUser);

        // Delete another user.
        userDAO.delete(foundAnotherUser);
        System.out.println("Another user successfully deleted: " + foundAnotherUser);

        // List all users again.
        users = userDAO.list();
        System.out.println("List of users successfully queried: " + users);
        System.out.println("Thus, amount of users in database is: " + users.size());
    }

}

On a fresh and empty database table this test harness should produce the following output (slightly changed with newlines to fit on this blog article's screen):

DAOFactory successfully obtained: com.example.dao.DriverManagerDAOFactory@74c74b55

UserDAO successfully obtained: com.example.dao.UserDAOJDBC@5b0f3bd7

User successfully created:

    User[id=1,email=foo@bar.com,firstname=null,lastname=null,birthdate=null]

Another user successfully created:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=Sun Mar 26 00:00:00 GMT-04:00 1978]

User successfully updated:

    User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

User successfully updated:

    User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

List of users successfully queried:

    [User[id=1,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null],

    User[id=8,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]]

Thus, amount of users in database is: 2

User successfully deleted:

    User[id=null,email=foo@bar.com,firstname=Foo,lastname=Bar,birthdate=null]

This email should not exist anymore, so this should print false: false

Another user's password successfully changed:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=Sun Mar 26 00:00:00 GMT-04:00 1978]

Another user successfully queried with new password:

    User[id=2,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]

Another user successfully deleted:

    User[id=null,email=bar@foo.com,firstname=Bar,lastname=Foo,birthdate=1978-03-26]

List of users successfully queried: []

Thus, amount of users in database is: 0

You see, it successfully creates the user (the user ID is been set by DAO), finds the user, updates the user, lists the users, deletes the user (the user ID is nulled out by DAO), checks the email and changes the password.

Back to top

Further exercises

To extend your data layer further, you may consider to extract interfaces from the DAOFactory and all DAO classes such as UserDAO, rename the existing implementations to DaoFactoryJDBC and UserDAOJDBC and write another implementations for those interfaces (DaoFactoryHibernate and UserDAOHibernate maybe?). You can also decide to let all your DAO interfaces extend a single base interface and replace all DAO getters in the DAOFactory by a single getter, something like:

    public extends BaseDAO> DAO getDAOImpl(Class daoInterface)
        throws DAOConfigurationException
    {
        String daoInterfaceName = daoInterface.getName();

        if (!daoInterface.isInterface()) {
            throw new DAOConfigurationException("Class '" + daoInterfaceName + "'"
                + " is actually not an Interface.");
        }

        String daoClassName = daoProperties.getProperty(daoInterfaceName, true);
        DAO daoImplementation;

        try {
            daoImplementation = daoInterface.cast(Class.forName(daoClassName).newInstance());
        } catch (ClassNotFoundException e) {
            throw new DAOConfigurationException("DAO class '" + daoClassName
                + "' is missing in classpath. Verify the class or the '" + daoInterfaceName
                + "' property.", e);
        } catch (IllegalAccessException e) {
            throw new DAOConfigurationException("DAO class '" + daoClassName
                + "' cannot be accessed. Verify the class or the '" + daoInterfaceName
                + "' property.", e);
        } catch (InstantiationException e) {
            throw new DAOConfigurationException("DAO class '" + daoClassName
                + "' cannot be instantiated. Verify the class or the '" + daoInterfaceName
                + "' property.", e);
        } catch (ClassCastException e) {
            throw new DAOConfigurationException("DAO class '" + daoClassName
                + "' does not implement '" + daoInterfaceName + "'. Verify the class or the '"
                + daoInterfaceName + "' property.", e);
        }

        daoImplementation.setDAOFactory(this);

        return daoImplementation;
    }

UserDAO userDAO = daoFactory.getDAOImpl(UserDAO.class);

Back to top

How about connection pooling?

Connecting the database is a fairly expensive task which can consume up to around 200ms of time. If your application needs to run a lot of queries, then it is really worth to consider connection pooling to improve performance. The idea of connection pooling is just an extra abstract layer between the existing JDBC code and the database which keeps the connections open for (re)use until it is inactive / timed-out by the database. Acquiring an already opened connection from the pool can be as fast as one millisecond or two.

There should be no need to change the existing JDBC code (the DAO classes). Only the way to acquire the connection will differ. In general a JNDI datasource is used to acquire a connection from the connection pool. Decent application servers ships with connection pool implementations. Apache Tomcat for example uses Apache Commons DBCP under the hood.

As explained in the DAO factory chapter, if you omit the driver property in the properties file, then the url property will be assumed as JNDI name of the datasource and the DAOFactory will make use of javax.sql.DataSource#getConnection() call to return the connection. With a DataSource you can easily make use of connection pooling to improve performance. So the dao.properties file should look like this:

javabase.jndi.url = java:comp/env/jdbc/javabase

NOTE: in full fledged application servers like Glassfish, the default JNDI root is the java:comp/env, but in Tomcat it is not. Hence the java:comp/env in the JNDI URL. If you're using for example Glassfish, you should remove that part:

javabase.jndi.url = jdbc/javabase

Here the jdbc/javabase part should be the JNDI name of the datasource. You can obtain the associated DAOFactory as follows:

DAOFactory javabase = DAOFactory.getInstance("javabase.jndi");

How to configure the datasource depends on the server used. In case of Tomcat, you normally define the datasource in the context.xml file. You can do that at two different places. If you want to make the one and the same datasource available for all webapplications which runs at the server, then you need to define it in the general tomcat/conf/context.xml of the appserver. Its configuration is explained in Apache Tomcat 6.0 JNDI Datasource HOW-TO. But this requires that you've full control over Tomcat installation. An alternative is to give the webapplication its own context.xml file with the datasource definition in webapp/webcontent/META-INF/context.xml (to be clear, the META-INF is at the same level as the WEB-INF of the webapp). We'll assume the last approach. In case of the MySQL example in this tutorial, just put the following in the file:

 version="1.0" encoding="UTF-8"?>


    
        name="jdbc/javabase" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000" 
        url="jdbc:mysql://localhost:3306/javabase"
        driverClassName="com.mysql.jdbc.Driver"
        username="java" password="d$7hF_r!9Y"
    />

This basically tells Tomcat that it should create a datasource with the JNDI name jdbc/javabase with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings. For more details, read the Apache Tomcat 6.0 JNDI Datasource HOW-TO.

Finally in your web project, edit the file webapp/webcontent/WEB-INF/web.xml to add the following entry to the file:

    
        jdbc/javabase
        javax.sql.DataSource
        Container
    

This basically tells the webapplication that is should use the datasource with the name jdbc/javabase which is managed by the container (thus, Tomcat).

That's all! Just deploy your webapplication with the above changes and run it. Oh, don't forget to place the database JDBC driver in the tomcat/lib.

Once again, no change in all of the DAO code is needed. Even not in the closing code. Heck, closing is still very important. It frees up the pooled connection and makes it available for reuse. Roughly said, the connection pool actually returns a wrapped implementation of the actual connection, where in the close() method is decorated like as the following pseudo code:

    public void close() throws SQLException {
        if (this.connection is still eligible for reuse) {
            do not close this.connection, but just return it to pool for reuse;
        } else {
            actually invoke this.connection.close();
        }
    }

So, there's nothing to worry about in the DAO code. That's the nice thing of properties files and well-written object oriented code.

Back to top

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair.

(C) July 2008, BalusC

Source:http://balusc.blogspot.com/2008/07/dao-tutorial-data-layer.html