JdbcResultSet

JdbcResultSet

package jdbc;

import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.*;

/**
 * @author 小邱
 * @version 0.0.1
 * @description QueryResultSetTest
 * @since 2021/9/27 21:03
 */
public class QueryResultSetTest {
    //针对user表的普通查询
    @Test
    public void queryTest1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取连接
            connection = JdbcUtils.getConnection();
            //sql语句
            String sql = "SELECT `id`,`username`,`password`,`email` FROM t_user WHERE id = ?";
            //预编译sql语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            preparedStatement.setObject(1, 1);

            //执行sql语句,得到返回结果集
            resultSet = preparedStatement.executeQuery();
            //处理结果集
            //next()判断结果集下一条是否有数据，ture表示有数据，next指针下移，反之则不下移
            if (resultSet.next()) {
                //获取当前这条数据的各个字段
                int id = resultSet.getInt(1);
                String username = resultSet.getString(2);
                String password = resultSet.getString(3);
                String email = resultSet.getString(4);

                //处理各个字段数据（推荐：封装成javabean对象）
                User user = new User(id, username, password, email);
                System.out.println(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtils.close(connection, preparedStatement, resultSet);

        }


    }

    //针对user表的通用查询
    public User query(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获取连接
            connection = JdbcUtils.getConnection();
            //预编译sql
            ps = connection.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //执行sql得到返回集
            rs = ps.executeQuery();
            //获取结果集的元数据（表头）
            ResultSetMetaData metaData = rs.getMetaData();
            //通过元数据获取结果集列数
            int columnCount = metaData.getColumnCount();
            //处理结果集,该条结果读取columnCount个数据
            if (rs.next()) {
                //创建User对象
                User user = new User();
                for (int i = 0; i < columnCount; i++) {
                    //封装读取出来的数据到User对象中
                    //通过结果集获取结果集的每个列列值
                    Object value = rs.getObject(i + 1);

                    //方法一：通过元数据调用getColumnName获取结果集每个列列名(此方法注意：JavaBean属性名要与数据库字段名保持一致否则报错，)
                    String columnName = metaData.getColumnName(i + 1);
                    //方法二：通过元数据调用getColumnLabel获取结果集每个列的别名(别名要与JavaBean属性名对应)，若sql语句中没取别名则获取列名，此时JavaBean属性名与数据库字段名可以不一致
                    //String columnName = metaData.getColumnLabel(i + 1);

                    //通过反射给User对象指定的columnName赋值value
                    //通过反射获取属性
                    Field field = User.class.getDeclaredField(columnName);
                    //修改成可赋值
                    field.setAccessible(true);
                    //赋值
                    field.set(user, value);
                }
                return user;
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(connection, ps, rs);
        }
        return null;


    }

    //针对user表的通用查询测试
    @Test
    public void test(){
        String sql = "SELECT `id`,`username`,`password`,`email` FROM t_user WHERE id = ?";
        System.out.println(query(sql, 2));
    }

}