CShop Project 03: Test QueryRunner & Ｈandlers

1. 查询一条记录

public static void main(String[] args) throws Exception {
        DataSource ds = new ComboPooledDataSource();
        
        QueryRunner r = new QueryRunner(ds);
        String sql = "select * from goods where id = ?";
        Map<String, Object> map = r.query(sql,new MapHandler(),3); // 查询id为3的商品信息
        for(String key : map.keySet()) {
            System.out.println(key+":"+map.get(key)); // <K,V>中K是列名, V是值
        }     
    }

 2.  使用QueryRunner做增加、删除和修改操作

<1> 删除

public static void main(String[] args) throws Exception {
        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "delete from type where id in(?,?)";
        int count = r.update(sql,6,7); // 类型6和类型7被删除了
        System.out.println(count);
        
    }

<2>修改

public static void main(String[] args) throws Exception {
        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "update type set name='生日系列' where id = ? ";
        int count = r.update(sql,8);
        System.out.println(count); // count 返回  1    
}

<3> 插入

public static void main(String[] args) throws Exception {
        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "insert into type (name) values(?) ";
        int count = r.update(sql,"特惠系列");
        System.out.println(count); // count 返回  1    
}

3. ArrayHandler和ArrayListHandler处理结果集

<1> ArrayHandler

　　　　 DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select * from goods";
        Object[] res = r.query(sql, new ArrayHandler()); 
        for(Object o : res) {
            System.out.println(o);
        } // 只输出了第一条记录

<2> ArrayListHandler

        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select * from goods";
        List<Object[]> list = r.query(sql, new ArrayListHandler()); 
        
        for(Object[] objs : list) {
            for(Object o : objs) {
                System.out.print(o+" ");
            }
            System.out.println();
        } // 输出了所有记录            

 4. BeanHandler得到一条记录 (方便准确地得到某个数据)

<1>　　为了方便查询, 对应着数据库里的数据, 创建goods.java

<2> BeanHandler (能够将结果集转化为对象)

　　　　 DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select * from goods";
        Goods goods = r.query(sql, new BeanHandler<Goods>(Goods.class)); 
        System.out.println(goods);    

 <3> 如果创建的Class数据与数据库不一致，则找不到对应的数据：

 

5.  BeanListHandler、MapListHandler、ScalarHandler等其他Handler

<1> 使用BeanListHandler获得多条记录

　　　　  DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select * from goods";
        List<Goods> list = r.query(sql, new BeanListHandler<Goods>(Goods.class)); 
        for(Goods good : list) {
            System.out.println(good);
        }

 <2> ColumnListHandler - 得到某一列的数据

        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select * from goods";
        List<Object> list = r.query(sql, new ColumnListHandler<Object>("name")); 
        // 得到"name"这一列的数据
        for(Object o : list) {
            System.out.println(o);
        }    

 <3> ScalarHandler - 统计个数

        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        String sql = "select count(*) from goods"; // 计算一共有多少条记录
        long count = r.query(sql, new ScalarHandler<Long>()); 
        System.out.println(count);
        // 结果 : goods表中共有 176 条 记录

<4> MapHandler / MapListHandler

        DataSource ds = new ComboPooledDataSource();
        QueryRunner r = new QueryRunner(ds);
        
        // 得到结果集里面的一条记录
        String sql = "select * from goods"; 
        Map<String,Object> map = r.query(sql, new MapHandler()); 
        for(String key: map.keySet()) {
            System.out.println(key+" "+ map.get(key));
        }
        
        // 多条记录
        String sql = "select * from goods"; 
        List<Map<String,Object>> list = r.query(sql, new MapListHandler()); 
        for(Map<String,Object> map: list) {
            for(String key: map.keySet()) {
                System.out.print(key+" "+ map.get(key)+" ");
            }
            System.out.println();
        }