实体类转SQL工具类

拼搏现实的明天。 2024-02-17 11:13 13阅读 0赞

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

## 单个实体类的转换工具类 ##

### 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);
        }
    }

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