ResultSetHandler的总结

傷城~ 2022-06-08 07:55 188阅读 0赞

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语句中控制（推荐） ####

![这里写图片描述][SouthEast]

#### b.是int类型控制列 ####

![这里写图片描述][SouthEast 1]

#### c.是string类型控制列名 ####

![这里写图片描述][SouthEast 2]

### ScalarHandler： ###

平常的都是和ColumnListHandler差不多 int ,string等用法  
比较特殊的就是算加法，count(\*)  
![这里写图片描述][SouthEast 3]

[SouthEast]: /images/20220608/53a49051b12f47e9b37d2205db24894c.png
[SouthEast 1]: /images/20220608/51f9eb52da564efe90e75e0d8b47ab44.png
[SouthEast 2]: /images/20220608/d50c294a5a39464fb7cb053d7f9634a1.png
[SouthEast 3]: /images/20220608/72eea9838ab142eabd46d81c933563df.png