结合反射和原始的jdbc模拟查询和封装的实现

---

前提准备

ps:表名和实体类同名，列名和列类型作为实体类的成员属性
实体类emp1

import java.util.Date;

public class Emp1 {
    private Integer id;
    private String name;
    private String gender;
    private Double salary;
    private Date join_date;
    private Integer dept_id;

    public String toString() {
        return "Emp1{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", gender='" + gender + '\'' +
                ", salary=" + salary +
                ", join_date=" + join_date +
                ", dept_id=" + dept_id +
                '}';
    }

    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;
    }
}

表emp1

CREATE TABLE `emp1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) DEFAULT NULL,
  `gender` CHAR(1) DEFAULT NULL,
  `salary` DOUBLE DEFAULT NULL,
  `join_date` DATE DEFAULT NULL,
  `dept_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `emp1_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept1` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

---

二、使用步骤

1.观察如下代码

可以确定三个参数：String sql(sql语句)，Class c(实体类)，Object…args(可变形参)
同时保证代码的灵活性，使用泛型

public void test1(){
        List<Emp1> list = template.query("select * from emp1", new BeanPropertyRowMapper<Emp1>(Emp1.class));
        System.out.println(list);
    }

2.开始实现

iimport org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TestJdbcQuery {
    private JdbcTemplate template=new JdbcTemplate(JdbcUtil.getDs());
    @Test
    public void testQuery(){
        List<Emp1> emp1s = myQuery("select * from emp1 where id>?", Emp1.class, 3);
        System.out.println(emp1s);
    }
    //结合反射和原始的jdbc模拟查询和封装的实现
    public <T> List<T> myQuery(String sql,Class<T> c,Object...args){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql:///db3?useSSL=false&serverTimezone=UTC", "root", "123456");
            //3.获取执行sql的对象
            PreparedStatement pstm = con.prepareStatement(sql);
            //给占位符赋值
            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);
                    String columnName = md.getColumnName(i).toLowerCase();//获取每一列的列名--和T中的属性名一致的
                    Field f = c.getDeclaredField(columnName);
                    f.setAccessible(true);//开启私有属性的操作权限
                    f.set(t,value);
                }
                ts.add(t);
            }
            return ts;
        } catch (Exception e) {
            e.printStackTrace();
        }
            return null;
    }

实现效果如下：

希望能和您一起进步！！