实体类转SQL工具类

木芒果呀

已于 2023-11-27 15:17:27 修改

阅读量1.3k

点赞数 9

文章标签： sql java

于 2023-11-27 14:18:28 首次发布

本文链接：https://blog.csdn.net/m0_63823719/article/details/134644111

版权

主要的目标是简化开发人员在有实体类的情况下时做的重复性工作，提高开发效率。

单个实体类的转换工具类

1.EntityToTableConverter工具类

import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;

/**
 * 将实体类转换为MySQL建表语句并输出到文件中的工具类
 * Author: 木芒果
 */
public class EntityToTableConverter {

    /**
     * 将实体类转换为MySQL建表语句并输出到文件中
     *
     * @param clazz    实体类的Class对象
     * @param filePath 输出文件的路径：如"User.sql",就会在项目根目录下创建一个名为User.sql的文件，文件路径为空则将建表语句输出到控制台
     * @throws IOException 如果输出文件时发生错误
     */
    public static void convertToCreateTableStatement(Class<?> clazz, String filePath) {
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("DROP TABLE IF EXISTS ");
            String simpleName = clazz.getSimpleName();
            sb.append(convertCamelToUnderline(simpleName));
            sb.append(";\n");
            sb.append("CREATE TABLE ");
            sb.append(convertCamelToUnderline(simpleName));
            sb.append(" (\n");

             Field[] fields = clazz.getDeclaredFields();
             for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                sb.append("    ");
                String fieldName = field.getName();
                if (fieldName != "serialVersionUID") {
                    sb.append(convertCamelToUnderline(fieldName));
                    sb.append(" ");
                    sb.append(getColumnType(field.getType()));
                    if (fieldName.equals("id")) { // 如果字段名为"id"，则自动设置为主键，自动增长
                        sb.append(" PRIMARY KEY AUTO_INCREMENT");
                    }
                    sb.append(",\n");
                }
            }
            sb.deleteCharAt(sb.lastIndexOf(","));
            sb.append(");");

            if (filePath == null) {
                System.out.println(sb);
                return;
            }
            FileWriter writer = new FileWriter(filePath);
            writer.write(sb.toString());
            writer.close();
            System.out.println("建表语句已输出到" + filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取字段类型对应的MySQL数据类型
     *
     * @param fieldType 字段类型
     * @return MySQL数据类型
     */
    private static String getColumnType(Class<?> fieldType) {
        if (fieldType == String.class) {
            return "VARCHAR(255)";
        } else if (fieldType == int.class || fieldType == Integer.class) {
            return "INT";
        } else if (fieldType == long.class || fieldType == Long.class) {
            return "BIGINT";
        } else if (fieldType == double.class || fieldType == Double.class) {
            return "DOUBLE";
        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
            return "BOOLEAN";
        } else if (fieldType == byte[].class) {
            return "LONGBLOB";
        } else if (fieldType == java.util.Date.class || fieldType == java.sql.Timestamp.class || fieldType == java.time.LocalDateTime.class) {
            return "DATETIME";
        } else if (fieldType == BigDecimal.class) {
            return "DECIMAL(10,2)"; // 可根据需要调整精度和小数位数
        }
        // 如果遇到无法识别的类型，可以根据实际需求进行扩展
        return "VARCHAR(255)";
    }

    /**
     * 将驼峰命名法转换为下划线命名法
     *
     * @param camel 驼峰命名法字符串
     * @return 下划线命名法字符串
     */
    private static String convertCamelToUnderline(String camel) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < camel.length(); i++) {
            char c = camel.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i == 0) {
                    sb.append(Character.toLowerCase(c));
                } else {
                    sb.append('_');
                    sb.append(Character.toLowerCase(c));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }
}

2.测试

/**
 * @author 木芒果
 */
public class Test {
    public static void main(String[] args) {
        //生成sql文件的方式
        EntityToTableConverter.convertToCreateTableStatement(User.class, "user.sql");
        //直接输出在控制台的方式
        EntityToTableConverter.convertToCreateTableStatement(User.class, null);
    }
}

整个包中的实体类转换工具类

1.EntityToTableConverter工具类

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.JarURLConnection;
import java.net.URL;
import java.util.Enumeration;
import java.util.HashSet;
import java.util.Set;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;

/**
 * 将整个包下的实体类转换为MySQL建表语句
 * Author: 木芒果
 */
public class EntityToTableConverter {

    /**
     * 将指定包下的所有实体类转换为MySQL建表语句并输出到文件中
     *
     * @param packageName 包名
     * @param filePath    输出文件的路径
     * @throws IOException 如果输出文件时发生错误
     */
    public static void convertEntitesToCreateTableStatements(String packageName, String filePath) {
        try {
            Set<Class<?>> classes = getClassesInPackage(packageName);
            StringBuilder sb = new StringBuilder();

            for (Class<?> clazz : classes) {
                sb.append(convertToCreateTableStatement(clazz));
                sb.append("\n\n");
            }

            if (filePath == null || filePath.isEmpty()) {
                System.out.println(sb);
                return;
            }
            FileWriter writer = new FileWriter(filePath);
            writer.write(sb.toString());
            writer.close();
            System.out.println("建表语句已输出到" + filePath);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取指定包下的所有类
     *
     * @param packageName 包名
     * @return 类集合
     */
    private static Set<Class<?>> getClassesInPackage(String packageName) {
        try {
            Set<Class<?>> classes = new HashSet<>();
            String packagePath = packageName.replace(".", "/");
            ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
            Enumeration<URL> resources = classLoader.getResources(packagePath);

            while (resources.hasMoreElements()) {
                URL resource = resources.nextElement();

                if (resource.getProtocol().equals("jar")) {
                    JarURLConnection jarConnection = (JarURLConnection) resource.openConnection();
                    JarFile jarFile = jarConnection.getJarFile();

                    for (Enumeration<JarEntry> entries = jarFile.entries(); entries.hasMoreElements(); ) {
                        JarEntry entry = entries.nextElement();
                        String entryName = entry.getName();

                        if (entryName.startsWith(packagePath) && entryName.endsWith(".class")) {
                            String className = entryName.substring(0, entryName.length() - 6)
                                    .replace("/", ".");
                            Class<?> clazz = Class.forName(className);
                            classes.add(clazz);
                        }
                    }
                } else if (resource.getProtocol().equals("file")) {
                    File packageDir = new File(resource.getFile());

                    if (packageDir.exists()) {
                        File[] files = packageDir.listFiles();

                        for (File file : files) {
                            String fileName = file.getName();
                            String className;

                            if (fileName.endsWith(".class")) {
                                className = packageName + "." + fileName.substring(0, fileName.length() - 6);
                                Class<?> clazz = Class.forName(className);
                                classes.add(clazz);
                            } else if (file.isDirectory()) {
                                String subPackageName = packageName + "." + fileName;
                                classes.addAll(getClassesInPackage(subPackageName));
                            }
                        }
                    }
                }
            }
            return classes;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 将实体类转换为MySQL建表语句
     *
     * @param clazz 实体类的Class对象
     * @return 建表语句
     */
    private static String convertToCreateTableStatement(Class<?> clazz) {
        StringBuilder sb = new StringBuilder();
        sb.append("DROP TABLE IF EXISTS ");
        String simpleName = clazz.getSimpleName();
        sb.append(convertCamelToUnderline(simpleName));
        sb.append(";\n");
        sb.append("CREATE TABLE ");
        sb.append(convertCamelToUnderline(simpleName));
        sb.append(" (\n");

        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            sb.append("    ");
            String fieldName = field.getName();
            if (fieldName != "serialVersionUID") {
                sb.append(convertCamelToUnderline(fieldName));
                sb.append(" ");
                sb.append(getColumnType(field.getType()));
                if (fieldName.equals("id")) { // 如果字段名为"id"，则自动设置为主键，自动增长
                    sb.append(" PRIMARY KEY AUTO_INCREMENT");
                }
                sb.append(",\n");
            }
        }

        sb.deleteCharAt(sb.lastIndexOf(","));
        sb.append("\n);");

        return sb.toString();
    }

    /**
     * 获取字段类型对应的MySQL数据类型
     *
     * @param fieldType 字段类型
     * @return MySQL数据类型
     */
    private static String getColumnType(Class<?> fieldType) {
        if (fieldType == String.class) {
            return "VARCHAR(255)";
        } else if (fieldType == int.class || fieldType == Integer.class) {
            return "INT";
        } else if (fieldType == long.class || fieldType == Long.class) {
            return "BIGINT";
        } else if (fieldType == double.class || fieldType == Double.class) {
            return "DOUBLE";
        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
            return "BOOLEAN";
        } else if (fieldType == byte[].class) {
            return "LONGBLOB";
        } else if (fieldType == java.util.Date.class || fieldType == java.sql.Timestamp.class || fieldType == java.time.LocalDateTime.class) {
            return "DATETIME";
        } else if (fieldType == BigDecimal.class) {
            return "DECIMAL(10,2)"; // 可根据需要调整精度和小数位数
        }
        // 如果遇到无法识别的类型，可以根据实际需求进行扩展
        return "VARCHAR(255)";
    }

    /**
     * 将驼峰命名法转换为下划线命名法
     *
     * @param camel 驼峰命名法字符串
     * @return 下划线命名法字符串
     */
    private static String convertCamelToUnderline(String camel) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < camel.length(); i++) {
            char c = camel.charAt(i);
            if (Character.isUpperCase(c)) {
                if (i == 0) {
                    sb.append(Character.toLowerCase(c));
                } else {
                    sb.append('_');
                    sb.append(Character.toLowerCase(c));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }
}

2.测试

public class Test {
    public static void main(String[] args) {
        //生成sql文件的方式
        EntityToTableConverter.convertEntitesToCreateTableStatements("com.mmg.entity", "D:\\test.sql");
        //直接输出在控制台的方式
        EntityToTableConverter.convertEntitesToCreateTableStatements("com.mmg.entity", null);
    }
}

如有更好的想法，可以在评论区交流，完善此工具类！