Apache——DBUtils

需要导入的Jar包：commons-dbutils-1.2.jar

简单的CRUD+总记录数：

package com.kexin.demo;

import java.sql.SQLException;
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.junit.Test;

import com.kexin.domain.Account;
import com.kexin.utils.JdbcUtils;

public class Demo1 {
    @Test
    public void insert() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into account(id,name,money) values(?,?,?)";
        Object params[] = {1,"vvv",1000};
        qrunner.update(sql, params);
    }
    @Test
    public void update() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "update account set money =? where id =?";
        Object params[] ={900,1};
        qrunner.update(sql,params);
    }
    @Test
    public void delete() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "delete from account where id = ?";
        Object params[] = {1};
        qrunner.update(sql, params);
    }
    @SuppressWarnings("deprecation")
    @Test
    public void find() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account where id = ?";
        Object params[] = {2};
        Account acc = (Account) qrunner.query(sql, params, new BeanHandler(Account.class));
        System.out.println(acc.getName()+"\t"+acc.getMoney());
    }
    @Test
    public void getAll() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from account";
        @SuppressWarnings("unchecked")
        List<Account> list = (List<Account>) qrunner.query(sql, new BeanListHandler(Account.class));
        for(int i =0;i<list.size();i++){
            System.out.println(((Account)list.get(i)).getName()+"\t"+((Account)list.get(i)).getMoney());
        }
    }
    @Test
    public void batch() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "insert into account(name,money) values(?,?)";
        Object params[][] = {{"aaa",123},{"bbb",321},{"ccc",444}};
        qrunner.batch(sql, params);
    }
    @Test
    public void getRecords() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from account";
        Object rs[] = (Object[]) qrunner.query(sql, new ArrayHandler());
        int totalRecords = ((Long)rs[0]).intValue();
        System.out.println(totalRecords);
    }
    @Test
    public void getRecords1() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from account";
        int totalRecords = ((Long)qrunner.query(sql, new ScalarHandler())).intValue();
        System.out.println(totalRecords);
    }
}

ResultSetHandle接口的实现类：

• ArrayHandle：把结果集中的第一行数据转成对象数组
• ArrayListHandle：把结果集中的每一行数据都转成一个数组，再存放到List中
• BeanHandle：将结果集中的每一行数据都封装到一个对应的JavaBean实例中
• BeanListHandle：将结果集中的每一行数据都封装到一个对应的JavaBean对象中，存放到List中
• ColumnListHandle：将结果集中的某一列数据存放到List中
• KeyedHandle(name)：将结果集中的每一行数据都封装到一个Map中，再把map存到一个map中，其key为指定的key
• MapHandle：将结果集中的第一行数据封装到一个Map里，key是列名，value是对应的值
• MapListHandle：将结果集中的每一行数据都放到map里，再存放到List中

package com.kexin.demo;

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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

import com.kexin.utils.JdbcUtils;

public class Demo2 {
    @Test
    public void TestArrayHandler() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account where id = ?";
        Object params[] = {2};
        Object rs[] = (Object[]) qrunner.query(sql, params, new ArrayHandler());
        for(int i=0;i<rs.length;i++){
            System.out.print(rs[i]+"\t");
        }
    }
    @Test
    public void TestArrayListHandle() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account";
        List list =  (List) qrunner.query(sql,new ArrayListHandler());
        for(int i=0;i<list.size();i++){
            System.out.print(((Object[])list.get(i))[0]+"\t");
        }
    }
    @Test
    public void TestColumnListHandle() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account";
        List list =  (List) qrunner.query(sql,new ColumnListHandler("name"));
        for(int i=0;i<list.size();i++){
            System.out.print((list.get(i))+"\t");
        }
    }
    @Test
    public void TestKeyedHandle() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account";
        Map map =  (Map) qrunner.query(sql,new KeyedHandler("id"));
        for(int i=0;i<map.size();i++){
            System.out.print(((Map)map.get(i+1)).get("name")+"\t");
        }
    }
    @Test
    public void TestMapHandle() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account";
        Map map =  (Map) qrunner.query(sql,new MapHandler());
        System.out.print((map.get("name"))+"\t");
    }
    @Test
    public void TestMapListHandle() throws SQLException{
        QueryRunner qrunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "select * from account";
        List list =  (List) qrunner.query(sql,new MapListHandler());
        for(int i=0;i<list.size();i++){
            System.out.print(((Map)list.get(i)).get("name")+"\t");
        }
    }
}