dbutils

 

import gz.itacast.c_e_metadata.Student;
import gz.itcast.util.JdbcUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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.ScalarHandler;
import org.junit.Test;

public class Demo2 {

//ArrayHandler:把结果集的第一行封装成一个对象数组
@Test
public void test1() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
Object[] arr = (Object[])qr.query("select * from student where id=?", new ArrayHandler(),new Object[]{2});
for (Object obj : arr) {
System.out.println(obj);
}
}

//ArrayListHandler: 把结果集第每行封装成对象数组，然后再把对象数组放入List
@Test
public void test2() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
List<Object[]> list = (List<Object[]>)qr.query("select * from student", new ArrayListHandler());
for (Object[] arr : list) { //行
for (Object obj : arr) { //列
System.out.print(obj+"\t");
}
System.out.println();
}

}

//BeanHandler: 把结果集的第一行封装成javabean对象(常用)
//约定前提： 表的字段名和对象的属性保持一致！！！
@Test
public void test3() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
Student stu = (Student)qr.query("select * from student where id=?", new BeanHandler(Student.class),new Object[]{2});
System.out.println(stu);
}

//BeanListHandler:把结果集第每行封装成javabean，然后再把javabean放入List(常用)
//约定前提： 表的字段名和对象的属性保持一致！！！
@Test
public void test4() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
List<Student> stu = (List<Student>)qr.query("select * from student", new BeanListHandler(Student.class));
for (Student student : stu) {
System.out.println(student);
}
}

//使用自定义的ResultSetHandler
@Test
public void test6() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
List<Student> stu = (List<Student>)qr.query("select * from student", new MyResultSetHandler());
for (Student student : stu) {
System.out.println(student);
}
}

//自定义ResutSetHandler
class MyResultSetHandler implements ResultSetHandler{

@Override
public Object handle(ResultSet rs) throws SQLException {
List list = new ArrayList();
while(rs.next()){
Student s = new Student();
s.setId(rs.getInt("id"));
s.setName(rs.getString("name"));
s.setAge(rs.getInt("age"));
list.add(s);
}
return list;
}

}


//ScalarHandler: 查询聚合函数 count(*)(常用)
@Test
public void test5() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
Long count = (Long)qr.query("select count(*) from student", new ScalarHandler(1));
System.out.println(count);
}


}