ResultSetHandler的总结

package com.qf.resultset;

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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import com.qf.domain.User;

public class TestResultSetHandler01 {
    /**
     * 1. BeanListHandler:返回值  List<T>
     *            (适合取多条数据)把查询到的每一条结果封装成实体类，再最终把每一个实体类放到list集合中
     * 2. BeanHanlder:返回值  T
     *         (适合取单条数据) 把查询到的一条结果封装成实体类。
     * 
     * 3. ArrayHandler: 返回值：  Object[]
     *        (适合取单条数据) 把查询的一条结果值依次封装到一个对象数组中。object[]
     * 4. ArrayListHandler:返回值：  List<Object[]>
     *     (适合取多条数据) 把查询的一条结果值依次封装到一个对象数组中。再最终把对象数组依次放入到list集合中。
     * 
     * 5. MapHandler:   返回值：  Map<String,Object>
     *          (适合取单条数据)把查询到的结果以键值对的形式进行封装，封装的时候列名是map的键，值就是map的值
     * 6. MapListHandler: 返回值：  List<Map<String,Object>>
     *        (适合取多条数据)把查询到的结果以键值对的形式进行封装，封装的时候列名是map的键，值就是map的值,
     * 最终把每一条数据封装到list中
     * 
     * 
     * 7. ColumnListHandler: 返回值：List<Object>
     *           
     *            取出某一列的数据存储到list集合
     *          三种取法：
     *           根据结果集的形式来决定sql语句如何写：
     *             第一种：  推荐一种
     *                ColumnListHandler()   : sql：select 具体的列名 from 表名
     *             第二种：   
     *                ColumnListHandler(列的位置int)   sql：   select * from 表名
     *             第三种：
     *                ColumnListHandler(列的名称)       sql：   select * from 表名
     *                
     * 8. ScalarHandler:适合于取单行单列的数据   
     * 
     * 9. KeyedHandler:   返回值是Map<Object,Map<String,Object> 
     *            把每一条查到的结果封装，把这条数据对应的列名当作map的键存入，把这条数据对应的列的值当作map的value存储，
                                在把每一条的数据再次存入到map中，把该条数据的唯一表示id当作键存储，这条数据的map当作外层map的value存入 
     * 
     * 
     * 必须掌握： BeanHandler  ,BeanListHandler
     *          ColumnListHandler,  ScalarHandler
     * @throws SQLException
     */

    @Test
    public void testBeanListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        List<User> list = queryRunner.query("select * from user", new BeanListHandler<User>(User.class));
        System.out.println(list);
    }

    @Test
    public void testBeanHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user where id =?";
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), 1);
        System.out.println(user);
    }

    @Test
    public void testArrayHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user where id =?";
        Object[] objects = queryRunner.query(sql, new ArrayHandler(), 1);
        for (int i = 0; i < objects.length; i++) {
            System.out.println(objects[i].toString());
        }
    }

    @Test
    public void testArrayListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user ";
        List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
        for (int i = 0; i < list.size(); i++) {
            Object[] objects = list.get(i);
            System.out.println(i + "===");
            for (int j = 0; j < objects.length; j++) {
                System.out.println(objects[j].toString());
            }
        }
    }

    @Test
    public void testMapHandler() throws SQLException {

        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user  where id= ?";
        // map中键是列名 ，值是列所对应的值
        Map<String, Object> map = queryRunner.query(sql, new MapHandler(), 1);
        for (Map.Entry<String, Object> m : map.entrySet()) {
            System.out.println(m.getKey() + "=" + m.getValue());
        }

    }

    @Test
    public void testMapListHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user ";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            System.out.println(i+"====");
            for (Map.Entry<String, Object> m : map.entrySet()) {
                System.out.println(m.getKey() + "=" + m.getValue());
            }

        }
    }
    @Test
    public void testColumnListHandler1() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select name from user ";
        List<Object> list = queryRunner.query(sql, new ColumnListHandler());
        for (int i = 0; i <list.size(); i++) {
            System.out.println(list.get(i).toString());
        }
    }
    @Test
    public void testColumnListHandler2() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user";
        //列的index从1开始
        List<Object> list = queryRunner.query(sql, new ColumnListHandler(2));
        for (int i = 0; i <list.size(); i++) {
            System.out.println(list.get(i).toString());
        }
    }
    @Test
    public void testColumnListHandler3() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from user ";
        List<Object> list = queryRunner.query(sql, new ColumnListHandler("money"));
        for (int i = 0; i <list.size(); i++) {
            System.out.println(list.get(i).toString());
        }
    }

    @Test
    //名字的第一个
    public void testScalarHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select name  from user  where id =?";
        Object object = queryRunner.query(sql, new ScalarHandler(),1);
        System.out.println(object.toString());
    }
    @Test
    //名字的第一个
    public void testScalarHandler1() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select *  from user  where id =?";
        Object object = queryRunner.query(sql, new ScalarHandler("name"),1);
        System.out.println(object.toString());
    }

    @Test
    //第二列的第一个
    public void testScalarHandler2() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select *  from user  where id =?";
        Object object = queryRunner.query(sql, new ScalarHandler(2),1);
        System.out.println(object.toString());
    }

    //获取数据库中总共有多少条数据 (单行单列)
    @Test
    public void testCount() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select count(*)  from user ";
        Object object  =  queryRunner.query(sql,new ScalarHandler());
        long count =(long) object;
        System.out.println(count);
    }
    @Test
    public void KeyedHandler() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from user";
        //查询结果： 把每一条查到的结果封装，把这条数据对应的列名当作map的键存入，把这条数据对应的列的值当作map的value存储，
       //          在把每一条的数据再次存入到map中，把该条数据的唯一表示id当作键存储，这条数据的map当作外层map的value存入         
        Map<Object, Map<String, Object>>   map = queryRunner.query(sql, new KeyedHandler());
         for (Map.Entry<Object, Map<String, Object>> m : map.entrySet()) {
             Object object  = m.getKey();
             System.out.println("==="+object); //每一条数据的id
             Map<String, Object> m1 = m.getValue();

             for (Map.Entry<String, Object> m2 : m1.entrySet()) {
                 //key:列名  value:列的值
                 System.out.println("=qqqqqq="+m2.getKey()+"=="+m2.getValue());
            }

        }

    }
}

其中这些中重要且常用的是：

BeanHandler（对象 实体类）、
BeanListHandler（每一条结果封装成实体类，再最终把每一个实体类放到list集合中）、
ColumnListHandler（单列的所有数据）、
ScalarHandler（某一行的某一列，是一个数据）

ColumnListHandler有三种：

a.是在sql语句中控制（推荐）

b.是int类型控制列

c.是string类型控制列名

ScalarHandler：

平常的都是和ColumnListHandler差不多 int ,string等用法
比较特殊的就是算加法，count(*)