IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

JDBC连接池案例

wen java案例 2

本文目录导读：

JDBC连接池案例

  1. 自定义简易连接池实现
  2. 连接池测试类
  3. Druid连接池工具类
  4. druid.properties配置文件
  5. JDBC操作示例类
  6. 添加Maven依赖
  7. 连接池关键配置说明

我来为您提供一个完整的JDBC连接池案例,包含自定义实现和使用Druid连接池两种方式。

自定义简易连接池实现

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.ReentrantLock;
public class CustomConnectionPool {
    private LinkedList<Connection> pool;
    private ReentrantLock lock = new ReentrantLock();
    private Condition condition = lock.newCondition();
    private int initialSize;
    private int maxSize;
    private String url;
    private String username;
    private String password;
    private int currentCount = 0;
    private int busyCount = 0;
    public CustomConnectionPool(String url, String username, String password, 
                               int initialSize, int maxSize) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.initialSize = initialSize;
        this.maxSize = maxSize;
        this.pool = new LinkedList<>();
        initPool();
    }
    private void initPool() {
        for (int i = 0; i < initialSize; i++) {
            try {
                pool.add(createConnection());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        currentCount = initialSize;
    }
    private Connection createConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    public Connection getConnection() {
        lock.lock();
        try {
            while (pool.isEmpty()) {
                if (currentCount < maxSize) {
                    try {
                        pool.add(createConnection());
                        currentCount++;
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                } else {
                    try {
                        // 等待连接释放
                        condition.await(5, TimeUnit.SECONDS);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
            }
            Connection conn = pool.removeFirst();
            busyCount++;
            System.out.println("获取连接，当前活跃连接数：" + busyCount + ", 空闲连接数：" + pool.size());
            return conn;
        } finally {
            lock.unlock();
        }
    }
    public void releaseConnection(Connection conn) {
        lock.lock();
        try {
            if (conn != null) {
                pool.add(conn);
                busyCount--;
                System.out.println("释放连接，当前活跃连接数：" + busyCount + ", 空闲连接数：" + pool.size());
                condition.signalAll();
            }
        } finally {
            lock.unlock();
        }
    }
    public int getCurrentCount() {
        return currentCount;
    }
    public int getBusyCount() {
        return busyCount;
    }
}

连接池测试类

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class ConnectionPoolTest {
    public static void main(String[] args) {
        // 测试自定义连接池
        testCustomPool();
        System.out.println("\n====================================\n");
        // 测试Druid连接池
        testDruidPool();
    }
    private static void testCustomPool() {
        String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "123456";
        CustomConnectionPool pool = new CustomConnectionPool(url, username, password, 2, 5);
        // 模拟并发获取连接
        for (int i = 0; i < 3; i++) {
            new Thread(() -> {
                try {
                    Connection conn = pool.getConnection();
                    // 模拟业务操作
                    Thread.sleep(2000);
                    // 执行查询
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("SELECT 1");
                    while (rs.next()) {
                        System.out.println("线程" + Thread.currentThread().getName() 
                                         + " 查询结果: " + rs.getInt(1));
                    }
                    rs.close();
                    stmt.close();
                    pool.releaseConnection(conn);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }, "Thread-" + i).start();
        }
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
    private static void testDruidPool() {
        // Druid连接池配置
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // 连接池配置
        dataSource.setInitialSize(5);         // 初始连接数
        dataSource.setMinIdle(2);              // 最小空闲连接数
        dataSource.setMaxActive(10);           // 最大连接数
        dataSource.setMaxWait(10000);          // 最大等待时间(毫秒)
        dataSource.setValidationQuery("SELECT 1"); // 验证连接是否有效
        try {
            Connection conn = dataSource.getConnection();
            System.out.println("Druid连接池获取连接成功!");
            // 执行数据库操作
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT 1 as result");
            while (rs.next()) {
                System.out.println("Druid查询结果: " + rs.getInt("result"));
            }
            rs.close();
            stmt.close();
            conn.close();
            System.out.println("Druid连接池活跃连接数: " + dataSource.getActiveCount());
            System.out.println("Druid连接池空闲连接数: " + dataSource.getPoolingCount());
            dataSource.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Druid连接池工具类

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DruidPoolUtil {
    private static DruidDataSource dataSource;
    static {
        try {
            // 方式一：配置文件加载
            Properties props = new Properties();
            InputStream in = DruidPoolUtil.class.getClassLoader()
                .getResourceAsStream("druid.properties");
            props.load(in);
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(props);
            // 或者在代码中直接配置
            initDataSource();
        } catch (Exception e) {
            e.printStackTrace();
            throw new ExceptionInInitializerError("Druid连接池初始化失败");
        }
    }
    private static void initDataSource() {
        dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        // 连接池配置
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(20);
        dataSource.setMaxWait(60000);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestWhileIdle(true);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        // 开启监控统计功能
        dataSource.setFilters("stat,wall,log4j");
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    public static void close(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(); }
        }
    }
    public static int getActiveCount() {
        return dataSource.getActiveCount();
    }
    public static int getIdleCount() {
        return dataSource.getPoolingCount();
    }
    public static void closePool() {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

druid.properties配置文件

# 数据库连接配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8
username=root
password=123456
# 连接池配置
initialSize=5
minIdle=5
maxActive=20
maxWait=60000
# 连接检测
validationQuery=SELECT 1
testWhileIdle=true
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
# 监控统计
filters=stat,wall,log4j

JDBC操作示例类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
    // 使用Druid连接池
    public List<User> findAll() {
        List<User> users = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DruidPoolUtil.getConnection();
            String sql = "SELECT id, username, email FROM users";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername(rs.getString("username"));
                user.setEmail(rs.getString("email"));
                users.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidPoolUtil.close(conn, ps, rs);
        }
        return users;
    }
    // 添加用户
    public boolean addUser(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean result = false;
        try {
            conn = DruidPoolUtil.getConnection();
            String sql = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            result = ps.executeUpdate() > 0;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DruidPoolUtil.close(conn, ps, null);
        }
        return result;
    }
    // 事务操作示例
    public void transactionExample() {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            conn = DruidPoolUtil.getConnection();
            conn.setAutoCommit(false);  // 开启事务
            // 操作1：更新用户余额
            String sql1 = "UPDATE account SET balance = balance - 100 WHERE username = 'A'";
            ps1 = conn.prepareStatement(sql1);
            ps1.executeUpdate();
            // 操作2：增加用户余额
            String sql2 = "UPDATE account SET balance = balance + 100 WHERE username = 'B'";
            ps2 = conn.prepareStatement(sql2);
            ps2.executeUpdate();
            conn.commit();  // 提交事务
            System.out.println("事务提交成功");
        } catch (SQLException e) {
            try {
                conn.rollback();  // 回滚事务
                System.out.println("事务回滚");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            if (ps1 != null) try { ps1.close(); } catch (SQLException e) {}
            if (ps2 != null) try { ps2.close(); } catch (SQLException e) {}
            if (conn != null) try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) {}
        }
    }
}

添加Maven依赖

<dependencies>
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.26</version>
    </dependency>
    <!-- Druid连接池 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.8</version>
    </dependency>
</dependencies>

连接池关键配置说明

配置项 说明 推荐值
initialSize 初始化连接数 5-10
minIdle 最小空闲连接数 5
maxActive 最大活跃连接数 20-50
maxWait 获取连接最大等待时间 60000ms
validationQuery 验证连接SQL SELECT 1
testWhileIdle 空闲时测试连接 true
timeBetweenEvictionRunsMillis 检测间隔时间 60000ms
minEvictableIdleTimeMillis 连接最小空闲时间 300000ms

使用连接池的优势：

  1. 性能优化：减少连接创建和销毁的开销
  2. 资源管理：有效控制数据库连接数量
  3. 高可用性：自动检测和恢复失效连接
  4. 监控统计：可以监控连接池使用情况

上一篇Java数据库分库分表案例

下一篇HikariCP连接池案例

相关文章

抱歉，评论功能暂时关闭!