the DAO-pattern

Berlin (Alexanderplatz), by Christoph Burmeister (own photo)

Berlin (Alexanderplatz), by Christoph Burmeister (own photo)

Just to remember and understand clearly the meaning and usage of Data Access Objects (DAOs). Let’s assume we have a database called „geobase“ for the inventory of rivers and mountains.
It has the following structure:

CREATE TABLE IF NOT EXISTS `mountains` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `rivers` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So accessing it with Java via JDBC is not a problem, but regarding the principle of „Write once, deploy everywhere“ we have to make sure that for example switching the database-management-system is done with minimal energy and workload, maybe by changing an external property or modifying the spring-configuration. This can be done by using abstraction of the code from real-implementation or environment. The client (our Java-aplication) shall only use so-called DAOs for CRUD-operations on the persistence-layer. The real implementation will be hidden from the client.

So first what we need is an abstract class for a factory which has some public members for the later choosing of the database-system. Furthermore we need abstract method-stubs which will be used in database-specific extensions of the abstract factory.

package org.jtaddeus.daotest;

import org.jtaddeus.daotest.daointerfaces.MountainDAO;
import org.jtaddeus.daotest.daointerfaces.RiverDAO;
import org.jtaddeus.daotest.derby.DerbyDAOFactory;
import org.jtaddeus.daotest.mysql.MysqlDAOFactory;

public abstract class DAOFactory {

	/** Static member for mysql-factory. */
	public static final int MYSQL = 0;	
	/** Static member for derby-factory. */
	public static final int DERBY = 1;

	/** Abstract method for the RiverDAO. */
	public abstract RiverDAO getRiverDAO();
	/** Abstract method for the MountainDAO. */
	public abstract MountainDAO getMountainDAO();

	/**
	 * Factory-method
	 * 
	 * @param database
	 *            the database to choose
	 * @return a matching factory
	 */
	public static DAOFactory getDAOFactory(int database) {
		switch (database) {
		case MYSQL:
			return new MysqlDAOFactory();
		case DERBY:
			 return new DerbyDAOFactory();
		default:
			return null;
		}
	}
}

I will only describe the MySql-Extension of the abstract factory, because the Derby is similiar.

package org.jtaddeus.daotest.mysql;

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

import org.apache.log4j.Logger;
import org.jtaddeus.daotest.DAOFactory;
import org.jtaddeus.daotest.daointerfaces.MountainDAO;
import org.jtaddeus.daotest.daointerfaces.RiverDAO;

/**
 * Extension of the abstract DAO-Factory-class for the MYSQL-Database.
 */
public class MysqlDAOFactory extends DAOFactory {

	/** The logger. */
	private static final Logger logger = Logger
			.getLogger(MysqlDAOFactory.class);

	/** The driver-class. */
	public static final String DRIVER = "com.mysql.jdbc.Driver";
	/** The url to database. */
	public static final String DBURL = "jdbc:mysql://localhost:3306/geobase";
	/** The username for database-operations. */
	public static final String USER = "christoph";
	/** The password for database-operations. */
	public static final String PASS = "12345678";

	/**
	 * Method to create a Connection on the mysql-database.
	 * 
	 * @return the Connection.
	 */
	public static Connection createConnection() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(DBURL, USER, PASS);
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} catch (ClassNotFoundException e) {
			logger.error(e.getMessage());
		}
		return conn;
	}

	@Override
	public MountainDAO getMountainDAO() {
		return new MysqlMountainDAO();
	}

	@Override
	public RiverDAO getRiverDAO() {
		return new MysqlRiverDAO();
	}
}

The real implementation of the data-access will be done in the database-specific DAOs which implement the generic DAO-interfaces. These interfaces describe all the methods that has to be implemented by the database-specific DAOs. I will only describe the RiverDAO-interface and the belonging MysqlRiverDAO-class:

package org.jtaddeus.daotest.daointerfaces;

import org.jtaddeus.daotest.River;

/**
 * DAO-Interface for the different RiverDAO-implementations. Defines the
 * CRUD-operations.
 */
public interface RiverDAO {
	/** Creates a river and returns the id. */
	public int create(River river);

	/** Receives a river by given id. */
	public River read(int id);

	/** Updates an existing river. */
	public boolean update(River river);

	/** Deletes a river by id. */
	public boolean delete(int id);
}

The following MysqlRiverDAO is really plain and simple JDBC. Not nice, because ORM (object-relational-mapping) frameworks will do the job much better, easier and in less code with better configuration. Actually this is not important for the developer who uses the DAO-interfaces because he will only see the interface. The implementation of the database-access in the database-specific classes is hidden and can be changed by another developer. So theses parts are decoupled.

package org.jtaddeus.daotest.mysql;

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

import org.apache.log4j.Logger;
import org.jtaddeus.daotest.River;
import org.jtaddeus.daotest.daointerfaces.RiverDAO;

/**
 * Implementation of the RiverDAO-interface for the MySQL-database.
 */
public class MysqlRiverDAO implements RiverDAO {
	/** The logger. */
	private static final Logger logger = Logger.getLogger(MysqlRiverDAO.class);
	/** The query for creation. */
	private static final String CREATE_QUERY = "INSERT INTO rivers (name, country) VALUES (?,?)";
	/** The query for read. */
	private static final String READ_QUERY = "SELECT id, name, country FROM rivers WHERE id = ?";
	/** The query for update. */
	private static final String UPDATE_QUERY = "UPDATE rivers SET name=? , country=? WHERE id = ?";
	/** The query for delete. */
	private static final String DELETE_QUERY = "DELETE FROM rivers WHERE id = ?";

	public int create(River river) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet result = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			preparedStatement = conn.prepareStatement(CREATE_QUERY,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, river.getName());
			preparedStatement.setString(2, river.getCountry());
			preparedStatement.execute();
			result = preparedStatement.getGeneratedKeys();

			if (result.next() && result != null) {
				return result.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			try {
				result.close();
			} catch (Exception rse) {
				logger.error(rse.getMessage());
			}
			try {
				preparedStatement.close();
			} catch (Exception sse) {
				logger.error(sse.getMessage());
			}
			try {
				conn.close();
			} catch (Exception cse) {
				logger.error(cse.getMessage());
			}
		}

		return -1;
	}

	public River read(int id) {
		River river = null;
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet result = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			preparedStatement = conn.prepareStatement(READ_QUERY);
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			result = preparedStatement.getResultSet();

			if (result.next() && result != null) {
				river = new River();
				river.setId(result.getInt(1));
				river.setName(result.getString(2));
				river.setCountry(result.getString(3));
			} else {
				// TODO
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			try {
				result.close();
			} catch (Exception rse) {
				logger.error(rse.getMessage());
			}
			try {
				preparedStatement.close();
			} catch (Exception sse) {
				logger.error(sse.getMessage());
			}
			try {
				conn.close();
			} catch (Exception cse) {
				logger.error(cse.getMessage());
			}
		}

		return river;
	}

	public boolean update(River river) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			preparedStatement = conn.prepareStatement(UPDATE_QUERY);
			preparedStatement.setString(1, river.getName());
			preparedStatement.setString(2, river.getCountry());
			preparedStatement.setInt(3, river.getId());
			preparedStatement.execute();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			try {
				preparedStatement.close();
			} catch (Exception sse) {
				logger.error(sse.getMessage());
			}
			try {
				conn.close();
			} catch (Exception cse) {
				logger.error(cse.getMessage());
			}
		}
		return false;
	}

	public boolean delete(int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = MysqlDAOFactory.createConnection();
			preparedStatement = conn.prepareStatement(DELETE_QUERY);
			preparedStatement.setInt(1, id);
			preparedStatement.execute();
			return true;
		} catch (SQLException e) {
			logger.error(e.getMessage());
		} finally {
			try {
				preparedStatement.close();
			} catch (Exception sse) {
				logger.error(sse.getMessage());
			}
			try {
				conn.close();
			} catch (Exception cse) {
				logger.error(cse.getMessage());
			}
		}
		return false;
	}
}

Last but not least the POJOs that handle the data-transfer-objects, which holds the data between the components. These are simple beans with no logic.

package org.jtaddeus.daotest;

/**
 * POJO for the representation of a river.
 */
public class River {
	/** The id for the record. */
	private int id;
	/** The name of the river. */
	private String name;
	/** The country of the river. */
	private String country;

	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}

	/**
	 * @param id
	 *            the id to set
	 */
	public void setId(int id) {
		this.id = id;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name
	 *            the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the country
	 */
	public String getCountry() {
		return country;
	}

	/**
	 * @param country
	 *            the country to set
	 */
	public void setCountry(String country) {
		this.country = country;
	}
}

Finally a little Start-class which shall demonstrate the usage of the DAOs. Here we see again that we do not need to know how or where we store the data.

package org.jtaddeus.daotest;

import org.jtaddeus.daotest.daointerfaces.RiverDAO;

public class Start {

	public static void main(String[] args) {
		DAOFactory mysqlFactory = DAOFactory.getDAOFactory(DAOFactory.MYSQL);
		RiverDAO riverDAO = mysqlFactory.getRiverDAO();
		
		
		// CREATE
		River river = new River();
		river.setName("moldau");
		river.setCountry("germany");
		
		int id = riverDAO.create(river);
		
		// READ
		River riverFromDB1 = riverDAO.read(id);
		System.out.println("after create: " + riverFromDB1.getName() + ":" + riverFromDB1.getCountry());
				
		// UPDATE
		riverFromDB1.setCountry("austria");
		riverDAO.update(riverFromDB1);
		
		// READ
		River riverFromDB2 = riverDAO.read(id);
		System.out.println("after update: " + riverFromDB2.getName() + ":" + riverFromDB2.getCountry());
		
		// DELETE
		riverDAO.delete(id);
	}
}

works like a charme 🙂

The eclipse-maven-project looks like this:

eclipse-project for the daotest-project

eclipse-project for the daotest-project