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

来源：互联网发布：java后台权限管理系统编辑：程序博客网时间：2024/05/18 00:55

这次所要做的测试是比较几种我们常用的数据库连接池的性能，他们分别是：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次时：