连接池c3p0 ,Proxool ,Druid ,Tomcat Jdbc Pool对比测试

这次所要做的测试是比较几种我们常用的 数据库连接池的性能，他们分别是：c3p0 ,Proxool ,Druid ,Tomcat Jdbc Pool这四种，测试将采用统一的参数配置力求比较“公平”的体现统一水平下几种连接池的不同。

        1.创建类TestDAO，封装统一的查询方法 ：

<span class="keyword" style="font-weight:bold">import</span> java.sql.Connection;
<span class="keyword" style="font-weight:bold">import</span> java.sql.ResultSet;
<span class="keyword" style="font-weight:bold">import</span> java.sql.SQLException;
<span class="keyword" style="font-weight:bold">import</span> java.sql.Statement;

<span class="keyword" style="font-weight:bold">public</span> <span class="class" style="color:#445588;font-weight:bold"><span class="keyword" style="color:#333333;">class</span> <span class="title">TestDAO</span> {</span>

    <span class="keyword" style="font-weight:bold">private</span> <span class="keyword" style="font-weight:bold">final</span> <span class="keyword" style="font-weight:bold">static</span> String sql = <span class="string" style="color:#dd1144;">"SELECT * FROM USER u WHERE u.USR_ID=9999"</span>;

    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">void</span> query(Connection conn) {
        <span class="keyword" style="font-weight:bold">try</span> {
            Statement st = conn.createStatement();
            ResultSet result = st.executeQuery(sql);
            result.close();
            st.close();
            conn.close();
        } <span class="keyword" style="font-weight:bold">catch</span> (SQLException e) {
            e.printStackTrace();
        }
    }
}

        2.创建测试类TestMain，其中包含：统一参数配置属性、获取各种连接池数据源方法、各种数据源调用方法等，代码较长但不难理解：

<span class="keyword" style="font-weight:bold">import</span> java.beans.PropertyVetoException;
<span class="keyword" style="font-weight:bold">import</span> java.io.IOException;
<span class="keyword" style="font-weight:bold">import</span> java.sql.SQLException;
<span class="keyword" style="font-weight:bold">import</span> org.apache.tomcat.jdbc.pool.DataSource;
<span class="keyword" style="font-weight:bold">import</span> org.logicalcobwebs.proxool.ProxoolDataSource;
<span class="keyword" style="font-weight:bold">import</span> com.alibaba.druid.pool.DruidDataSource;
<span class="keyword" style="font-weight:bold">import</span> com.mchange.v2.c3p0.ComboPooledDataSource;

<span class="keyword" style="font-weight:bold">public</span> <span class="class" style="color:#445588;font-weight:bold"><span class="keyword" style="color:#333333;">class</span> <span class="title">TestMain</span> {</span>

    <span class="comment" style="color:#999988;font-style:italic">// 数据库驱动名称</span>
    <span class="keyword" style="font-weight:bold">static</span> String driver = <span class="string" style="color:#dd1144;">"com.mysql.jdbc.Driver"</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 数据库连接地址</span>
    <span class="keyword" style="font-weight:bold">static</span> String jdbcUrl = <span class="string" style="color:#dd1144;">"jdbc:mysql://192.168.0.1:3306/test"</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 数据库用户名</span>
    <span class="keyword" style="font-weight:bold">static</span> String user = <span class="string" style="color:#dd1144;">"dba"</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 数据库密码</span>
    <span class="keyword" style="font-weight:bold">static</span> String passwd = <span class="string" style="color:#dd1144;">"123456"</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 连接池初始化大小</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> initialSize = <span class="number" style="color:#09999;">5</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 连接池最小空闲</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> minPoolSize = <span class="number" style="color:#09999;">10</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 连接池最大连接数量</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> maxPoolSize = <span class="number" style="color:#09999;">50</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 最小逐出时间，100秒</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> maxIdleTime = <span class="number" style="color:#09999;">100000</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 连接失败重试次数</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> retryAttempts = <span class="number" style="color:#09999;">10</span>;

    <span class="comment" style="color:#999988;font-style:italic">// 当连接池连接耗尽时获取连接数</span>
    <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">int</span> acquireIncrement = <span class="number" style="color:#09999;">5</span>;

    <span class="comment" style="color:#999988;font-style:italic">// c3p0数据源</span>
    <span class="keyword" style="font-weight:bold">static</span> ComboPooledDataSource c3p0DataSource = getC3p0DataSource();

    <span class="comment" style="color:#999988;font-style:italic">// Druid数据源</span>
    <span class="keyword" style="font-weight:bold">static</span> DruidDataSource druidDataSource = getDruidDataSource();

    <span class="comment" style="color:#999988;font-style:italic">// Proxool数据源</span>
    <span class="keyword" style="font-weight:bold">static</span> ProxoolDataSource proxoolDataSource = getProxoolDataSource();

    <span class="comment" style="color:#999988;font-style:italic">// Tomcat Jdbc Pool数据源</span>
    <span class="keyword" style="font-weight:bold">static</span> DataSource tomcatDataSource = getTomcatDataSource();

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * 测试方式： 每种数据源配置信息尽量相同，以求结果更加准确
     * 每种数据源做10次、100次、500次、1000次、2000次、4000次、8000次查询操作 每种查询重复100次，查看100次执行时间的波动图
     * <span class="javadoctag">@param</span> args
     * <span class="javadoctag">@throws</span> IOException
     * <span class="javadoctag">@throws</span> SQLException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">void</span> main(String[] args) <span class="keyword" style="font-weight:bold">throws</span> IOException, SQLException {

        TestDAO testDAO = <span class="keyword" style="font-weight:bold">new</span> TestDAO();
        <span class="comment" style="color:#999988;font-style:italic">// 查询次数</span>
        <span class="keyword" style="font-weight:bold">int</span> count = <span class="number" style="color:#09999;">10</span>;
        System.out.println(<span class="string" style="color:#dd1144;">"查询次数为："</span> + count);
        System.out.println();
        System.out.println(<span class="string" style="color:#dd1144;">"==========================c3p0 测试开始=========================="</span>);
        <span class="comment" style="color:#999988;font-style:italic">// 测试c3p0</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">100</span>; i++) {
            queryC3p0(testDAO, c3p0DataSource, count);
        }
        System.out.println(<span class="string" style="color:#dd1144;">"==========================c3p0 测试结束=========================="</span>);
        System.out.println();
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Proxool 测试开始=========================="</span>);
        <span class="comment" style="color:#999988;font-style:italic">// 测试Proxool</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">100</span>; i++) {
            queryProxxool(testDAO, proxoolDataSource, count);
        }
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Proxool 测试结束=========================="</span>);
        System.out.println();
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Druid 测试开始=========================="</span>);
        <span class="comment" style="color:#999988;font-style:italic">// 测试Druid</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">100</span>; i++) {
            queryDruid(testDAO, druidDataSource, count);
        }
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Druid 测试结束=========================="</span>);
        System.out.println();
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Tomcat Jdbc Pool 测试开始=========================="</span>);
        <span class="comment" style="color:#999988;font-style:italic">// 测试Tomcat Jdbc Pool</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">100</span>; i++) {
            queryTomcatJDBC(testDAO, tomcatDataSource, count);
        }
        System.out.println(<span class="string" style="color:#dd1144;">"==========================Tomcat Jdbc Pool 测试结束=========================="</span>);
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * c3p0测试
     * <span class="javadoctag">@param</span> testDAO
     * <span class="javadoctag">@param</span> ds
     * <span class="javadoctag">@param</span> count
     * <span class="javadoctag">@throws</span> SQLException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">void</span> queryC3p0(TestDAO testDAO, ComboPooledDataSource ds, <span class="keyword" style="font-weight:bold">int</span> count) <span class="keyword" style="font-weight:bold">throws</span> SQLException {
        <span class="comment" style="color:#999988;font-style:italic">// 查询10次以初始化连接池</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">10</span>; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 开始时间</span>
        <span class="keyword" style="font-weight:bold">long</span> startMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 循环查询</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < count; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 结束时间</span>
        <span class="keyword" style="font-weight:bold">long</span> endMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 输出结束时间</span>
        System.out.println(endMillis - startMillis);
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * Proxxool测试
     * <span class="javadoctag">@param</span> testDAO
     * <span class="javadoctag">@param</span> ds
     * <span class="javadoctag">@param</span> count
     * <span class="javadoctag">@throws</span> SQLException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">void</span> queryProxxool(TestDAO testDAO, ProxoolDataSource ds, <span class="keyword" style="font-weight:bold">int</span> count) <span class="keyword" style="font-weight:bold">throws</span> SQLException {
        <span class="comment" style="color:#999988;font-style:italic">// 查询10次以初始化连接池</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">10</span>; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 开始时间</span>
        <span class="keyword" style="font-weight:bold">long</span> startMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 循环查询</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < count; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 结束时间</span>
        <span class="keyword" style="font-weight:bold">long</span> endMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 输出结束时间</span>
        System.out.println(endMillis - startMillis);
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * Druid测试
     * <span class="javadoctag">@param</span> testDAO
     * <span class="javadoctag">@param</span> ds
     * <span class="javadoctag">@param</span> count
     * <span class="javadoctag">@throws</span> SQLException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">void</span> queryDruid(TestDAO testDAO, DruidDataSource ds, <span class="keyword" style="font-weight:bold">int</span> count) <span class="keyword" style="font-weight:bold">throws</span> SQLException {
        <span class="comment" style="color:#999988;font-style:italic">// 查询10次以初始化连接池</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">10</span>; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 开始时间</span>
        <span class="keyword" style="font-weight:bold">long</span> startMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 循环查询</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < count; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 结束时间</span>
        <span class="keyword" style="font-weight:bold">long</span> endMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 输出结束时间</span>
        System.out.println(endMillis - startMillis);
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * Tomcat Jdbc Pool测试
     * <span class="javadoctag">@param</span> testDAO
     * <span class="javadoctag">@param</span> ds
     * <span class="javadoctag">@param</span> count
     * <span class="javadoctag">@throws</span> SQLException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> <span class="keyword" style="font-weight:bold">void</span> queryTomcatJDBC(TestDAO testDAO, DataSource ds, <span class="keyword" style="font-weight:bold">int</span> count) <span class="keyword" style="font-weight:bold">throws</span> SQLException {
        <span class="comment" style="color:#999988;font-style:italic">// 查询10次以初始化连接池</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < <span class="number" style="color:#09999;">10</span>; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 开始时间</span>
        <span class="keyword" style="font-weight:bold">long</span> startMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 循环查询</span>
        <span class="keyword" style="font-weight:bold">for</span> (<span class="keyword" style="font-weight:bold">int</span> i = <span class="number" style="color:#09999;">0</span>; i < count; i++) {
            testDAO.query(ds.getConnection());
        }
        <span class="comment" style="color:#999988;font-style:italic">// 结束时间</span>
        <span class="keyword" style="font-weight:bold">long</span> endMillis = System.currentTimeMillis();
        <span class="comment" style="color:#999988;font-style:italic">// 输出结束时间</span>
        System.out.println(endMillis - startMillis);
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * 获取c3p0数据源
     * <span class="javadoctag">@throws</span> PropertyVetoException
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> ComboPooledDataSource getC3p0DataSource() {
        <span class="comment" style="color:#999988;font-style:italic">// 设置参数</span>
        ComboPooledDataSource cpds = <span class="keyword" style="font-weight:bold">new</span> ComboPooledDataSource();
        <span class="keyword" style="font-weight:bold">try</span> {
            cpds.setDriverClass(driver);
        } <span class="keyword" style="font-weight:bold">catch</span> (PropertyVetoException e) {
            e.printStackTrace();
        }
        cpds.setJdbcUrl(jdbcUrl);
        cpds.setUser(user);
        cpds.setPassword(passwd);
        cpds.setInitialPoolSize(initialSize);
        cpds.setMinPoolSize(minPoolSize);
        cpds.setMaxPoolSize(maxPoolSize);
        cpds.setMaxIdleTime(maxIdleTime);
        cpds.setAcquireRetryAttempts(retryAttempts);
        cpds.setAcquireIncrement(acquireIncrement);
        cpds.setTestConnectionOnCheckin(<span class="keyword" style="font-weight:bold">false</span>);
        cpds.setTestConnectionOnCheckout(<span class="keyword" style="font-weight:bold">false</span>);
        <span class="keyword" style="font-weight:bold">return</span> cpds;
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * 获取Druid数据源
     * <span class="javadoctag">@return</span>
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> DruidDataSource getDruidDataSource() {
        DruidDataSource dds = <span class="keyword" style="font-weight:bold">new</span> DruidDataSource();
        dds.setUsername(user);
        dds.setUrl(jdbcUrl);
        dds.setPassword(passwd);
        dds.setDriverClassName(driver);
        dds.setInitialSize(initialSize);
        dds.setMaxActive(maxPoolSize);
        dds.setMaxWait(maxIdleTime);
        dds.setTestWhileIdle(<span class="keyword" style="font-weight:bold">false</span>);
        dds.setTestOnReturn(<span class="keyword" style="font-weight:bold">false</span>);
        dds.setTestOnBorrow(<span class="keyword" style="font-weight:bold">false</span>);
        <span class="keyword" style="font-weight:bold">return</span> dds;
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * 获取Proxool数据源
     * <span class="javadoctag">@return</span>
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> ProxoolDataSource getProxoolDataSource() {
        ProxoolDataSource pds = <span class="keyword" style="font-weight:bold">new</span> ProxoolDataSource();
        pds.setAlias(<span class="string" style="color:#dd1144;">"mysql"</span>);
        pds.setUser(user);
        pds.setPassword(passwd);
        pds.setDriverUrl(jdbcUrl);
        pds.setDriver(driver);
        pds.setMaximumActiveTime(maxIdleTime);
        pds.setMaximumConnectionCount(maxPoolSize);
        pds.setMinimumConnectionCount(initialSize);
        pds.setPrototypeCount(minPoolSize);
        pds.setTestBeforeUse(<span class="keyword" style="font-weight:bold">false</span>);
        pds.setTestAfterUse(<span class="keyword" style="font-weight:bold">false</span>);
        <span class="keyword" style="font-weight:bold">return</span> pds;
    }

    <span class="javadoc" style="color:#999988;font-style:italic">/**
     * 获取Apache tomcat jdbc pool数据源
     * <span class="javadoctag">@return</span>
     */</span>
    <span class="keyword" style="font-weight:bold">public</span> <span class="keyword" style="font-weight:bold">static</span> DataSource getTomcatDataSource() {
        DataSource ds = <span class="keyword" style="font-weight:bold">new</span> DataSource();
        ds.setUrl(jdbcUrl);
        ds.setUsername(user);
        ds.setPassword(passwd);
        ds.setDriverClassName(driver);
        ds.setInitialSize(initialSize);
        ds.setMaxIdle(minPoolSize);
        ds.setMaxActive(maxPoolSize);
        ds.setTestWhileIdle(<span class="keyword" style="font-weight:bold">false</span>);
        ds.setTestOnBorrow(<span class="keyword" style="font-weight:bold">false</span>);
        ds.setTestOnConnect(<span class="keyword" style="font-weight:bold">false</span>);
        ds.setTestOnReturn(<span class="keyword" style="font-weight:bold">false</span>);
        <span class="keyword" style="font-weight:bold">return</span> ds;
    }
}

        3.将测试结果粘贴到excel中，生成图表进行对比，可以很直观的看出几种数据源的性能差异，本文底部有此次测试的结果文档。

        以下就是此次测试结果（本结果只供参考，更具体更准确的测试可以自行进行）：

        1.测试次数为10次时：

        平均用时（单位：毫秒）：

c3p0	36.29
Proxool	41.46
Druid	32.83
Tomcat Jdbc Pool	32.91

        评论：

        但从时间来看Druid >Tomcat Jdbc Pool >c3p0 >Proxool,在测试的最后Proxool出现了一次异常导致前面较好的波动曲线大幅震荡。 
        只做简单的10次查询数据库时从图中观察到稳定性较好的是c3p0 ，他没有出现较高峰值的情况，虽然平均用时并不是最短的。

        2.测试次数为100次时：

  

        平均用时（单位：毫秒）：

c3p0	342.73
Proxool	344.65
Druid	317.15
Tomcat Jdbc Pool	293.23

        评论：

        这里单单从图表中就会很清晰的发现Tomcat Jdbc Pool的性能稳定且耗时最短。Druid与Proxool依旧出现不稳定的情况。

        3.测试次数为500次时：

        平均用时（单位：毫秒）：

c3p0	1420.67
Proxool	1551.69
Druid	1519.61
Tomcat Jdbc Pool	1491.88

        评论：

        此次测试结果比较意外Tomcat Jdbc Pool出现了一次大幅度的波动，Proxool依旧不稳定。在500次查询过程中c3p0表现出来非常好的稳定性及性能。

        4.测试次数为1000次时：
 

        平均用时（单位：毫秒）：

c3p0	3530.26
Proxool	3140.97
Druid	3090.06
Tomcat Jdbc Pool	3164.57