数据库连接池& DButil no 15.

数据库连接池介绍：

1. 数据库的连接对象创建工作，比较消耗性能。
2. 一开始现在内存中开辟一块空间（集合），一开始先往池子里面放置 多个连接对象。后面需要连接的话 ，直接从池子里面去。不要去创建连接了。使用完毕，要记得归还连接。确保连接对象能循环利用。
   代码演示：
   取名规范 连接池 一般类名 叫做MyDataSource.java。这是因为sun 公司发布的连接池规范 里面就叫DataSource.
   实现 DataSource.

package com.st.web.util;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class MyDataSource implements DataSource{
	
	@Override
	public Connection getConnection() throws SQLException {
		
		
		
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

整个数据库连接池大体架构：
1.开始创建十个连接

List<Connection> list=new ArrayList<Connection>();
	public MyDataSource() {
		for(int i=0;i<10;i++) {
			Connection connection=JDBCUtil.getConn();
			list.add(connection);
			
		}
		
	}

2.来的程序通过getConnection后去连接

	//移除list里面的第一个
		Connection conn=list.remove(0);		
		return conn;

3.用完之后，使用addBack归还连接.

public   void addBack(Connection conn) {
		list.add(conn);
	}

4.扩容

if (list.size()==0) {
			for(int i=0;i<5;i++) {
				Connection connection=JDBCUtil.getConn();
				list.add(connection);
			}
			
		}

完整代码：

package com.st.web.util;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

/*
 * 这是一个数据库连接池 
 * 一开始往数据库连接池放十个连接
 * 1.开始创建十个连接
 * 
 * 2.来的程序通过getConnection后去连接
 * 
 * 3.用完之后，使用addBack归还连接.
 * 
 * 4.扩容
 * 
 * */

public class MyDataSource implements DataSource{
	
	
	List<Connection> list=new ArrayList<Connection>();
	public MyDataSource() {
		for(int i=0;i<10;i++) {
			Connection connection=JDBCUtil.getConn();
			list.add(connection);
			
		}
		
	}
	
	//该连接池对外公布的获取连接的方法
	@Override
	public Connection getConnection() throws SQLException {
		
		//判断过来的连接池是否用完 用完就在添加5个.
		if (list.size()==0) {
			for(int i=0;i<5;i++) {
				Connection connection=JDBCUtil.getConn();
				list.add(connection);
			}
			
		}
		//移除list里面的第一个
		Connection conn=list.remove(0);		
		return conn;
	}
	
	//退还连接
	public   void addBack(Connection conn) {
		list.add(conn);
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

数据库连接池的简单使用：
建立一个测试类DataSourceTest.java

package com.st.web.util;

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

import org.junit.jupiter.api.Test;

import javafx.css.PseudoClass;

public class DataSourceTest {
	@Test
	public void testdatasource(){
		MyDataSource dataSource=new MyDataSource();
		PreparedStatement ps=null;
		Connection conn=null;
	
		try {
		conn=dataSource.getConnection();
			
			String sql="insert into account values(null, 'xiaoming',500)";
			 ps=conn.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				//关闭资源
				ps.close();
				
			} catch (SQLException e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
			
			//归还连接
			dataSource.addBack(conn);
		}
		
		
	};
	
	
	
	

}

运行结果

自定义数据库连接池出现的问题？

1. 需要额外记住addBack的这个归还方法
2. 单列。
3. 无法面向接口编程

	DataSource dataSource=new MyDataSource();

这个是因为接口里面本身就没有addBack方法 这个是自己定义的

怎么解决？
以addBack为切入点。
解决自定义数据库连接池出现的问题：
由于多了一个addBack方法，所有使用这个连接池的地方，需要额外记住这个方法。并且还不能面向接口编程。
所以 可以修改接口中的那个close方法。原来的Connection对象的close方法，是真的关闭连接。

这里也就是说修改conn.close 方法让他变成归还数据库连接池。

如何扩展某一个方法？

原有的方法逻辑，不是我们想要的。想修改自己的逻辑.

1. 直接改源码 无法实现
2. 继承， 必须知道这个接口的具体是谁，Connection这个是个接口 不知道具体，所以无法实现
3. 使用装饰者模式。可以解决
4. 动态代理。 可以解决 动态代理很灵活 所以相对来说比较复杂

装饰者模式解决：
例子：
1.现建立一个接口interface：
2.实现接口里面的方法
3.实现接口 并且装饰 让程序先走你写的方法
4.创建 装饰实例对象 实现 装饰中自定义的方法
Demo
Water.java

public interface Water {
	void service();

}

Waters.java

public class Waters implements Water{

	@Override
	public void service() {
		// TODO Auto-generated method stub
		System.out.println("服务中....");
		
	}

}

WatersWrap.java

public class WatersWrap implements Water{
	Water water;
	public WatersWrap(Water water) {
		// TODO Auto-generated constructor stub
		this.service();
	}
	@Override
	public void service() {
		// TODO Auto-generated method stub
		
		
		System.out.println("微笑...");
		water.service();
		
	}

}

TestWaterWarp.java

public class TestWaterWrap {

	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		
		/*Water water=new Waters();
		water.service();*/
		
		WatersWarp warp=new WatersWrap(new Waters());
		warp.service();
	}

}

结果：

所以按照这个面向接口的思想来 应该implements Connection. 并且装饰 close这个方法
新建ConnectionWrap.java
让这个类继承Connection

package com.st.web.util;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

public class ConnectionWrap implements Connection{
	
	
	Connection conn=null;
	private List<Connection> list;
	public ConnectionWrap(Connection conn,List <Connection> list) {
		super();
		this.conn = conn;
		this.list=list;
	}

	@Override
	public void close() throws SQLException {
		// TODO Auto-generated method stub
		//conn.close();
		
		System.out.println("有人来归还连接对象了归还之前池子是："+list.size());
		list.add(conn);
		System.out.println("有人来归还连接对象了归还之后池子是："+list.size());
		
	}
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return conn.prepareStatement(sql);
	}

	
	
	
	
	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void abort(Executor executor) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void clearWarnings() throws SQLException {
		// TODO Auto-generated method stub
		
	}

	

	@Override
	public void commit() throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Blob createBlob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Clob createClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public NClob createNClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SQLXML createSQLXML() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean getAutoCommit() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public String getCatalog() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Properties getClientInfo() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getClientInfo(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getHoldability() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public DatabaseMetaData getMetaData() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getNetworkTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public String getSchema() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getTransactionIsolation() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SQLWarning getWarnings() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isClosed() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean isReadOnly() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public boolean isValid(int timeout) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public String nativeSQL(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	

	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void rollback() throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setCatalog(String catalog) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setHoldability(int holdability) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public Savepoint setSavepoint() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setSchema(String schema) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setTransactionIsolation(int level) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		// TODO Auto-generated method stub
		
	}

}

MyDataSource.java

package com.st.web.util;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

/*
 * 这是一个数据库连接池 
 * 一开始往数据库连接池放十个连接
 * 1.开始创建十个连接
 * 
 * 2.来的程序通过getConnection后去连接
 * 
 * 3.用完之后，使用addBack归还连接.
 * 
 * 4.扩容
 * 
 * */

public class MyDataSource implements DataSource{
	
	
	List<Connection> list=new ArrayList<Connection>();
	public MyDataSource() {
		for(int i=0;i<10;i++) {
			Connection connection=JDBCUtil.getConn();
			list.add(connection);
			
		}
		
	}
	
	//该连接池对外公布的获取连接的方法
	@Override
	public Connection getConnection() throws SQLException {
		
		//判断过来的连接池是否用完 用完就在添加5个.
		if (list.size()==0) {
			for(int i=0;i<5;i++) {
				Connection connection=JDBCUtil.getConn();
				list.add(connection);
			}
			
		}
		//移除list里面的第一个
		Connection conn=list.remove(0);		
		
		
		Connection connection=new ConnectionWrap(conn, list);
		return connection;
	}
	
	//退还连接
	public   void addBack(Connection conn) {
		list.add(conn);
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

DataSourceTest.java里面

package com.st.web.util;

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

import org.junit.jupiter.api.Test;

import com.mysql.jdbc.Statement;

import javafx.css.PseudoClass;

public class DataSourceTest {
	@Test
	public void testdatasource(){
		MyDataSource dataSource=new MyDataSource();
		PreparedStatement ps=null;
		Connection conn=null;
	
		try {
		conn=dataSource.getConnection();
			
			String sql="insert into account values(null, 'xiaoming',500)";
			 ps=conn.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				//关闭资源
				ps.close();
				
			} catch (SQLException e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
			
			//归还连接
			//dataSource.addBack(conn);
			JDBCUtil.release((Statement) ps, conn);
		}
		
		
	};
	
	
	
	

}

结果

开源连接池
Java发展至今 早就有一套开源免费的连接池如:

• DBCP
• C3P0

如何使用DBCP？
创建工程导入dbcp支持jar包

DBCPDemo.java

package com.st.web.dbcp;

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

import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Test;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;

public class DBCPDemo {
	@Test
	public void testdbcp () {
		Connection conn=null;
		PreparedStatement ps=null;
	
		
	
		try {
			
			
			
			BasicDataSource dataSource=new BasicDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8");	
			dataSource.setUsername("root");
			dataSource.setPassword("123456");
			
			
			conn=dataSource.getConnection();
			String sql="insert into account values(null, ?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "liu");
			ps.setInt(2, 800);
			ps.executeUpdate();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

运行结果：

上文代码 要用到一些set手动实现。其实还有一种方式：
使用配置文件方式
创建配置文件
dbcpconfig.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8
username=root
password=123456

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

读取配置文件 执行数据库操作

package com.st.web.dbcp;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;

import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;


public class DBCPDemo1 {
	@Test
	public void testdbcp () {
		Connection conn=null;
		PreparedStatement ps=null;
	
	
		
	
		try {
			
			BasicDataSourceFactory factory=new BasicDataSourceFactory();
			Properties properties=new Properties();
			InputStream is=new FileInputStream("src//dbcpconfig.properties");
			properties.load(is);
			DataSource dataSource=factory.createDataSource(properties);
			
			
		//	BasicDataSource dataSource1=new BasicDataSource();
			
			
			conn=dataSource.getConnection();
			String sql="insert into account values(null, ?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "liu");
			ps.setInt(2, 800);
			ps.executeUpdate();
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
			JDBCUtil.release((Statement) ps, conn);
		}
		
	}

}

结果：

C3P0
如何使用C3P0？
创建工程导入dbcp支持jar包

package com.st.web.c3p0;

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

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;

public class C3P0Demo {
	@Test
	public void c3p0test() {
		//创建combopooleddatasource对象
	
		Connection conn=null;
		PreparedStatement ps=null;
		try {
				
			//设置连接账户信息
			ComboPooledDataSource dataSource=new ComboPooledDataSource();
			dataSource.setJdbcUrl("jdbc:mysql://localhost/bank?useUnicode=true&characterEncoding=utf8");
			dataSource.setDriverClass("com.mysql.jdbc.Driver");
			dataSource.setUser("root");
			dataSource.setPassword("123456");
			
			
			conn=dataSource.getConnection();
			String sql="insert into account values(null, ?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "zhanger");
			ps.setInt(2, 800);
			ps.executeUpdate();
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}
		
	}

	}

使用配置文件方式
创建配置文件c3p0-config.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/bank?useUnicode=true&amp;characterEncoding=utf8</property>
    <property name="user">root</property>
    <property name="password">123456</property>
    
    
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</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>
	

代码：

package com.st.web.c3p0;

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

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.Statement;
import com.st.web.util.JDBCUtil;

public class C3P0Demo {
	@Test
	public void c3p0test() {
		//创建combopooleddatasource对象
	
		Connection conn=null;
		PreparedStatement ps=null;
		try {
				
			//设置连接账户信息
			ComboPooledDataSource dataSource=new ComboPooledDataSource();
		
			
			
			conn=dataSource.getConnection();
			String sql="insert into account values(null, ?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "zhanger");
			ps.setInt(2, 800);
			ps.executeUpdate();
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.release(conn, ps);
		}
		
	}

	}

结果：

DButil使用配置相关

数据库连接池 等都有相应的工具 那么jdbcutil也应该有相应的工具类。（包含了各种查询方式）
导入DButil工具类
commons-dbutils-1.4.jar
先创建一个Javaben
Account.java

package com.st.web.ben;

public class Account {
	private int money;
	private String name;
	public int getMoney() {
		return money;
	}
	public void setMoney(int money) {
		this.money = money;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Account [money=" + money + ", name=" + name + "]";
	}
	
	

}

TestDBUtils.java如下：

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

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

import com.itheima.domain.Account;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/*
//针对增加  、 删除 、 修改
queryRunner.update(sql)

//针对查询
queryRunner.query(sql, rsh);*/
public class TestDBUtils {

	
	@Test
	public void testInsert() throws SQLException, InstantiationException, IllegalAccessException{
		
//		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		
		//dbutils 只是帮我们简化了CRUD 的代码， 但是连接的创建以及获取工作。 不在他的考虑范围
		QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
	
		
		//增加
		//queryRunner.update("insert into account values (null , ? , ? )", "aa" ,1000);
		
		//删除
//		queryRunner.update("delete from account where id = ?", 5);
		
		//更新
		//queryRunner.update("update account set money = ? where id = ?", 10000000 , 6);
		
		
		//去执行查询，查询到的数据还是在哪个result里面。 然后调用下面的handle方法，由用户手动去封装。
		/*Account  account =  queryRunner.query("select * from account where id = ?", new ResultSetHandler<Account>(){

			@Override
			public Account handle(ResultSet rs) throws SQLException {
				Account account  =  new Account();
				while(rs.next()){
					String name = rs.getString("name");
					int money = rs.getInt("money");
					
					account.setName(name);
					account.setMoney(money);
				}
				return account;
			}
			 
		 }, 6);
		
		System.out.println(account.toString());
		*/
		
		
		// 通过类的字节码得到该类的实例
		
/*		
		Account a = new Account();
		
		//创建一个类的实例。
		Account a1=  Account.class.newInstance();
		*/
		
		
		//查询单个对象
		Account account = queryRunner.query("select * from account where id = ?", 
				new BeanHandler<Account>(Account.class), 8);
		System.out.println(account.toString());
		
		/*List<Account> list = queryRunner.query("select * from account ",
				new BeanListHandler<Account>(Account.class));
		
		for (Account account : list) {
			System.out.println(account.toString());
		}*/
	}

DButil总结：

1. 导入DButil支持jar包
2. 执行 SQL语句
3. 如果查询 先实例化 在 进行查询。

ResultSetHandler 常用的实现类
以下两个是使用频率最高的

**BeanHandler**,  查询到的单个数据封装成一个对象
**BeanListHandler,** 查询到的多个数据封装 成一个List<对象>

---

ArrayHandler,  查询到的单个数据封装成一个数组
ArrayListHandler,  查询到的多个数据封装成一个集合 ，集合里面的元素是数组。 



MapHandler,  查询到的单个数据封装成一个map
MapListHandler,查询到的多个数据封装成一个集合 ，集合里面的元素是map。 

ColumnListHandler
KeyedHandler
ScalarHandler