JDBC Framework with Connection Pools and Design Patterns

JDBC Fundamentals and Framework Development

Core JDBC API Overview

DriverManager Functionality

The DriverManager serves as the central registry for JDBC drivers. When loading a driver class via Class.forName(), the driver registers itself automatically through a static initialization block that calls DriverManager.registerDriver(). Modern MySQL drivers (version 5.0+) can omit this explicit registration since the JAR includes metadata in java.sql.Driver configuration files.

Connection establishment uses the getConnection() method witth a URL following the pattern jdbc:mysql://hostname:port/database. The returned Connection object represents an active database session and provides methods for creating statement objects and managing transactions.

Connection Interface Operations

The Connection interface provides several critical capabilities:

Statement Creation: createStatement() generates basic statement objects, while prepareStatement(String sql) creates pre-compiled statements for parameterized queries
Transaction Control: setAutoCommit(false) initiates transaction mode, followed by explicit commit() or rollback() calls
Resource Management: The close() method releases the connection back to its pool or closes it entire

Statement and ResultSet Usage

The Statement interface executes static SQL and returns results through two primary methods:

executeUpdate(String sql) handles INSERT, UPDATE, DELETE operations, returning affected row count
executeQuery(String sql) executes SELECT statements, returning a ResultSet containing rows

ResultSet navigation uses next() to advance the cursor, with accessor methods like getString(columnLabel) and getInt(columnLabel) retrieving column values by name or position.

Student Management CRUD Implementation

Domain Entity

package com.example.domain;

import java.util.Date;

public class Student {
    private Integer id;
    private String name;
    private Integer age;
    private Date birthDate;

    public Student() {}

    public Student(Integer id, String name, Integer age, Date birthDate) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birthDate = birthDate;
    }

    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }
    public Date getBirthDate() { return birthDate; }
    public void setBirthDate(Date birthDate) { this.birthDate = birthDate; }

    @Override
    public String toString() {
        return "Student{id=" + id + ", name='" + name + "', age=" + age + ", birthDate=" + birthDate + "}";
    }
}

Database Schema

CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    age INT,
    birthday DATE
);

INSERT INTO student VALUES 
    (NULL, 'Alice', 22, '2002-03-15'),
    (NULL, 'Bob', 23, '2001-07-22'),
    (NULL, 'Charlie', 21, '2003-11-08');

Data Access Layer

public class StudentDaoImpl implements StudentDao {
    
    public List<Student> findAll() {
        List<Student> results = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rows = null;
        
        try {
            conn = DBConnectionProvider.getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM student");
            rows = pstmt.executeQuery();
            
            while (rows.next()) {
                Student s = new Student();
                s.setId(rows.getInt("sid"));
                s.setName(rows.getString("name"));
                s.setAge(rows.getInt("age"));
                s.setBirthDate(rows.getDate("birthday"));
                results.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rows);
        }
        return results;
    }

    public Student findById(Integer id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rows = null;
        Student student = null;
        
        try {
            conn = DBConnectionProvider.getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM student WHERE sid = ?");
            pstmt.setInt(1, id);
            rows = pstmt.executeQuery();
            
            if (rows.next()) {
                student = new Student();
                student.setId(rows.getInt("sid"));
                student.setName(rows.getString("name"));
                student.setAge(rows.getInt("age"));
                student.setBirthDate(rows.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rows);
        }
        return student;
    }

    public int save(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int affectedRows = 0;
        
        try {
            conn = DBConnectionProvider.getConnection();
            pstmt = conn.prepareStatement(
                "INSERT INTO student VALUES (NULL, ?, ?, ?)"
            );
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getAge());
            pstmt.setDate(3, new java.sql.Date(student.getBirthDate().getTime()));
            affectedRows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, null);
        }
        return affectedRows;
    }

    public int update(Student student) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int affectedRows = 0;
        
        try {
            conn = DBConnectionProvider.getConnection();
            pstmt = conn.prepareStatement(
                "UPDATE student SET name=?, age=?, birthday=? WHERE sid=?"
            );
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getAge());
            pstmt.setDate(3, new java.sql.Date(student.getBirthDate().getTime()));
            pstmt.setInt(4, student.getId());
            affectedRows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, null);
        }
        return affectedRows;
    }

    public int remove(Integer id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int affectedRows = 0;
        
        try {
            conn = DBConnectionProvider.getConnection();
            pstmt = conn.prepareStatement("DELETE FROM student WHERE sid = ?");
            pstmt.setInt(1, id);
            affectedRows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, null);
        }
        return affectedRows;
    }

    private void closeResources(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } }
        if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } }
        if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
    }
}

JDBC Utility Class

Configuration File

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/school_db?useSSL=false&amp;serverTimezone=UTC
db.username=root
db.password=password123

Connection Provider

public class DBConnectionProvider {
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static {
        try (InputStream configStream = DBConnectionProvider.class
                .getClassLoader().getResourceAsStream("database.properties")) {
            Properties config = new Properties();
            config.load(configStream);
            
            driverClass = config.getProperty("db.driver");
            url = config.getProperty("db.url");
            username = config.getProperty("db.username");
            password = config.getProperty("db.password");
            
            Class.forName(driverClass);
        } catch (IOException | ClassNotFoundException e) {
            throw new RuntimeException("Database initialization failed", e);
        }
    }

    private DBConnectionProvider() {}

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException("Failed to establish connection", e);
        }
    }

    public static void cleanup(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) { try { rs.close(); } catch (SQLException ignored) {} }
        if (stmt != null) { try { stmt.close(); } catch (SQLException ignored) {} }
        if (conn != null) { try { conn.close(); } catch (SQLException ignored) {} }
    }

    public static void cleanup(Connection conn, Statement stmt) {
        cleanup(conn, stmt, null);
    }
}

SQL Injection Prevention with PreparedStatement

Vulnerability Demonstration

Using string concatenation for SQL queries creates security vulnerabilities:

// VULNERABLE CODE - DO NOT USE
String query = "SELECT * FROM user WHERE name='" + inputName + "' AND pass='" + inputPass + "'";
// Attacker input: ' OR '1'='1' -- results in永真条件

Secure Implementation

public User authenticate(String loginName, String password) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rows = null;
    User user = null;

    try {
        conn = DBConnectionProvider.getConnection();
        String sql = "SELECT * FROM user WHERE loginname = ? AND password = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, loginName);
        pstmt.setString(2, password);
        rows = pstmt.executeQuery();

        if (rows.next()) {
            user = new User();
            user.setUid(rows.getString("uid"));
            user.setLoginName(rows.getString("loginname"));
            user.setUserName(rows.getString("username"));
            user.setGender(rows.getString("gender"));
        }
    } catch (SQLException e) {
        throw new RuntimeException("Authentication failed", e);
    } finally {
        DBConnectionProvider.cleanup(conn, pstmt, rows);
    }
    return user;
}

Transaction Management

Batch Operations with Transaction Support

public class TransactionalUserService {
    private UserDao userDao;

    public void batchImportUsers(List<User> userList) {
        Connection conn = null;
        try {
            conn = DBConnectionProvider.getConnection();
            conn.setAutoCommit(false);
            
            for (User user : userList) {
                user.setUid(UUID.randomUUID().toString().replace("-", "").toUpperCase());
                userDao.save(conn, user);
            }
            
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
            }
            throw new RuntimeException("Batch import failed", e);
        } finally {
            DBConnectionProvider.cleanup(conn, null, null);
        }
    }
}

Connection Pool Architecture

DataSource Interface

Java provides the javax.sql.DataSource interface as the standard for connection pooling. Applications request connections via getConnection() and return them by calling close() on the connection object.

Custom Connection Pool Implementation

public class BasicConnectionPool implements DataSource {
    private final List<Connection> availableConnections;
    private final List<Connection> usedConnections;
    private static final int INITIAL_POOL_SIZE = 10;

    public BasicConnectionPool() {
        availableConnections = Collections.synchronizedList(new ArrayList<>());
        usedConnections = Collections.synchronizedList(new ArrayList<>());
        
        for (int i = 0; i < INITIAL_POOL_SIZE; i++) {
            availableConnections.add(createRawConnection());
        }
    }

    private Connection createRawConnection() {
        try {
            return DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "password"
            );
        } catch (SQLException e) {
            throw new RuntimeException("Unable to create connection", e);
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        synchronized (this) {
            if (availableConnections.isEmpty()) {
                throw new SQLException("No available connections in pool");
            }
            Connection conn = availableConnections.remove(0);
            usedConnections.add(conn);
            return wrapConnection(conn);
        }
    }

    private Connection wrapConnection(Connection rawConnection) {
        return new PooledConnectionWrapper(rawConnection, this);
    }

    public void releaseConnection(Connection conn) {
        synchronized (this) {
            usedConnections.remove(conn);
            availableConnections.add(conn);
        }
    }

    public int getAvailableCount() { return availableConnections.size(); }
    public int getUsedCount() { return usedConnections.size(); }
    
    // Unused DataSource methods implementation omitted for brevity
}

PooledConnectionWrapper - Decorator Pattern

The wrapper implements the Connection interface while delegating all operations to the underlying connection except for close(), which returns the connection to the pool.

public class PooledConnectionWrapper implements Connection {
    private final Connection delegate;
    private final BasicConnectionPool pool;
    private boolean isClosed = false;

    public PooledConnectionWrapper(Connection delegate, BasicConnectionPool pool) {
        this.delegate = delegate;
        this.pool = pool;
    }

    @Override
    public void close() throws SQLException {
        if (!isClosed) {
            isClosed = true;
            pool.releaseConnection(this);
        }
    }

    // Delegate all other Connection methods to underlying connection
    @Override
    public Statement createStatement() throws SQLException {
        return delegate.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return delegate.prepareStatement(sql);
    }

    @Override
    public void commit() throws SQLException { delegate.commit(); }
    
    @Override
    public void rollback() throws SQLException { delegate.rollback(); }
    
    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        delegate.setAutoCommit(autoCommit);
    }
    
    @Override
    public boolean getAutoCommit() throws SQLException { return delegate.getAutoCommit(); }
    
    @Override
    public boolean isClosed() throws SQLException { return isClosed; }
    
    // ... remaining 30+ Connection interface methods delegate to delegate
}

Adapter Pattern for Connection Wrapper

The adapter pattern reduces boilerplate by providing default implementations for all Connection methods:

Connection Adapter Base Class

public abstract class ConnectionAdapter implements Connection {
    protected final Connection adaptedConnection;

    public ConnectionAdapter(Connection adaptedConnection) {
        this.adaptedConnection = adaptedConnection;
    }

    @Override
    public Statement createStatement() throws SQLException {
        return adaptedConnection.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return adaptedConnection.prepareStatement(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return adaptedConnection.prepareCall(sql);
    }

    // All other methods follow the same delegation pattern
    // Only the close() method is left abstract for customization
}

Simplified Pooled Connection

public class PooledConnection extends ConnectionAdapter {
    private final BasicConnectionPool pool;
    private boolean released = false;

    public PooledConnection(Connection conn, BasicConnectionPool pool) {
        super(conn);
        this.pool = pool;
    }

    @Override
    public void close() throws SQLException {
        if (!released) {
            released = true;
            pool.releaseConnection(this);
        }
    }
}

Dynamic Proxy Approach

Java Proxy for Connection Pooling

public class ProxyConnectionPool implements DataSource {
    private final List<Connection> connectionPool;
    private static final int POOL_SIZE = 10;

    public ProxyConnectionPool() {
        connectionPool = Collections.synchronizedList(new ArrayList<>());
        for (int i = 0; i < POOL_SIZE; i++) {
            connectionPool.add(createConnection());
        }
    }

    private Connection createConnection() {
        try {
            return DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_db", "root", "password"
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if (connectionPool.isEmpty()) {
            throw new SQLException("Connection pool exhausted");
        }
        
        final Connection originalConn = connectionPool.remove(0);
        
        Connection proxiedConnection = (Connection) Proxy.newProxyInstance(
            Connection.class.getClassLoader(),
            new Class<?>[] { Connection.class },
            (proxy, method, args) -> {
                if ("close".equals(method.getName())) {
                    connectionPool.add(originalConn);
                    return null;
                }
                return method.invoke(originalConn, args);
            }
        );
        
        return proxiedConnection;
    }
}

Third-Party Connection Pools

C3P0 Configuration

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school_db</property>
        <property name="user">root</property>
        <property name="password">password</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">20</property>
        <property name="checkoutTimeout">3000</property>
        <property name="minPoolSize">3</property>
    </default-config>
</c3p0-config>

public class C3P0Example {
    public static void main(String[] args) throws SQLException {
        DataSource ds = new ComboPooledDataSource();
        
        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM student");
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                System.out.println(rs.getInt("sid") + " - " + rs.getString("name"));
            }
        }
    }
}

Druid Configuration

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school_db?useSSL=false
username=root
password=password
initialSize=5
maxActive=20
maxWait=5000
timeBetweenEvictionRunsMillis=60000
minIdle=3
validationQuery=SELECT 1

public class DruidPoolManager {
    private static DataSource dataSource;

    static {
        try (InputStream config = DruidPoolManager.class
                .getClassLoader().getResourceAsStream("druid.properties")) {
            Properties props = new Properties();
            props.load(config);
            dataSource = DruidDataSourceFactory.createDataSource(props);
        } catch (IOException | PropertyVetoException e) {
            throw new RuntimeException("Druid initialization failed", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

Custom JDBCTemplate Framework

Metadata Utilities

public class JdbcMetaDataUtil {
    
    public static int getParameterCount(PreparedStatement pstmt) throws SQLException {
        return pstmt.getParameterMetaData().getParameterCount();
    }
    
    public static int getColumnCount(ResultSet rs) throws SQLException {
        return rs.getMetaData().getColumnCount();
    }
    
    public static String getColumnName(ResultSet rs, int index) throws SQLException {
        return rs.getMetaData().getColumnName(index);
    }
}

ResultSet Handler Interface

public interface ResultSetHandler<T> {
    T process(ResultSet resultSet) throws SQLException;
}

Handler Implementations

public class BeanResultHandler<T> implements ResultSetHandler<T> {
    private final Class<T> targetType;

    public BeanResultHandler(Class<T> targetType) {
        this.targetType = targetType;
    }

    @Override
    public T process(ResultSet rs) throws SQLException {
        T instance = null;
        if (rs.next()) {
            instance = targetType.getDeclaredConstructor().newInstance();
            ResultSetMetaData metaData = rs.getMetaData();
            
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i).toLowerCase();
                Object columnValue = rs.getObject(i);
                
                PropertyDescriptor pd = new PropertyDescriptor(columnName, targetType);
                Method setter = pd.getWriteMethod();
                setter.invoke(instance, columnValue);
            }
        }
        return instance;
    }
}

public class BeanListResultHandler<T> implements ResultSetHandler<List<T>> {
    private final Class<T> elementType;

    public BeanListResultHandler(Class<T> elementType) {
        this.elementType = elementType;
    }

    @Override
    public List<T> process(ResultSet rs) throws SQLException {
        List<T> results = new ArrayList<>();
        ResultSetHandler<T> singleHandler = new BeanResultHandler<>(elementType);
        
        while ((rs.next())) {
            results.add(singleHandler.process(new SingleRowResultSet(rs)));
        }
        return results;
    }
}

public class ScalarResultHandler<T> implements ResultSetHandler<T> {
    @Override
    public T process(ResultSet rs) throws SQLException {
        if (rs.next()) {
            return (T) rs.getObject(1);
        }
        return null;
    }
}

Simple Wrapper for Single Row Access

class SingleRowResultSet extends AbstractResultSetWrapper {
    public SingleRowResultSet(ResultSet delegate) {
        super(delegate);
    }
}

abstract class AbstractResultSetWrapper implements ResultSet {
    protected final ResultSet delegate;
    
    protected AbstractResultSetWrapper(ResultSet delegate) {
        this.delegate = delegate;
    }
    
    // Delegate all ResultSet methods
}

JDBCTemplate Class

public class JdbcTemplate {
    private final DataSource dataSource;

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

    public int modify(String sql, Object... parameters) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, parameters);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("Update operation failed", e);
        } finally {
            closeQuietly(conn, pstmt);
        }
    }

    public <T> T queryForObject(String sql, ResultSetHandler<T> handler, Object... parameters) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, parameters);
            rs = pstmt.executeQuery();
            return handler.process(rs);
        } catch (SQLException e) {
            throw new RuntimeException("Query operation failed", e);
        } finally {
            closeQuietly(conn, pstmt, rs);
        }
    }

    public <T> List<T> queryForList(String sql, ResultSetHandler<T> handler, Object... parameters) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, parameters);
            rs = pstmt.executeQuery();
            
            List<T> results = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            while (rs.next()) {
                results.add(extractRow(rs, columnCount, handler));
            }
            return results;
        } catch (SQLException e) {
            throw new RuntimeException("List query operation failed", e);
        } finally {
            closeQuietly(conn, pstmt, rs);
        }
    }

    private <T> T extractRow(ResultSet rs, int columnCount, ResultSetHandler<T> handler) 
            throws SQLException {
        return handler.process(new IndexBasedResultSetWrapper(rs, columnCount));
    }

    private void setParameters(PreparedStatement pstmt, Object... parameters) 
            throws SQLException {
        for (int i = 0; i < parameters.length; i++) {
            pstmt.setObject(i + 1, parameters[i]);
        }
    }

    private void closeQuietly(Connection conn, Statement stmt) {
        if (stmt != null) { try { stmt.close(); } catch (SQLException ignored) {} }
        if (conn != null) { try { conn.close(); } catch (SQLException ignored) {} }
    }

    private void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) { try { rs.close(); } catch (SQLException ignored) {} }
        closeQuietly(conn, stmt);
    }
}

JdbcTemplate Usage Examples

public class JdbcTemplateDemo {
    private JdbcTemplate template = new JdbcTemplate(DruidPoolManager.getDataSource());

    public void demonstrateOperations() {
        // Count records
        Long count = template.queryForObject(
            "SELECT COUNT(*) FROM student",
            rs -> rs.next() ? rs.getLong(1) : 0L
        );
        System.out.println("Total students: " + count);

        // Find all students
        List<Student> allStudents = template.queryForList(
            "SELECT * FROM student",
            rs -> {
                Student s = new Student();
                s.setId(rs.getInt("sid"));
                s.setName(rs.getString("name"));
                s.setAge(rs.getInt("age"));
                return s;
            }
        );

        // Find by id
        Student found = template.queryForObject(
            "SELECT * FROM student WHERE sid = ?",
            rs -> {
                Student s = new Student();
                s.setId(rs.getInt("sid"));
                s.setName(rs.getString("name"));
                return s;
            },
            1
        );

        // Insert
        int inserted = template.modify(
            "INSERT INTO student VALUES (NULL, ?, ?, ?)",
            "David", 24, java.sql.Date.valueOf("2000-01-15")
        );

        // Update
        int updated = template.modify(
            "UPDATE student SET age = ? WHERE name = ?",
            25, "David"
        );

        // Delete
        int deleted = template.modify(
            "DELETE FROM student WHERE name = ?",
            "David"
        );
    }
}

Conclusion

This framework covers the essential aspects of database connectivity in Java applications, including core JDBC API usage, connection pooling strategies, design patterns for connection management, and template-based query execution. The combination of these techniques provides a robust foundation for building data access layers in enterprise applications.

Tags: JDBC java MySQL database connection-pool

Posted on Sun, 30 Aug 2026 16:32:44 +0000 by blink359

Freaks City