java 数据库连接池之dbcp与c3p0使用

DBCP

导入jar包

使用dbcp需要导入以下jar包，版本可以不同，在Apache Commons官网下载即可
commons-dbcp2-2.7.0.jar
commons-pool2-2.3.jar
commons-logging-1.2.jar

dbcp的使用

设置DataSource

package com.mypackage.pool.dbcp;

import org.apache.commons.dbcp2.BasicDataSource;

import javax.sql.DataSource;
import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBCPTest {
    public static void main(String[] args) {
        //设置数据库的连接池资源，在之后操作的时候只需要从池中获取即可
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/demo");//注意改成自己的数据库，这里是demo
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        //dataSource

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
        	//从连接池中获取连接
            connection = dataSource.getConnection();
            
            String sql = "select * from emp";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                System.out.println(resultSet.getString("ename"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
            	//关闭连接
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

配置dbcp.properties
在当前项目的src目录下可以配置dbcp.properties配置连接池的各项属性

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo
username=root
password=123456
initialSize=2              //连接池建立时创建的连接的数量
maxActive=15          //连接池同一时间内最多能够分配的活动连接的数量
maxIdle=2                //在其他连接没有被释放的情况下，连接池中最多能够保留的闲置连接
minIdle=1                //在没有其他连接被创建的情况下，连接池中最少可以保留的闲置连接
maxWait=30000      //当遇到请求，而连接池中没有连接可以被分配，连接池最大等待时间，超过这个时间将会抛出一个异常。为-1时，将会无限期等待。

之后便可用如下方法配置加载dataSource

Properties properties=new Properties();
properties.load(new FileReader("dbcp.properties"));//从文件中读取设置值
DataSource dataSource=BasicDataSourceFactory.createDataSource(properties);//设置数据库连接池
Connection conn=dataSource.getConnection();//从连接池中获取连接

---

C3P0

导入jar包
jar包可以在github、c3p0官网、maven上下载，jar包放在文件夹的lib目录下
lib/c3p0-0.9.4.jar
lib/mchange-commons-java-0.2.15.jar

c3p0使用

设置DataSource

package com.mypackage.pool.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3P0Test {

    public static Connection connection;
    public static ComboPooledDataSource dataSource;

    public static void getConnection(){
        dataSource = new ComboPooledDataSource();

    }

    public static void queryData(){
        try {
            connection = dataSource.getConnection();
            String sql = "select * from emp";
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("ename"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }


    public static void main(String[] args) throws Exception {
        /**
         * 直接在类的方法中设置连接的参数，一般不使用，不太建议，最好使用配置文件
         */
//        ComboPooledDataSource cpds = new ComboPooledDataSource();
//        cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
//        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/demo" );
//        cpds.setUser("root");
//        cpds.setPassword("123456");
//        Connection connection = cpds.getConnection();
//        System.out.println(connection);
//        connection.close();
        getConnection();
        queryData();
    }
}

配置c3p0.properties或c3p0-config.xml
在当前项目src目录下配置c3p0.properties 或 c3p0-config.xml其中一个文件即可，之后无需用上面代码的方式设置数据源参数

c3p0.properties

#JDBC具备自己的规范，在JDBC4之前是必须要填写驱动名称的，但是之后版本不需要填写
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/demo
c3p0.user=root
c3p0.password=123456

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/demo5</property>
        <property name="user">root</property>
        <property name="password">123456</property>
    </default-config>
</c3p0-config>

---

获取DataSource的其他方式

通过DataSource类的静态工厂类获取DataSource

package com.mypackage.pool.c3p0;

import com.mchange.v2.c3p0.DataSources;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class C3p0Test2 {
    public static void main(String[] args) throws SQLException {
    	//静态工厂获取DataSource
        DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost:3306/demo",
                "root",
                "123456");
//        DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled );
		//通过Map配置DataSource的参数
        Map overrides = new HashMap();
        overrides.put("maxStatements", "200");         //Stringified property values work
        overrides.put("maxPoolSize", new Integer(50)); //"boxed primitives" also work
        DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled ,overrides);
        Connection connection = ds_pooled.getConnection();
        System.out.println(connection);
        connection.close();
    }
}

自定义DataSource

package com.mypackage.pool.c3p0;

import com.mchange.v2.c3p0.DataSources;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class C3p0Test2 {
    public static void main(String[] args) throws SQLException {
        DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost:3306/demo",
                "root",
                "123456");
//        DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled );
        Map overrides = new HashMap();
        overrides.put("maxStatements", "200");         //Stringified property values work
        overrides.put("maxPoolSize", new Integer(50)); //"boxed primitives" also work
        DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled ,overrides);
        Connection connection = ds_pooled.getConnection();
        System.out.println(connection);
        connection.close();
    }
}

---

最后提供一个常用的连接池xml配置

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 这是默认配置信息 -->
	<default-config>
		<!-- 连接四大参数配置注意更改为自己的数据库和端口号 -->
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/demo</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<!-- 池参数配置 -->
		<property name="acquireIncrement">3</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">10</property>
	</default-config>
	
	<!-- 专门为oracle提供的配置信息 -->
	<named-config name="oracle-config">
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="user">root</property>
	<property name="password">123</property>
	<property name="acquireIncrement">3</property>
	<property name="initialPoolSize">10</property>
	<property name="minPoolSize">2</property>
	<property name="maxPoolSize">10</property>
	</named-config>
</c3p0-config>