DBUtils工具类的使用

package pers.C082802.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import pers.C082802.dao.EmpDao;
import pers.C082802.domain.Emp;
import pers.C082802.utils.DataSourceUtils;

import java.sql.SQLException;
import java.util.List;

/*
 * wgy 2019/8/28 9:32
 * 佛祖保佑，永无BUG!
 */
public class EmpDaoImpl implements EmpDao {

    @Override
    public List<Emp> findAll() {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
                //查询语句查询后的结果集封装成对象，(Emp.class)用来说明对象的类型是Emp类型；
            return qr.query("select * from emp", new BeanListHandler<Emp>(Emp.class)); 
        } catch (SQLException e) {
            e.printStackTrace();
            throw  new RuntimeException("查询失败", e);
        }
    }

    @Override
    public Emp findByEmpno(Integer empno) {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp where empno=?", new BeanHandler<Emp>(Emp.class),empno);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询单个对象失败", e);
        }
    }

    @Override
    public void add(Emp e) {
        //1创建查询执行器，并指定连接池
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        //2执行
        Object[] params={e.getEmpno(),e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno()};
        try {
            qr.update("insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)",params);
        } catch (SQLException e1) {
            e1.printStackTrace();
            throw new RuntimeException("添加失败",e1);
        }
    }

    @Override
    public void update(Emp e) {
        //1创建查询执行器，并指定连接池
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        //2执行
        Object[] params={e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),e.getEmpno()};
        try {
            qr.update("update  emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?",params);
        } catch (SQLException e1) {
            e1.printStackTrace();
            throw new RuntimeException("更新失败",e1);
        }

    }

    @Override
    public void delte(Integer empno) {
        //1创建查询执行器，并指定连接池
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        //2执行
        try {
            qr.update("delete from emp where empno=?",empno);
        } catch (SQLException e1) {
            e1.printStackTrace();
            throw new RuntimeException("删除失败",e1);
        }
    }

    @Override
    public long getCount()  {   // 获取数据的个数

        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            Long count = (Long) qr.query("select count(*) from emp", new ScalarHandler<>()); //ScalarHandler类：适合获取一行一列数据
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public Object[] findArrayEmp(Integer empno){

        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp where empno=?", new ArrayHandler(),empno);
        } catch (SQLException e) {
            e.printStackTrace();
            throw  new RuntimeException("查询失败", e);
        }

    }
    public List<Object[]> findArrayListEmp(){

        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp", new ArrayListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
            throw  new RuntimeException("查询失败", e);
        }

    }

    @Override
    public List<String> findNames() {
        QueryRunner qr=new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from emp", new ColumnListHandler<String>("ename"));
        } catch (SQLException e) {
            e.printStackTrace();
            throw  new RuntimeException("查询姓名失败", e);
        }
    }
}