Apache DBUtils

Apache DBUtils

下载Commons-dbutils-1.7.jar,其中包含一下几个重点类:
Dbutils、QueryRunner、ResultSetHandler

1.DuUtils:辅助

 

2.QueryRunner:增删改查
update()
query()
oracle:dml,commit
mysql:dml自动提交

3.如果是查询，则需要ResultSetHandler接口,有很多实现类，一个实现类对应于一种不同的查询类型

--Object[]
实现类ArrayHandler:返回结果集的第一行数据,并用Object[]接收
//查询单行数据
public static void testArrayHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
Object[] student = runner.query("select * from student where sno>?", new ArrayHandler(), 1);
System.out.println(student[0]+","+student[1]+","+student[2]+","+student[3]);
}
实现类ArrayListHandler:返回结果的多行数据，List<Object[]>
//查询多行数据
public static void testArrayListHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
List<Object[]> students = runner.query("select * from student where sno>?", new ArrayListHandler(), 1);
for(Object[] student:students){
System.out.println(student[0]+","+student[1]+","+student[2]+","+student[3]);
}
}
--Student
实现类BeanHandler:返回结果集的第一行数据，用对象()接受
//查询单行数据(放入对象中)
public static void testBeanHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
Student student = runner.query("select * from student where sno>?", new BeanHandler<Student>(Student.class), 1);
System.out.println(student.getSno()+","+student.getSname()+","+student.getSage()+","+student.getSaddress());
}
实现类BeanListHandler:返回结果集的多行数据,用对象()接受
//查询多行数据(放入对象中)
public static void testBeanListHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
List<Student> students= runner.query("select * from student where sno>?", new BeanListHandler<Student>(Student.class), 1);
for(Student student:students){
System.out.println(student.getSno()+","+student.getSname()+","+student.getSage()+","+student.getSaddress());
}
}
实现类BeanMapHandler:返回结果集的多行数据,用对象()接受
//查询多行数据(map) ---jav中oracle默认的数值类型 BigDecimal
public static void testBeanMapHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
Map<BigDecimal, Student> students = runner.query("select * from student where sno>?", new BeanMapHandler<BigDecimal,Student>(Student.class,"sno"), 1);
for(Map.Entry<BigDecimal, Student> student:students.entrySet()){
System.out.println(student.getKey()+":"+student.getValue());
}
Student student=students.get(new BigDecimal(3));//int
System.out.println(student.getSno()+","+student.getSname()+","+student.getSage()+","+student.getSaddress());
}

//反射会通过无参构造来创建对象
--Map

MapHandler:返回结果集的第一行数据 Map<String,Object>
{id=1,name=zs}
//查询单行数据 map
public static void testMapHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
Map<String,Object> student = runner.query("select * from student where sno>?", new MapHandler(), 1);
System.out.println(student);
}
MapListHandler:返回结果集的多行数据 List<Map<String,Object>>
{id=2,name=ls},{id=3,name=ww}
//查询多行数据 map
public static void testMapListHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
List<Map<String,Object>> students = runner.query("select * from student where sno>?", new MapListHandler(), 1);
System.out.println(students);
}

KeyedHandlder Map<String, Map<String, Object>>
{ls={id=2,name=ls},ww={id=3,name=ww}}
//查询多行数据 keyed
public static void testKeyedHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
Map<String, Map<String, Object>> students = runner.query("select * from student where sno>?", new KeyedHandler<String>("sname"), 1);
System.out.println(students);
}
ColumnListHandler：把结果集的某一列保存到List中
2,ls
3,ww
//查询多行数据中的某一列 list
public static void testColumnListHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
List<String> students = runner.query("select * from student where sno>?", new ColumnListHandler<String>("sname") ,1);
System.out.println(students);
}

-->
select count(1) from xxx;
select name from student where id =2;
ScalarHandler:单值结果
//查询单值数据
public static void testScalarHandler() throws SQLException {
QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSourceWithC3P0ByXml());//自动提交事务
BigDecimal result = runner.query("select count(1) from student where sno>?", new ScalarHandler<BigDecimal>(),1);
System.out.println(result);
String name= runner.query("select sname from student where sno=?", new ScalarHandler<String>(),1);
System.out.println(name);
}