DBUtils框架的使用:
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
API介绍:
org.apache.commons.dbutils.QueryRunner(类)
org.apache.commons.dbutils.ResultSetHandler(接口)
工具类:
org.apache.commons.dbutils.DbUtils。
1)目的:减化CURD操作API介绍:
org.apache.commons.dbutils.QueryRunner(类)
org.apache.commons.dbutils.ResultSetHandler(接口)
工具类:
org.apache.commons.dbutils.DbUtils。
2)DBUtils框架最核心的类,就是QueryRunner类,
QueryRunner类 :
该类简单化了SQL查询,它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作,能够大大减少编码量。
QueryRunner类提供了两个构造方法:
默认的构造方法
需要一个 javax.sql.DataSource 来作参数的构造方法。
构造其有二种方式:
a)空参构造b)通过DataSource构造
3)DBUtils对象的update()方法,内部已经关闭相关的连接对象
4)update(Connection)方法带有Connection对象的,需要手工关闭,其它对象自动关闭
update()方法无Connection对象的,DBUtils框架自动关闭
5)为什么作者要这样设计?
主要考虑了在分层结构中,需要用到同一个Connection的问题
6)对于query()操作与update()操作有着一致的含义
7)对于query()操作的实现类含义如下;
BeanHandler/BeanListHandler:争对JavaBean
ArrayHandler/ArrayListHandler:争对数组
MapHandler/MapListHandler:争对Map
ScalarHandler:争对Long
//演示DBUtils框架的使用
public class Demo3 {
public static void main(String[] args) throws SQLException {
//QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
//String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
//String sql = "update user set username=? where username=?";
//String sql = "delete from user where id = ?";
//runner.update(sql,new Object[]{4});
Connection conn = JdbcUtil.getMySqlConnection();
QueryRunner runner = new QueryRunner();
String sql = "insert into user(username,password,birthday,salary) values(?,?,?,?)";
runner.update(conn,sql,new Object[]{"tim","111222","2011-10-10",5000});
JdbcUtil.close(conn);
}
}
//演示ResultSetHandler接口的各种实现类的用法
public class Demo4 {
@Test
public void testBeanHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
User user = (User) runner.query(sql,new BeanHandler(User.class));
System.out.println("用户名:" + user.getUsername());
}
@Test
public void testBeanListHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
List<User> userList = (List<User>) runner.query(sql,new BeanListHandler(User.class));
for(User user : userList){
System.out.println("用户名:" + user.getUsername());
System.out.println("密码:" + user.getPassword());
}
}
@Test
public void testArrayHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
Object[] array = (Object[]) runner.query(sql,new ArrayHandler());
System.out.println("编号 : " + array[0]);
System.out.println("用户名 : " + array[1]);
}
@Test
public void testArrayListHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
List<Object[]> list = (List<Object[]>) runner.query(sql,new ArrayListHandler());
for(Object[] array : list){
System.out.print("编号 : " + array[0] + "\t");
System.out.println("用户名 : " + array[1]);
}
}
@Test
public void testMapHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
Map<Object,Object> map = (Map<Object, Object>) runner.query(sql,new MapHandler());
System.out.println("用户名:" + map.get("username"));
}
@Test
public void testMapListHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select * from user";
List<Map<Object,Object>> list = (List<Map<Object, Object>>) runner.query(sql,new MapListHandler());
for(Map<Object,Object> map : list){
System.out.println("用户名:" + map.get("username"));
System.out.println("薪水:" + map.get("salary"));
}
}
@Test
public void testScalarHandler() throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtil.getDataSource());
String sql = "select count(*) from user";
Long sum = (Long) runner.query(sql,new ScalarHandler());
System.out.println("共有" + sum + "人");
}
}
DbUtils提供的封装结果的一些对象:
1) BeanHandler: 查询返回单个对象
2) BeanListHandler: 查询返回list集合,集合元素是指定的对象
3) ArrayHandler, 查询返回结果记录的第一行,封装对对象数组,即返回:Object[]
4) ArrayListHandler, 把查询的每一行都封装为对象数组,再添加到list集合中
5) ScalarHandler 查询返回结果记录的第一行的第一列 (在聚合函数统计的时候用)
6) MapHandler 查询返回结果的第一条记录封装为map
public class DbUtilsExample {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
public static void main(String[] args) throws Exception {
DbUtilsExample dbe = new DbUtilsExample();
// dbe.testUpdate();
dbe.testUpdate2();
}
private void testUpdate2() throws Exception {
// TODO Auto-generated method stub
BaseDao db = new BaseDao();
String sql = "select * from admin";
conn = db.getConnection();
// 创建核心工具类
QueryRunner qr = new QueryRunner();
try {
// 查询的每一行封装成对象,装到listj集合
List<Admin> list = qr.query(conn, sql, new BeanListHandler<Admin>(
Admin.class));
// 查询返回记录的第一行,封装成数组
// Object[] object=qr.query(conn, sql, new ArrayHandler());
System.out.println(list.get(0).getAdminName());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void testUpdate() {
BaseDao db = new BaseDao();
try {
conn = db.getConnection();
String sql = "select * from admin where adminNumber=?";
QueryRunner qr = new QueryRunner();
// 把记录结果封装成一个对象
Admin admin = qr.query(conn, sql, new ResultSetHandler<Admin>() {
// 封装一个admin对象
@Override
public Admin handle(ResultSet rs) throws SQLException {
if (rs.next()) {
Admin admin = new Admin();
String id = rs.getString(1);
String pwd = rs.getString(2);
String name = rs.getString(3);
admin.setAdminNumber(id);
admin.setAdminName(name);
admin.setAdminPwd(pwd);
return admin;
}
return null;
}
}, "1");
System.out.println(admin.getAdminName());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}