数据库连接池 Demo（1）单线程初步

作者：萧

2021 年 11 月 15 日
本文字数：5360 字
阅读完需：约 18 分钟

简介

在上周阅读 Alibaba Druid 数据库连接池后，感觉光看有点领会不到精髓，后面这几篇文章将尝试自己实现一个数据库连接池 Demo

原生 JDBC 与 Alibaba Druid 使用

我们先把相关的测试给搭建起来，把 JDBC、Druid 的相关示例代码跑起来，看看效果和性能

在实现一个最简单的自定义连接池，然后运行三者进行对比

原生 JDBC

我们先使用原生 JDBC 将数据库数据初始化，然后去查询，代码如下：

public class Main {
    static final String DB_URL = "jdbc:h2:file:./demo-db";    static final String USER = "sa";    static final String PASS = "sa";
    /**     * 生成数据     */    private static void initData() {        final String drop = "drop table `user_example` if exists;";        final String createTable = "CREATE TABLE IF NOT EXISTS `user_example` (" +                "`id` bigint NOT NULL AUTO_INCREMENT, " +                "`name` varchar(100) NOT NULL" +                ");";        final String addUser = "insert into user_example (name) values(%s)";        try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);            Statement stmt = conn.createStatement()) {            stmt.execute(drop);            stmt.execute(createTable);            for (int i=0; i<10; i++) {                stmt.execute(String.format(addUser, i));            }            conn.commit();        } catch (SQLException e) {            e.printStackTrace();        }    }}

复制代码

然后原始暴力查询：

public class Main {
    /**     * 原生JDBC查询    */    private static void rawExample() {        for (int i=0; i<queryAmount; i++) {            // Open a connection            try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);                Statement stmt = conn.createStatement();                ResultSet rs = stmt.executeQuery(QUERY);) {                // Extract data from result set                while (rs.next()) {                    // Retrieve by column name                    System.out.print("ID: " + rs.getInt("id"));                    System.out.print(", name: " + rs.getString("name"));                    System.out.print(";");                }                System.out.println();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

复制代码

Alibaba Druid 查询

使用 Alibaba Druid 进行相同的查询操作：

public class Main {    /**     * Alibaba Druid查询    */    private static void druidExample() throws Exception {        DruidDataSource dataSource = new DruidDataSource();        dataSource.setInitialSize(1);        dataSource.setMaxActive(1);        dataSource.setMinIdle(1);        dataSource.setDriverClassName("org.h2.Driver");        dataSource.setUrl(DB_URL);        dataSource.setUsername(USER);        dataSource.setPassword(PASS);
        for (int i=0; i<queryAmount; i++) {            // Open a connection            try(Connection conn = dataSource.getConnection();                Statement stmt = conn.createStatement();                ResultSet rs = stmt.executeQuery(QUERY)) {                // Extract data from result set                while (rs.next()) {                    // Retrieve by column name                    System.out.print("ID: " + rs.getInt("id"));                    System.out.print(", name: " + rs.getString("name"));                    System.out.print(";");                }                System.out.println();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

复制代码

自写最简单版本查询

自定义 Database

列出来的就是要进行自己实现，其他方法暂时默认：

在测试代码中，就涉及到一个 getConnection 函数，以及自定义的连接池复用相关

public class SelfDataSource implements DataSource {    /**     * 放置空闲可用的连接     */    private final Queue<SelfPoolConnection> idle = new LinkedList<>();    /**     * 放置正在使用的连接     */    private final Set<SelfPoolConnection> running = new HashSet<>();    private final String url;    private final String username;    private final String password;
    public SelfDataSource(final String url, final String username, final String password) {        this.url = url;        this.username = username;        this.password = password;    }
    /**     * 初步将Connection从运行池中异常，放入空闲池     * 从正在使用连接池中移除，放入空闲连接池中     * @param selfPoolConnection 自定义Connection     */    public void recycle(final SelfPoolConnection selfPoolConnection) {        running.remove(selfPoolConnection);        idle.add(selfPoolConnection);        System.out.println("回收连接");    }
    /**     * 自定义的获取数据库物理连接     * 1.无空闲连接则生成新的物理连接，并且放入正在使用连接池中     * 2.如果有空闲连接，则获取，并放入正在使用连接池中     * @return 自定义的数据库物理连接（自定义以能够自定义Close方法）     * @throws SQLException     */    @Override    synchronized public Connection getConnection() throws SQLException {        if (idle.isEmpty()) {            System.out.println("生成新物理连接");            SelfPoolConnection conn = new SelfPoolConnection(this, url, username, password);            running.add(conn);            return conn.getConnection();        }        SelfPoolConnection conn = idle.poll();        running.add(conn);        return conn.getConnection();    }}

复制代码

自定义 Connection

因为在测试代码中，涉及的需要实现的函数如下：

createStatement
close（try 自动调用）
getConnection（自定义 DataSource 调用）

public class SelfPoolConnection implements javax.sql.PooledConnection, Connection {
    private final SelfDataSource selfDatasource;    private Connection connection;
    public SelfPoolConnection(final SelfDataSource selfDatasource, final String url, final String username, final String password) {        this.selfDatasource = selfDatasource;        System.out.println("初始化物理连接");        try {            connection = DriverManager.getConnection(url, username, password);        } catch (SQLException e) {            e.printStackTrace();        }    }
    @Override    public Statement createStatement() throws SQLException {        return connection.createStatement();    }
    @Override    synchronized public Connection getConnection() throws SQLException {        return this;    }
    /**     * 关闭连接，调用自定义DataSource用于复用     * 目前感觉这样不规范，但时间紧张，前期先简单实现     * @throws SQLException     */    @Override    public void close() throws SQLException {        selfDatasource.recycle(this);    }}

复制代码

运行对比

运行函数与结果如下：

public class Main {
    public static void main(String[] args) throws Exception {        initData();
        long current = System.currentTimeMillis();        rawExample();        System.out.printf("原生查询耗时：%d 毫秒\n", System.currentTimeMillis() - current);
        current = System.currentTimeMillis();        druidExample();        System.out.printf("连接池查询耗时：%d 毫秒\n", System.currentTimeMillis() - current);
        current = System.currentTimeMillis();        selfExample();        System.out.printf("自写连接池查询耗时：%d 毫秒\n", System.currentTimeMillis() - current);
        Thread.sleep(3000);    }}

复制代码

结果：

ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;原生查询耗时：220 毫秒11月 15, 2021 10:09:04 下午 com.alibaba.druid.pool.DruidDataSource error严重: testWhileIdle is true, validationQuery not set11月 15, 2021 10:09:04 下午 com.alibaba.druid.pool.DruidDataSource info信息: {dataSource-1} initedID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;连接池查询耗时：69 毫秒生成新物理连接初始化物理连接ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;回收连接ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;回收连接ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;回收连接ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;回收连接ID: 1, name: 0;ID: 2, name: 1;ID: 3, name: 2;ID: 4, name: 3;ID: 5, name: 4;ID: 6, name: 5;ID: 7, name: 6;ID: 8, name: 7;ID: 9, name: 8;ID: 10, name: 9;回收连接自写连接池查询耗时：6 毫秒Disconnected from the target VM, address: '127.0.0.1:54211', transport: 'socket'

复制代码

总结

从接口上看，一部分是符合我们预期的：连接池的性能是远远优于不使用连接池的

但自定义的连接池竟然比 Druid 还快，是我没有想到的，一度有些怀疑

但相关的地址确实是实现了的，从日志上来看，确实是只初始化了一次，后面没有再初始物理连接

目前的例子是单线程，没有考虑加锁、检查、异常处理等，可能是这些有影响，后面我们再研究研究

代码参考地址：https://github.com/lw1243925457/DataSourcePoolDemo

参考链接

发布于: 2021 年 11 月 15 日阅读数: 9

原文链接:【http://xie.infoq.cn/article/a57179ba452993afa40e195d9】。

萧

关注

还未添加个人签名 2018.09.09 加入

代码是门手艺活，也是门艺术活

发布

暂无评论

创作场景

数据库连接池 Demo（1）单线程初步

简介

原生 JDBC 与 Alibaba Druid 使用

原生 JDBC

Alibaba Druid 查询

自写最简单版本查询

自定义 Database

自定义 Connection

运行对比

总结

参考链接

萧

评论