JDBC编程----DBUtils的使用

DBUtils

0、简单介绍

  给QueryRunner一个连接池(DataSourse),它自己会自动的从连接池中获取一个连接

1、DBUtils的使用方式如下：

package pack05_dbutils;

import java.sql.SQLException;

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

/*
 * 工具类:DBUtils
 * QueryRunner
 *  构造方法:
 *   public QueryRunner();
 *   public QueryRunner(DataSource);  //有参构造需要传入的是连接池
 *  成员方法 :
 *  update(String sql, Object... params) :除了select之外
 *  query(String sql, ResultSetHandler<T> rsh, Object... params) :select
 */
public class Demo01DBUtils {
	
	//向表中添加一条数据
	@Test
	public void insert() throws SQLException{
		//1:创建QueryRunner对象,使用有参构造
		//你给了QueryRunner一个连接池,它自己会自动的从连接池中获取一个连接
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//2:执行sql
		int rows = qr.update("insert into category values(NULL,?)", "水果");
		System.out.println(rows);
		
		//--------------------------------------
//		new QueryRunner(C3P0Utils.getDataSource()).update("insert into category values(NULL,?)", "水果");
		
	}
	
	@Test
	public void update() throws SQLException{
		QueryRunner qr = new  QueryRunner(C3P0Utils.getDataSource());
		int rows = qr.update("update category set cname=? where cid = ?", "果果",4);
		System.out.println(rows);
	}
}

此时C3P0Utils需要有一个提供连接池的方法

package pack05_dbutils;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	public static Connection getConnection() throws SQLException{
		return dataSource.getConnection();
	}
	
	public static DataSource getDataSource(){
		return dataSource;
	}
	//4:释放资源
	public static void close(ResultSet rs, Statement stat, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close(); //这里的close不是把连接断开,而是把连接重新放回连接池
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

2、DBUTils中的query方法

package pack05_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.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
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;

/*
 * DBUtil提供的工具类:
 *  query(String sql, ResultSetHandler<T> rsh, Object... params) :select
 *  
 *  操作一行
 *  	ArrayHandler      : 数组   Obejct[]
 *      BeanHandler       : 对象   Object   !!!!!!!!!!!!!!!
 *      MapHandler        : Map集合   Map<String,Object>
 *  操作所有行
 *      ArrayListHandler: List集合   List<Obejct[]>
 *      BeanListHandler : List集合   List<Object> !!!!!!!!!!!!!!!!!!
 *      MapListHandler  : List集合   List<Map<String,Object>>
 *  操作一列
 *     ColumnListHandler: List集合　List<Object>
 *  操作单值
 *    ScalarHandler  :    Object对象  Object
 */
public class Demo02DBUtilsQuery {
	
	//8:ScalarHandler
	//可以获取单值:sum(),count()
	@Test
	public void func8() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		Object object = qr.query("select sum(price) 's' ,count(*) 'c'from product", new ScalarHandler("c"));
		
		System.out.println(object);
	}
	
	//7:MapListHander
	//将负责将结果集中每一行的数据放入Map集合
	//Map多了,存入集合: List<Map<String,Object>>
	@Test
	public void func7() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		List<Map<String, Object>> list = qr.query("select * from product", new MapListHandler());
		for (Map<String, Object> map : list) {
			System.out.println(map);
		}
	}
	
	
	//6:MapHandler
	//将负责将结果集中第一行的数据放入Map集合
	//Map键:列的名字
	//Map值:列的值
	@Test
	public void func6() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		Map<String, Object> map = qr.query("select * from product", new MapHandler());
		System.out.println(map);
	}
	
	//5:ColumnListHandler
	//将某一列的数据存入集合
	@Test
	public void func5() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//可以获取某一列或多列的值
		List<Object> list = qr.query("select * from product where pid = 6", new ColumnListHandler("pname"));
		for (Object object : list) {
			System.out.println(object);
		}
	}
	
	//4:BeanListHandler
	//负责将结果集中每一行的数据放入Java Ｂｅａｎ对象
	//多个Java Bean对象存入集合:List<Product>
	@Test
	public void func4() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		List<Product> list = qr.query("select * from product", new BeanListHandler<Product>(Product.class));
		for (Product product : list) {
			System.out.println(product);
		}
	}
	
	
	//3:BeanHandler
	//负责将结果集中第一行的数据放入Java Ｂｅａｎ对象
	@Test
	public void func3() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		Product p = qr.query("select * from product", new BeanHandler<Product>(Product.class));
		System.out.println(p);
	}
	
	
	
	//2:ArrayListHandler
	//负责将结果集中每一行的数据放入数组
	//将多个数组将入集合中:List<Object[]>
	@Test
	public void func2() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		List<Object[]> list = qr.query("select * from product", new ArrayListHandler());
		for (Object[] objects : list) {
			for (Object object : objects) {
				System.out.print(object+"\t");
			}
			System.out.println();
		}
		
	}
	//1:ArrayHandler:
	//负责将结果集中第一行的数据放入数组
	@Test
	public void func1() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		//	Object[] array = {1,"联想",5000,1};
		Object[] array = qr.query("select * from product", new ArrayHandler());
		for (Object object : array) {
			System.out.println(object);
		}
	}
}