c3p0,Druid,Tomcat Jdbc Pool,dbcp2,proxoo数据源性能比较

- 日理万妓 2022-08-09 16:38 35阅读 0赞

### 1.数据源jar包 ###

<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid</artifactId>
    			<version>1.0.13</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>c3p0</groupId>
    			<artifactId>c3p0</artifactId>
    			<version>0.9.1.2</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.tomcat</groupId>
    			<artifactId>tomcat-jdbc</artifactId>
    			<version>8.0.28</version>
    		</dependency>
    
    
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-dbcp2</artifactId>
    			<version>2.1.1</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>com.cloudhopper.proxool</groupId>
    			<artifactId>proxool</artifactId>
    			<version>0.9.1</version>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.37</version>
    		</dependency>

## 2.测试接口 ##

package com.zhaochao.demo.Demo;
    
    import java.beans.PropertyVetoException;
    
    import java.io.IOException;
    import java.sql.SQLException;
    
    import org.apache.commons.dbcp2.BasicDataSource;
    import org.apache.tomcat.jdbc.pool.DataSource;
    import org.logicalcobwebs.proxool.ProxoolDataSource;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    public class TestMain {
    
        // 数据库驱动名称
        static String                driver           = "com.mysql.jdbc.Driver";
    
        // 数据库连接地址
        static String                jdbcUrl          = "jdbc:mysql://localhost:3306/datasource";
    
        // 数据库用户名
        static String                user             = "root";
    
        // 数据库密码
        static String                passwd           = "root";
    
        // 连接池初始化大小
        static int                   initialSize      = 5;
    
        // 连接池最小空闲
        static int                   minPoolSize      = 10;
    
        // 连接池最大连接数量
        static int                   maxPoolSize      = 50;
    
        // 最小逐出时间，100秒
        static int                   maxIdleTime      = 100000;
    
        // 连接失败重试次数
        static int                   retryAttempts    = 10;
    
        // 当连接池连接耗尽时获取连接数
        static int                   acquireIncrement = 5;
    
        // c3p0数据源
        static ComboPooledDataSource c3p0DataSource   = getC3p0DataSource();
        
        static ProxoolDataSource     proxoolDataSource=getProxoolDataSource();
        
        // Druid数据源
        static DruidDataSource       druidDataSource  = getDruidDataSource();
    
        // Tomcat Jdbc Pool数据源
        static DataSource            tomcatDataSource = getTomcatDataSource();
    
        // DBCP 数据源
        static BasicDataSource       dbcpDataSource   = getDBCPDataSource();
    
        /**
         * 测试方式： 每种数据源配置信息尽量相同，以求结果更加准确
         * 每种数据源做10次、100次、500次、1000次、2000次、4000次、8000次查询操作 每种查询重复100次，查看100次执行时间的波动图
         * 
         * @param args
         * @throws IOException
         * @throws SQLException
         */
        public static void main(String[] args) throws IOException, SQLException {
    
            TestDAO testDAO = new TestDAO();
            // 查询次数
            int count = 500;
    
            // 循环次数
            int size = 50;
            System.out.println("查询次数为：" + count);
            System.out.println();
            
            System.out.println("==========================proxool 测试开始==========================");
            // 测试c3p0
            for (int i = 0; i < size; i++) {
                queryProxool(testDAO, proxoolDataSource, count);
            }
            System.out.println("==========================proxool 测试结束==========================");
            System.out.println();
            
            
            
            System.out.println("==========================c3p0 测试开始==========================");
            // 测试c3p0
            for (int i = 0; i < size; i++) {
                queryC3p0(testDAO, c3p0DataSource, count);
            }
            System.out.println("==========================c3p0 测试结束==========================");
            System.out.println();
    
            System.out.println("==========================Druid 测试开始==========================");
            // 测试Druid
            for (int i = 0; i < size; i++) {
                queryDruid(testDAO, druidDataSource, count);
            }
            System.out.println("==========================Druid 测试结束==========================");
            System.out.println();
            System.out.println("==========================Tomcat Jdbc Pool 测试开始==========================");
            // 测试Tomcat Jdbc Pool
            for (int i = 0; i < size; i++) {
                queryTomcatJDBC(testDAO, tomcatDataSource, count);
            }
            System.out.println("==========================Tomcat Jdbc Pool 测试结束==========================");
    
            System.out.println("==========================DBCP Jdbc Pool 测试开始==========================");
            // 测试Tomcat Jdbc Pool
            for (int i = 0; i < size; i++) {
                queryDBCPDataSource(testDAO, dbcpDataSource, count);
            }
            System.out.println("==========================DBCP Jdbc Pool 测试结束==========================");
        }
    
        /**
         * c3p0测试
         * 
         * @param testDAO
         * @param ds
         * @param count
         * @throws SQLException
         */
        public static void queryC3p0(TestDAO testDAO, ComboPooledDataSource ds, int count) throws SQLException {
            // 查询10次以初始化连接池
            for (int i = 0; i < 10; i++) {
                testDAO.query(ds.getConnection());
            }
            // 开始时间
            long startMillis = System.currentTimeMillis();
            // 循环查询
            for (int i = 0; i < count; i++) {
                testDAO.query(ds.getConnection());
            }
            // 结束时间
            long endMillis = System.currentTimeMillis();
            // 输出结束时间
            System.out.println(endMillis - startMillis);
        }
        public static void queryProxool(TestDAO testDAO, ProxoolDataSource ds, int count) throws SQLException {
            // 查询10次以初始化连接池
            for (int i = 0; i < 10; i++) {
                testDAO.query(ds.getConnection());
            }
            // 开始时间
            long startMillis = System.currentTimeMillis();
            // 循环查询
            for (int i = 0; i < count; i++) {
                testDAO.query(ds.getConnection());
            }
            // 结束时间
            long endMillis = System.currentTimeMillis();
            // 输出结束时间
            System.out.println(endMillis - startMillis);
        }
    
        /**
         * Proxxool测试
         * 
         * @param testDAO
         * @param ds
         * @param count
         * @throws SQLException
         */
        /*
         * public static void queryProxxool(TestDAO testDAO, ProxoolDataSource ds,
         * int count) throws SQLException { // 查询10次以初始化连接池 for (int i = 0; i < 10;
         * i++) { testDAO.query(ds.getConnection()); } // 开始时间 long startMillis =
         * System.currentTimeMillis(); // 循环查询 for (int i = 0; i < count; i++) {
         * testDAO.query(ds.getConnection()); } // 结束时间 long endMillis =
         * System.currentTimeMillis(); // 输出结束时间 System.out.println(endMillis -
         * startMillis); }
         */
    
        /**
         * Druid测试
         * 
         * @param testDAO
         * @param ds
         * @param count
         * @throws SQLException
         */
        public static void queryDruid(TestDAO testDAO, DruidDataSource ds, int count) throws SQLException {
            // 查询10次以初始化连接池
            for (int i = 0; i < 10; i++) {
                testDAO.query(ds.getConnection());
            }
            // 开始时间
            long startMillis = System.currentTimeMillis();
            // 循环查询
            for (int i = 0; i < count; i++) {
                testDAO.query(ds.getConnection());
            }
            // 结束时间
            long endMillis = System.currentTimeMillis();
            // 输出结束时间
            System.out.println(endMillis - startMillis);
        }
    
        /**
         * Tomcat Jdbc Pool测试
         * 
         * @param testDAO
         * @param ds
         * @param count
         * @throws SQLException
         */
        public static void queryTomcatJDBC(TestDAO testDAO, DataSource ds, int count) throws SQLException {
            // 查询10次以初始化连接池
            for (int i = 0; i < 10; i++) {
                testDAO.query(ds.getConnection());
            }
            // 开始时间
            long startMillis = System.currentTimeMillis();
            // 循环查询
            for (int i = 0; i < count; i++) {
                testDAO.query(ds.getConnection());
            }
            // 结束时间
            long endMillis = System.currentTimeMillis();
            // 输出结束时间
            System.out.println(endMillis - startMillis);
        }
    
        public static void queryDBCPDataSource(TestDAO testDAO, BasicDataSource ds, int count) throws SQLException {
            // 查询10次以初始化连接池
            for (int i = 0; i < 10; i++) {
                testDAO.query(ds.getConnection());
            }
            // 开始时间
            long startMillis = System.currentTimeMillis();
            // 循环查询
            for (int i = 0; i < count; i++) {
                testDAO.query(ds.getConnection());
            }
            // 结束时间
            long endMillis = System.currentTimeMillis();
            // 输出结束时间
            System.out.println(endMillis - startMillis);
        }
    
        /**
         * 获取c3p0数据源
         * 
         * @throws PropertyVetoException
         */
        public static ComboPooledDataSource getC3p0DataSource() {
            // 设置参数
            ComboPooledDataSource cpds = new ComboPooledDataSource();
            try {
                cpds.setDriverClass(driver);
            } catch (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(false);
            cpds.setTestConnectionOnCheckout(false);
            return cpds;
        }
    
        /**
         * 获取Druid数据源
         * 
         * @return
         */
        public static DruidDataSource getDruidDataSource() {
            DruidDataSource dds = new DruidDataSource();
            dds.setUsername(user);
            dds.setUrl(jdbcUrl);
            dds.setPassword(passwd);
            dds.setDriverClassName(driver);
            dds.setInitialSize(initialSize);
            dds.setMaxActive(maxPoolSize);
            dds.setMaxWait(maxIdleTime);
            dds.setTestWhileIdle(false);
            dds.setTestOnReturn(false);
            dds.setTestOnBorrow(false);
            return dds;
        }
    
        /**
         * 获取Apache tomcat jdbc pool数据源
         * 
         * @return
         */
        public static DataSource getTomcatDataSource() {
            DataSource ds = new DataSource();
            ds.setUrl(jdbcUrl);
            ds.setUsername(user);
            ds.setPassword(passwd);
            ds.setDriverClassName(driver);
            ds.setInitialSize(initialSize);
            ds.setMaxIdle(minPoolSize);
            ds.setMaxActive(maxPoolSize);
            ds.setTestWhileIdle(false);
            ds.setTestOnBorrow(false);
            ds.setTestOnConnect(false);
            ds.setTestOnReturn(false);
            return ds;
        }
    
        public static BasicDataSource getDBCPDataSource() {
            BasicDataSource ds = new BasicDataSource();
            ds.setUrl(jdbcUrl);
            ds.setUsername(user);
            ds.setPassword(passwd);
            ds.setDriverClassName(driver);
            ds.setInitialSize(initialSize);
            ds.setMaxIdle(minPoolSize);
            ds.setTestWhileIdle(false);
            ds.setTestOnBorrow(false);
            ds.setTestOnReturn(false);
            return ds;
        }
        public static ProxoolDataSource getProxoolDataSource() {
            ProxoolDataSource ds = new ProxoolDataSource();
            ds.setDriverUrl(jdbcUrl);
            ds.setUser(user);
            ds.setPassword(passwd);
            ds.setDriver(driver);
            return ds;
        }
    }

## 4.测试结果 ##

![Center][]

[Center]: /images/20220731/ae90936374f6401fbaf476d545f8a9a5.png