mysql dbutils

一、DbUtils简介：

DBUtils是apache下的一个小巧的JDBC轻量级封装的工具包，其最核心的特性是结果集的封装，可以直接将查询出来的结果集封装成JavaBean。使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

下载地址：http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

可以从下载的文件中看到apidoc文档。1.4版本中，整个dbutils总共才3个包。1.7版本中，包增加到5个。使用不同版本时注意看相应的文档。

org.apache.commons.dbutils   

 

org.apache.commons.dbutils.handlers

org.apache.commons.dbutils.handlers.columns

 org.apache.commons.dbutils.handlers.properties

org.apache.commons.dbutils.wrappers

 

 后续主要还是介绍的1.4版本的。

1、包org.apache.commons.dbutils 
       
接口摘要
ResultSetHandler    将ResultSet转换为别的对象的工具。
RowProcessor        将ResultSet行转换为别的对象的工具。 

类摘要
BasicRowProcessor   RowProcessor接口的基本实现类。 
BeanProcessor       BeanProcessor匹配列明到Bean属性名，并转换结果集列到Bean对象的属性中。
DbUtils             一个JDBC辅助工具集合。 
ProxyFactory        产生JDBC接口的代理实现。 
QueryLoader         属性文件加载器，主要用于加载属性文件中的 SQL 到内存中。 
QueryRunner         使用可插拔的策略执行SQL查询并处理结果集。
ResultSetIterator   包装结果集为一个迭代器。 
  
2、包org.apache.commons.dbutils.handlers 
 ResultSetHandler接口的实现类。该接口用于处理java.sql.ResultSet，将数据按要求转换为另一种形式。ResultSetHandler接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
类摘要
AbstractListHandler 将ResultSet转为List的抽象类
ArrayHandler 将ResultSet转为一个Object[]的ResultSetHandler实现类
ArrayListHandler 将ResultSet转换为List<Object[]>的ResultSetHandler实现类
BeanHandler 将ResultSet行转换为一个JavaBean的ResultSetHandler实现类
BeanListHandler 将ResultSet转换为List<JavaBean>的ResultSetHandler实现类
ColumnListHandler 将ResultSet的一个列转换为List<Object>的ResultSetHandler实现类
KeyedHandler 将ResultSet转换为Map<Map>的ResultSetHandler实现类
MapHandler 将ResultSet的首行转换为一个Map的ResultSetHandler实现类
MapListHandler 将ResultSet转换为List<Map>的ResultSetHandler实现类
ScalarHandler 将ResultSet的一个列到一个对象。

3、包org.apache.commons.dbutils.wrappers 
 添加java.sql类中功能包装类。

类摘要
SqlNullCheckedResultSet  在每个getXXX方法上检查SQL NULL值的ResultSet包装类。
StringTrimmedResultSet    取出结果集中字符串左右空格的ResultSet包装类。

 

二，使用DBUtils

其实只是使用的话，重点看两个类（DbUtils 和QueryRunner）和一个接口（ResultSethandler）就可以了。

 

1，DbUtils
DbUtils是一个为做一些诸如关闭连接、装载JDBC驱动程序之类的常规工作提供有用方法的类，它里面所有的方法都是静态的。
这个类里的重要方法有：
close()：
    DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭连接、声明和结果集（ResultSet）。
CloseQuietly:
  这一方法不仅能在连接、声明或者结果集（ResultSet）为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。如果你不想捕捉这些异常的话，这对你是非常有用的。

在重载CloseQuietly方法时，特别有用的一个方法是closeQuietly(Connection conn,Statement stmt,ResultSet rs)，这是因为在大多数情况下，连接、声明和结果集（ResultSet）是要用的三样东西，而且在最后的块必须关闭它们。使用这一方法，最后的块就可以只需要调用这一方法即可。
CommitAndCloseQuietly(Connection conn)：
这一方法用来提交连接，然后关闭连接，并且在关闭连接时不向上抛出在关闭时发生的一些SQL异常。
LoadDriver(String driveClassName):

这一方法装载并注册JDBC驱动程序，如果成功就返回TRUE。这一方法装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

 

2,ResultSetHandler
这一接口执行处理一个jaca.sql.ResultSet，将数据转变并处理为任何一种形式，这样有益于其应用而且使用起来更容易。
这一组件提供了ArrayHandler, ArrayListHandler, BeanHandler, BeanListHandler, MapHandler, MapListHandler, and ScalarHandler等执行程序。
 
ResultSetHandler接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
因此任何ResultSetHandler 的执行需要一个结果集（ResultSet）作为参数传入，然后才能处理这个结果集，再返回一个对象。因为返回类型是java.lang.Object，所以除了不能返回一个原始的Java类型之外，其它的返回类型并没有什么限制。

• ArrayHandler：适合取一条记录。把结果集中的第一行数据转成对象数组。
• ArrayListHandler：适合取多条记录。把结果集中的每一行数据都转成一个数组，再存放到List中。
• BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。如按ID精确查询。
• BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。如全表查询。
• ColumnListHandler：取一列数据，将结果集中某一列的数据存放到List中。
• KeyedHandler(name)：取多条记录，将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
• MapHandler：适合取1条记录，将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
• MapListHandler：适合取多条记录，将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
• ScalarHandler:适合取单行单列数据 比如统计数

3,QueryRunner
这个类使执行SQL查询简单化了，它与ResultSetHandler串联在一起有效地履行着一些平常的任务，它能够大大减少你所要写的编码。 

> new QueryRunner(); 它的事务可以手动控制。也就是说此对象调用的方法（如：query、update、batrch）参数中要有Connection对象。

> new QueryRunner(DataSource ds); 事务是自动控制的。一个sql一个事务，此对象调用的方法（如：query、update、batrch）参数中无需Connection对象。

 

QueryRunner中提供对sql语句操作的API.它主要有三个方法

query() 用于执行select

update() 用于执行insert update delete

batch() 批处理

重要方法包括以下这些：
query(Connection conn, String sql, Object[] params, ResultSetHandler rsh):
执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理PreparedStatement和ResultSet的创建和关闭。ResultSetHandler对把从 ResultSet得来的数据转变成一个更容易的或是应用程序特定的格式来使用。
query(String sql, Object[] params, ResultSetHandler rsh):
几乎与第一种一样；不同在于它不将数据库连接提供给方法，并且它是从提供给构造器的数据源(DataSource) 或使用的setDAtaSource 方法中重新获得的。
query(Connection conn, String sql, ResultSetHandler rsh):
这执行一个不要参数的选择查询。
update(Connection conn, String sql, Object[] params):
用来执行一个更新（插入、更新或删除）操作。

 

1.7中增加了execute、insert一系列方法。

execute(Connection conn, String sql, Object... params)

insert(Connection conn, String sql, ResultSetHandler<T> rsh)

 

 三、使用介绍

步骤： 

> 导入jar包  加载JDBC驱动程序类，并用DriverManager来得到一个数据库连接conn。----从配置文件、直接写文件各种方式不限             

> 创建QueryRunner对象

> 使用query方法执行select语句

> 使用ResultSetHandler封装结果集

> 使用DbUtils类释放资源

这里用C3P0连接池。

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.abc.entity.User;
import com.sun.org.apache.bcel.internal.generic.ARRAYLENGTH;

public class TestCURD {
    @Test
    public void testInsert() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        //执行sql语句
        qr.update("insert into users(username,password,email,birthday) values(?,?,?,?)", "b10","123","c10@163.com",new Date());
        
    }
    
    @Test
    public void testUpdate() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("update users set username=?,password=? where id=?", "李明","123456",10);
    }
    
    
    @Test
    public void testDelete() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("delete from users where id=?", 10);
    }
    
    @Test//批处理，只能执行相同的sql语句
    public void testBatch() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        
        Object[][] params = new Object[5][];//高维代表执行多少次sql语句
        for (int i = 0; i < params.length; i++) {
            params[i] = new Object[]{"b10"+i,"123","c10@163.com",new Date()};//给每次执行的sql语句中的？赋值
        }
        qr.batch("insert into users(username,password,email,birthday) values(?,?,?,?)", params );
        
    }
    @Test
    public void testSelect() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<User> list = qr.query("select * from users", new ResultSetHandler<List<User>>(){
            //当query方法执行select语句后，将结果集以参数的形式传递过来
            public List<User> handle(ResultSet rs) throws SQLException {
                List<User> list  = new ArrayList<User>();
                while(rs.next()){
                    User u = new User();
                    u.setId(rs.getInt(1));
                    u.setName(rs.getString(2));
                    u.setPassword(rs.getString(3));
                    u.setEmail(rs.getString(4));
                    u.setBirthday(rs.getDate(5));
                    list.add(u);
                }
                return list;
            }
            
        });
        
        for (User user : list) {
            System.out.println(user);
        }
    }
    
    
    @Test
    public void testSelect2() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        //执行查询语句，并返回结果
        List<User> list = qr.query("select * from users where id=? and username=?", new BeanListHandler<User>(User.class),8,"jerry");
        
        for (User user : list) {
            System.out.println(user);
        }
    }

}

练习ResultSetHandler

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
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.abc.entity.User;

public class TestResultSetHandler {
    @Test//ArrayHandler:适合取1条记录。把该条记录的每列值封装到一个数组中Object[]
    public void tese1() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] arr  = qr.query("select * from users", new ArrayHandler());
        
        for (Object o : arr) {
            System.out.println(o);
        }
    }
    
    @Test//ArrayListHandler:适合取多条记录。把每条记录的每列值封装到一个数组中Object[]，把数组封装到一个List中
    public void tese2() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object[]> query = qr.query("select * from users", new ArrayListHandler());
        
        for (Object[] os : query) {
            for (Object o : os) {
                System.out.println(o);
            }
            System.out.println("--------------");
        }
    }
    
    @Test //ColumnListHandler:取某一列的数据。封装到List中。
    public void tese3() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object> list = qr.query("select username,password from users", new ColumnListHandler(1));
        
        for (Object o : list) {
            System.out.println(o);
        }
    }
    
    @Test //KeyedHandler:取多条记录，每一条记录封装到一个Map中，再把这个Map封装到另外一个Map中，key为指定的字段值。
    public void tese4() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        //大Map的key是表中的某列数据，小Map中的key是表的列名，所以大Map的key是Object类型，小Map的key是String类型
        Map<Object,Map<String,Object>> map = qr.query("select * from users", new KeyedHandler(1));
        
        for (Map.Entry<Object, Map<String,Object>> m : map.entrySet()) {
            System.out.println(m.getKey());//大Map中key值就是id列的值
            for (Map.Entry<String, Object> mm : m.getValue().entrySet()) {
                System.out.println(mm.getKey()+"\t"+mm.getValue());//取出小Map中的列名和列值
            }
            System.out.println("---------------------");
        }
        
    }
    
    
    @Test//MapHandler:适合取1条记录。把当前记录的列名和列值放到一个Map中
    public void tese5() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Map<String,Object> map = qr.query("select * from users where id=?", new MapHandler(),20);
        
        for (Map.Entry<String, Object> m : map.entrySet()) {
            System.out.println(m.getKey()+"\t"+m.getValue());
        }
        
    }
    
    
    @Test//MapListHandler:适合取多条记录。把每条记录封装到一个Map中，再把Map封装到List中
    public void tese6() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Map<String,Object>> list = qr.query("select * from users", new MapListHandler());
        
        for (Map<String, Object> map : list) {
            for (Map.Entry<String, Object> m : map.entrySet()) {
                System.out.println(m.getKey()+"\t"+m.getValue());
            }
            System.out.println("---------------");
        }
    }
    
    @Test //ScalarHandler:适合取单行单列数据
    public void tese7() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object o = qr.query("select count(*) from users", new ScalarHandler(1));
        System.out.println(o.getClass().getName());
    }
    
    @Test //BeanHandler:适合取单行单列数据
    public void tese8() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        User user = qr.query("select * from users where id=?", new BeanHandler<User>(User.class),1);
        System.out.println(user);
    }
    
    
    @Test //BeanListHandler 
    public void tese9() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<User> list = qr.query("select * from users where id=?", new BeanListHandler<User>(User.class),1);
        
        System.out.println(list.size());
    }
}

 

 

以查找图书为例

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.abc.domain.Book;
import com.abc.util.C3P0Util;
import com.sun.org.apache.bcel.internal.generic.ARRAYLENGTH;

public class BookDaoImpl {
    
    /**
     * 查找所有图书
     * @return
     * @throws SQLException
     */
    public List<Book> findAllBooks() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        return qr.query("select * from book", new BeanListHandler<Book>(Book.class));
    }
    /**
     * 添加图书信息
     * @param book
     * @throws SQLException
     */
    public void addBook(Book book) throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("INSERT INTO book VALUES(?,?,?,?,?,?)",book.getId(),book.getName(),book.getPrice(),book.getPnum(),book.getCategory(),book.getDescription());
    }
    
    /**
     * 
     * @param id
     * @return 
     * @throws SQLException 
     */
    public Book findBookById(String id) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        return qr.query("select * from book where id=?", new BeanHandler<Book>(Book.class),id);
    }
    
    /**
     * 修改图书信息
     * @param book
     * @throws SQLException
     */
    public void updateBook(Book book) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("update book set name=?,price=?,pnum=?,category=?,description=? where id=?",
                book.getName(),book.getPrice(),book.getPnum(),book.getCategory(),book.getDescription(),book.getId());
        
        
    }
    /**
     * 根据id删除图书
     * @param id
     * @throws SQLException 
     */
    public void delBook(String id) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("delete from book where id=?",id);
    }
    
    /**
     * 批量删除
     * @param ids
     * @throws SQLException 
     */
    public void deleAllBooks(String[] ids) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[][] params = new Object[ids.length][];
        for (int i = 0; i < params.length; i++) {
            params[i] = new Object[]{ids[i]};//循环给每个一维数组中的元素赋值，值是id
        }
        qr.batch("delete from book where id=?", params );
    }
    
    /**
     * 多条件查询图书
     * @param id
     * @param category
     * @param name
     * @param minprice
     * @param maxprice
     * @return
     * @throws SQLException 
     */
    public List<Book> searchBooks(String id, String category, String name,
            String minprice, String maxprice) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        String sql = "select * from book where 1=1";
        List list = new ArrayList();
        if(!"".equals(id.trim())){
            sql+=" and id like ?"; //  不能在这写%   %'1003'%
            list.add("%"+id.trim()+"%");// '%1003%'
        }
        
        if(!"".equals(category.trim())){
            sql+=" and category=?";
            list.add(category.trim());
        }
        
        if(!"".equals(name.trim())){
            sql+=" and name like ?";
            list.add("%"+name.trim()+"%");
        }
        
        if(!"".equals(minprice.trim())){
            sql+=" and price>?";
            list.add(minprice.trim());
        }
        if(!"".equals(maxprice.trim())){
            sql+=" and price< ?";
            list.add(maxprice.trim());
        }
        
        return qr.query(sql, new BeanListHandler<Book>(Book.class),list.toArray());
    }
    
    /**
     * 得到总记录数
     * @return
     * @throws SQLException
     */
    public int count() throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        long l =  (Long)qr.query("select count(*) from book", new ScalarHandler(1));
        return (int)l;
    }
    
    /**
     * 查找分页数据
     * @param currentPage
     * @param pageSize
     * @return
     * @throws SQLException 
     */
    public List<Book> findBooks(int currentPage, int pageSize) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        return qr.query("select * from book limit ?,?", new BeanListHandler<Book>(Book.class),(currentPage-1)*pageSize,pageSize);
    }
    
    /**
     * 根据书名查找图书 模糊查询
     * @param name
     * @return
     * @throws SQLException
     */
    public List<Object> searchBookByName(String name) throws SQLException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        return qr.query("select name from book where name like ?", new ColumnListHandler(),"%"+name+"%");
    }
}

 

 

 

 

 

参考资料：

基础：MySQL数据库学习笔记（十二）----开源工具DbUtils的使用（数据库的增删改查）

Apache的DBUtils框架学习

开源框架：DBUtils使用详解