利用反射封装对student表的单条查询（JDBC连接MySQL8.0.28）

一、直接查询student表

@Test
    /**
     * 直接查询student表
     */
    public void test1() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select id,name from student where id = ?;";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, 1001);
            resultSet = ps.executeQuery();
            if(resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                Student student = new Student(id, name);
                System.out.println(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection, ps, resultSet);
        }
    }

二、 对student表的通用查询（封装）

@Test
    public void test() {
        /*
        * 常常Java类中属性名与表中列名不同
        * 因此必须对查询起别名，使其与类属性名一致，方便通过属性名反射给结果对象赋值
        * 且若没取别名，必须用getColumnLabel()也能获取列名
        * 使用ResultSetMetaData获取结果集信息，必须用getColumnLabel()代替getColumnName()
        * */
        //对查询起别名，使表名对应类属性名
        String sql = "select id stuId, name stuName from student where id = ?;";
        //调用封装好的查询方法
        Student student = studentQuery(sql, 1001);
        System.out.println(student);
    }

    /**
     * 将对student表的查询封装起来
     * @param sql   查询语句
     * @param args  sql中的占位
     * @return
     */
    public Student studentQuery(String sql, Object...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for(int i = 0; i < args.length; i++) {
                //对sql语句设置占位符
                ps.setObject(i + 1, args[i]);
            }
            //获取结果集
            resultSet = ps.executeQuery();
            //获取结果集的元数据（数据的描述）
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //获取结果集列数
            int columnCount = rsmd.getColumnCount();
            if(resultSet.next()) {
                Student student = new Student();    //准备将查询结果写入student对象
                for(int i = 0; i < columnCount; i++) {
                    //获取每个列的值
                    Object objectValue = resultSet.getObject(i + 1);
                    //获取结果集每个列的别名 ---> 对应Java类属性名
                    //String columnLabel = rsmd.getColumnName(i + 1);   //不靠谱，不推荐使用！！！
                    String columnLabel = rsmd.getColumnLabel(i + 1);    //推荐使用！！！
                    //利用反射获取列名columnName对应的属性
                    Field field = Student.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    //将student对象对应的属性赋值为objectValue
                    field.set(student, objectValue);
                }
                return student;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection, ps, resultSet);
        }
        return null;
    }

示意图：

三、Student类（student表的抽象）

/**
 * ORM编程思想
 * 一个表对应Java一个类
 * 一条记录对应java类一个对象
 * 表中一个字段对应java类一个属性
 * MySQL数据库student表对应的student类
 * @author bh
 * @create 2022-03-04 15:55
 */
public class Student {
    private int stuId;
    private String stuName;

    public Student() {
    }

    public Student(int stuId, String stuName) {
        this.stuId = stuId;
        this.stuName = stuName;
    }

    public int getStuId() {
        return stuId;
    }

    public void setStuId(int stuId) {
        this.stuId = stuId;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuId=" + stuId +
                ", stuName='" + stuName + '\'' +
                '}';
    }
}

四、上面使用到的工具类

    /**
     * 获取MySQL数据库dbtest1的连接
     * @return Connection连接对象
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        //类加载：同时自动执行静态代码块(自动注册驱动)
        Class.forName("com.mysql.cj.jdbc.Driver");
        System.out.println("驱动加载成功");
        connection = DriverManager.getConnection("jdbc:mysql://localhost/dbtest1?serverTimezone=UTC", "root", "abc123");
        System.out.println("连接mysql dbtest1数据库成功");
        return connection;
    }

    /**
     * 关闭SQL连接
     * @param connection
     * @param ps
     */
    public static void closeResource(Connection connection, PreparedStatement ps, ResultSet resultSet) {
        try {
            if(connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps != null) ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(resultSet != null) resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }