JDBC ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-21

DAO Pattern and HikariCP Connection Pool in JDBC

Almost everyone's first DAO looks the same: every method calls DriverManager.getConnection(url, user, password), runs a query and closes the connection. In a training program that works. In a web application, every such call opens a new TCP connection to MySQL and goes through authentication, which takes longer than the SELECT itself. That is why real projects give the DAO connections from a pool.

A data access object (DAO) is an object that provides an abstract interface to some type of database or other persistence mechanism. A DAO can sit on top of different kinds of database access, such as JDBC or JPA. The rest of the code calls methods like findById or create and does not know which SQL runs inside or whether it goes through JDBC or Hibernate.

1. Why Use the DAO Pattern

The DAO pattern separates database code from business logic: all SQL for the users table lives in one UserDao class, and services and controllers call its methods. When the table schema or the data access technology changes, you edit one class instead of dozens of places across the project.

Without a DAO, "SELECT ... FROM users" strings spread through the code quickly: one in registration, another in the user profile, a third in a report. Rename a column and you have to hunt them all down. A DAO gives you two more benefits:

  • the implementation can be swapped: today UserDao uses JDBC, tomorrow JPA, and the calling code stays the same because it depends on the interface;
  • a DAO is easy to test on its own: pass it a DataSource for an in-memory H2 database, and the test no longer needs a running MySQL server.

2. The DAO Interface

A generic DAO interface declares the CRUD operations (create, read, update, delete) shared by all entities. The type parameter K is the primary key type and T is the entity type. Each table gets its own implementation: UserDao, PhoneDao, OrderDao.

Example 1. A generic Dao interface

import java.util.List;
import java.util.Optional;

public interface Dao<K, T> {
    List<T> findAll();

    Optional<T> findById(K id);

    T create(T entity);

    boolean update(T entity);

    boolean delete(K id);
}

findById returns an Optional instead of null, so the caller sees right away that the user may not exist. update and delete return true when exactly one row in the table was changed. If interfaces with type parameters still look unfamiliar, review Java Generics.

The examples use a simple users table:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

The entity is a plain class whose fields mirror the table columns:

public class User {
    private Integer id;
    private String username;

    public User(String username) {
        this(null, username);
    }

    public User(Integer id, String username) {
        this.id = id;
        this.username = username;
    }

    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    @Override
    public String toString() {
        return "User{id=" + id + ", username='" + username + "'}";
    }
}

A new user has id set to null: the database assigns it on insert.

3. Implementing UserDao with JDBC

UserDao implements Dao<Integer, User>: each method takes a connection, runs one PreparedStatement and turns ResultSet rows into User objects. It gets connections from a DataSource passed to the constructor, so the DAO itself knows neither the database URL nor the password.

Example 2. The UserDao class

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class UserDao implements Dao<Integer, User> {
    private static final String SELECT_ALL = "SELECT id, username FROM users ORDER BY id";
    private static final String SELECT_BY_ID = "SELECT id, username FROM users WHERE id = ?";
    private static final String INSERT = "INSERT INTO users (username) VALUES (?)";
    private static final String UPDATE = "UPDATE users SET username = ? WHERE id = ?";
    private static final String DELETE = "DELETE FROM users WHERE id = ?";

    private final DataSource dataSource;

    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<User> findAll() {
        List<User> users = new ArrayList<>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(SELECT_ALL);
             ResultSet rs = statement.executeQuery()) {
            while (rs.next()) {
                users.add(mapRow(rs));
            }
        } catch (SQLException e) {
            throw new DaoException("Failed to load users", e);
        }
        return users;
    }

    @Override
    public Optional<User> findById(Integer id) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(SELECT_BY_ID)) {
            statement.setInt(1, id);
            try (ResultSet rs = statement.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }
        } catch (SQLException e) {
            throw new DaoException("Failed to find user with id=" + id, e);
        }
    }

    @Override
    public User create(User user) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement =
                     connection.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS)) {
            statement.setString(1, user.getUsername());
            statement.executeUpdate();
            try (ResultSet keys = statement.getGeneratedKeys()) {
                if (keys.next()) {
                    user.setId(keys.getInt(1));
                }
            }
            return user;
        } catch (SQLException e) {
            throw new DaoException("Failed to save user " + user.getUsername(), e);
        }
    }

    @Override
    public boolean update(User user) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(UPDATE)) {
            statement.setString(1, user.getUsername());
            statement.setInt(2, user.getId());
            return statement.executeUpdate() == 1;
        } catch (SQLException e) {
            throw new DaoException("Failed to update user with id=" + user.getId(), e);
        }
    }

    @Override
    public boolean delete(Integer id) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(DELETE)) {
            statement.setInt(1, id);
            return statement.executeUpdate() == 1;
        } catch (SQLException e) {
            throw new DaoException("Failed to delete user with id=" + id, e);
        }
    }

    private User mapRow(ResultSet rs) throws SQLException {
        return new User(rs.getInt("id"), rs.getString("username"));
    }
}

A few decisions in this code deserve a closer look.

The create method and getGeneratedKeys(). The id column is AUTO_INCREMENT, so MySQL assigns its value. The Statement.RETURN_GENERATED_KEYS flag asks the driver to return the generated key, and getGeneratedKeys() hands it back as a one-row ResultSet. Running SELECT MAX(id) FROM users after the insert does not work: if two users sign up at the same moment, both get the same, latest id.

The DaoException class. SQLException is a checked exception, and it is tempting to silence it with System.out.println(e.getMessage()). Then, when the database is down, findAll() quietly returns an empty list, and the user sees "you have no orders" instead of an error. It is better to wrap it in your own unchecked exception and keep the original as the cause:

public class DaoException extends RuntimeException {
    public DaoException(String message, Throwable cause) {
        super(message, cause);
    }
}

More on such classes in How to Create a Custom Exception in Java.

The mapRow method. Turning a row into an object happens in one place, and columns are read by name: rs.getInt("id") keeps working if someone reorders the columns in the SELECT, while rs.getInt(1) breaks.

All resources, including the ResultSet, are closed with try-with-resources. Every query uses PreparedStatement with ? placeholders rather than string concatenation: the driver sends values separately from the SQL text, which rules out SQL injection.

4. Connection Settings in database.properties

The database URL, user name and password do not belong in DAO code: they go into a database.properties file that the program reads at startup. Switching between a test and a production database then needs no recompilation, and the DAO never sees these settings at all, because it receives a ready DataSource.

Example 3. Contents of database.properties

db.url = jdbc:mysql://localhost:3306/catalog
db.user = root
db.password = admin

In a Maven project the file goes into src/main/resources, and the build puts it at the root of the classpath (the directory layout is covered in How to Create a Maven Project). The db.driver line from older examples is not needed: since JDBC 4.0 the driver registers itself, see JDBC Drivers and Connecting Java to a Database.

Example 4. Reading credentials with ResourceBundle

Older projects often read these settings with ResourceBundle. Here the file stores the server URL (db.url = jdbc:mysql://localhost:3306/) and the database name (db.name = catalog) separately:

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

public class ConnectorDB {
    public static Connection getConnection() throws SQLException {
        ResourceBundle resource = ResourceBundle.getBundle("database");
        String url = resource.getString("db.url");
        String user = resource.getString("db.user");
        String pass = resource.getString("db.password");
        String dbName = resource.getString("db.name");

        return DriverManager.getConnection(url + dbName, user, pass);
    }
}

Example 5. Reading settings with Properties

For connection settings, java.util.Properties is the more common choice:

import java.io.IOException;
import java.io.InputStream;
import java.io.UncheckedIOException;
import java.util.Properties;

public final class DbProperties {
    private DbProperties() {
    }

    public static Properties load() {
        Properties props = new Properties();
        try (InputStream in = DbProperties.class.getClassLoader()
                .getResourceAsStream("database.properties")) {
            if (in == null) {
                throw new IllegalStateException("database.properties not found on the classpath");
            }
            props.load(in);
        } catch (IOException e) {
            throw new UncheckedIOException(e);
        }
        return props;
    }
}
Aspect ResourceBundle Properties
Designed for Localization: texts in different languages Arbitrary "key = value" settings
Missing key MissingResourceException null or a default from getProperty(key, default)
Where it reads from Classpath only, looks up files by locale (database_en.properties and so on) Any InputStream: classpath or a file on disk
Caching Caches the loaded file, edits are not visible without a restart Reads the file again on every load

Keep the password out of Git

A file with the real production password is never committed. The repository gets a database.properties.example with empty values, and on the server the password comes from an environment variable: System.getenv("DB_PASSWORD").

5. What a Connection Pool Is

A connection pool is a set of database connections opened in advance: the program borrows one for a single operation and gives it back. Opening a connection is expensive (TCP, authentication, session setup), and a pool pays that price once at startup and then hands out ready connections to threads.

A pool implements javax.sql.DataSource, so DAO code stays the same: it still calls dataSource.getConnection() and closes the connection with try-with-resources. The difference is inside: close() on a pooled connection does not disconnect from MySQL, it returns the connection to the pool. How DataSource differs from DriverManager is shown in the table in JDBC Drivers and Connecting Java to a Database.

A pool also limits the load on the database. If 500 requests arrive at once and the pool has 10 connections, only 10 queries reach MySQL and the rest wait in line. Without a pool, all 500 threads try to open their own connections, and MySQL answers with Too many connections as soon as its max_connections limit is exceeded.

6. HikariCP Connection Pool

HikariCP is the most widely used JDBC connection pool in Java, and Spring Boot uses it by default. Adding HikariCP takes one Maven dependency; then you create a HikariConfig with the URL, user name and password and pass it to the HikariDataSource constructor.

Example 6. HikariCP Maven dependency

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.3.0</version>
</dependency>

HikariCP 5.x and 6.x require Java 11 or later; for Java 8 there is the 4.0.x line. You still need the MySQL driver (mysql-connector-j): a pool does not replace the driver, it manages the connections the driver opens. Adding dependencies is covered in Maven Dependencies.

Example 7. Configuring HikariDataSource in code

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/catalog");
config.setUsername("root");
config.setPassword("admin");
config.setMaximumPoolSize(10);
config.setPoolName("catalog-pool");

HikariDataSource dataSource = new HikariDataSource(config);

The same settings can live in a file. The HikariConfig(String propertyFileName) constructor reads it from the classpath, and the keys match the setter names without set:

jdbcUrl=jdbc:mysql://localhost:3306/catalog
username=root
password=admin
maximumPoolSize=10
poolName=catalog-pool
HikariDataSource dataSource = new HikariDataSource(new HikariConfig("/hikari.properties"));

Key HikariCP settings

Setting What it controls Default
maximumPoolSize How many connections the pool can hold at once, idle and in use 10
minimumIdle How many idle connections the pool tries to keep ready Same as maximumPoolSize
connectionTimeout How many milliseconds getConnection() waits for a free connection before throwing SQLTransientConnectionException 30,000 (30 seconds)
idleTimeout After how many idle milliseconds an extra connection is closed (applies only when minimumIdle is less than maximumPoolSize) 600,000 (10 minutes)
maxLifetime Maximum lifetime of a connection. Should be a few seconds shorter than the timeout after which the database itself drops connections 1,800,000 (30 minutes)
leakDetectionThreshold After how many milliseconds the pool logs a warning about a connection that was borrowed and not returned 0 (disabled)

A pool keeps connections open and runs background threads, so close it when the program stops: dataSource.close(). HikariDataSource implements Closeable, so in a small program you can create it right in a try-with-resources block.

SLF4J warning on startup

HikariCP logs through SLF4J. If the project has no logger implementation, you get an SLF4J warning at startup that no provider was found, and the pool's messages (including leak warnings) are lost. Add org.slf4j:slf4j-simple or Logback.

7. Putting DAO and HikariCP Together

To connect the pool to your DAOs, create one HikariDataSource at application startup and pass it to the constructor of every DAO. There should be one pool per application: creating it inside a DAO or in every method opens dozens of extra connections and defeats the purpose of pooling.

Example 8. The ConnectionPool class

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.util.Properties;

public final class ConnectionPool {
    private ConnectionPool() {
    }

    public static HikariDataSource create() {
        Properties props = DbProperties.load();

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(props.getProperty("db.url"));
        config.setUsername(props.getProperty("db.user"));
        config.setPassword(props.getProperty("db.password"));
        config.setMaximumPoolSize(10);
        config.setPoolName("catalog-pool");
        return new HikariDataSource(config);
    }
}

Example 9. Using UserDao

import com.zaxxer.hikari.HikariDataSource;

public class UserDaoDemo {
    public static void main(String[] args) {
        try (HikariDataSource dataSource = ConnectionPool.create()) {
            UserDao userDao = new UserDao(dataSource);

            User misha = userDao.create(new User("misha"));
            userDao.create(new User("grisha"));
            System.out.println(misha);
            System.out.println(userDao.findAll());

            misha.setUsername("mikhail");
            System.out.println(userDao.update(misha));
            System.out.println(userDao.delete(100));
            System.out.println(userDao.findById(1));
            System.out.println(userDao.findById(100));
        }
    }
}

Output when the users table has just been created and is empty:

User{id=1, username='misha'}
[User{id=1, username='misha'}, User{id=2, username='grisha'}]
true
false
Optional[User{id=1, username='mikhail'}]
Optional.empty

After create, the misha object already has id=1, taken from getGeneratedKeys(). delete(100) returned false: there is no row with that id, and executeUpdate returned 0. For a test, you can build the same UserDao with an in-memory H2 DataSource without changing a single line of it.

8. Connection Leaks and Other Common Mistakes

A connection leak is a connection that was borrowed from the pool and never closed. The pool cannot tell that it is no longer needed and keeps it marked as busy. Once the number of lost connections reaches maximumPoolSize, the next getConnection() waits for connectionTimeout and fails, and it fails in a completely different part of the program.

What will this program print?

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/catalog");
config.setUsername("root");
config.setPassword("admin");
config.setMaximumPoolSize(2);
config.setConnectionTimeout(1000);

try (HikariDataSource dataSource = new HikariDataSource(config)) {
    for (int i = 1; i <= 3; i++) {
        Connection connection = dataSource.getConnection(); // never closed
        System.out.println("Got connection " + i);
    }
}

The pool hands out two connections immediately, and there is no free third one. After one second (connectionTimeout = 1000), getConnection() throws:

Got connection 1
Got connection 2
Exception in thread "main" java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 1004ms (total=2, active=2, idle=0, waiting=0)

The number of milliseconds will be slightly different on your machine, but active=2 with total=2 always means the same thing: every connection is handed out and nobody gave it back. The fix is to borrow connections in try-with-resources: try (Connection connection = dataSource.getConnection()) { ... }. To find a leak in a large project, enable config.setLeakDetectionThreshold(2000): if a connection is not returned within 2 seconds, HikariCP logs a warning with the stack trace of the place where it was borrowed.

Mistake What happens How to fix it
A Connection stored in a DAO field All threads share one connection, transactions get mixed up, and after the MySQL timeout (wait_timeout, 8 hours by default) every query fails Store the DataSource in the field and borrow a Connection per operation
A new HikariDataSource in every DAO or method Each pool opens its own 10 connections, and MySQL soon hits Too many connections One pool per application, passed to DAO constructors
catch (SQLException e) { System.out.println(...); } A database error turns into an empty list or null, and the cause has to be dug out of the logs Throw a DaoException with the cause
The pool is not closed on shutdown Connections hang on the MySQL side until they time out; in a web app, pools pile up with each redeploy Call dataSource.close() when the application stops

Frequently Asked Questions

What is the difference between DAO and Repository?

A DAO describes access to storage and usually mirrors a table: UserDao with CRUD methods for users. A Repository from DDD describes a collection of domain objects and may assemble an object from several tables. In small projects there is almost no difference, and in Spring Data the interfaces are called Repository even though they often remain DAOs in practice.

Do I need to configure HikariCP myself in Spring Boot?

No. If the project has spring-boot-starter-jdbc or spring-boot-starter-data-jpa, Spring Boot creates a HikariDataSource from the spring.datasource.url, username and password properties. Pool settings use the spring.datasource.hikari prefix, for example spring.datasource.hikari.maximum-pool-size=20.

How big should a connection pool be?

Start with the default of 10 and adjust based on measurements. Bigger is not faster: the database runs as many queries in parallel as it has cores and disks, and extra connections only compete for them. The HikariCP authors suggest a starting point of "database server cores × 2 + number of disks". A pool of hundreds of connections usually points to slow queries, not a small pool.

Do I need a DAO if the project uses Hibernate or JPA?

You still need a data access layer; only the implementation changes: inside the DAO, an EntityManager does the work instead of JDBC. In Spring projects, Spring Data interfaces play this role and generate the implementation for you. The connection pool stays too: Hibernate also takes its connections from a DataSource.

Comments

Please log in or register to have a possibility to add comment.