java学习笔记—第三方操作数据库包专诚接收DataSource-dbutils （30）

工厂类创建一个唯一的dataSorce
package cn.itcast.utils;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DataSourceUtils {
    private static DataSource ds;
    static{
        try{
            //读取资源文件
            InputStream in = DataSourceUtils.class
                                .getClassLoader().getResourceAsStream("dbcp.properties");
            //读取资源对象
            Properties prop = new Properties();
            prop.load(in);
            //创建ds
            ds = new BasicDataSourceFactory().createDataSource(prop);
        }catch(Exception e){
            throw new RuntimeException(e.getMessage(),e);
        }
    }
    //返回整个池对象
    public static DataSource getDatasSource(){
        return ds;
    }
}

@Test
    public void insert1() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "insert into users values('U002','李四','888')";
        run.update(sql);
    }
    //接收?
    @Test
    public void insert2() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "insert into users values(?,?,?)";
        run.update(sql,"U003","王五","7777");
    }
    //删除
    @Test
    public void del1() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "delete from users where name=?";
        int len = run.update(sql,"李四");
        System.err.println(len);
    }
    @Test
    public void udpate1() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "update users set name=? where id=?";
        run.update(sql,"赵'七","U001");
    }

ArrayHandler,　　－　用于查询一行唯一的数据。Object[]

QueryRunner run = new QueryRunner(getDatasSource());

       String sql = "select * from users";

       Object[] os = run.query(sql,new ArrayHandler());

       for(Object o:os){

           System.err.println(o);

       }

ArrayListHandler,  - 将所有的结果封装成List<Object[]>
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select * from users";
        List<Object[]> list = run.query(sql,new ArrayListHandler());
        for(Object[] os:list){
            for(Object o:os){
                System.err.print(o+"\t");
            }
            System.err.println("\n=====");
        }

 BeanHandler,  　－　返回一个 Bean，结果封装到Bean。只查询第一行。适合根据id查询唯一的一行。
@Test
    public void query2() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select * from users where id=?";
        User user = 
                run.query(sql,new BeanHandler<User>(User.class),"U003");
        System.err.println(user);
    }

BeanListHandler , 　－　返回List<Bean>所有行，都封装到List，适合于查询多个结果
@Test
    public void query3() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select * from users";
        List<User> us = 
                run.query(sql,new BeanListHandler<User>(User.class));
        for(User u:us){
            System.err.println(u);
        }
        
    }

ColumnListHandler,　－　查询一个字段时就可以使用这种方式 select name from table;
QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select * from users";
        List<Object> list = run.query(sql, new ColumnListHandler("pwd"));
        for(Object o:list){
            System.err.println(o);
        }

 KeyedHandler -- 
QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select * from users";
        Map<Object,Map<String,Object>> mm = 
                run.query(sql, new KeyedHandler("id"));
        Iterator it = mm.keySet().iterator();
        while(it.hasNext()){
            Map m1 = mm.get(it.next());
            System.err.println(m1.get("id")+","+m1.get("name")+","+m1.get("pwd"));
        }

, MapHandler ,　　与 BeanHandler应，只返回第一行，封装成Map类型　－　也适合于查询一个结果集的
    适合于关联查询，且没有匹配的Bean。

MapListHandler , 　－　
List<Map>
@Test
    public void query6() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "SELECT u.name as uname,c.name as cname"+
                     " FROM users u INNER JOIN contacts c ON u.id=c.uid";
        System.err.println(sql);
        List<Map<String,Object>> mm = run.query(sql,new MapListHandler());
        System.err.println(mm);
    }
SELECT u.name as uname,c.name as cname FROM users u INNER JOIN contacts c ON u.id=c.uid
[{uname=Jack, cname=Rose}, {uname=Jack, cname=李四}, {uname=Jack, cname=王五}, {uname=张三, cname=马六}, {uname=张三, cname=赵七}]

ScalarHandler – 标量- 用于select count(1)|sum|avg|max …一些聚合函数
@Test
    public void query7() throws Exception{
        QueryRunner run = new QueryRunner(getDatasSource());
        String sql = "select count(*) from contacts";
        Object o = run.query(sql,new ScalarHandler());
        Integer ss = Integer.valueOf(o.toString());
        System.err.println(ss);
    }