（18）JDBC连接池技术（二部分：C3P0，DBCP）

1 何为连接池

连接池是用来管理Conection的，前面的博客中使用JDBC时每次都需要获取连接，这其实对数据库的资源消耗较大。有了连接池，我们就不需要自己来创建Connecton，而是通过池来获取Connection对象，用完了再还回去。目前两种连接池较为常见：C3P0 和 DBCP。

2 C3P0连接池

2.1 导入jar包

c3p0-0.9.1.2.jar

2.2 配置文件

名称：c3p0-config.xml

位置：src下

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

	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///web09</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>

	<named-config name="itheima">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///web09</property>
		<property name="user">root</property>
		<property name="password">root</property>
	</named-config>


</c3p0-config>

2.3 C3P0工具类

package cn.itheima.jdbc.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	public static DataSource getDataSource(){
		return dataSource;
	}
	public static Connection getConnection(){
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

2.4 测试

package cn.itheima.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import cn.itheima.jdbc.utils.C3P0Utils;
import cn.itheima.jdbc.utils.JDBCUtils_v3;

public class TestC3P0 {
	@Test
	public void testAddUser1(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			//2.从池子中获取连接
			conn = C3P0Utils.getConnection();
			
			String sql = "INSERT INTO USER VALUES(?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 7);
			pstmt.setString(2, "韩某某");
			int rows = pstmt.executeUpdate();
			if(rows > 0){
				System.out.println("添加成功");
			}else{
				System.out.println("添加失败");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally{
			JDBCUtils_v3.release(conn, pstmt, null);
	}
	}
	
	
	
	
	/**
	 * 添加用户
	 * 使用未改造的connection
	 */
	@Test
	public void testAddUser(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		//1.创建c3p0连接池对象
		ComboPooledDataSource dataSource = new ComboPooledDataSource();//加载默认配置
		//ComboPooledDataSource dataSource = new ComboPooledDataSource("itheima");//加载named-config配置
		
		try {
			//2.从池子中获取连接
			conn = dataSource.getConnection();
			
			String sql = "INSERT INTO USER VALUES(?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, 7);
			pstmt.setString(2, "韩某某");
			int rows = pstmt.executeUpdate();
			if(rows > 0){
				System.out.println("添加成功");
			}else{
				System.out.println("添加失败");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally{
			JDBCUtils_v3.release(conn, pstmt, null);
	}
	}
}

3 DBCP连接池

3.1 导入jar包

commons-dbcp-1.4.jar

3.2 书写配置文件

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web09?useUnicode=true&characterEncoding=utf8
username=root
password=root

3.3 DBCP工具类

package cn.itheima.jdbc.utils;


import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtils {
	private static DataSource dataSource;
	static{
		try {
			//1.加载找properties文件输入流
			InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");
			//2.加载输入流
			Properties props = new Properties();
			props.load(is);
			//3.创建数据源
			dataSource = BasicDataSourceFactory.createDataSource(props);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public static DataSource getDataSource(){
		return dataSource;
	}
	
	public static Connection getConnection(){
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

3.4 测试

package cn.itheima.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import cn.itheima.jdbc.utils.DBCPUtils;

public class TestDBCP {

	@Test
	public void testUpdateUserById(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DBCPUtils.getConnection();
			String sql ="UPDATE USER SET NAME=? WHERE id=?;";
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(1, "刘某某");
			pstmt.setInt(2, 2);
			int rows = pstmt.executeUpdate();
			if(rows>0){
				System.out.println("更新成功!");
			}else{
				System.out.println("更新失败!");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}