Database Interaction with DAO and DTO Design Patterns

DAO means Data Access Object and is an industry design pattern. In this article, I will discuss What is a DAO and how to create a DAO. I will also discuss what is the significance of creating Data Access Objects.

There are tools which can generate DAO code for an application. But one can also write DAO classes manually. Also one more design pattern very closely related to DAO is the DTO (Data Access Object).

A DAO design pattern helps an application to perform various CRUD operations on the database. The DAO classes provide methods for insertion, deletion, updation and finder methods. The basic purpose of creating DAO is the loose coupling and non-repeation of code.

In any application which is going to interact with the database, there is need of performing CRUD operations on the database tables and since the table operations may be done by different classes and hence it becomes cumbersome to repeat the same code in various classes. Moreover, even after repeating the code, it becomes difficult to maintain the datbase interaction code whenever changes are required in the way database interaction is being done.

To overcome the above mentioned problems, the DAO design pattern was invented. The DAO pattern is supposed to have a DAO interface, class and factory corresponding to each table in the table. For example if there is a table name Employees in the database then a interface named EmployeesDAO, a class named EmployeesDAOImpl and a factory class EmployeeDAOFactory is supposed to be in place.

The EmployeesDAO interface should look something like:

package com.company.app.dao;public interface {    public Employee[] findAll() throws SQLException;    public Employee findByPK(EmployeePK) throws SQL Exception;    public Employee[] findbyemployeename(String EmployeeName) throws SQLException;    public boolean insert(Employee employee) throws SQLException;    public boolean update(Employee employee) throws SQLException;   public boolean delete(Employee employee) throws SQLException;}

The DAO Implementation class looks like:

package com.company.aap.doaimpl;public class EmployeeDAOImpl implements EmployeeDAO {    Connection con;    ResultSet rs;    PreparedStatement stmt;    public Employee[] findAll() throws SQLException {        Employee[] employees;        String SQL_QUERY= “Select * from Employee”;        con=ResourceManager.getConnection();        stmt = con.prepareStatement(SQL_QUERY);        rs = stmt.executeQuery();       while(rs.next) {            //get columns and store in array       }        return employees;   }   public Employee findByPK(EmployeePK) throws SQL Exception {       //Implementation code   }    public Employee[] findbyemployeename(String EmployeeName) throws SQLException{       //Implementation code    }    public boolean insert(Employee employee) throws SQLException{        //Implementation code    }    public boolean update(Employee employee) throws SQLException{        //Implementation code    }    public boolean delete(Employee employee) throws SQLException{        //Implementation code     }}

In the above DAO implementation code, the Employee class is the data transfer object which has member variables corresponding to the Employee table in the database. Thus Employee is a POJO in a sense with getters and setters for the member variables.

The DAO Factory class which is EmployeeDAOFactory is used for returning the object of EmployeeDAOImpl. Here is the code for DAO facrory for the EmployeeDAO:

package com.company.app.factory;public class EmployeeDAOFactory {      public static EmployeeDAO create() {           return (new EmployeeDAOImpl());      }}  

In the EmployeeDAOImpl there is a ResourceManager class being used whose purpose is to create database connection and return it back. This class thus acts as a helper class for various other DAO implementation classes.

As I mentioned in the start of this article that there are tools which can generate the DAO, DTO and Factory class code for you. These tools are helpful because they automate the generation of DAO classes. The importance of these tools even becomes great in the initial phase of application development because the database schema keeps of changes frequently and hence manually making the changes in database tables and the DAO code. Here I am listing the tools which can generate the DAO code for the Java applications:

1. FireStrom DAO by Codefutures
2. DaoGen by TitanicLinux.net
3. Dao4J
4. DB Visual Architect

What these tools do is import the tables from the database by asking the connection details and then generate the DAO/DTO classes based on the tables being imported. One more important point that is useful when using these tools is that they keep a check on the primary and foreign keys of the table structure. Moreover, the database datatypes are converted into corresponding datatypes in Java in the DAO/DTO classes.

After the code has been generated by these tools, one can always modify the code to optimize the DAO code.

Always remember that the DAO, DTO, Factory, Loose Coupling (A SOLID principle), Factory design patterns all go along. One can also create a connection pool while using the DAO design pattern. The important point is that the code becomes clean and easy to understand when using the DAO, DTO design patterns.

From http://extreme-java.blogspot.com/2011/05/database-interaction-daodto.html