数据库连接池-DBUtils

第三方操作数据库包专门接收DataSource－dbutils

Dbutils
操作数据第三方包。依赖数据源DataSource（DBCP|C3p0）。
QueryRunner – 接收DataSource｜Connection，查询数据删除修改操作。返回结果。
ResultSetHandler – 结果集句柄，将结果数据封装成程序所需要的数据类型Map,List,Bean。

第一步：创建一个新的java项目

第二步：导入包

Mysql.jar
Dbcp.jar – 连接池的包。
Dbtutil.jar

第三步：创建dataSource数据源

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db909?characterEncoding=UTf8
username=root
password=1234
maxActive=5

工厂类创建一个唯一的dataSorce
package cn.itcast.utils;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DataSourceUtils {
	private static DataSource ds;
	static{
		try{
			//读取资源文件
			InputStream in = DataSourceUtils.class
								.getClassLoader().getResourceAsStream("dbcp.properties");
			//读取资源对象
			Properties prop = new Properties();
			prop.load(in);
			//创建ds
			ds = new BasicDataSourceFactory().createDataSource(prop);
		}catch(Exception e){
			throw new RuntimeException(e.getMessage(),e);
		}
	}
	//返回整个池对象
	public static DataSource getDatasSource(){
		return ds;
	}
}

第四步：开始测试dbutils的使用

Queryrunner.query　只可以执行select语句。
Queryrunner.update只可以接收update,delte,insert语句。

@Test
	public void insert1() {
		QueryRunner run=new QueryRunner(DataSourceUtils.getDatasSource());
		String sql="insert into users values ('u001','张三','88')";
		try {
			run.update(sql);
		} catch (SQLException e) {
		throw new RuntimeException(e.getMessage(),e);
		}
	}

// 接收?

	@Test
	public void insert2() throws Exception {
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "insert into users values(?,?,?)";
		run.update(sql, "U003", "王五", "7777");
	}

// 删除
	@Test
	public void del1() throws Exception {
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "delete from users where name=?";
		int len = run.update(sql, "李四");
		System.err.println(len);
	}

//	修改
	@Test
	public void udpate1() throws Exception{
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "update users set name=? where id=?";
		run.update(sql,"赵'七","U001");
	}

查询：

查询时除了使用queryrunner还要使用别一个核心类resultsethandler：结果集的句本周　封装结果到List,Map,Bean。

org.apache.commons.dbutils 
Interface ResultSetHandler<T>
All Known Implementing Classes: 
AbstractKeyedHandler, AbstractListHandler, ArrayHandler, ArrayListHandler, BeanHandler, BeanListHandler, ColumnListHandler, KeyedHandler, MapHandler, MapListHandler, ScalarHandler 

abstaract类不需要讲解

ArrayHandler,　　－　用于查询一行唯一的数据。Object[]

无论sql语句返回多少行 ArrayHandler至多返回一行结果集

@Test
	public void query1() throws SQLException {
		QueryRunner run = new QueryRunner(DataSourceUtils.getDatasSource());
		String sqlString="select * from users";
		ArrayHandler arrayHandler=new ArrayHandler();
		 Object[] ooObjects=	run.query(sqlString, arrayHandler);
		for (Object object : ooObjects) {
			System.err.println(object);
		}
	}

ArrayListHandler, - 将所有的结果封装成List<Object[]>

@Test
	public void query2() throws SQLException {
		QueryRunner run = new QueryRunner(DataSourceUtils.getDatasSource());
		String sqlString="select * from users";
		List<Object []> list=	run.query(sqlString, new ArrayListHandler());
		for (Object[] objects : list) {
			for (Object object : objects) {
				System.err.print(object+"\t");
			}
			System.err.println();
			
		}
	}

BeanHandler,(掌握) 　－　返回一个 Bean，结果封装到Bean。只查询第一行。适合根据id查询唯一的一行。

//封装成唯一的一个bean
		@Test
		public void query3() throws Exception{
			QueryRunner run = new QueryRunner(getDatasSource());
			String sql = "select * from users where id=?";
			User user = 
					run.query(sql,new BeanHandler<User>(User.class),"U003");
			System.err.println(user);
		}

BeanListHandler,(掌握)－　返回List<Bean>所有行，都封装到List，适合于查询多个结果

/**
	 * 查询返回List< Bean>
	 * @throws Exception
	 */
	@Test
	public void query4() throws Exception{
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "select * from users";
		List<User> us = 
				run.query(sql,new BeanListHandler<User>(User.class));
		for(User u:us){
			System.err.println(u);
		}
		
	}

ColumnListHandler,　－　查询一个字段时就可以使用这种方式 select name from table;

@Test
	public void query5() throws SQLException{
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "select * from users";
		List<Object> list = run.query(sql, new ColumnListHandler(2));
		for(Object o:list){
			System.err.println(o);
		}

	}

KeyedHandler --不太常用

@Test
	public void query6() throws Exception {
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "select * from users";
		Map<Object, Map<String, Object>> mm = run.query(sql, new KeyedHandler(
				"id"));
		System.err.println(mm);
		Iterator it = mm.keySet().iterator();
		while (it.hasNext()) {
			Map m1 = mm.get(it.next());
			System.err.println(m1.get("id") + "," + m1.get("name") + ","
					+ m1.get("pwd"));
		}

	}

{u001={id=u001, age=88, name=赵'七}, U003={id=U003, age=7777, name=王五}}
u001,赵'七,null
U003,王五,null

 MapHandler(掌握)　与 BeanHandler应，只返回第一行，封装成Map类型　－　也适合于查询一个结果集的
适合于关联查询，且没有匹配的Bean。

// 返回Map一行
	@Test
	public void query7() throws Exception {
		QueryRunner run = new QueryRunner(getDatasSource());
		String sqlString="select * from users";
		Map<String, Object>  mm= run.query(sqlString, new MapHandler());
		System.err.println(mm);
	}

//返回Map一行
		@Test
		public void query8() throws Exception{
			QueryRunner run = new QueryRunner(getDatasSource());
			String sql = "SELECT u.name as uname,c.name as cname"+
						 " FROM users u INNER JOIN contacts c ON u.id=c.uid where u.id='U001'";
			System.err.println(sql);
			Map<String,Object> mm = run.query(sql,new MapHandler());
			System.err.println(mm);
		}

此处注意java操作mysql默认不支持别名

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db909?useUnicode=true&characterEncoding=UTF8&useOldAliasMetadataBehavior=true
username=root
password= 
maxActive=3

MapListHandler, (掌握)　－　

//返回Map多行
		@Test
		public void query9() throws Exception{
			QueryRunner run = new QueryRunner(getDatasSource());
			String sql = "SELECT u.name as uname,c.name as cname"+
						 " FROM users u INNER JOIN contacts c ON u.id=c.uid";
			System.err.println(sql);
			List<Map<String,Object>> mm = run.query(sql,new MapListHandler());
			System.err.println(mm);
		}

ScalarHandler – 标量- 用于select count(1)|sum|avg|max …一些聚合函数

@Test
	public void query10() throws Exception {
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "select count(1) from users";//用1速度快
		Object object = run.query(sql, new ScalarHandler());
		System.err.println(object);
	}

当Bean与表字段不能对应时

用别名机制

手工进行封装：

	@Test
	public void query11() throws Exception{
		QueryRunner run = new QueryRunner(getDatasSource());
		String sql = "select * from contacts";
		List<Contact> cs =
				run.query(sql, 
				new ResultSetHandler<List<Contact>>(){
					@Override
					public List<Contact> handle(ResultSet rs)
							throws SQLException {
						List<Contact> list = new ArrayList<Contact>();
						while(rs.next()){
							Contact c = new Contact();
							c.setCid(rs.getString("name"));
							c.setCname(rs.getString("id"));
							c.setCtel(rs.getString("tel"));
							list.add(c);
						}
						return list;
					}
				});
		System.err.println(">>>:"+cs);
	}

总结：

１：包装HttpServletRequest。对getParaqmgermap增强。
　　什么是包装：
A extends B{
Privet B b;
A(B b){
This.b=b;
}
}
２：用dbcp连接数据库，并在一个项目中维护一个唯一的dataSource。
工厂类，一个datasource的成员静态。
　　Static代码块中读取配置文件连接数据库。创建datasource。
　　　　提供一个工厂方法返回整个池对象。
3：dbutils的所有查询方法
所有ResultSetHandler的子类做一次。
MapHandler
MapListHandler
BeanHandler
BeanListHandler
ScalerHandler