使用JDBC操作数据库时查询通用处理

使用查询数据通用处理的前提是数据库表中字段名必须和实体类的属性名相同

数据库表

实体类

//这里我使用的是lombok插件
package com.ssm.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
    private String id;
    private String username;
    private String password;
}

具体代码

//在这里我使用的是单元测试运行额程序
@Test
    public void test02(){
        String sql="select * from user";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "root", "123");
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            //下面的时候传入你的实体类
            ArrayList<User> users = this.returnResult(resultSet, User.class);
            System.out.println("users = " + users);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private <T> ArrayList<T> returnResult(ResultSet resultSet, Class<T> clazz) {
        ArrayList<T> arrayList=new ArrayList<>();
        try {
            //获取表结构
            ResultSetMetaData metaData = resultSet.getMetaData();
           //获取数据库表字段数也就是有多少列
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()){
                //通过反射创建实体类对象
                T newInstance = clazz.newInstance();
                for (int i = 1; i <=columnCount; i++) {
                    //获取数据库表中字段名结果集的属性名
                    String toLowerCase = metaData.getColumnName(i);
                    //截取首字母
                    String substring = toLowerCase.substring(0, 1);
                    //将属性名第一个首字母大写，目的是为了调用实体类的set方法
                    String firstName = toLowerCase.replaceFirst(substring, substring.toUpperCase());
                    //获取参数类型
                    Class<?> type=clazz.getDeclaredField(toLowerCase).getType();
                    //根据类型和名称来获取对应的set方法
                    Method method = clazz.getMethod("set" + firstName, type);

                    //根据属性类型利用反射进行属性赋值
                    if (type.isAssignableFrom(String.class)) {
                        method.invoke(newInstance, resultSet.getString(i));
                    } else if (type.isAssignableFrom(byte.class) || type.isAssignableFrom(Byte.class)) {
                        method.invoke(newInstance, resultSet.getByte(i));
                    } else if (type.isAssignableFrom(short.class) || type.isAssignableFrom(Short.class)) {
                        method.invoke(newInstance, resultSet.getShort(i));
                    } else if (type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)) {
                        method.invoke(newInstance, resultSet.getInt(i));
                    } else if (type.isAssignableFrom(long.class) || type.isAssignableFrom(Long.class)) {
                        method.invoke(newInstance, resultSet.getLong(i));
                    } else if (type.isAssignableFrom(float.class) || type.isAssignableFrom(Float.class)) {
                        method.invoke(newInstance, resultSet.getFloat(i));
                    } else if (type.isAssignableFrom(double.class) || type.isAssignableFrom(Double.class)) {
                        method.invoke(newInstance, resultSet.getDouble(i));
                    } else if (type.isAssignableFrom(BigDecimal.class)) {
                        method.invoke(newInstance, resultSet.getBigDecimal(i));
                    } else if (type.isAssignableFrom(boolean.class) || type.isAssignableFrom(Boolean.class)) {
                        method.invoke(newInstance, resultSet.getBoolean(i));
                    } else if (type.isAssignableFrom(Date.class)) {
                        method.invoke(newInstance, resultSet.getDate(i));
                    }
                }
                arrayList.add(newInstance);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return arrayList;
    }