数据库连接池-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.Driverurl=jdbc:mysql:///db909?characterEncoding=UTf8username=rootpassword=1234maxActive=5工厂类创建一个唯一的dataSorcepackage 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);//创建dsds = 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语句。

@Testpublic 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);}}

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

// 删除@Testpublic 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);}

//修改@Testpublic 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至多返回一行结果集

@Testpublic 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[]>

@Testpublic 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@Testpublic 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 */@Testpublic 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;

@Testpublic 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 --不太常用

@Testpublic 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,赵'七,nullU003,王五,null

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

// 返回Map一行@Testpublic 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一行@Testpublic 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多行@Testpublic 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 …一些聚合函数

@Testpublic 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与表字段不能对应时

用别名机制

手工进行封装：

@Testpublic void query11() throws Exception{QueryRunner run = new QueryRunner(getDatasSource());String sql = "select * from contacts";List<Contact> cs =run.query(sql, new ResultSetHandler<List<Contact>>(){@Overridepublic 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