javawebday50(ThreadLocal dbutils简易盗版 体会内部内容 queryrunner 增删)

ThreadLocal
Thread->人
Runnable->任务类
1、ThreadLocal API
    ThreadLocal类只有三个方法
        void set(Tvalue)保存值
        T get()获取值
        void remove() 移除值
2、ThreadLocal的内部是Map
    ThreadLocal内部其实是个Map来保存数据，虽然在使用ThreadLocal时只给出了值，没有给出键，其实它内部使用了当前线程作为键

common-dbutils.jar
QueryRunner
update方法
int update(String sql,Object[] params) 可执行增、删、改语句
int update(Connection con,String sqsl,Object... params)-->需要调用者提供Connection，这说明本方法不再管理Connection。支持事务

T query(String sql,ResultSetHandler rsh,Object[] params)-->可执行查询
    会先得到ResultSet，然后调用rsh的handler()把rs转换成需要的类型
T query(Connection con,String sql,ResultSetHandler rsh,Object...params) 支持事务

ResultSetHandler接口
    BeanHandler(单行)-->构造器需要一个Class类型的参数，用来把一行结果集转换成指定类型的javaBean对象

    BeanListHandler(多行)-->构造器也是需要一个Class类型的参数，用来把一行结果集转换成javaBean，那么多行就是转换成List对象。一堆javaBean    

    MapHandler(单行)-->把一行结果集转换成Map对象
        一行记录
            sid sname age gender
            100 ls    22    male
        一个Map
            {sid:100,sname:ls,age:22,gender:male}
    MapListHandler(多行)-->把一行记录转换成一个Map，多行就是多个Map，即List<Map>

    ScalarHandler(单行单列)-->通常用于 select count(*) from student 语句，结果集是单行单列的，返回一个Object 

/**
 * ThreadLocal对象的用法
 * @author Administrator
 *
 */
public class Demo1 {
    @Test
    public void fun1(){
        ThreadLocal<String> tl = new ThreadLocal<String>();
//      tl.set("hello");//存
//      String s = tl.get();//取
//      tl.remove();//删
//      System.out.println(s);
        System.out.println(tl.get());

        new Thread(){
            public void run(){
                tl.set("内部类存");
//              System.out.println("内容"+tl.get());
            }
        }.start();
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println(tl.get());//取

    }

    @Test
    public void fun2(){
        final Map<Thread,String> map = new HashMap<Thread, String>();
        map.put(Thread.currentThread(),"hello");
        System.out.println(map.get(Thread.currentThread()));

        new Thread(){
            public void run(){
                System.out.println(Thread.currentThread().getName());
                System.out.println(map.get(Thread.currentThread()));
            }
        }.start();
    }
}
/**
 * 内饰是一个Map
*/
class TL<T>{
    private Map<Thread,T> map = new HashMap<Thread, T>();

    public void set(T data){
        //使用当前线程做key
        map.put(Thread.currentThread(), data);
    }

    public T get(){
        return map.get(Thread.currentThread());
    }

    public void remove(){
        map.remove(Thread.currentThread());
    }
}

/**
 *ThreadLocal通常用在一个类的成员上 
 *多个线程访问它时，每个线程都有自己的副本，互不干扰
 *Spring中把Connection放到了ThreadLocal中
 * @author Administrator
 *
 */
class User{
    private ThreadLocal<String> usernameTl = new ThreadLocal<String>();
}

/**
 * commons-dbutils
 * 简化jdbc的代码
 * @author Administrator
 *
 */
public class Demo2 {
    @Test
    public void addStu(Stu stu){
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "insert into student values(?,?,?)";
            pstmt  = con.prepareStatement(sql);
            pstmt.setInt(1, stu.getSid());
            pstmt.setString(2, stu.getSname());
            pstmt.setDouble(3, stu.getSal());

            pstmt.executeUpdate();
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            //关闭
        }
    }
    @Test
    public void updateStu(Stu stu){
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "update student set sname=?,sal=? where sid=?";
            pstmt  = con.prepareStatement(sql);
            pstmt.setInt(3, stu.getSid());
            pstmt.setString(1, stu.getSname());
            pstmt.setDouble(2, stu.getSal());

            pstmt.executeUpdate();
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            //关闭
        }
    }
    @Test
    public void deleteStu(int sid){
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "delete from student where sid = ?";
            pstmt  = con.prepareStatement(sql);
            pstmt.setInt(1, sid);

            pstmt.executeUpdate();
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            //关闭
        }
    }

    @Test
    public Stu load(int sid){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "select * from student where sid =?";
            pstmt  = con.prepareStatement(sql);
            pstmt.setInt(1, sid);

            rs = pstmt.executeQuery();
            if(!rs.next()) return null;
            /*
             * 需要把rs转换成Stu对象
             * rs-->javaBean
             */
            Stu stu = new Stu();
            stu.setSid(rs.getInt("sid"));
            stu.setSname(rs.getString("sname"));
            stu.setSal(rs.getDouble("sal"));

            return stu;
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            //关闭
        }
    }

}

public class Demo3 {
    @Test
    public void fun1(){
        Stu s = new Stu(322,"bb1b",123.1);
        addStu(s);
        Stu s1 = load(322);
        System.out.println(s1);
    }

    public void addStu(Stu stu){
        QR qr = new QR(JdbcUtils.getDataSource());//创建对象时给出连接池
        String sql ="insert into student values(?,?,?)";//给出sql模版
        //给出参数
        Object[] params = {stu.getSid(),stu.getSname(),stu.getSal()};
        //调用update执行增、删、改
        qr.update(sql, params);

    }

    public Stu load(int sid){
        QR qr = new QR(JdbcUtils.getDataSource());//创建对象时给出连接池
        String sql ="select * from student where sid=?";//给出sql模版
        Object[] params ={sid};

        RsHandler<Stu> rh = new RsHandler() {
            @Override
            public Object handle(ResultSet rs) throws SQLException {
                if(!rs.next()) return null;
                Stu stu = new Stu();
                stu.setSid(rs.getInt("sid"));
                stu.setSname(rs.getString("sname"));
                stu.setSal(rs.getDouble("sal"));
                return  stu;
            }

        };
        return (Stu) qr.query(sql, rh, params);
    }
}

public class Demo4 {
    @Test
    public void fun1() throws SQLException{
        //创建QueryRunner，需要提供数据库连接池对象
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        //给出sql模版
        String sql ="insert into student values(?,?,?)";
        //给出参数
        Object[] params = {333,"lisiw",132.1};
        //
        qr.update(sql,params);
    }
    @Test
    public void fun2() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from student where sid =?";
        Object[] params = {333};
//      ResultSetHandler rsh  = new ResultSetHandler() {
//          @Override
//          public Object handle(ResultSet arg0) throws SQLException {
//              
//              return null;
//          }
//          
//      };
        //执行query()方法，需要给出结果集处理器，即ResultSetHandler的实现类对象
        //我们给的是BeanHandler，它实现了ResultSetHandler
        //它需要一个类型，然后会把rs中的数据封装到指定类型的javaBean对象中，然后返回javabean
        Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class),params);
        System.out.println(stu);
    }
    /**
     * BeanListHandler的应用，是多行处理器
     * 每行对应一个Stu对象
     * @throws SQLException
     */
    @Test
    public void fun3() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from student";
        List<Stu> stuList = qr.query(sql,new BeanListHandler<Stu>(Stu.class));
        System.out.println(stuList);
    }
    /**
     * MapHandler,是单行处理器，把一行转换成一个Map对象
     * @throws SQLException 
     */
    @Test
    public void fun4() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from student where sid = ?";
        Object[] params = {333};
        Map map = qr.query(sql,new MapHandler(),params);
        System.out.println(map);
    }
    /**
     * MapListHandler是多行处理器，把每行都转换成一个Map，即List<Map>
     */
    @Test
    public void fun5() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select * from student ";
        List<Map<String, Object>> mapList = qr.query(sql,new MapListHandler());
        System.out.println(mapList);
    }
    /**
     * ScalarHandler 单行单列使用时 最为合适
     * @throws SQLException
     */
    @Test
    public void fun6() throws SQLException{
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "select count(*) from student ";
        Object obj = qr.query(sql, new ScalarHandler());
        //以前返回的版本有Integer、Long、BigInteger 
        //long cnt = (long) qr.query(sql, new ScalarHandler());
        //number是数字类型的父类 可以先获取number 然后获取想要的数值类型
        Number cnt = (Number) qr.query(sql, new ScalarHandler());
        long c = cnt.longValue();
        System.out.println(obj.getClass().getName());
    }

}

public class QR <T> {
    private DataSource dataSource;

    public QR() {
        super();
    }

    public QR(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 做insert、update、delete
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql,Object... params){
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = dataSource.getConnection();//通过连接池得到连接对象
            pstmt  = con.prepareStatement(sql);//使用sql创建pstmt
            initParams(pstmt,params);//设置参数
            return pstmt.executeUpdate();//执行
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            try {
                if(pstmt!=null) pstmt.close();
                if(con!=null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
    //给参数赋值
    private void initParams(PreparedStatement pstmt, Object... params) 
            throws SQLException {
        for(int i = 0;i<params.length;i++){
            pstmt.setObject(i+1, params[i]);
        }
    }

    public T query(String sql,RsHandler<T> rh,Object... params){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();//通过连接池得到连接对象

            pstmt  = con.prepareStatement(sql);//使用sql创建pstmt
            initParams(pstmt,params);//设置参数

            rs =  pstmt.executeQuery();//执行

            return rh.handle(rs);
        } catch (Exception e) {
            //处理异常
            throw new RuntimeException(e);
        } finally{
            try {
                if(pstmt!=null) pstmt.close();
                if(con!=null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}
//用来把结果集转换成需要的类型
interface RsHandler<T>{
    public T handle(ResultSet rs) throws SQLException;
}

public class JdbcUtils {
    //配置文件的默认配置。要求必须给出c3p0-config.xml
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    /**
     * 使用连接池返回一个连接对象
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
    /**
     * 返回连接池对象
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
}