c3p0简介及简单案例

c3p0所需jar包和配置文件下载地址：点击打开链接

1C3P0简介

　　C3P0也是开源免费的连接池！C3P0被很多人看好！

2C3P0的使用

　　C3P0中池类是：ComboPooledDataSource。

public void fun1() throws PropertyVetoException, SQLException {
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setJdbcUrl("jdbc:mysql://localhost:3306/mydb1");
ds.setUser("root");
ds.setPassword("123");
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setAcquireIncrement(5);
ds.setInitialPoolSize(20);
ds.setMinPoolSize(2);
ds.setMaxPoolSize(50);
Connection con = ds.getConnection();
System.out.println(con);
con.close();
}

配置文件要求： 

l 文件名称：必须叫c3p0-config.xml

l 文件位置：必须在src下

1.JdbcUtils.java

package cn.itcast.jdbc;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JdbcUtils {
	// 配置文件的默认配置！要求你必须给出c3p0-config.xml！！！
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	/**
	 * 使用连接池返回一个连接对象
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	/**
	 * 返回连接池对象！
	 * @return
	 */
	public static DataSource getDataSource() {
		return dataSource;
	}
}

2.Demo3.java

package cn.itcast.dbutils;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import cn.itcast.jdbc.JdbcUtils;

public class Demo3 {
	@Test
	public void fun1() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "insert into t_stu values(?,?,?,?)";
		Object[] params = {1002, "liSi", 88, "female"};
		
		qr.update(sql, params);
	}
	
	@Test
	public void fun2() throws SQLException {
		// 创建QueryRunner，需要提供数据库连接池对象
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		// 给出sql模板
		String sql = "select * from t_stu where sid=?";
		// 给出参数
		Object[] params = {1001};
		
//		ResultSetHandler<Stu> rsh = new ResultSetHandler<Stu>() {
//
//			@Override
//			public Stu handle(ResultSet rs) throws SQLException {
//				// TODO Auto-generated method stub
//				return null;
//			}
//		};
		// 执行query()方法，需要给出结果集处理器，即ResultSetHandler的实现类对象
		// 我们给的是BeanHandler，它实现了ResultSetHandler
		// 它需要一个类型，然后它会把rs中的数据封装到指定类型的javabean对象中，然后返回javabean
		Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class), params);
		System.out.println(stu);
	}
	
	/**
	 * BeanListHandler的应用，它是多行处理器
	 * 每行对象一个Stu对象！
	 * @throws Exception
	 */
	@Test
	public void fun3() throws Exception {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from t_stu";
		List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class));
		
		System.out.println(stuList);
	}
	
	/**
	 * MapHandler的应用，它是单行处理器，把一行转换成一个Map对象
	 * @throws SQLException 
	 */
	@Test
	public void fun4() throws SQLException  {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from t_stu where sid=?";
		Object[] params = {1001};
		Map map = qr.query(sql, new MapHandler(), params);
		
		System.out.println(map);
	}
	
	/**
	 * MapListHandler，它是多行处理器，把每行都转换成一个Map，即List<Map>
	 * @throws SQLException
	 */
	@Test
	public void fun5() throws SQLException  {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select * from t_stu";
		List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());
		
		System.out.println(mapList);
	}
	
	/**
	 * ScalarHandler，它是单行单列时使用，最为合适！
	 * @throws SQLException
	 */
	@Test
	public void fun6() throws SQLException {
		QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
		String sql = "select count(*) from t_stu";
		/*
		 * Integer、Long、BigInteger
		 */
		Number cnt = (Number)qr.query(sql, new ScalarHandler());
		
		long c = cnt.longValue();
		System.out.println(c);
	}
}

3.c3p0-config.xml(名字不能变)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 这是默认配置信息 -->
	<default-config> 
		<!-- 连接四大参数配置 -->
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb3</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>
	</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>