数据库连接池C3P0&DBUtils结合使用，纯属个人学习笔记！

一、数据库连接池的产生原因

1、如果每次访问都走一次数据库的连接对象创建工作，比较消耗性能。

2、为了更好的满足日常中的高并发访问，提前在内存开辟一个空间，用来储存预先创建的一定数量的数据库连接，后续的访问直接从池子里面拿取连接，使用完归还即可

二、开源数据库连接池C3P0

  常见的开源数据库连接池有DBCP和C3P0,这里记录的是C3P0简单使用。

使用前先导入相对应的Jar包
创建c3p0-config.xml文件，在里面定义默认的连接配置信息，名字不能修改！

连接代码示例

package com.gzgs.C3P0;

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

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class TestDemo {
	@Test
	public void test() {
		Connection conn=null;
		PreparedStatement ps = null;
		ComboPooledDataSource comboPooledDataSource=null;
		try {
			comboPooledDataSource=new ComboPooledDataSource();
			conn=comboPooledDataSource.getConnection();
			String sql="insert into stu values(?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "张三");
			ps.setString(2, "1234");
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			comboPooledDataSource.close();
		}
	}
}

XML文件内容

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

	<!-- default-config 默认的配置，  -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost/student</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    
    
    <property name="initialPoolSize">1</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">1</property>
    <property name="maxStatements">200</property>
  </default-config>
  
   <!-- This app is massive! -->
  <named-config name="oracle"> 
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>

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

    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe"> 
      <property name="acquireIncrement">1</property>
      <property name="initialPoolSize">1</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>
      <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
  </named-config>

 
</c3p0-config>
	

三、DBUtils

  DBUtils是一个方便我们对数据库进行CRUD的jar包，它只能帮我们简化了CRUD 的代码， 但是连接的创建以及获取工作。 不在他的考虑范围，所以我们可以将它和数据库连接池结合使用。

示例代码

---------------test代码----------------------

package com.gzgs.dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

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

import com.gzgs.domain.Student;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class TestDBUtils {
	//测试增删改
	@Test
	public void test() {
		Connection conn=null;
		PreparedStatement ps = null;
		ComboPooledDataSource comboPooledDataSource=null;
	
		try {
			comboPooledDataSource=new ComboPooledDataSource();
			QueryRunner queryRunner=new QueryRunner(comboPooledDataSource);
			//插入
			queryRunner.update("insert into stu values(?,?)", "李五四","789");
			//删除
			queryRunner.update("delete from stu where username=?","李五四");
			
			//修改
			queryRunner.update("update stu set password=? where username=?","133","张三");
		
			
			//查询---查询单个对象
			Student stu = queryRunner.query("select * from stu where username=?", new BeanHandler<Student>(Student.class),"张三");
			//查询---查询多个对象
			System.out.println(stu);
			
			//查询多个学生
			List<Student> list = queryRunner.query("select * from stu", new BeanListHandler<Student>(Student.class));
			for (Student student : list) {
				System.out.println(student);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			comboPooledDataSource.close();
		}
	}
}

--------------------C3P0配置文件--------------------

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

	<!-- default-config 默认的配置，  -->
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost/student</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    
    
    <property name="initialPoolSize">1</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">1</property>
    <property name="maxStatements">200</property>
  </default-config>
  
   <!-- This app is massive! -->
  <named-config name="oracle"> 
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>

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

    <!-- he's important, but there's only one of him -->
    <user-overrides user="master-of-the-universe"> 
      <property name="acquireIncrement">1</property>
      <property name="initialPoolSize">1</property>
      <property name="minPoolSize">1</property>
      <property name="maxPoolSize">5</property>
      <property name="maxStatementsPerConnection">50</property>
    </user-overrides>
  </named-config>

 
</c3p0-config>
	

------------------demain---------------------------

package com.gzgs.domain;

public class Student {
	private String username;
	private String password;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "Student [username=" + username + ", password=" + password + "]";
	}
	
}