jdbc通用查询方法

• 获取单条数据
• 获取多条数据

一、查询单条数据

    /**
     * 功能描述：获取实体
     * @param clazz
     * @param sql
     * @param objects
     * @param <T>
     * @return
     */
    public <T> T getInstance(Class<T> clazz, String sql, Object... objects){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            connection = JDBCUtils.getConnection();

            ps = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1, objects[i]);
            }
            rs = ps.executeQuery();
            // 获取结果集的元数据 :ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // 通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                // 获取实例对象
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object columnValue = rs.getObject(i + 1);
                    // 获取列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    // 给t对象指定的columnName属性，赋值为columValue：通过反射
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return null;
    }

二、查询多条数据

    public <T> List<T> queryList(Class<T> clazz, String sql, Object... args){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            // 1、获取连接
            connection = JDBCUtils.getConnection();
            // 预编译
            ps = connection.prepareStatement(sql);
            if (args != null){
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);
                }
            }

            // 执行sql，获取结果集
            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取元数据的列数
            int columnCount = rsmd.getColumnCount();
            // 返回结果
            List<T> resultList = new ArrayList<>();
            while(rs.next()){
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object object = rs.getObject(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, object);
                }
                resultList.add(t);
            }
            return resultList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return null;
    }

JDBCUtils工具类

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 功能描述：操作数据库的工具类
 * @author leo-zu
 * @date 2022-01-10
 *
 */
public class JDBCUtils {

    /**
     * 功能描述：获取数据库连接
     * @return 连接
     * @throws Exception
     */
    public static Connection getConnection() throws Exception{
        // 1、加载配置文件
        InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        pro.load(inputStream);
        String driverClass = pro.getProperty("driverClass");
        String url = pro.getProperty("url");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");

        // 2、加载驱动
        Class.forName(driverClass);

        // 3、获取连接
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 功能描述：关闭资源
     * @param conn
     * @param ps
     */
    public static void closeResource(Connection conn, Statement ps){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 功能描述：关闭资源
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}