JDBC学习笔记—数据库连接池（DBCP和C3P0）

DBCP数据库连接池用法

第一步：创建dbcp.properties文件并写入配置

#<!-- 连接设置 -->
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://172.0.0.1:1433;DatabaseName=MyDB;
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#<!-- 最大连接数量 -->
initialSize=10
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 60000毫秒/1000等于60秒 -->
maxWait=60000

第二步：读取dbcp.properties文件并获得连接

public class DBCP_Demo {
    @Test
    public void test() {
        //读取dbcp.properties文件
        InputStream in = DBCP_Demo.class.getClassLoader().getResourceAsStream("dbcp.properties");
        Properties prop = new Properties();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //加载dbcp.properties文件
            prop.load(in);
            //获得数据源
            DataSource ds = BasicDataSourceFactory.createDataSource(prop);
            //获得连接
            Connection conn = ds.getConnection();
            ps = conn.prepareStatement("select * from person");
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

注意：通过DBCP获取的Connection不需要手动关闭

C3P0数据库连接池用法

第一步：创建c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>

    <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>

    <user-overrides user="test-user">
      <property name="maxPoolSize">10</property>
      <property name="minPoolSize">1</property>
      <property name="maxStatements">0</property>
    </user-overrides>

  </default-config>

  <!-- This app is massive! -->
  <named-config name="intergalactoApp"> 
    <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>

    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe"> 
      <property name="acquireIncrement">1</property>
      <property name="initialPoolSize">1</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>
      <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
  </named-config>
</c3p0-config>

第二步：获得连接

public class C3P0_Demo {

@Test
public void test1() {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        //注册驱动
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //获得非池化的数据源
        DataSource unpoolds = DataSources.unpooledDataSource("jdbc:sqlserver://172.0.0.1:1433;DatabaseName=MyDB;","root","root");
        //把非池的数据源转换为池的数据源
        DataSource poolds =DataSources.pooledDataSource(unpoolds);
        //获得连接
        Connection conn = poolds.getConnection();
        ps = conn.prepareStatement("select * from person where isvalid=1");
        rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2));
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

}
注意：通过C3P0获取的Connection不需要手动关闭