Druid Connection Pool: Complete Java Setup Guide with Helper Class & Tests

Druid is an open-source database connection pool developed by Alibaba, widely used in China for its flexible, stable, and high-performance characteristics, often outperforming alternatives like c3p0. The article recommends prioritizing Druid for project and product development.

Maven Dependencies

Create a JavaSE Maven project and add the following dependencies to pom.xml using the latest versions from https://mvnrepository.com:

Druid: com.alibaba:druid MySQL Connector/J: mysql:mysql-connector-java After adding dependencies, refresh the Maven tool window to download the JAR files.

Properties Configuration

Create a druid.properties file in the resources directory with core configuration keys:

# Database connection parameters
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testdb
username=root
password=123456

# Initial connection count
initialSize=3
# Maximum connection count
maxActive=20
# Maximum wait time for a connection (milliseconds)
maxWait=3000

Many optional configuration items exist; the full list is documented at https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE.

Basic Usage

Use DruidDataSourceFactory to create a DataSource from the properties file, then call getConnection() to obtain a connection. The returned connection behaves like a standard JDBC connection, but calling close() returns it to the pool instead of destroying it.

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class druidtest {
    public static void main(String[] args) throws Exception {
        // Load configuration file
        InputStream is = druidtest.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties prop = new Properties();
        prop.load(is);

        // Create DataSource from configuration
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        // Get connection from pool (waits up to maxWait if exhausted)
        Connection con = dataSource.getConnection();

        // Execute SQL and print results
        String sql = "select e_id,e_name,e_age from employee";
        PreparedStatement pst = con.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt("e_id") + "\t" +
                               rs.getString("e_name") + "\t" +
                               rs.getInt("e_age"));
        }

        // Release resources
        rs.close();
        pst.close();
        // close() returns connection to pool
        con.close();
    }
}

Reusable Helper Class: jdbcHelper

A complete utility class encapsulates common operations. It initializes the DataSource statically, provides getConnection(), overloaded close() methods for resource cleanup, and four main execution methods: queryForValue(sql, returnType, params...) – returns a single scalar value. queryForObject(sql, returnType, params...) – maps a single row to a JavaBean using reflection ( PropertyDescriptor and setter invocation). queryForList(sql, returnType, params...) – returns a List<T> of mapped beans. executeSql(sql, params...) – executes INSERT/UPDATE/DELETE and returns affected row count.

All methods use PreparedStatement with positional parameters, handle exceptions by printing stack traces, and ensure resources are closed in finally blocks.

public class jdbcHelper {
    private static DataSource dataSource;
    static {
        try {
            InputStream is = druidtest.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties prop = new Properties();
            prop.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception ex) { ex.printStackTrace(); }
    }
    public static Connection getConnection() { ... }
    public static void close(Connection con, Statement stat, ResultSet rs) { ... }
    public static void close(Connection con, Statement stat) { ... }
    public static <T> T queryForValue(String sql, Class<T> returnType, Object... parameters) { ... }
    public static <T> T queryForObject(String sql, Class<T> returnType, Object... parameters) { ... }
    public static <T> List<T> queryForList(String sql, Class<T> returnType, Object... parameters) { ... }
    public static int executeSql(String sql, Object... parameters) { ... }
}

JUnit Test Cases

The article provides a test class jdbcHelperTest demonstrating each helper method against an employee table: queryForValue: select count(*) from employee → prints count. queryForList: select * from employee → prints all employees. queryForObject: select * from employee WHERE e_id=? with parameter 1 → prints one employee. insert: insert into employee(e_id,e_name,e_age) values(?,?,?) with {6, "任天蓬", 40} → prints affected rows. update: update employee set e_age=? where e_id=? with {38, 6} → prints affected rows. delete: DELETE FROM employee WHERE e_name=? with "任天蓬" → prints affected rows.

These tests verify the helper class works for full CRUD cycles.