4天贯通JDBC技术九、DBUtils
提供了QueryRunner类,类中有诸多重载update() 和 query()方法,供使用,用于堆数据库实现操作:增删改查
public class TestDBUtils {
QueryRunner runner = new QueryRunner();
// ScalarHandler:用于查询其他的一些信息,比如表中的记录数,薪资最高的员工姓名。
//因为返回的类型不确定,故使用Object接收
@Test
public void testScalarHandler() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
//String sql = "select count(*) from customers";
String sql = "select min(birth) from customers";
ResultSetHandler<Object> handler = new ScalarHandler();
//long count = (long) runner.query(conn, sql, handler);
Date d = (Date)runner.query(conn, sql, handler);
System.out.println(d);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
// MapListHandler
@Test
public void testMapListHandler() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
String sql = "select id,name,email,birth from customers where id < ?";
ResultSetHandler<List<Map<String, Object>>> handler = new MapListHandler();
// MapListHandler handler = new MapListHandler();
List<Map<String, Object>> maps = runner.query(conn, sql, handler,
12);
System.out.println(maps);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
// MapHandler:返回数据库中一条记录,key为记录的列的列名,value为记录的列的值。一条记录
// 的多个列构成了多个key-value,组成了一个map
@Test
public void testMapHandler() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
String sql = "select id,name,email,birth from customers where id = ?";
// ResultSetHandler<Map<String,Object>> handler = new MapHandler();
MapHandler handler = new MapHandler();
Map<String, Object> map = runner.query(conn, sql, handler, 22);
System.out.println(map);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close1(null, null, conn);
}
}
// 实现对数据库中表的查询的操作:返回表中的多条记录,用List接口接收。使用的是:
// ResultSetHandler的实现类:BeanListHandler
@Test
public void testQueryList() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
String sql = "select id,name,email,birth from customers where id < ?";
// 使用BeanListHandler接收多条记录,以多个对象组成的list返回。
ResultSetHandler<List<Customer>> handler = new BeanListHandler<>(
Customer.class);
List<Customer> custs = runner.query(conn, sql, handler, 15);
// System.out.println(custs);
Iterator<Customer> iterator = custs.iterator();
while (iterator.hasNext()) {
System.out.println(iterator.next());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
// 自定义一个ResultSetHandler的实现类,实现与BeanHandler一样的功能,用于返回
// 数据库中表的一条记录
@Test
public void testSelfHander() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
String sql = "select id,name,email,birth from customers where id = ?";
ResultSetHandler<Customer> handler = new ResultSetHandler() {
@Override
public Object handle(ResultSet rs) throws SQLException {
Customer cust = null;
if (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
cust = new Customer(id, name, email, birth);
}
return cust;
}
};
Customer cust = runner.query(conn, sql, handler, 22);
System.out.println(cust);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
// 实现对数据库中表的查询的操作:返回表中的一条记录,对应着一个对象使用的是:
// ResultSetHandler的实现类:BeanHandler
@Test
public void testQuery1() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection5();
String sql = "select id,name,email,birth from customers where id = ?";
ResultSetHandler<Customer> handler = new BeanHandler<>(
Customer.class);
Customer cust = runner.query(conn, sql, handler, 22);
System.out.println(cust);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
@Test
public void testDelete() {
Connection conn = null;
try {
// 使用DBCP数据库连接池,获取数据库的连接
conn = JDBCUtils.getConnection5();
String sql = "delete from customers where id = ?";
// 对数据库中表的INSERT UPDATE DELETE操作,都是调用runner的update()方法
runner.update(conn, sql, 23);
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
@Test
public void testInsert() {
Connection conn = null;
try {
// 使用DBCP数据库连接池,获取数据库的连接
conn = JDBCUtils.getConnection5();
String sql = "insert into customers(name,email,birth) values (?,?,?)";
runner.update(conn, sql, "张嘉译", "zjy@gmail.com", new Date(
new java.util.Date().getTime()));
} catch (Exception e) {
e.printStackTrace();
} finally {
// “关闭连接”,实际上是对conn的释放
JDBCUtils.close(null, null, conn);
}
}
}
DBUtils类
DBUtils类中有close(ResultSet rs) 、 close(Statement st) 、close(Connection conn)用于释放数据库的连接
public static void close1(ResultSet rs, Statement st, Connection conn){
try {
DbUtils.close(rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
DbUtils.close(st);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
DbUtils.close(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
重构DAO
//返回一个对象
QueryRunner runner = new QueryRunner();
public T get(Connection conn,String sql,Object ...args){
// PreparedStatement ps = null;
// ResultSet rs = null;
// try{
// //1.预编译sql语句,获取PreparedStatement对象
// ps = conn.prepareStatement(sql);
// //2.填充占位符
// for(int i = 0;i < args.length;i++){
// ps.setObject(i + 1, args[i]);
// }
// //3.返回一个结果集
// rs = ps.executeQuery();
// ResultSetMetaData rsmd = rs.getMetaData();
// int columnCount = rsmd.getColumnCount();
// if(rs.next()){
// T t = clazz.newInstance();
//
// //给t对象的相应属性赋值
// for(int i = 0;i < columnCount;i++){
// Object columnVal = rs.getObject(i + 1);
// String columnLabel = rsmd.getColumnLabel(i + 1);
// PropertyUtils.setProperty(t, columnLabel, columnVal);
// }
// return t;
// }
//
// }catch(Exception e){
// e.printStackTrace();
// }finally{
// JDBCUtils.close(rs, ps, null);
// }
// //System.out.println(clazz);
// return null;
ResultSetHandler<T> handler = new BeanHandler<>(clazz);
try{
return runner.query(conn, sql, handler, args);
}catch(Exception e){
e.printStackTrace();
}
return null;
}
//通用的增删改的操作
public void update(Connection conn,String sql,Object ... args){
// PreparedStatement ps = null;
// try{
// ps = conn.prepareStatement(sql);
//
// for(int i = 0;i < args.length;i++){
// ps.setObject(i + 1, args[i]);
// }
//
// ps.executeUpdate();
// }catch(Exception e){
// e.printStackTrace();
// }finally{
// JDBCUtils.close(null, ps, null);
// }
try{
runner.update(conn, sql, args);
}catch(Exception e){
e.printStackTrace();
}
}
关于JDBC详细内容及课件、视频,请登录www.atguigu.java下载区下载