数据库连接池c3p0学习

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

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

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

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

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

然后在java代码中：

    public void closeInputStream(InputStream inputStream){
        if(inputStream!=null){
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public InputStream loadResource(String resource) {
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(resource);
        if (in == null) {
            in = PropertiesUtil.class.getResourceAsStream(resource);
        }
        return in;
    }

    public Properties getProperties(String resource){
        LOG.debug("[Method]   读取数据库配置信息");
        Properties properties = new Properties();
        InputStream in = null;
        try {
            in = loadResource(resource);
            if(in!=null){
                properties.load(in);
            }
        } catch (IOException e) {
            LOG.error("load properties error",e);
        }finally {
            closeInputStream(in);
        }
        LOG.debug(properties.getProperty("url"));
        return properties;
    }

            Properties properties = PropertiesUtil.getInstance().getProperties("/resource/jindun.properties");
            c3p0DataSource.setDriverClass(properties.getProperty("driverClass"));
            c3p0DataSource.setDriverClass(properties.getProperty("jdbcUrl"));
            c3p0DataSource.setDriverClass(properties.getProperty("user"));
            c3p0DataSource.setDriverClass(properties.getProperty("password"));
            ......

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

......
ComboPooledDataSource c3p0DataSource = new ComboPooledDataSource()
......

这样就可以直接使用了，不需要再去说明。
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>

    public static void main(String[] args) {
        // 默认配置
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        // 固定名字配置
        ComboPooledDataSource comboPooledDataSource2 = new ComboPooledDataSource("jinDunApp");
        // 数据源使用
        PreparedStatement psta;
        ResultSet rs;
        try {
            // 这个的comboPooledDataSource改comboPooledDataSource2就执行jinDunApp配置了
            Connection conn = comboPooledDataSource.getConnection();
            psta = conn.prepareStatement("SELECT empName FROM t_sc_usermanager");
            rs = psta.executeQuery();
            while (rs.next() ){
                System.out.println(rs.getString(1) + "\n");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

上面就是一个实例。

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

参考资料：

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

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