Monday, July 7, 2008

DAO tutorial - the data layer

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.
 * <p>
 * This class requires a properties file named 'dao.properties' in the classpath with among others
 * the following properties:
 * <pre>
 * name.url *
 * name.driver
 * name.username
 * name.password
 * </pre>
 * 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.
 * <ul>
 * <li>The 'name' must represent the database name in {@link #getInstance(String)}.</li>
 * <li>The 'name.url' must represent either the JDBC URL or JNDI name of the database.</li>
 * <li>The 'name.driver' must represent the full qualified class name of the JDBC driver.</li>
 * <li>The 'name.username' must represent the username of the database login.</li>
 * <li>The 'name.password' must represent the password of the database login.</li>
 * </ul>
 * 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.
 * <p>
 * Here are basic examples of valid properties for a database with the name 'javabase':
 * <pre>
 * 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
 * </pre>
 * <pre>
 * javabase.jndi.url = jdbc/javabase
 * </pre>
 * Here is a basic use example:
 * <pre>
 * DAOFactory javabase = DAOFactory.getInstance("javabase.jdbc");
 * UserDAO userDAO = javabase.getUserDAO();
 * </pre>
 *
 * @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<User> 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<User> list() throws DAOException {
        List<User> 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
 * <code>java.util.Date</code> to <code>java.sql.Date</code> 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:
 * <ol>
 * <li>A MySQL server running at localhost:3306 with a database named 'javabase'.
 * <li>A 'user' table in the 'javabase' database which is created as follows:
 * <pre>
 * 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)
 * )
 * </pre>
 * <li>A MySQL user with the name 'java' and password 'd$7hF_r!9Y' which has sufficient rights on
 * the javabase.user table.
 * <li>A MySQL JDBC Driver JAR file in the classpath.
 * <li>A properties file 'dao.properties' in the classpath with the following entries:
 * <pre>
 * 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
 * </pre>
 * </ol>
 *
 * @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<User> 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 <DAO extends BaseDAO> DAO getDAOImpl(Class<DAO> 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:

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

<Context>
    <Resource
        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"
    />
</Context>

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:


    <resource-ref>
        <res-ref-name>jdbc/javabase</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

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

79 comments:

Unknown said...

Will you please tell me that how could i run this program..When i compile the code without changing anything except the package name it shows cannot find symbol error for all the classes like UserDAO etc.. It'd be helpful if you explain it to me......

Vladimir Kroz said...

Great tutorial, my high respects to BalusC!!
However (IMHO) this example is too theoretical and good for study DAO design patterns but not for design a real-world application. In real development it would produce too much boilerplate code, that is waste of development time and pain in maintenance. This is the reason for existence of numerous products like Hibernate, JPA, Spring DAO etc.
I think that a good example of integration between JPA and web application would be very interesting.

BalusC said...

@rajkumar: your classpath setting/argument might be wrong.

@vladimir: That's why I mentioned about extracting interfaces in 'Further exercises'. If you extract an interface (or abstract class) from DAOFactory and an interface from UserDAO then you could provide implementations by dao.properties. Or keep the DAOFactory as-is and add an enum TYPE {JDBC, JPA, HIBERNATE} and in a switch statement inside getUserDAO() (or getDAOImpl()) return the desired implementation.

emprice26 said...

Very nice explanation. But when you have an object hierarchy, a composition, what is the best way to create the composition using DAOs? For example, say you have a Car represented by one database table and an Engine represented by another table. The composition is a Car has an Engine. So you have a CarDAO and an EngineDAO. When you create the Car with CarDAO.create(), how would you also create the Engine? Do you (1) directly instantiate the EngineDAO in the CarDAO.create() method;

CarDAO {
public Car create(..boolean loadEngine..) {
Car c = loadCarFromDataSource();
if (loadEngine) {
EngineDAO eDAO = new EngineDAO();
c.setEngine( eDAO.getEngineForCar( c ) );
}
}
}

or (2) use a Factory to get a reference to an EngineDAO in the CarDAO.create() method; or (3) first create the Car using the CarDAO.create() method, then use a method on EngineDAO that takes a Car param and returns the associated engine, then assign the Engine to the Car, like:

Car c = CarDAO.create();
Engine e = EngineDAO.getEngineForCar( c );
c.setEngine( e );

In general, which method is perferred? Thanks!

BalusC said...

@emprice26: I would make EngineDAO a property of CarDAO and assign it in the constructor of CarDAO using the associated DAOFactory. There is no need to retrieve/create the same DAO multiple times. Then in the CarDAO methods just do the EngineDAO thing whenever needed.

Snotty said...

Could you please explain, why is "getConntection()" a method of the factory class - couldn't it be a method of utility class, like "close(Connection)"?
And the other thing I cannot seem to understand is why do we need factory class (considering, that the way we're going to access our database isn't going to change in future)? What stops us from making UserDAO's constructor public and simply writing "UserDAO userDAO = new UserDAO();" instead of "UserDAO userDAO = javabase.getUserDAO();"

I'd be very grateful if you could answer these questions.

BalusC said...

@Snotty: the factory class reads the properties file and may decide where to get the connection from. The utility class is just a 'dumb' class providing helper methods.

Using the factory is just a good practice. It makes interfacing of the DAO possible, it could provide different implementations whenever required.

Snotty said...

Thanks! Now it seems to be clear.

Dan said...

Hello,

Very helpfully code, tanx.
for this example can I map User.java for view ?

Regards,
Dan

BalusC said...

Check the follow-up tutorials for JSP/Servlet and JSF.

shiva said...

hi,
i read tomcat with eclipse tutorial,
similary, i like to know how to create and build myfaces with tomahawk..

armen said...

Hello,
Your DAO objects remind me EJB3 architecture. Instead I use Entity beans and session facades of ejb3.

Jaimico said...

Hello BalusC:

Thank you very much for this post, know I understand a little more the DAO architecture.

Could you explain me a little how do I extract interfaces from the DAOFactory and the other DAO classes? or point me to some documentation that could help me understand? :)

Thanks!!!

squirtle23 said...

Hi Balusc,

This was quite a tutorial. I learned so much about the DAO Architecture. In the project that I am working right now, I only have Value Object(DTO) map into the database layer..
For my current need, it suits me since this is Stand Alone SWT GUI Application

Thanks to you..

Unknown said...

Great tutorial BalusC! I did find an issue with the DOATest when using MySQL 5.1.33 and Connector/J 5.1.7. The !Statement.GeneratedKeysNotRequested! exception is thrown when attempting to retrieve the keys following the preparedStatement.executeUpdate. After looking in the forums, it appears that the latest version of the connector requires the parameter PreparedStatement.RETURN_GENERATED_KEYS to be included on the connection.prepareStatement call in DOAUtil. See this post for more details: http://bugs.mysql.com/bug.php?id=41448

Benabdallah Mohammed said...

Realy awesome, can't leave this page without a 'THANK YOU you are a life saver :p'
Thanks, God bless you

Sherali Inamdar said...

Great Tutorial.!! you are simply amazing person. :)

super.corrosive.zinc said...

Hi,

Will you please tell me, why are the arguments in some methods are seen as "Object...values"?? What does it mean?

Thanks

BalusC said...

Those are varargs which are supported since Java 5.0 since 2004.

super.corrosive.zinc said...

thank you! did not know about that!

JavaRunner said...

One of the best tutorials I've come across. So are the JSF related tutorials - the dataTable scroller was particularly useful. BalusC must have a hell of a work ethic!
Well done!

muneer ahmed said...

i could not found UserSessionDAO file


would you tell me where it is?

BalusC said...

Write it yourself. It's straightforward enough if you understand the UserDAO example in this article.

Qussay Najjar said...

First of all a very big thank you, this is one of the best tutorials I've ever seen, and sure i did get lots of information using it.
I was trying your same code, but my database is Oracle Express Edition 10g.
my properties became like this:

javabase.url = jdbc:oracle:thin:@localhost:1521:XE
javabase.driver = oracle.jdbc.driver.OracleDriver
javabase.username = myusername
javabase.password = mypassword

The connection is retrieved correctly, and no exception is happening, but for example I get a record, try to normally print it, and JDeveloper just prints out "Process exited with exit code 0."
and my record is not printed out of course.
am i missing anything in here..
Thanks again..

BalusC said...

Oracle's JDBC driver doesn't support getGeneratedKeys() (or at least didn't support it for a very long time). Check if support is available. If still not, then you need to replace getGeneratedKeys() by an executeQuery("SELECT CURRVAL sequence_id") on the same PreparedStatement as used for INSERT.

Qussay Najjar said...

I know that oracle doesn't support the auto generated keys.
from your code am only using the part of getting the connection using DriverManager, and i've modified my queries to work on with oracle rules.

Qussay Najjar said...

everything is working fine now, it was just the database, i had to reinstall it, and it's perfect.
thanks man, you're really a tough developer..

omasampath said...

Please help,

I'm new to web developing. I have tested the DAO tutorial with mysql setting dao.property and working perpectly.
Then i configured apache dbcp following tutorial and removed dao. property from the classpath. Then exception occured saying
"Properties file 'dao.properties' is missing in classpath." , So how get the connection from dbcp pool in the coding?

what are the changes?

Thanks
Sampath

BalusC said...

This is covered by the last chapter of the article.

Unknown said...

Thanks for the great article.

Currently here it is only one DAO, the "User DAO". But how to add more like ... UserPurchase, UserHistory something like that or normally different DAOs which created in web application w.r.t to Users.

Would we have to create separate DAOFactory for the above two mentioned as well as DAO for the same?

Can you please put more light on this by giving some illustration.

Thanks
-jw

BalusC said...

There's a comment line in DAOFactory class.
"// You can add more DAO getters here."

Unknown said...

ok ... thanks for that.

This mean I can have "UserPurchase, UserHistory" DAO only, not factory as it will will only one.

Am I correct?

Shrinivas said...

im not able to run your tutorial.
it shows a error like "requested resource not found" . im using netbeans.please tell me how should i change my context root path.
and for oracle 10g what changes have to do with this tutorial.

BalusC said...

Consult Netbeans tutorial to learn how to use it.

avanrotciv said...

Brilliant piece of software. I'm taking a lot of notes from your coding style.

Anonymous said...

Great tutorial BalusC!

I've some problem to print it because all sample code table are not broable when page is full. Can you put a PDF link on this page ?

Thanks.

Unknown said...

I am novice in Java World and luckily got this site, first accept my high respect to BaluC who provided great article which is using in real world application my only concern is that i was not able to compile this DAO tutorial, please give me little bit idea how to extract interface and implement it? It would be appreciate, if you can answer this question, I know you have busy schedual but please help me to move ahead

Unknown said...

Hello, I have to say great work, truly a great source of knowledge and experience, I am a bit lost in the UserDAO class, when you use declare the 'public UserDTO find(Long id)' function, it seems to call itself recursively, I mean the find function isn't declared anywhere else except in the DAO class how does it know how to search?, any way hope you still answers this article. Thanks for writing the article.

BalusC said...

No, it calls another method in the same class which does the real job: "private User find(String sql, Object... values)".

If you use an IDE like Eclipse, you would be able to ctrl+click the methodname to land in its method declaration.

Unknown said...

Thats a fast reply, thank you. Oh, I missed the 'private' scope of the 'find' function that you say, that explains it. Thanks a lot, I'll continue to absorb your knowledge hehe...

Unknown said...

Where would you draw the line in your DAO/DTO implementations ?


Simple DTO and DAO (one per table) I get, but what about more advanced DAOs?

For example:

Let's say you have three tables:

Employee (contains employee_id, employee_name, and dept_id)
Department (contains dept_id, dept_name, loc_id)
Location (contains loc_id, location_name)

How deep will your classes go to replicate the data?
What would you do? This?

public class Employee {
private int id;
private String name;
private int deptId; // just the id
// ...
}

Or this?

public class Employee {
private int id;
private String name;
private Department dept;
// ...
}

and so on and so on. Class Department has the same type of problem. Does it hold just the id for location or a variable class Location?

BalusC said...

@Theme: make Department a property of Employee and so on. Make DepartmentDAO a property of EmployeeDAO. E.g.

if (resultSet.next()) {
    employee = new Employee();
    employee.setId(resultSet.getInt("id"));
    employee.setName(resultSet.getString("name"));
    employee.setDepartment(departmentDAO.find(resultSet.getInt("deptId")));
}

Unknown said...

First of all, thank you for quick reply.
And I suppose you will do the same in case of one-to-many relationship.
Let's say you have two tables:

Employee (contains employee_id, employee_name,)
Phones (contains phone_id, phone_number, employee_id)
Then, Employee class could look like:
public class Employee {
private int id;
private String name;
private ArrayList phones;
// ...
}

What about Phone class? Could it look like:
public class Phone{
private int id;
private String number;
private Employee employee;
}

Or not?
And what about DAOs in this case?
Once again, thank you for your answers.

BalusC said...

Depends. Is it interesting to know about the employee when you have a phone? Not always. You could omit it or load it lazily.

I just want to mention, when it goes pretty complex, I warmly recommend to take a look for an ORM framework like JPA or just good ol' Hibernate. It eases mapping and lazy loading, among others.

Unknown said...

Hey hi,its a very good article...i tried placing my dao.properties in my classpath... its not working...can u please tell m the correct way of adding dao.properties in netbeans

bucksboy said...

By far the best written article on this subject that I can find.

I have a general question; to what extent should application code externalise the nature of the data layer implementation?

E.g. the test harness acts as a client application in the article and one of the first lines involves asking for JDBC technology...

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

I see the same thing in the Sun DAO patterns article too.

Should it really care? After all the client application just wants a piece of code that will get it some user entities (in this case).

If doing the data layer were somebody else's job, and I was writing the client then I'd like to care as little as possible about the data implementation side e.g. by writing something similar to..

DAOFactory daoFactory = DAOFactory.getInstance();

UserDAO userDAO = daoFactory.getDAO(USER);

I guess all things are possible, and doing things like this will make the factory / properties file side of things a bit more complex (e.g. by forcing the factory to do a double lookup to save duplicating details).

And if the properties file were shared by more than one application, it might also need to qualify any settings by the application name as well.

E.g. the factory would need to know it has to get an implementation for User entities for the Finance application, which could use a different technology versus the Distribution application say. So the call might then be...

UserDAO userDAO = daoFactory.getDAO(USER,SALES);

I know it can depend on the circumstance, but I'm interested whether anyone would ever consider it worthwhile going to these lengths.

Regards,
Mark.

Viren said...

That's an awesome tutorial. Many thanks for posting such great tutorials. Wish to read a few more from you.

gregrinald said...

Hello BalusC

Your tutorial aboit the DAOFactory is amazing, one of the best i could find on the net!!! great thanks a lot! keep them coming.

But i am struggling with the last issue about pooling connections.

You gava an example how to use it on webpages and some pseudo code. Do you need to first make a normal connection ad the connect via pool? Could you maybe give saome example code about that , that woul greatly round this super tutorial! THANKS!

necipâkif said...

Really, really awesome code. Clear to understand and without EJB mentality, i think, best choice to write dao and dto with your code.

Best regards,

Krishnakant Kadam said...

Really This is great tetorial for jsp servlet (MVC Archit.)

onymakris said...

One quick mark: if you use MS SQL Server as database, the use [user] instead of user in SQL statements, as the word 'user' is a reserved word in SQL Server. Good job BalusC.

Marco Dani said...

Really a good job!
Many thanks!!
My best regards.
Marco.

Marco Dani said...

Hi BalusC,

I've found a little issue on the UserDAO class, hashMD5IfNecessary method: to work properly the line:

return !"^[a-f0-9]{32}$".matches(password) ? DAOUtil.hashMD5(password) : password;

should be changed in

return !password.matches("^[a-f0-9]{32}$") ? DAOUtil.hashMD5(password) : password;

According to java.lang.String.matches(String regex) documentation.

Thanks,
Marco

Anonymous said...

Hello,

I've tried this pattern with success using JDBC and MySQL. Thanks for it. It's really great !!!

Is it possible to use a pool with this pattern in batch mode, i.e.. without anything else that this pattern and JDBC ?

You talk about JNDI. I don't know JNDI.

Thanks.

nfffffff7n said...

Great tutorial BalusC!

I have a question: shouldn't be DAOUtils class package-private?

m said...

Hi BalusC,

First of all thank you very much and congratulations for such great tutorials. Really well-done stuff.

A couple things i've found while going through 'DAO utorial - The Data Layer':
the 'public abstract class DAOFactory' has the method 'public static DAOFactory getInstance(String name)'. In that method, the variable 'DAOFactory instance' is declared and afterwards, depending on the parameters received, it is initialized to a specific object from a class that extends 'DAOFactory'. The classes are:
- DriverManagerDAOFactory
- DataSourceWithLoginDAOFactory
- DataSourceDAOFactory

The problem i've found is that these classes extending 'DAOFactory' are not static and thus a exception is thrown and execution interrupted. So I declared these clases as 'static' and there is no error anymore.

Not sure that it was the real problem because now the application fails later on...

If you got a minute and can confirm whether these classes should be 'static' or not i would really appreciate it.

Thank you and congratulations one more time.

BalusC said...

@m: perhaps you've moved them into the DAOFactory class instead of placing them outside? This way they should then indeed be static. The DAOFactory code in the article is just as-is, in a single DAOFactory class file.

ddd said...

if i wanna use your List()
method but with accepting a list of parameters, something like this:
public List getUser(List userId){
return users;
}
so in this case, i can pass a list of userIds and return all the corresponding users by only calling this method one time. i believe this would be a good idea to avoid calling the method multiple times, hence avoiding database traffic. Could you please show me how to modify the method to get it working based on this. i tried but for some reason it wouldn't work for me..Thanks!

Gary said...

I realize this is an old article, but please PLEASE PLEASE don't use MD5 to hash passwords, especially without a salt. Unsalted MD5 can be cracked for many users within hours on just a single CPU by using a common password list, and even faster with a rainbow table. A good way to hash passwords is by using bcrypt with a random salt for each password.

nvs said...

Thank you very much for posting this article.

It was very very helpful to me.

SaiRaghava K said...

I like your blog. Great post.

Arstrea said...

Great tutorial, but i still can NOT make a clear difference between three layer - data, business and presentation, especially business layer is unclear. Can you explain with a few words? Thanks!!!

venkat said...

Awesome.Thanks bro.

IMP said...

Thanks for such quality article.
:D keep up the good thing.

aman said...

Hats off to the author. Really Great tutorial.........

WAdrienW said...

Hi, thanks a lot for your nice post.

Can you just explain more why you don't need to close your resultset, statement or connection in your userDAO. DAOFactory just get the connection with getConnection but no closeConnection ?

Thx

asdbsadbsa said...

Hello BalusC thanks for the tutorial. I have a question, how can i insert and delete user from a web page JSP? So how i can talk with DAO? There's some example?

Yogesh said...

Thanks a lot BalusC for this simple and vividly explained article.

Unknown said...

This works fine for simple applications, but if you have to do multiple operations in a transaction and rollback, you would never create your database connections in a DAO ever.

Mark Abraham said...

BalusC, I often refer to your solutions, but haven't thanked you personally. Your work is SO appreciated.
MarkA

Unknown said...

How should I change this code If I need to use transactions and operate multiple DAOs?

;;; said...

hi sir this excellent post.what is my need is how to validate the date?

;;; said...

sry sry because we put the date as 0 inside the date constructor it takes the previous month,that's why i'm asking
thank you..

;;; said...

hi sir this excellent post.what is my need is how to validate the date?

andi said...

Very useful ,, let alone I'm still a cloud

tscissors said...

thank you very much for this example!

Anupam said...

best blog i have seen about this topic. my respect to you.

Izhari Ishak Aksa said...

Thanks for excellent tutorial sir :)

Unknown said...

First of all, thank you very much for your endeavor. I think that that tutorial has helped several generations of java programmers

I have a question about using your code in the context of Tomcat (version 9.0) and JNDI. While testing the code I couldn't make it work by simply executing main() in the DAOTest and getting the instance from JNDI. No matter what I do, I keep getting "NoInitialContextException: Need to specify class name in environment or system property, or as an applet parameter, or in an application resource file: java.naming.factory.initial". DataSource lookup() just returns null.

However, if I call main() in the DAOTest from the servlet (using init()) everything works like a charm. What is the reason?