Mediumjee-integration15 min read

DAO (Data Access Object) Pattern

1. What is the DAO Pattern?

Definition

The DAO (Data Access Object) pattern is a JEE Integration Tier Pattern. A DAO class is a Java class that contains only persistence logic (CRUD operations on the database) using the support of Data Access technologies like JDBC, Hibernate, Spring JDBC, Spring Data JPA, etc.

One line: Separate the data access logic from the business logic into dedicated DAO classes, one per database table.

Oracle, Hibernate, Spring JDBC, Spring JPA/Spring Data JPA, and others are called Data Storage technologies because they store and maintain data persistently. They are also called Data Access technologies because they let you access and manipulate the data.

JEE Pattern Categories

JEE patterns are given by Sun Microsystems addressing problems of Layered Application development. The JEE pattern catalog has 3 categories:

Tier	Patterns
Presentation Tier	Intercepting Filter, Front Controller, View Helper, Composite View
Business Tier	Business Delegate, Service Locator, Session Facade, Message Facade
Integration Tier	DAO (Data Access Object)

2. Java Bean Types: VO, DTO, BO

Before understanding DAO, we need to know the three types of Java Beans used in layered applications:

a) VO Class (Value Object)

The Java bean whose object holds either input values or output values is called VO class. Generally this class maintains all its properties as String type irrespective of the kind of data they hold.

// StudentVO.java
public class StudentVO {
    private String sno;
    private String sname;
    private String m1, m2, m3;
    // setters and getters
}

b) DTO Class (Data Transfer Object)

DTO

The Java bean whose object holds shippable/transferable data either within the project or between the projects is called DTO class. It implements Serializable for network transfer.

// CardDetailsDto.java
public class CardDetailsDto implements Serializable {
    private long cardNo;
    private String gateway;
    private Date expiryDate;
    private int cvc;
    // setters and getters
}
// Case: Flipkart sends credit/debit card details to PayPal (payment broker)
// in the form of DTO class object. PayPal sends back transaction status
// in the form of another DTO class object.

c) BO Class (Business Object / Model / Domain / Entity / Persistence class)

The Java bean whose object holds either persistable data (to be saved) or persistent data (collected from DB table records) is called BO class / Entity class / Domain class / Model class. Generally it will be taken as 1 per DB table basis.

// EmployeeBO.java - maps to Employee DB table
//
// DB table in Oracle:
// Employee (DB table)
//   |--empId (N)
//   |--ename (vc2)
//   |--eaddr (vc2)
//   |--salary (F)

public class EmployeeBO {
    private int eno;
    private String ename;
    private String eaddr;
    private float esalary;
    // setters and getters
}

Important

Model class = Entity class = BO class = Domain class = Persistence class. These are all the same thing. In modern style, the industry uses a single Java bean class (Model class) in all places instead of maintaining separate VO, DTO, and BO classes. The model class itself becomes DTO class if the project is sending data to another project over the network.

3. Layered Architecture

Legacy Style (Standalone Layered App): Client App -----> Controller class -----> Service class -----> DAO class -----> DB s/w (presentation (monitoring logic) (b.logic) (persistence logic) (data) logic) VO class obj DTO class obj BO class obj SQL queries Modern Style (reduces number of classes): Client App -----> Controller -----> Service class -----> DAO class -----> DB s/w (presentation (monitoring) (b.logic) (persistence logic) (data) logic) Model class obj Model class obj Model class obj SQL queries Web-based Layered Application: html/jsp file -----> Controller Servlet -----> Service class -----> DAO class -----> DB s/w (presentation (Monitoring logic) (B.logic) (persistence logic) (Data) logic) Model class obj Model class obj Model class obj SQL queries

4. DAO Pattern Rules

DAO class separates the persistence logic from other logics of the App and makes that logic as flexible/modifiable.
If total DB tables of the Project is 1000 then we take 1 DAO class per DB table.
If total DB tables of Project is 5 then we take 5 DAO classes per 5 related DB tables.
DAO class should not have any other logics except persistence logic. Even Java code-level persistence logic also should not be there.
Instead of writing logic of establishing the connection with DB s/w in multiple DAO classes, it is recommended to write it separately in a ConnectionFactory class and use it in multiple DAO classes.

Advantages of DAO Pattern

It makes the persistence logic as flexible logic to modify by separating that logic from other logics.
The modifications done in DAO class persistence logic does not affect other layer logics.
Since we implement DAO Pattern having DAO interface and DAO impl classes, there is possibility of writing same Persistence logic using different technologies in different impl classes.

DAO Interface: +-------------------+ | IEmployeeDAO | | (persistence | | method decl.) | +-------------------+ / | \ DAO impl class1 DAO impl class2 DAO impl class3 (JDBC persistence (Hibernate (Spring JDBC logic) for oracle persistence persistence logic) logic) for mysql for postgresql

DAO Class Structure: Two Parts

Part	Description	Example
Query Part	All SQL Queries declared at the top of the DAO class as String constants (`private static final`)	`private static final String EMPLOYEE_INSERT_QUERY = "INSERT INTO EMPLOYEE VALUES(?,?,?,?)";`
Code Part	The persistence logic for different CRUD operations using JDBC/Hibernate/etc. The methods that house persistence logic developed in different technologies.	`public int insertEmployee(Employee employee) { // jdbc/hibernate/spring jdbc code }`

Exception Propagation

The methods of DAO class should propagate the raised exception to service class methods (caller methods) to support exception propagation. For this we need to use throws Exception in method signatures. Except in Client App, in all remaining layers we need to declare the exception to be thrown using "throws" keyword. In that process we can go for Exception rethrowing when needed. Due to this, the exception thrown in DAO class propagates to Service class to Controller to Client App and we catch and handle the exception in the Client App using try-catch block to display the exception-related messages as required for the end users.

5. DAO Pattern Components

Component	Role
a) DAO Interface	Declares the persistence methods (CRUD operations)
b) Entity/BO classes	Java beans representing DB table records
c) DAOFactory	Creates the right DAO impl class based on the data we pass
d) ConnectionFactory	Handles code for connection establishment and connection closing with DB s/w. Reads jdbc.properties for DB connection details. Can work with either direct JDBC connection or pooled JDBC connection.

DAO Pattern Architecture (Flow of Execution)

Client App ---(a)---> Controller ---(b)---> Service class ---(c)---> (presentation (monitoring (b.logic) logic) logic) ---(d)---> DAO Impl class ---(e)---> ConnectionFactory ---(f)---> DB s/w (persistence logic) (logic to establish (persistence store) connection with DB s/w and logic to close the ^ connection with DB s/w) | DAOFactory jdbc.properties (logic to create right (url, driver, user, pwd) DAO impl class) Connection: Making all requests using same jdbc connection for DB operations is bad practice. Each request using its own jdbc con object for DB operations is good practice. request1 ---> insert query ---> DB s/w (blocks all requests) request2 ---> update query ---> DB s/w using shared connection: request3 ---> validate ---> DB s/w object(s). the third request update and it ultimately validates request1 insert query, request2 update query execution. request1 ---> insert query ---> DB s/w (now every request is request2 ---> update query ---> DB s/w getting its own con object. request3 ---> validate ---> DB s/w So any pending request's rollback/commit does not effect other request's related persistence operations.)

6. Full-Length Industry Project: Customer Registration System

Business Scenario

National Bank of India needs a customer registration system. When a new customer visits the branch, the staff enters their details (name, email, city, phone) into the system. The data is saved to a MySQL database. The system follows the DAO pattern with proper layering: Client App -> Service -> DAO -> DB, with a ConnectionFactory for JDBC connection management and a DAOFactory for creating the correct DAO implementation.

High-Level Design (HLD)

Low-Level Design (LLD)

+-------------------------------------------------------+ | Customer (BO/Entity/Model class) | +-------------------------------------------------------+ | - custId: int | | - custName: String | | - email: String | | - city: String | | - phone: String | +-------------------------------------------------------+ +-------------------------------------------------------+ | ICustomerDAO (DAO Interface) | +-------------------------------------------------------+ | + register(Customer): int throws Exception | | + findById(int): Customer throws Exception | | + findAll(): List<Customer> throws Exception | | + update(Customer): int throws Exception | | + delete(int): int throws Exception | +-------------------------------------------------------+ ^ | +-------------------------------------------------------+ | CustomerMySQLDAOImpl (DAO Impl) | +-------------------------------------------------------+ | - INSERT_QUERY: String [static final] | | - SELECT_BY_ID: String [static final] | | - SELECT_ALL: String [static final] | | - UPDATE_QUERY: String [static final] | | - DELETE_QUERY: String [static final] | +-------------------------------------------------------+ | + register(Customer): int | | + findById(int): Customer | | + findAll(): List<Customer> | | + update(Customer): int | | + delete(int): int | +-------------------------------------------------------+ +-------------------------------------------------------+ | ConnectionFactory | +-------------------------------------------------------+ | - props: Properties [static] | +-------------------------------------------------------+ | + getConnection(): Connection [static] | | + closeConnection(conn): void [static] | +-------------------------------------------------------+ +-------------------------------------------------------+ | DAOFactory | +-------------------------------------------------------+ | + getCustomerDAO(String): ICustomerDAO [static] | +-------------------------------------------------------+

Project Structure

nbi-customer-registration/ ├── src/ │ └── main/ │ ├── java/com/nbi/customer/ │ │ ├── model/ │ │ │ └── Customer.java (BO/Entity/Model class) │ │ ├── dao/ │ │ │ ├── ICustomerDAO.java (DAO interface) │ │ │ └── CustomerMySQLDAOImpl.java (JDBC implementation) │ │ ├── factory/ │ │ │ ├── ConnectionFactory.java (JDBC connection manager) │ │ │ └── DAOFactory.java (creates DAO impl) │ │ ├── service/ │ │ │ └── CustomerRegService.java (business logic) │ │ └── Application.java (client app) │ └── resources/ │ └── jdbc.properties (DB connection details) ├── src/ │ └── test/java/com/nbi/customer/ │ └── dao/ │ └── CustomerDAOTest.java └── pom.xml

jdbc.properties

# Database connection configuration
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/national_bank_db
jdbc.user=nbi_admin
jdbc.password=nbi_secure_pwd

Customer.java (BO/Entity/Model class)

package com.nbi.customer.model;

public class Customer {
    private int custId;
    private String custName;
    private String email;
    private String city;
    private String phone;

    public Customer() {}

    public Customer(int custId, String custName, String email,
                    String city, String phone) {
        this.custId = custId;
        this.custName = custName;
        this.email = email;
        this.city = city;
        this.phone = phone;
    }

    // Getters and setters
    public int getCustId() { return custId; }
    public void setCustId(int custId) { this.custId = custId; }
    public String getCustName() { return custName; }
    public void setCustName(String custName) { this.custName = custName; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getCity() { return city; }
    public void setCity(String city) { this.city = city; }
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }

    @Override
    public String toString() {
        return "Customer{" + custId + ", " + custName + ", " + email
            + ", " + city + ", " + phone + "}";
    }
}

ConnectionFactory.java

package com.nbi.customer.factory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnectionFactory {
    private static Properties props = new Properties();

    static {
        try (InputStream is = ConnectionFactory.class.getClassLoader()
                .getResourceAsStream("jdbc.properties")) {
            props.load(is);
            Class.forName(props.getProperty("jdbc.driver"));
        } catch (Exception e) {
            throw new RuntimeException("Failed to load jdbc.properties", e);
        }
    }

    public static Connection getConnection() throws Exception {
        return DriverManager.getConnection(
            props.getProperty("jdbc.url"),
            props.getProperty("jdbc.user"),
            props.getProperty("jdbc.password")
        );
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                System.err.println("Error closing connection: " + e.getMessage());
            }
        }
    }
}

ICustomerDAO.java (DAO Interface)

package com.nbi.customer.dao;

import com.nbi.customer.model.Customer;
import java.util.List;

public interface ICustomerDAO {
    int register(Customer customer) throws Exception;
    Customer findById(int custId) throws Exception;
    List<Customer> findAll() throws Exception;
    int update(Customer customer) throws Exception;
    int delete(int custId) throws Exception;
}

CustomerMySQLDAOImpl.java (JDBC Implementation)

package com.nbi.customer.dao;

import com.nbi.customer.factory.ConnectionFactory;
import com.nbi.customer.model.Customer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CustomerMySQLDAOImpl implements ICustomerDAO {

    // Query Part: all SQL queries as static final constants
    private static final String INSERT_CUSTOMER =
        "INSERT INTO customers (cust_id, cust_name, email, city, phone) VALUES (?, ?, ?, ?, ?)";
    private static final String SELECT_BY_ID =
        "SELECT * FROM customers WHERE cust_id = ?";
    private static final String SELECT_ALL =
        "SELECT * FROM customers ORDER BY cust_id";
    private static final String UPDATE_CUSTOMER =
        "UPDATE customers SET cust_name=?, email=?, city=?, phone=? WHERE cust_id=?";
    private static final String DELETE_CUSTOMER =
        "DELETE FROM customers WHERE cust_id=?";

    // Code Part: CRUD operations

    @Override
    public int register(Customer customer) throws Exception {
        Connection conn = null;
        try {
            conn = ConnectionFactory.getConnection();
            PreparedStatement ps = conn.prepareStatement(INSERT_CUSTOMER);
            ps.setInt(1, customer.getCustId());
            ps.setString(2, customer.getCustName());
            ps.setString(3, customer.getEmail());
            ps.setString(4, customer.getCity());
            ps.setString(5, customer.getPhone());
            int count = ps.executeUpdate();
            return count;
        } finally {
            ConnectionFactory.closeConnection(conn);
        }
    }

    @Override
    public Customer findById(int custId) throws Exception {
        Connection conn = null;
        try {
            conn = ConnectionFactory.getConnection();
            PreparedStatement ps = conn.prepareStatement(SELECT_BY_ID);
            ps.setInt(1, custId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return mapResultSetToCustomer(rs);
            }
            return null;
        } finally {
            ConnectionFactory.closeConnection(conn);
        }
    }

    @Override
    public List<Customer> findAll() throws Exception {
        Connection conn = null;
        List<Customer> customers = new ArrayList<>();
        try {
            conn = ConnectionFactory.getConnection();
            PreparedStatement ps = conn.prepareStatement(SELECT_ALL);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                customers.add(mapResultSetToCustomer(rs));
            }
            return customers;
        } finally {
            ConnectionFactory.closeConnection(conn);
        }
    }

    @Override
    public int update(Customer customer) throws Exception {
        Connection conn = null;
        try {
            conn = ConnectionFactory.getConnection();
            PreparedStatement ps = conn.prepareStatement(UPDATE_CUSTOMER);
            ps.setString(1, customer.getCustName());
            ps.setString(2, customer.getEmail());
            ps.setString(3, customer.getCity());
            ps.setString(4, customer.getPhone());
            ps.setInt(5, customer.getCustId());
            return ps.executeUpdate();
        } finally {
            ConnectionFactory.closeConnection(conn);
        }
    }

    @Override
    public int delete(int custId) throws Exception {
        Connection conn = null;
        try {
            conn = ConnectionFactory.getConnection();
            PreparedStatement ps = conn.prepareStatement(DELETE_CUSTOMER);
            ps.setInt(1, custId);
            return ps.executeUpdate();
        } finally {
            ConnectionFactory.closeConnection(conn);
        }
    }

    private Customer mapResultSetToCustomer(ResultSet rs) throws SQLException {
        Customer c = new Customer();
        c.setCustId(rs.getInt("cust_id"));
        c.setCustName(rs.getString("cust_name"));
        c.setEmail(rs.getString("email"));
        c.setCity(rs.getString("city"));
        c.setPhone(rs.getString("phone"));
        return c;
    }
}

DAOFactory.java

package com.nbi.customer.factory;

import com.nbi.customer.dao.ICustomerDAO;
import com.nbi.customer.dao.CustomerMySQLDAOImpl;

public class DAOFactory {

    public static ICustomerDAO getCustomerDAO(String dbType) {
        if ("mysql".equalsIgnoreCase(dbType)) {
            return new CustomerMySQLDAOImpl();
        }
        // Future: "oracle" -> CustomerOracleDAOImpl
        //         "postgres" -> CustomerPostgresDAOImpl
        throw new IllegalArgumentException("Unsupported DB type: " + dbType);
    }
}

CustomerRegService.java (Business Logic)

package com.nbi.customer.service;

import com.nbi.customer.dao.ICustomerDAO;
import com.nbi.customer.factory.DAOFactory;
import com.nbi.customer.model.Customer;
import java.util.List;

public class CustomerRegService {
    private final ICustomerDAO customerDAO;

    public CustomerRegService() {
        this.customerDAO = DAOFactory.getCustomerDAO("mysql");
    }

    public String registerCustomer(Customer customer) throws Exception {
        // Business validation
        if (customer.getCustName() == null || customer.getCustName().isEmpty()) {
            return "Customer name is required";
        }
        if (customer.getEmail() == null || !customer.getEmail().contains("@")) {
            return "Valid email is required";
        }

        int count = customerDAO.register(customer);
        if (count > 0) {
            return "Customer registered successfully. ID: " + customer.getCustId();
        }
        return "Customer registration failed";
    }

    public Customer getCustomer(int custId) throws Exception {
        Customer customer = customerDAO.findById(custId);
        if (customer == null) {
            throw new Exception("Customer not found with ID: " + custId);
        }
        return customer;
    }

    public List<Customer> getAllCustomers() throws Exception {
        return customerDAO.findAll();
    }
}

Application.java (Client App)

package com.nbi.customer;

import com.nbi.customer.model.Customer;
import com.nbi.customer.service.CustomerRegService;
import java.util.List;

public class Application {
    public static void main(String[] args) {
        CustomerRegService service = new CustomerRegService();

        try {
            // Scenario 1: Register a new customer
            System.out.println("=== Scenario 1: Register Customer ===");
            Customer rajesh = new Customer(1001, "Rajesh Kumar",
                "rajesh@nationalbankofIndia.com", "Hyderabad", "+91-9876543210");
            String result = service.registerCustomer(rajesh);
            System.out.println(result);

            // Scenario 2: Register another customer
            System.out.println("\n=== Scenario 2: Register Another ===");
            Customer priya = new Customer(1002, "Priya Singh",
                "priya@nationalbankofIndia.com", "Mumbai", "+91-9876543211");
            System.out.println(service.registerCustomer(priya));

            // Scenario 3: Find customer by ID
            System.out.println("\n=== Scenario 3: Find by ID ===");
            Customer found = service.getCustomer(1001);
            System.out.println("Found: " + found);

            // Scenario 4: List all customers
            System.out.println("\n=== Scenario 4: List All ===");
            List<Customer> all = service.getAllCustomers();
            all.forEach(c -> System.out.println("  " + c));

            // Scenario 5: Validation failure
            System.out.println("\n=== Scenario 5: Validation Error ===");
            Customer invalid = new Customer(1003, "", "bad-email", "Delhi", "123");
            System.out.println(service.registerCustomer(invalid));

        } catch (Exception e) {
            // Exception propagated from DAO -> Service -> here
            System.out.println("ERROR: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Expected Output

=== Scenario 1: Register Customer === Customer registered successfully. ID: 1001 === Scenario 2: Register Another === Customer registered successfully. ID: 1002 === Scenario 3: Find by ID === Found: Customer{1001, Rajesh Kumar, rajesh@nationalbankofIndia.com, Hyderabad, +91-9876543210} === Scenario 4: List All === Customer{1001, Rajesh Kumar, rajesh@nationalbankofIndia.com, Hyderabad, +91-9876543210} Customer{1002, Priya Singh, priya@nationalbankofIndia.com, Mumbai, +91-9876543211} === Scenario 5: Validation Error === Customer name is required

Key observations from the project

DAO class has ONLY persistence logic. No validation, no business rules, no presentation. Pure CRUD with SQL queries.
SQL queries are static final constants. Declared at the top (Query Part), used in methods (Code Part). Easy to find, easy to modify.
ConnectionFactory separates connection management. DAO methods call getConnection() and closeConnection(). If we switch from direct JDBC to connection pooling, only ConnectionFactory changes.
DAOFactory enables technology switching. Today MySQL, tomorrow PostgreSQL. Add a new DAO impl class, change one factory line. Service and Client are untouched.
Exception propagation via throws. DAO throws, Service throws, Client catches. Error messages flow from the database layer all the way to the user.
Each request gets its own connection. The finally block closes the connection after each operation. No shared connection between requests.

7. DAO with Different Technologies

ICustomerDAO (interface) | +-----+-------+----------+ | | | CustomerMySQL CustomerHib CustomerSpringJDBC DAOImpl DAOImpl DAOImpl (JDBC code) (Hibernate) (Spring JdbcTemplate) Switch technology by changing ONE line in DAOFactory.

Technology	DAO Class	Connection Method
Plain JDBC	CustomerMySQLDAOImpl	DriverManager.getConnection()
Hibernate	CustomerHibernateDAOImpl	SessionFactory.openSession()
Spring JDBC	CustomerSpringJdbcDAOImpl	JdbcTemplate (injected)
Spring Data JPA	CustomerRepository (interface only!)	Auto-generated by Spring

8. Shared vs Dedicated Connection

Bad Practice: Shared Connection

Making all requests use the same JDBC connection object for DB operations is bad practice. If one request's transaction is pending and another request uses the same connection, the second request's commit/rollback can affect the first request's data.

Good Practice: Dedicated Connection

Each request using its own JDBC connection object for DB operations is good practice. Each request gets its own connection, so any pending request's rollback/commit does not affect other request's persistence operations.

9. When to Use and When NOT to Use

When to use DAO

Scenario	Why DAO
Any application with database access	Separates persistence logic from business logic
Multiple database technologies	Same interface, different implementations per technology
Testability needed	Mock the DAO interface for unit testing service layer
Team development	DAO team and service team work independently

When NOT to use DAO (or use lighter alternatives)

Scenario	Alternative
Spring Data JPA project	Use Repository interfaces. Spring generates DAO automatically.
Simple CRUD with no custom logic	Use JpaRepository/CrudRepository directly
Microservice with single DB table	Repository pattern (thinner than DAO)

10. DAO vs Repository Pattern

Aspect	DAO Pattern	Repository Pattern
Origin	JEE Pattern (Sun Microsystems)	Domain-Driven Design (Eric Evans)
Focus	Data access technology abstraction	Domain object collection abstraction
Interface	Technology-oriented (CRUD methods)	Domain-oriented (findByName, findActive)
Implementation	One impl per technology (JDBC, Hibernate)	One impl, technology is hidden
Spring equivalent	Custom DAO classes	JpaRepository, CrudRepository

11. Quick Reference Card

Component	Role	In Customer Project
DAO Interface	Declares CRUD methods	`ICustomerDAO`
DAO Impl	Implements using specific technology	`CustomerMySQLDAOImpl`
Entity/BO	Maps to DB table	`Customer`
DAOFactory	Creates correct DAO impl	`DAOFactory`
ConnectionFactory	Manages DB connections	`ConnectionFactory`
Service	Business logic, calls DAO	`CustomerRegService`

JEE Pattern Integration Tier Medium

DAO (Data Access Object) Pattern in Java