commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作
主要使用的类:
1.QueryRunner
2.ResultSetHandler的具体实现类
public class QueryRunnerTest {
//测试插入
@Test
public void testInset(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="insert into customers(name,email,birth)values(?,?,?)";
int insertCount = runner.update(conn, sql, "蔡徐坤", "caixukun@126.com", "1997-09-08");
System.out.println("添加了"+insertCount+"条记录");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*测试查询
*
* BeanHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
* */
@Test
public void testQuery(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select id,name,email,birth from customers where id=?";
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(conn, sql, handler, 20);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*测试查询
*
* BeanListHandler:是ResultSetHandler接口的实现类,用于封装表中的多条记录构成的集合
* */
@Test
public void testQuery1() throws Exception {
Connection conn=null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select name,email,birth from customers where id<?";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
List<Customer> list = runner.query(conn, sql, handler, 20);
list.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*
* MapHandler:是ResultSetHandler接口的实现类,用于封装表中的一条记录
* 将字段及相应字段的值作为map中的key和value
* */
@Test
public void testQuery2(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select id,name,email,birth from customers where id=?";
MapHandler handler= new MapHandler();
Map<String, Object> map = runner.query(conn, sql, handler, 20);
System.out.println(map);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*
* MapListHandler:是ResultSetHandler接口的实现类,对应表中的多条记录
* 将字段及相应字段的值作为map中的key和value。将这些map添加到List中
* */
@Test
public void testQuery3(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select id,name,email,birth from customers where id<?";
MapListHandler handler= new MapListHandler();
List<Map<String, Object>> list = runner.query(conn, sql, handler, 20);
list.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*
* ScalarHandler:用于查询特殊值
* */
@Test
public void testQuery4(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select count(*) from customers";
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(conn, sql, handler, 20);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*
* ScalarHandler:用于查询特殊值
* */
@Test
public void testQuery5(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select max(birth) from customers";
ScalarHandler handler = new ScalarHandler();
Date maxBirth = (Date) runner.query(conn, sql, handler);
System.out.println(maxBirth);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
/*
* 自定义ResultSetHandler的实现类
* */
@Test
public void testQuery6(){
Connection conn = null;
try {
QueryRunner runner = new QueryRunner();
conn = JDBCUtils.getConnection3();
String sql="select id,name,email,birth from customers where id = ? ";
ResultSetHandler<Customer> handler = new ResultSetHandler<>() {
@Override
public Customer handle(ResultSet rs) throws SQLException {
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
return customer;
}
return null;
}
};
Customer customer = runner.query(conn, sql, handler, 20);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.close(conn,null);
}
}
}