自己编写update,query,queryForObject,queryForMap,queryForList方法

1.新建一个工程,写一个工具类，该类至少有1个无参构造，2个有参构造，参数分别是：连接对象或连接池对象。
2.该类中至少要有实现单表：增删改的update方法，和实现各种查询的query,queryForObject,queryForMap,queryForList方法
3.将这个类所在的工程打成jar包，导入另一个工程进行测试使用。

public final class JDBCUtil {
    private DataSource da;
    private Connection con;

    public JDBCUtil(DataSource da) {
        this.da = da;
        try {
             this.con=da.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public DataSource getDa() {
        return da;
    }

    public void setDa(DataSource da) {
        this.da = da;
    }

    public Connection getCon() {
        return con;
    }

    public void setCon(Connection con) {
        this.con = con;
    }

    public JDBCUtil(Connection con) {
        this.con = con;
    }

    private JDBCUtil() {

    }
    // //增删改的update方法
    public int updata(String sql,Object ...args)  {
        try {
            //3.获取执行sql的对象
            PreparedStatement pstm = con.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                pstm.setObject(i+1,args[i]);
            }
            return pstm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    query方法 把每一个对象放入List集合
    public <T> List<T> query(String sql, Class<T> c, Object ...args){
        try {
            //3.获取执行sql的对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //4.给占位符赋值
            for (int i = 0; i <args.length ; i++) {
                pstm.setObject(i+1,args[i]);
            }
            //5.执行sql
            ResultSet rs = pstm.executeQuery();
            //6.处理结果集--拆开rs
            //元数据--可以获取结果集中的列数，列名，列类型
            ResultSetMetaData md = rs.getMetaData();
            //获取总列数
            int columnCount = md.getColumnCount();
            ArrayList<T> ts = new ArrayList<>();
            while (rs.next()){//外层循环遍历每一行
                T t = c.newInstance();
                for (int i = 1; i <=columnCount ; i++) {//内层循环遍历每一行的每一个单元格
                    Object value = rs.getObject(i);
                    //获取每一个列的列名--和T中的属性名一致的
                    String columnName = md.getColumnName(i).toLowerCase();
                    Field f = c.getDeclaredField(columnName);
                    //开启私有属性的操作权限
                    f.setAccessible(true);
                    f.set(t,value);
                }
                ts.add(t);
            }

            return ts;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //测试queryForList方法 通过键值对的把一行数据放入map集合 在把map集合放入list集合
    public Map<String, Object> queryForMap(String sql,Object ...args){
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                pstm.setObject(i+1,args[i]);
            }
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            HashMap<String, Object> hashMap = new HashMap<>();
            if (rs.next()){
                for (int i = 1; i <=columnCount ; i++) {
                    Object value = rs.getObject(i);
                    String columnName = md.getColumnName(i).toLowerCase();
                    hashMap.put(columnName,value);
                }
            }
            return hashMap;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //queryForObject 查询聚合函数 或者 查询一行数据放入一个对象中
    public List<Map<String, Object>> queryForList(String sql,  Object ...args){
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                pstm.setObject(i+1,args[i]);
            }
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            ArrayList<Map<String, Object>> ts = new ArrayList<>();
            while (rs.next()){
                HashMap<String, Object> map = new HashMap<>();
                for (int i = 1; i <=columnCount ; i++) {
                    Object value = rs.getObject(i);
                    String columnName = md.getColumnName(i);
                    map.put(columnName,value);
                }
                ts.add(map);
            }
            return ts;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //queryForObject 查询聚合函数 或者 查询一行数据放入一个对象中
    public <T> T queryForObject(String sql, Class<T> c, Object ...args){
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
                pstm.setObject(i+1,args[i]);
            }
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            T t=null;
            if(rs.next()){
                if((sql.toLowerCase().contains("count")||sql.toLowerCase().contains("sum")||
                        sql.toLowerCase().contains("max")||sql.toLowerCase().contains("min")||
                        sql.toLowerCase().contains("avg"))&&columnCount==1){
                    return (T)rs.getObject(1);
                }
                t=c.newInstance();
                for (int i = 1; i <=columnCount ; i++) {
                    Object value = rs.getObject(i);
                    String columnName = md.getColumnName(i).toLowerCase();
                    Field f = c.getDeclaredField(columnName);
                    f.setAccessible(true);
                    f.set(t,value);
                }
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

测试类

public class Hello01 {
   private static DataSource ds;
    static {
        try {
            InputStream is = Hello01.class.getClassLoader().getResourceAsStream("druid.properties");
            Properties prop = new Properties();
            prop.load(is);
            ds = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Test//测试增删改的update方法
    public void test1(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
        int i = jdbcUtils.update("update emp set NAME =? where id =? ", "观音", 5);
        System.out.println(i);
    }

    @Test//测试query方法 把每一个对象放入List集合
    public void test2(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
        List<Emp> emp = jdbcUtils.query("select * from emp", Emp.class);
        System.out.println(emp);
    }

    @Test//测试queryForMap方法 通过键值对的把一行数据放入map集合
    public void test3(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
        Map<String, Object> map = jdbcUtils.queryForMap("select * from emp");
        System.out.println(map);
    }

    @Test//测试queryForList方法 通过键值对的把一行数据放入map集合 在把map集合放入list集合
    public void test4(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
       List<Map<String, Object>>  list = jdbcUtils.queryForList("select * from emp");
        System.out.println(list);
    }

    @Test//测试queryForObject 查询聚合函数 或者 查询一行数据放入一个对象中
    /*public void test5(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
        Double aLong = jdbcUtils.queryForObject("select max(salary) from emp", Double.class);
        System.out.println(aLong);
    }
*/
    public void test6(){
        JDBCUtils jdbcUtils = new JDBCUtils(ds);
        Emp emp = jdbcUtils.queryForObject("select * from emp where id=?", Emp.class, 1);
        System.out.println(emp);


    }

}
}

实体类：Emp

public class Emp {
    private Integer id;
    private String name;
    private  String gender;
    private Double salary;
    private Date join_date;
    private Integer dept_id;
    private Dept dept;
    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", salary=" + salary +
                ", join_date=" + join_date +
                ", dept_id=" + dept_id +
                '}';
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Date getJoin_date() {
        return join_date;
    }

    public void setJoin_date(Date join_date) {
        this.join_date = join_date;
    }

    public Integer getDept_id() {
        return dept_id;
    }

    public void setDept_id(Integer dept_id) {
        this.dept_id = dept_id;
    }

druid.properties文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db1?useSSLfalse&serverTimezone=UTC
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000