常见的数据库连接池

所有的数据库连接池实现都要指定sun定义的连接池接口-javax.sql.DataSource

1.DBCP

DBCP连接池是tomcat默认支持的连接池，在tomcat的lib目录下可看见tomcat-dbcp.jar；
如果没有的话，须执行引入commons-dbcp-1.2.2.jar和commons-pool.jar 早期版本可能还需引入commons-collection.jar
简单入门实例：

public class DBCP {
    public static void getConnection() throws Exception {
        // 1. 获取连接池对象

        BasicDataSource source = new BasicDataSource();
        // 2. 设置连接池对象

        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql:///dbutils");
        source.setUsername("root");
        source.setPassword("root");
        source.setDefaultAutoCommit(true);

        source.setDefaultTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        source.setInitialSize(10);
        source.setMaxActive(5);
        source.setMaxIdle(10);
        source.setMinIdle(0);
        source.setMaxWait(5000);

        Connection conn = source.getConnection();
        String sql = "insert into test(name) values(?)";
        PreparedStatement state = conn.prepareStatement(sql);
        state.setString(1, "scoield");
        state.executeUpdate();
        state.close();
        System.out.println("插入数据成功");

    }
    public static void main(String[] args) throws Exception {
        getConnection();

    }
}

上面的代码发现设置参数的方法过多，得一个个的设置，过于繁琐！

配置文件方式代码实现：
dbcp.properties

username=root
password=root
url=jdbc\:mysql\:///dbutils
driverClassName=com.mysql.jdbc.Driver
defaultAutoCommit=true
defaultTransactionIsolation=REPEATABLE_READ
initialSize=10
maxActive=5
maxIdle=5
minIdle=0
maxWait=5000

java代码：

public class DBCP {
    public static void getConnection2() throws Exception{
        Properties prop = new Properties();
        InputStream url = DBCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
        prop.load(url);
        BasicDataSource source = (BasicDataSource) BasicDataSourceFactory.createDataSource(prop);
     Connection conn = source.getConnection();
     String sql = "insert into test(name) values(?)";
        PreparedStatement state = conn.prepareStatement(sql);
        state.setString(1, "scoield1");
        state.executeUpdate();
        state.close();
        System.out.println("插入数据成功");
    }
    public static void main(String[] args) throws Exception {
        getConnection2();

    }
}

2.C3P0
在Hibernate和Spring中默认支持该数据库连接池
需要引入：c3p0-0.9.1.2.jar包，如果报错再引入mchange-commons-0.2.jar

1. 在类路径下编写一个c3p0-config.xml文件

<c3p0-config>
  <!--
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql:///dbutils</property>
    <property name="user">root</property>
    <property name="password">root</property>

    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>
  </default-config>
   -->
   <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///dbutils</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">6</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">10</property>
    </named-config>
</c3p0-config>

2.获取默认的配置：

public static void getConnection1() throws Exception {
        ComboPooledDataSource source = new ComboPooledDataSource();
        Connection conn = source.getConnection();
        String sql = "insert into users (name,address) values (?,?)";
        PreparedStatement state = conn.prepareStatement(sql);
        state.setString(1, "c3p0");
        state.setString(2, "c3p0");
        state.executeUpdate();
        source.close();
        System.out.println("OK");
    }

3.获取指定名的配置：

public static void getConnection2() throws Exception {
        ComboPooledDataSource source = new ComboPooledDataSource("mysql");
        Connection conn = source.getConnection();
        String sql = "insert into users (name,address) values (?,?)";
        PreparedStatement state = conn.prepareStatement(sql);
        state.setString(1, "c3p02");
        state.setString(2, "c3p02");
        state.executeUpdate();
        source.close();
        System.out.println("OK");
    }

总结：连接池技术可以快速的获取数据库连接的重量级资源但是操作数据库依旧比较繁琐……
  解决方案---DBUtils!