常用的连接池工具

1.DBCP

package com.lpy.jdbc.dbcp;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class Demo1 {
    private static String url = "jdbc:mysql://localhost:3306/lpy?characterEncoding=utf8&useSSL=false&rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "mysql123";
    private static String driverClass = "com.mysql.jdbc.Driver";

    public static void main(String[] args) {
        //test1();
        try {
            Properties prop = new Properties();
            InputStream in = Demo1.class.getResourceAsStream("/jdbc1.properties");
            prop.load(in);
            BasicDataSource bds = BasicDataSourceFactory.createDataSource(prop);
            for (int i = 0; i < 11; i++) {
                Connection conn = bds.getConnection();
                System.out.println(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void test1() {
        //创建连接池对象
        BasicDataSource bds = new BasicDataSource();
        //设置参数
        bds.setUrl(url);
        bds.setUsername(user);
        bds.setPassword(password);
        bds.setDriverClassName(driverClass);
        //初始化
        bds.setInitialSize(5);
        bds.setMaxTotal(10);
        bds.setMaxWaitMillis(3000);
        try {
            for (int i = 0; i < 11; i++) {
                Connection conn = bds.getConnection();
                System.out.println(conn);
                if (i == 3)
                    conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

2.C3P0连接池

需要自己配置c3p0-config.xml

package com.lpy.jdbc.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

public class Demo1 {
    private static String url = "jdbc:mysql://localhost:3306/lpy?characterEncoding=utf8&useSSL=false&rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "mysql123";
    private static String driverClass = "com.mysql.jdbc.Driver";
    public static void main(String[] args) throws Exception{
        //test();
        ComboPooledDataSource cpds=new ComboPooledDataSource();
        for (int i = 0; i <= 10; i++) {
            Connection conn=cpds.getConnection();
            System.out.println(conn);
            if(i==3)
                conn.close();
        }
    }

    private static void test() throws PropertyVetoException, SQLException {
        ComboPooledDataSource cpds = new ComboPooledDataSource();

        cpds.setJdbcUrl(url);
        cpds.setUser(user);
        cpds.setPassword(password);
        cpds.setDriverClass(driverClass);

        cpds.setInitialPoolSize(5);
        cpds.setMaxPoolSize(10);
        cpds.setCheckoutTimeout(3000);

        for (int i = 0; i <= 10; i++) {
            Connection conn=cpds.getConnection();
            System.out.println(conn);
            if(i==3)
                conn.close();
        }
    }
}

config配置如下，注意jdbcurl配置，使用CDATA，因为url中有&这种符号，对于xml解析会产生影响，所以使用CDATA块

<c3p0-config>
   <default-config>
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl"><![CDATA[jdbc:mysql://localhost:3306/lpy?characterEncoding=utf8&useSSL=false&rewriteBatchedStatements=true]]></property>
      <property name="user">root</property>
      <property name="password">mysql123</property>

      <property name="initialPoolSize">5</property>
      <property name="maxPoolSize">10</property>
      <property name="checkoutTimeout">5000</property>
   </default-config>

   <named-config name="mysql">
      <property name="acquireIncrement">50</property>
      <property name="initialPoolSize">100</property>
      <property name="minPoolSize">50</property>
      <property name="maxPoolSize">1000</property><!-- intergalactoApp adopts a different approach to configuring statement caching -->
      <property name="maxStatements">0</property>
      <property name="maxStatementsPerConnection">5</property>
   </named-config>

   <named-config name="oracle">
      <property name="acquireIncrement">50</property>
      <property name="initialPoolSize">100</property>
      <property name="minPoolSize">50</property>
      <property name="maxPoolSize">1000</property><!-- intergalactoApp adopts a different approach to configuring statement caching -->
      <property name="maxStatements">0</property>
      <property name="maxStatementsPerConnection">5</property>
   </named-config>
</c3p0-config>