Dbutils 的使用

创建一个java工程   我用的是--myeclipse

---

第一步配置 ｃ3p0-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/jdbc</property>
		<property name="user">root</property>
		<property name="password">password</property>
		
		<property name="acquireIncrement">50</property>
		<property name="initialPoolSize">100</property>
		<property name="minPoolSize">50</property>
		<property name="maxPoolSize">1000</property>
	</default-config>

<!-- 
    <named-config name="oracle"> 
		<property name="driverClass">oracle.jdbc.OracleDriver</property>
		<property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521/orcl</property>
		<property name="user">scott</property>
		<property name="password">tiger</property>
		
		<property name="acquireIncrement">50</property>
		<property name="initialPoolSize">100</property>
		<property name="minPoolSize">50</property>
		<property name="maxPoolSize">1000</property>    
    </named-config>
 -->
</c3p0-config>

---

第二步创建一个ＪＤＢＣ工具类提供数据库　 JDBCUtils.jaava

---

package cn.itcast.jdbc.dbutils.utils;

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

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {

	//创建一个基于配置文件的C3P0数据库连接池
	private static ComboPooledDataSource ds = new ComboPooledDataSource("");
	
	//连接oracle
	//private static ComboPooledDataSource ds = new ComboPooledDataSource("oracle");
	
	public static ComboPooledDataSource getDataSource() {
		return ds;
	}
	
	//维护一个容器，保存当前执行线程上的connection
	public static ThreadLocal<Connection> container = new ThreadLocal<Connection>();
	
	/*
	 * 作用: 
	          1. 得到connection
	 *       2. connection.setAutoCommit(false);
	 *       3. 将该connection放到ThreadLocal上去
	 */
	public static void startTransaction(){
		//从当前执行的线程上去连接
		Connection conn = null;
		try{
			conn = container.get();
			if(conn == null){
				conn = getConnection();
			}
			//开启事务
			conn.setAutoCommit(false);
			
			//放到当前执行的线程上
			container.set(conn);
		}catch(Exception ex){
			throw new RuntimeException(ex);
		}
	}
	
	//利用这个方法提交事务
	public static void commitTransaction(){
		//从当前执行的线程上取出connection
		Connection conn = container.get();
		if(conn == null){
			return;
		}
		try {
			//事务提交
			conn.commit();
			//从当前执行的线程上去掉该连接
			container.remove();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			release(conn, null, null);
		}
	}
	
	public static void rollbackTransaction(){
		//从当前执行的线程上取出connection
		Connection conn = container.get();
		if(conn == null){
			return;
		}
		try {
			//事务回滚
			conn.rollback();
			//从当前执行的线程上去掉该连接
			container.remove();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally{
			release(conn, null, null);
		}
	}
	
	

	public static Connection getConnection(){
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//释放资源
	public static void release(Connection conn,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;
			}
		}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				st = null;
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
		}
	}		
}

---

第三步使用DBUtils的update方法完成insert,update,delete,batch,clob,blob

---

package cn.itcast.jdbc.dbutils.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.SQLException;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import cn.itcast.jdbc.dbutils.utils.JDBCUtils;

/*
 * 使用DBUtils的update方法完成insert,update,delete,batch,clob,blob
 * 步骤:
 * 1. 创建QueryRunner
 *      private QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
 * 2. SQL语句
 * 3. 构造参数值     
 * 4. 调用QueryRunner.update(batch)方法
 */
public class Demo1 {

	//定义DBUtils的QueryRunner
	private QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
	
	@Test
	public void testInsert() throws SQLException{
		String sql = "insert into account values(?,?,?)";
		Object[] params = new Object[]{3,"Mary",3000};
		//调用QueryRunner.update插入数据
		runner.update(sql, params);
	}
	
	@Test
	public void testUpdate() throws SQLException{
		String sql = "update account set name=?,money=? where id=?";
		Object[] params = new Object[]{"Mary123",1000,3};
		runner.update(sql, params);
	}
	
	@Test
	public void testDelete() throws SQLException{
		String sql = "delete  from account where id=?";
		runner.update(sql, 3);
	}
	
	@Test
	public void testBatch() throws SQLException{
		String sql = "insert into account values(?,?,?)";
		//插入10条记录到account表中，需要构造一个二维数组
		Object[][] params = new Object[10][];
		for(int i=0;i<10;i++){
			params[i] = new Object[]{10+i,"Name"+i,1000};
		}
		
		//调用DBUtils的batch执行
		runner.batch(sql, params);
	}
	
	@Test
	public void testClob() throws Exception{
		//使用DBUtils的update方法将一个大文本插入的数据库中
		String sql = "insert into testclob(resume) values(?)";
		
		//使用javax.sql.rowset.serial.SerialClob构造一个大文本对象，
		//该文本对象代表了我们要插入的大文本
		File file = new File("d:\\temp\\resume.txt");
		Reader reader = new FileReader(file);
		
		//构造一个缓存区
		char[] buffer = new char[(int) file.length()];
		reader.read(buffer);
		reader.close();
		
		//构造javax.sql.rowset.serial.SerialClob的对象
		SerialClob clob = new SerialClob(buffer);
		
		//调用QueryRunner.update将大文本插入到表中
		runner.update(sql, clob);
	}
	
	@Test
	public void testBlob() throws Exception{
		//使用DBUtils.update方法将一张照片插入到表中
		String sql = "insert into testblob(image) values(?)";
		
		//使用javax.sql.rowset.serial.SerialBlob类的一个实例代表要插入的照片
		//构造字节数组
		File file = new File("d:\\temp\\wo.jpg");
		InputStream in = new FileInputStream(file);
		
		//字节缓存区，保存照片
		byte[] buffer = new byte[(int) file.length()];
		
		in.read(buffer);
		in.close();
		
		//构造javax.sql.rowset.serial.SerialBlob类的一个实例
		SerialBlob blob = new SerialBlob(buffer);

		//调用QueryRunner.update将大二进制插入到表中
		runner.update(sql, blob);
	}
}

---