数据库连接池c3p0学习

这里只记录c3p0的数据源，不会涉及到其它方面和别的数据库连接池的对比

配置文件主要的实现方式有三种：

1.手写代码去加载一个配置文件

创建一个config.properties文件如下：

driverClass= com.mysql.jdbc.Driver
jdbcUrl = jdbc:mysql://127.0.0.1:3306/soc_db?autoReconnect=true&amp;autoReconnectForPools=true
user = root
password = root
......

然后在java代码中：

 1     public void closeInputStream(InputStream inputStream){
 2         if(inputStream!=null){
 3             try {
 4                 inputStream.close();
 5             } catch (IOException e) {
 6                 e.printStackTrace();
 7             }
 8         }
 9     }
10 
11     public InputStream loadResource(String resource) {
12         InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(resource);
13         if (in == null) {
14             in = PropertiesUtil.class.getResourceAsStream(resource);
15         }
16         return in;
17     }
18 
19     public Properties getProperties(String resource){
20         LOG.debug("[Method]   读取数据库配置信息");
21         Properties properties = new Properties();
22         InputStream in = null;
23         try {
24             in = loadResource(resource);
25             if(in!=null){
26                 properties.load(in);
27             }
28         } catch (IOException e) {
29             LOG.error("load properties error",e);
30         }finally {
31             closeInputStream(in);
32         }
33         LOG.debug(properties.getProperty("url"));
34         return properties;
35     }

1             Properties properties = PropertiesUtil.getInstance().getProperties("/resource/jindun.properties");
2             c3p0DataSource.setDriverClass(properties.getProperty("driverClass"));
3             c3p0DataSource.setDriverClass(properties.getProperty("jdbcUrl"));
4             c3p0DataSource.setDriverClass(properties.getProperty("user"));
5             c3p0DataSource.setDriverClass(properties.getProperty("password"));
6             ......

2.在src下创建一个c3p0.properties文件（固定名字哦）

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://10.30.79.10:3306/test
c3p0.user=root
c3p0.password=root
......

1 ......
2 ComboPooledDataSource c3p0DataSource = new ComboPooledDataSource()
3 ......

这样就可以直接使用了，不需要再去说明。
3.在src下创建一个c3p0-config.xml文件

（1）XML格式更加直观

（2）可以在文件中配置多个数据源

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

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">500</property>

        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>

    </default-config>

    <!-- This app is jindun -->
    <named-config name="jinDunApp">
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://10.30.79.10:3306/HrINetDB?autoReconnect=true&amp;autoReconnectForPools=true</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">500</property>

        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>

    </named-config>

    <!-- This app is Tianji -->
    <named-config name="tianJiApp">
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://10.30.67.95:3306/config_db?autoReconnect=true&amp;autoReconnectForPools=true</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">500</property>

        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>


</c3p0-config>

 1     public static void main(String[] args) {
 2         // 默认配置
 3         ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
 4         // 固定名字配置
 5         ComboPooledDataSource comboPooledDataSource2 = new ComboPooledDataSource("jinDunApp");
 6         // 数据源使用
 7         PreparedStatement psta;
 8         ResultSet rs;
 9         try {
10             // 这个的comboPooledDataSource改comboPooledDataSource2就执行jinDunApp配置了
11             Connection conn = comboPooledDataSource.getConnection();
12             psta = conn.prepareStatement("SELECT empName FROM t_sc_usermanager");
13             rs = psta.executeQuery();
14             while (rs.next() ){
15                 System.out.println(rs.getString(1) + "
");
16             }
17         } catch (SQLException e) {
18             e.printStackTrace();
19         }
20     }

上面就是一个实例。

　　配置的参数列表如下（具体含义可以查看参考资料）：

参考资料：

http://haoran-10.iteye.com/blog/1753332

http://www.mchange.com/projects/c3p0/#c3p0-config.xml