dbutils学习

package com.utils.test.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
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 com.javaweb.jdbc.JdbcUtil;
import com.javaweb.utils.CommonsUtils;
import com.utils.test.model.Dept;
import com.utils.test.model.Emp;
import com.utils.test.model.User;

public class UserDao {
    /**
     * 1.获取Connection
     * 2.给出sql模版，得到PreparedStatement
     * 3.赋值
     * 4.执行 excuteQuery();
     * 5.关闭
     */
    // 创建QueryRunner对象，给它一个连接池
    private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
    // 添加
    public void add(User user) throws SQLException{
        String sql = "insert into user values(null,?,?)";
        // 参数可变 sql 加上 多少个问号 多少个参数
        qr.update(sql, user.getUsername(),user.getPassword());
    }
    //修改
    public void update(User user) throws SQLException{
        String sql = "update user set username=?,password=? where id=?";
        qr.update(sql, user.getUsername(),user.getPassword(),user.getId());
    }
    // 删除
    public void delete(String id) throws SQLException{
        String sql = "delete user where id=?";
        qr.update(sql,id);
    }
    /**
     * 自定义结果集处理器
     * @param id
     * @return
     * @throws SQLException 
     */
    public User load1(int id) throws SQLException{
        String sql = "select * from user where id=?";
        // 结果集处理器
        ResultSetHandler<User> rsh = new ResultSetHandler<User>(){
                public User handle(ResultSet rs) throws SQLException {
                    if(rs.next()){
                        return new User(rs.getInt(1),rs.getString(2),rs.getString(3));
                    }
                    return null;
                }
        };
        return qr.query(sql, rsh, id);
    }
    /**
     * BeanHandler处理器     单表查询
     * 返回单个对象     
     * @param id
     * @return
     * @throws SQLException
     */
    public User load2(int id) throws SQLException{
        String sql = "select * from user where id=?";
        return qr.query(sql, new BeanHandler<User>(User.class),id);
    }
    /**
     * BeanListHandler处理器  单表查询
     * 返回多个对象
     * @return
     * @throws SQLException
     */
    public List<User> findAll() throws SQLException{
        String sql = "select * from user";
        // 把查询结果集转成List
        return qr.query(sql, new BeanListHandler<User>(User.class));
    }
    /**
     * MapHandler处理器  多表查询
     * 返回单个对象
     * @param empno
     * @return
     * @throws SQLException
     */
    @SuppressWarnings("rawtypes")
    public Emp loadEmp(int empno) throws SQLException{
        String sql = "select * from emp e, dept d where e.deptno=d.deptno and empno=?";
        // 把查询结果集转成Map
        Map map = qr.query(sql, new MapHandler(),empno);
        // 通过CommonsUtils.toBean 把Map 转换成 指定对象
        Emp emp = CommonsUtils.toBean(map, Emp.class);
        Dept dept = CommonsUtils.toBean(map, Dept.class);
        // 建立关联
        emp.setDept(dept);
        return emp;
    }
    /**
     * MapListHandler处理器  多表查询
     * 返回多个对象
     * @return
     * @throws SQLException
     */
    public List<Emp> findAllEmp() throws SQLException{
        String sql = "select * from emp e, dept d where e.deptno=d.deptno";
         List<Map<String, Object>> mapList =  qr.query(sql, new MapListHandler());
         List<Emp> empList = new ArrayList<Emp>();
        for(Map<String, Object> map : mapList){
            // 通过CommonsUtils.toBean 把Map 转换成 指定对象
            Emp emp = CommonsUtils.toBean(map, Emp.class);
            Dept dept = CommonsUtils.toBean(map, Dept.class);
            // 建立关联
            emp.setDept(dept);
            empList.add(emp);
        }
        return empList;
    }
    /**
     * ColumnListHandler 获取单列
     * @return
     * @throws SQLException
     */
    public List<Object> findEnames() throws SQLException{
        String sql = "select ename from emp";
        return qr.query(sql, new ColumnListHandler());
    }
    /**
     * ScalarHandler 获取记录总数
     * @return
     * @throws SQLException
     */
    public long count() throws SQLException{
        String sql = "select count(*) from emp";
        return (Long)qr.query(sql, new ScalarHandler());
    }
}