java中根据反射设计DBUtils工具类

package com.qdu.lesson.utils;


import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@SuppressWarnings({"all"})
public class DBUtils {

    @Test
    public void test() {
        System.out.println("爪巴");
    }

    //从properties文件获得数据库连接
    public static Connection getConnection() {
        Class<?> dbUtilsClass = DBUtils.class;
        ClassLoader classLoader = dbUtilsClass.getClassLoader();
        URL resource = classLoader.getResource("jdbc.properties");
        String path = resource.getPath();
        File file = new File(path);
        Properties properties = new Properties();
        try {
            FileInputStream fileInputStream = new FileInputStream(file);
            properties.load(fileInputStream);
            String driverClass = properties.getProperty("db.driverClass");
            String jdbcUrl = properties.getProperty("db.jdbcUrl");
            String userName = properties.getProperty("db.username");
            String password = properties.getProperty("db.password");
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(jdbcUrl, userName, password);
            return connection;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    //对数据库进行增删改操作
    public static int insert(String sql, Object... args) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        if (connection != null) {
            try {
                preparedStatement = connection.prepareStatement(sql);

                if (args != null && args.length > 0) {
                    int temp = 1;
                    for (Object obj : args) {
                        preparedStatement.setObject(temp, obj);
                        temp++;
                    }
                }

                return preparedStatement.executeUpdate();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return -1;
    }

    //根据类中属性名称得到set方法名称
    public static String buildSetterMethodName(String field) {
        StringBuilder stringBuilder = new StringBuilder();
        char firstChar = field.charAt(0);
        stringBuilder.append("set").append(Character.toUpperCase(firstChar)).append(field.substring(1));

        return stringBuilder.toString();
    }

    //执行select的sql语句
    public static List<?> executeQuery(Class<?> clazz, String sql, Object... args) {
        List list = new ArrayList<>();
        Connection connection = getConnection();
        if (connection != null) {
            PreparedStatement preparedStatement2 = null;
            ResultSet resultSet = null;

            try {
                preparedStatement2 = connection.prepareStatement(sql);
                if (args != null && args.length > 0) {
                    int temp = 1;
                    for (Object obj : args) {
                        preparedStatement2.setObject(temp, obj);
                        temp++;

                    }
                }
                //得到结果集
                resultSet = preparedStatement2.executeQuery();

                //通过class对象得到类中所有属性的集合
                Field[] declaredFields = clazz.getDeclaredFields();

                while (resultSet.next()) {
                    Object o = clazz.newInstance();
                    for (Field field : declaredFields) {
                        if (field.getModifiers() == Modifier.PRIVATE) {
                            String fieldName = field.getName();

                            //根据属性得到数据类型
                            field.getType();
                            //根据类的属性名称得到对应的表中字段的名称
                            String dbByField = getDBByField(fieldName);
                            //得到结果集中数据
                            Object infoInDB = resultSet.getObject(dbByField);
                            //根据类的属性名称得到对应的set方法名称
                            String fieldSet = buildSetterMethodName(fieldName);
                            //根据方法名称得到方法实例
                            Method setName = clazz.getMethod(fieldSet, field.getType());


                            setName.invoke(o, infoInDB);


                        }

                    }
                    list.add(o);


                }
                return list;

            } catch (SQLException | NoSuchMethodException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
                e.printStackTrace();
            } finally {
                try {
                    preparedStatement2.close();
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return null;
    }

    //根据属性名称得到数据库中的字段名称
    public static String getDBByField(String field) {
        StringBuilder stringBuilder = new StringBuilder();
        char[] chars = field.toCharArray();
        for (char temp : chars) {
            if (Character.isUpperCase(temp)) {
                stringBuilder.append("_");
            }
            char c = Character.toUpperCase(temp);
            stringBuilder.append(c);

        }
        return stringBuilder.toString();

    }


}

简单运行了一下

import java.util.List;

public class UserDao {
    @Test
    public void test(){
        int i = insertInTo();
        System.out.println(i);
    }
    @Test
    public void test2(){
        List<?> list = selectFromDB();
        System.out.println(list);
    }
    static int insertInTo(){
        String sql = "INSERT INTO user_test VALUES (?,?,?)";
        return DBUtils.insert(sql,"张三",18,"青岛");
    }
    static List<?> selectFromDB(){
        String sql = "SELECT * FROM user_test WHERE USER_ID = ?";
        return DBUtils.executeQuery(User.class,sql,18);
    }
}

发现了一个问题

Incorrect string value: ‘\xE5\xBC\xA0\xE4\xB8\x89’ for column ‘USER_NAME’ at row 1

检查发现是数据库的默认字符有问题 ,这里建议将默认字符更改为utf8

这里给出数据库语句

ALTER TABLE user_test DEFAULT CHARACTER SET utf8;