dbutils框架简化JDBC开发

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，它是对JDBC的简单封装， 学习成本极低，并且使用dbutils能极大简化JDBC编码的工作量，同时也不会影响程序的性能。

要用到的jar包：commons-dbutils-1.4.jar

1. 建表：

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.使用dbutils完成对数据库的增删改查

连接数据库时就不需要自己释放连接JdbcUtils_dbcp

package cn.itcast.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;

/**
 * Created by yvettee on 2017/10/9.
 */
public class JdbcUtils_dbcp {
    private static DataSource ds = null;

    static {
        try {
            InputStream in = JdbcUtils_dbcp.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
            Properties prop = new Properties();
            prop.load(in);
            BasicDataSourceFactory factory = new BasicDataSourceFactory();
            ds = factory.createDataSource(prop);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static DataSource getDataSource() {
        return ds;
    }
}

//使用dbutils完成数据库的crud
    
    @Test
    public void insert() throws SQLException{
        //上来就给它一个连接池
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
        Object params[] = {2,"bbb","123","aa@sina.com",new Date()};
        runner.update(sql, params);
    }
    
    @Test
    public void update() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "update users set email=? where id=?";
        Object params[] = {"aaaaaa@sina.com",1};
        runner.update(sql, params);
    }
    
    @Test
    public void delete() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "delete from users where id=?";
        runner.update(sql, 1);
    }
    
    @Test
    public void find() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users where id=?";
        User user = (User) runner.query(sql, 1, new BeanHandler(User.class));
        System.out.println(user.getEmail());
    }
    
    
    @Test
    public void getAll() throws Exception{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users";
        List list = (List) runner.query(sql, new BeanListHandler(User.class));
        System.out.println(list);
    }
    
    @Test
    public void batch() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
        Object params[][] = new Object[3][5];
        for(int i=0;i<params.length;i++){  //3
            params[i] = new Object[]{i+1,"aa"+i,"123",i + "@sina.com",new Date()};
        }
        runner.batch(sql, params);
    }

我们在查询的过程中，经常要对查询的结果进行处理，那么只需要了解到Dbutils给我们提供的一些常见结果集处理器，碰到不同的查询情况就可以解决了。

ResultSetHandler接口的实现类：

• ArrayHandler：把结果集中的第一行数据转成对象数组
• ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中
• BeanHandler：把结果集中的第一行数据封装到一个对应的JavaBean实例中
• BeanListHandler：把结果集中的第一行数据都封装到一个对应的JavaBean实例中，存放到List里。
• ColumnListHandler：将结果集中某一列数据存放到List里。
• KeyedHandler(name)：把结果集中的每一行数据都封装到一个Map里，然后再把这些map再存到一个map里，其key为指定的key。
• MapHandler：把结果集中的第一行数据封装到一个Map里，key是列名，value是对应的值。
• MapListHandler：把结果集中的每一行数据都封装到一个Map里，然后再存放到List中。

测试dbutils的各个结果集处理器

@Test
    public void test1() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users";
        Object result[] = (Object[]) runner.query(sql, new ArrayHandler());
        System.out.println(result[0]);
        System.out.println(result[1]);
    }
    
    
    @Test
    public void test2() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users";
        List list = (List) runner.query(sql, new ArrayListHandler());
        System.out.println(list);
    }
    
    @Test
    public void test3() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users";
        List list = (List) runner.query(sql, new ColumnListHandler1("name"));
        System.out.println(list);
    }
    
    
    @Test
    public void test4() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from users";
        Map<Integer,Map<String,Object>> map = (Map) runner.query(sql, new KeyedHandler("id"));
        for(Map.Entry<Integer,Map<String,Object>> me : map.entrySet()){
            int id = me.getKey();
            for(Map.Entry<String, Object> entry : me.getValue().entrySet()){
                String name = entry.getKey();
                Object value = entry.getValue();
                System.out.println(name + "=" + value);
            }
        }
    }
    
    @Test
    public void test5() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from users";
        //Object result[] = (Object[]) runner.query(sql, new ArrayHandler());
        /*long totalrecord = (Long)result[0];
        int num = (int)totalrecord;
        System.out.println(num);
        int totalrecord = ((Long)result[0]).intValue();
        */
        
        int totalrecord = ((Long)runner.query(sql, new ScalarHandler(1))).intValue();
        System.out.println(totalrecord);
    }