1. DButils简介
- DBUtil是一个轻量级的JDBC框架,封装了原来的方法,并且可以自动释放资源,不需要手动释放资源
1. 实例
- QueryRunner 执行器,代替了Statement和PreparedStatement,封装了原来的执行器
package com.etoak.dao;
import com.etoak.factory.Factory;
import com.etoak.po.Person;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.List;
public class PersonDaoImpl3 implements PersonDaoIf3 {
Connection con;
//拿取选择执行器,这个执行器封装了原先的执行器
QueryRunner qr = new QueryRunner();
//当我们使用DBUtils框架时,不需要手动释放资源了,此框架自动释放
@Override
public boolean addPerson(Person per) {
try {
String sql = "insert into person values (null,?,?,?,?,?)";
/*
* 如果执行DML语句
*
* int qr.update(con,sql,填充的占位符)
* 返回值是更改的记录数
* con:链接
* sql:带有占位符或者拼接的sql语句
* */
return qr.update(Factory.getCon(),sql
,per.getName(),per.getPass(),per.getAge(),per.getSalary(),per.getBirth())==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public boolean delPersonById(Integer id) {
try {
String sql = "delete from person where id = ?";
con = Factory.getCon();
return qr.update(con,sql,id)==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public boolean delPersonByName(String name) {
try {
String sql = "delete from person where name = ?";
con = Factory.getCon();
return qr.update(con,sql,name)>=1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public boolean delPersonByAgeAsc(Integer age) {
try {
String sql = "delete from person where age > ?";
con = Factory.getCon();
return qr.update(con,sql,age)>=1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public boolean multiDelById(String[] args) {
try {
String sql = "delete from person where id in (";
String sum = "";
for(int i = 0;i<args.length;i++){
sum = args[i]+","+sum;
}
sum = sum.substring(0,sum.length()-1);
sql += sum+")";
con = Factory.getCon();
return qr.update(con,sql)>=1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public List<Person> queryAll() {
try {
String sql = "select * from person";
con = Factory.getCon();
/*
* 如果执行DQL语句
*
* qr.query(con,sql,new ResultSetHandler(),填充的占位符);
* con:链接
* sql:拼接或者带有占位符的sql语句
* ResultSetHandler():接口 封装了各种形式的结果集,根据返回值不同一般我们调用其
* 子接口
* 1)返回实体类 new BeanHandler(实体类.class)
* 2)返回封装实体类的List new BeanListHandler(实体类.class)
* 3)返回count max min avg调用的函数 new ScalarHandler()
* */
return qr.query(con,sql,new BeanListHandler<Person>(Person.class));
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public boolean queryName(String name) {
try {
String sql = "select * from person where name = ?";
con = Factory.getCon();
/*
* 注意这里不能使用new BeanListHandler()
* 因为此接口封装了结果集,存在表头,永远不为空
* */
return qr.query(con,sql,new BeanHandler<Person>(Person.class),name)!=null;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
@Override
public Person queryPerson(String name, String pass) {
try {
String sql = "select * from person where name = ? and pass = ?";
con = Factory.getCon();
return qr.query(con,sql,new BeanHandler<Person>(Person.class),name,pass);
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public Integer queryCount() {
try {
String sql = "select count(*) from person";
con = Factory.getCon();
return Integer.parseInt(qr.query(con,sql,new ScalarHandler()).toString());
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public List<Person> queryPage(Integer index, Integer max) {
try {
String sql = "select * from person limit ?,?";
con = Factory.getCon();
return qr.query(con,sql,new BeanListHandler<Person>(Person.class),index,max);
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public List<Person> queryBirthByPage(String birthBegin, String birthEnd, Integer index, Integer max) {
try {
String sql = "select * from person where birth between ? and ? limit ?,?";
con = Factory.getCon();
return qr.query(con,sql,new BeanListHandler<Person>(Person.class),birthBegin,birthEnd,index,max);
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public List<Person> queryNameLikeByPage(String args, Integer index, Integer max) {
try {
String sql = "select * from person where name like ? limit ?,?";
con = Factory.getCon();
return qr.query(con,sql,new BeanListHandler<Person>(Person.class),"%"+args+"%",index,max);
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public List<Person> queryByPage(Person per, Integer index, Integer max) {
try {
/*
* 姓名 模糊
* 密码 模糊
* 年龄 准确
* 薪资 准确
* 生日 准确
*
* 模块化书写
* 不管结果如何必须分页,如果用户不填写任何查询条件,则全部拿取
* */
String sql = "select * from person where 1 = 1 and ";
if(per.getName()!=null){
sql += "name like '%"+per.getName()+"%' and ";
}
if(per.getPass()!=null){
sql += "pass like '%"+per.getPass()+"%' and ";
}
if(per.getAge()!=null){
sql += "age = "+per.getAge()+" and ";
}
if(per.getSalary()!=null){
sql += "salary = "+per.getSalary()+" and ";
}
if(per.getBirth()!=null){
sql += "birth = '"+new SimpleDateFormat("yyyy-MM-dd").format(per.getBirth()) +"' and ";
}
sql = sql.substring(0,sql.length()-4);
sql += " limit "+index+","+max;
return qr.query(Factory.getCon(),sql,new BeanListHandler<Person>(Person.class));
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
@Override
public boolean updatePerson(Person per) {
try {
String sql = "update person set name = ?,pass = ?,age = ?,salary = ?,birth = ? where id = ?";
con = Factory.getCon();
return qr.update(con,sql,per.getName(),per.getPass(),per.getAge()
,per.getSalary(),per.getBirth(),per.getId())==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
}
}
2.注意点
1. 执行DML 语句
当执行增,删,改 操作时,使用update()方法,
QueryRunner qr = new Queryunner();
public boolean addPerson(Person per){
String sql = "insert into person values(null,?,?)";
conn = Factory.getCOnn();
return qr.update(conn,sql,per.getName(),per.getPass(),);
}
2. 执行DQL语句
执行查询数据时,使用query方法
qr.query(conn,sql,new ResultSetHandler(),填充的占位符)
前两个与上边的一样
ResultSetHandler()这个接口封装了各种形势的结果集,根据返回结果不同,调用其子接口,把要获取的实体类的反射传入构造方法中
接口 | 范例 | 返回类型 |
BeanHandler | new BeanHandler(实体类.class) | 实体类 |
BeanListHandler | new BeanListHandler(Person.class) | 返回封装类的集合 |
ScalarHandler | new ScalarHander() | 返回count min avg等调用函数结果 |
return qr.query(conn,sql,new BeanHandele<Person>(Person,class),per.getName()) != null;
3. 接口
接口 | 用途 |
ArrayHandler | 将查询结果的第一行数据,保存到Object数组中 |
ArrayListHandler | 将查询的结果,每一行先封装到Object数组中,然后将数据存入List集合 |
BeanHandler | 将查询结果的第一行数据,封装到user对象 |
BeanListHandler | 将查询结果的每一行封装到user对象,然后再存入List集合 |
ColumnListHandler | 将查询结果的指定列的数据封装到List集合中 |
MapHandler | 将查询结果的第一行数据封装到map结合(key列名,value列值) |
MapListHandler | 将查询结果的每一行封装到map集合(key列名,value列值),再将map集合存入List集合 |
BeanMapHandler | 将查询结果的每一行数据,封装到User对象,再存入mao集合中(key列名,value列值) |
KeyedHandler | 将查询的结果的每一行数据,封装到map1(key列名,value列值 ),然后将map1集合(有多个)存入map2集合(只有一个) |
ScalarHandler | 封装类似count、avg、max、min、sum…函数的执行结果 |