<十五>JDBC_使用 DBUtils 进行更新、查询操作
详解待续。。。
DBUtilsTest.java
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import com.kk.jdbc.JDBCTools;
public class DBUtilsTest {
/*
* ScalarHandler:把结果集转为一个数值(可以是任意基本数据类型和字符串，Date等)返回
* */
@Test
public void testScalarHandler(){
Connection con = null;
try {
con=JDBCTools.getConnection();
String sql = "select count(id) from customers";
Object result=qr.query(con, sql, new ScalarHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
/*
* MapListHandler:将结果集转为一个Map的List，
* Map对应查询的一条记录，键：SQL查询的列名(不是列的别名)，值：列的值
* MapListHandler返回的是多条记录对应的Map的集合
* */
@Test
public void testMapListHandler(){
Connection con = null;
try {
con=JDBCTools.getConnection();
String sql = "select name from customers where id=?";
List<Map<String,Object>> result = (List<Map<String, Object>>) qr.query(con, sql, new MapListHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
/*
* MapHandler:返回SQL对应的第一条记录对应的Map对象；
* 键：SQL查询的列名(不是列的别名)
* 值：列的值
* */
@Test
public void testMapHandler(){
Connection con = null;
try {
con=JDBCTools.getConnection();
String sql = "select id,name,email,birth from customers";
Map<String,Object> result = (Map<String, Object>) qr.query(con, sql, new MapHandler());
System.out.println(result);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
/*
* BeanListHandler:把结果集转为一个List，该List不为空，但可能为空集合(size()方法返回0)
* 若SQL语句能够查询到记录，List中存放创建BeanListHandler传入的Class对象对应的对象。
* */
@Test
public void testBeanListHandler(){
Connection con = null;
try {
con=JDBCTools.getConnection();
String sql = "select id,name,email,birth from customers";
List<Customer> customers = (List<Customer>) qr.query(con, sql, new BeanListHandler(Customer.class));
System.out.println(customers);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
/*
* BeanHandler:把结果集的第一条记录转为创建BeanHandler对象时传入的Class参数对应的对象
* */
@Test
public void testBeanHandler(){
Connection con = null;
try {
con=JDBCTools.getConnection();
String sql = "select id,name,email,birth from customers where id=?";
Customer customer=(Customer) qr.query(con, sql, new BeanHandler(Customer.class), 6);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
// 1、创建QueryRunner的实现类
QueryRunner qr = new QueryRunner();
/*
* QueryRunner的query方法的返回值取决于ResultSetHandler参数的handle方法的返回值
* */
@Test
public void testQuery() {
Connection con = null;
class MyResultSetHandler implements ResultSetHandler{
@Override
public Object handle(ResultSet rs) throws SQLException {
List<Customer> customers=new ArrayList<>();
while(rs.next()){
Integer id=rs.getInt(1);
String name=rs.getString(2);
String email=rs.getString(3);
Date birth=rs.getDate(4);
Customer customer=new Customer(id,name,email,birth);
customers.add(customer);
}
return customers;
}
}
try {
con = JDBCTools.getConnection();
String sql = "select id,name,email,birth from customers";
Object ob=qr.query(con, sql, new MyResultSetHandler());
System.out.println("谢荣康:"+ob);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, con);
}
}
/**
* 测试 QueryRunner类的update方法(insert 、update、delete)
*/
@Test
public void testQueryRunnerUpdate() {
// 2、使用update方法
String sql = "delete from customers where id in (?,?)";
Connection con = null;
try {
con = JDBCTools.getConnection();
qr.update(con, sql, 2, 5);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.release(null, null, con);
}
}
}
