Sandeep is a ITA at Tata Consultancy Services. He also is the author of java blog. Sandeep has posted 24 posts at DZone. You can read more from them at their website. View Full User Profile

Database Interaction with DAO and DTO Design Patterns

  • submit to reddit

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:


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:


public class EmployeeDAOImpl implements EmployeeDAO {

Connection con;
ResultSet rs;
PreparedStatement stmt;

public Employee[] findAll() throws SQLException {

Employee[] employees;
String SQL_QUERY= “Select * from Employee”;

stmt = con.prepareStatement(SQL_QUERY);
rs = stmt.executeQuery();

while( {
//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:


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
  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.


Published at DZone with permission of its author, Sandeep Bhandari.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)


Frederic Cuillandre replied on Fri, 2011/05/13 - 2:25am

Your example is a bit out of date. Now we have generics to reduce code:


Slim Ouertani replied on Fri, 2011/05/13 - 3:14am

Thanks even if DTO is dead :)

Mladen Girazovski replied on Fri, 2011/05/13 - 3:22am in response to: Slim Ouertani

Others would argue that DAO is dead (since JPA), but DTO only appears to be in the title of the article, and a few generic references in the text, next to some other pattern names that are not described at all.

Sam Lewis replied on Fri, 2011/05/13 - 6:58am

A few minor issues:

1) DAO Interfaces shouldnt throw SQLException, the point of the DAO is that it abstracts you away from the persistance mechanism.

2) That is possibly the shoddiest JDBC implementation have ever seen because it is not thread safe and it doesnt close database resources.

3) You should probobally spring JDBC or similar to automatically close your jdbc resource and have less boiler plate code.

4) Most people do dependency injection these days rather that a 2004 style DAO Factory


Alessandro Santini replied on Fri, 2011/05/13 - 2:08pm

Sorry Sandeep, articles like this have the only effect of moving more interesting posts away from the main page: buggy code (connection leaks, non re-entrant code), bad design (SQLException exposed, no use of generics, DAOs exposing DTOs (!!!)), and superficial explanation. I am sure you can do much better than this.

Zheng Dan replied on Fri, 2011/05/13 - 10:55pm

It seem to take me back to the Java version 1.4 with less O-R Mapping components,but today we can concentrate on our bussiness with even no SQL Snippet

Carlos Hoces replied on Sat, 2011/05/14 - 6:26am

I really do not want to be rude, but I may call your attention to a perspective you may have been missing along your series of recent posts:

These pages are a well known reference for both professionals and naive Java developers.

Having said that, the quality expected from article contents and code snippets has to be the best possible one. Otherwise, professionals would get angry, and stop loosing their time with nonsenses, and naives would get totally confused at best, and completely mislead-ed at worst.

My opinion is you are doing your “best” to lower Javalobby quality to a useless reference level for professionals, and doing a serious damage to the language itself, and to newcomers willing to raise their knowledge, or simply grasp “what is this Java about”.

Please, do care a "bit" more the quality for all your published contents from now on!.

Sathya Sayee replied on Mon, 2011/05/16 - 11:59am

Hi Sandeep,
Please do us and in turn you a favor. Either stop posting articles to dzone/javalobby or get to know stuff before posting an article.
Posts like this seem to get users interest down wtih Java / Javalobby articles in general.

Carla Brian replied on Wed, 2012/04/25 - 12:27pm

J2EE developers use the Data Access Object (DAO) design pattern to separate low-level data access logic from high-level business logic. - Arthur van der Vant

Jammer Man replied on Tue, 2012/05/22 - 9:24am

Wow ... absolutely clueless.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.