/**

* 使用dbutils工具

*/

public class Demo1 {

@Test

public void testInsert() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner对象

QueryRunner qr = new QueryRunner(ds);

//2.执行操作

//qr.update("INSERT INTO student(NAME,age,address) VALUES('张三11',20,'广州天河')");

qr.update("INSERT INTO student(NAME,age,address) VALUES(?,?,?)", new Object[]{"eric11",20,"广州天河"});

}

@Test

public void testInsert2() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

Connection conn = ds.getConnection();

QueryRunner qr = new QueryRunner();

qr.update(conn,"INSERT INTO student(NAME,age,address) VALUES('张三22',20,'广州天河')");

//手动关闭连接

conn.close();

}

}

/**

* dbutils执行查询操作

*/

public class Demo2 {

/**

* ArrayHandler: 把结果集的第一行的数据封装成对象数组。

*/

@Test

public void test1() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

//2.执行sql

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中

* @throws Exception

*/

@Test

public void test2() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

//2.执行sql

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

* 约定前提： 表的字段名称和javabean的属性名称保持一致！！

*/

@Test

public void test3() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

//2.执行sql

Student student = (Student)qr.query("select * from student", new BeanHandler(Student.class));

System.out.println(student);

}

/**

* BeanListHandler: 把结果集的每一行数据封装成javabean，把这个javabean放入LIst中

* 约定前提： 表的字段名称和javabean的属性名称保持一致！！

*/

@Test

public void test4() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

//2.执行sql

List<Student> list = (List<Student>)qr.query("select * from student", new BeanListHandler(Student.class));

for (Student student : list) {

System.out.println(student);

}

}

/**

* ScalarHandler： 把结果集的第一行第一列取出。通常用于聚合函数查询。例如（count()/max()）

*/

@Test

public void test5() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

//2.执行sql

Long count = (Long)qr.query("select count(id) from student", new ScalarHandler(1));

System.out.println("行数： "+count);

}

/**

* 如果表的字段名称和javabean的属性名称不一致时，需要自定义ResultSetHandler的实现类

*/

@Test

public void test6() throws Exception{

ComboPooledDataSource ds = new ComboPooledDataSource();

//1.创建QueryRunner

QueryRunner qr = new QueryRunner(ds);

List<Student> list = (List<Student>)qr.query("select * from student2", new MyStudentHandler());

for (Student student : list) {

System.out.println(student);

}

}

}

/**

* 自定义ResultSetHandler

*/

class MyStudentHandler implements ResultSetHandler{

@Override

public Object handle(ResultSet rs) throws SQLException {

List<Student> list = new ArrayList<Student>();

while(rs.next()){

Student s = new Student();

s.setId(rs.getInt("sid"));

s.setName(rs.getString("sname"));

s.setAge(rs.getInt("sage"));

s.setAddress(rs.getString("saddress"));

list.add(s);

}

return list;

}

}