根据model实体类DO生成建表SQL

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.sql.Timestamp;
import java.util.Date;

/**
 * 根据model实体类DO生成建表SQL
 * 注意:生成的SQL会有一定出入
 * @author zlin
 * @date 20200919
 */
public class DoToSqlUtil {
 
    public static void main(String[] args) {
        // 1.将本类放在待转换的项目中
        // 2.执行前先配置如下信息
        // 3.执行此方法
        // 需转换为SQL的实体类包路径
        String packageName = "com.example.demo.entity";
        // 待转换的实体类是否都有int类型自动递增的"id"字段为主键,若配置为true将以id生成自增主键,若配置为false,则转换后的SQL无主键
        boolean idKey = false;
        // 生成结果sql文路径
        String filePath = "../result.sql";
        
        generate(packageName, filePath, idKey);
    }

    private static void generate(String packageName, String filePath, boolean idKey) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("\nSET NAMES utf8mb4;\n").append("SET FOREIGN_KEY_CHECKS = 0;\n");
        // 通过包名生成SQL
        sqlBuilder.append(generateSql(packageName, idKey));
        sqlBuilder.append("\nSET FOREIGN_KEY_CHECKS = 1;\n");
        System.out.println(sqlBuilder.toString());
        sql2File(sqlBuilder.toString(), filePath);
    }

    /**
     * 根据实体类生成建表语句
     */
    private static String generateSql(String packageName, boolean idKey) {
        StringBuilder sb = new StringBuilder();
        String targetPath = getTargetPathByPackageName(packageName);
        File targetDir = new File(targetPath);
        if (targetDir.exists()) {
            File[] files = targetDir.listFiles();
            if (null != files && files.length > 0) {
                for (File file : files) {
                    String fileFullName = file.getName();
                    if (file.isDirectory()) {
                        sb.append(generateSql(packageName + "." + file.getName(), idKey));
                        continue;
                    }
                    if (file.isFile() && fileFullName.endsWith(".class")) {
                        String simpleName = fileFullName.split("\\.")[0];
                        Class<?> clazz;
                        try {
                            clazz = Class.forName(packageName.replace("/", "\\.") + "." +  simpleName);
                        } catch (ClassNotFoundException e) {
                            e.printStackTrace();
                            continue;
                        }
                        Field[] fields = clazz.getDeclaredFields();
                        if (fields.length < 1) {
                            continue;
                        }
                        String tableName = simpleNameToTableName(simpleName);
                        StringBuilder column = new StringBuilder();
                        for (Field field : fields) {
                            column.append("\n  `").append(fieldNameToTableName(field.getName())).append("` ");
                            if (idKey && fieldIsIntId(field)) {
                                column.append("int(11) NOT NULL AUTO_INCREMENT").append(",");
                                continue;
                            }
                            column.append(TypeSqlEnum.getSqlByClass(field.getType())).append(",");
                        }
                        sb.append("\nDROP TABLE IF EXISTS `").append(tableName).append("`;")
                                .append("\nCREATE TABLE `").append(tableName).append("`  (");
                        if (idKey) {
                            sb.append(column)
                                    .append("\n  PRIMARY KEY (`id`) USING BTREE")
                                    .append("\n) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n");
                        }else {
                            String columnStr = column.toString();
                            sb.append(columnStr, 0, columnStr.length() - 1)
                                    .append("\n) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n");
                        }
                    }
                }
            }else {
                System.out.println("路径下无文件,path:" + targetPath);
                return "";
            }
        }else {
            System.out.println("路径不存在,path:" + targetPath);
            return "";
        }
        return sb.toString();
    }

    private static boolean fieldIsIntId(Field field) {
        return "id".equals(field.getName()) &&
                (field.getType().equals(Integer.class) || field.getType().equals(int.class));
    }

    private static String getTargetPathByPackageName(String packageName) {
        String curFilePath = DoToSql.class.getResource("").getPath();
        return curFilePath.substring(0, curFilePath.indexOf(packageName.split("\\.")[0])) + packageName.replaceAll("\\.", "/");
    }

    private static String simpleNameToTableName(String className) {
        return ("t_" + fieldNameToTableName(className));
    }

    private static String fieldNameToTableName(String fieldName) {
        StringBuilder sb = new StringBuilder();
        char[] chars = fieldName.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            if (i > 0 && isUpperCase(c) && isLowerCase(chars[i-1])) {
                sb.append("_");
            }
            sb.append(c);
        }
        return sb.toString().toLowerCase();
    }

    private static boolean isUpperCase(char c) {
        return c >='A' && c <= 'Z';
    }

    private static boolean isLowerCase(char c) {
        return c >='a' && c <= 'z';
    }

    /**
     * sql写入文件
     */
    private static void sql2File(String sql, String path) {
        byte[] sourceByte = sql.getBytes();
        try {
            Files.deleteIfExists(new File(path).toPath());
            Files.write(new File(path).toPath(), sourceByte);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    enum TypeSqlEnum {

        /**
         * 属性类型对应SQL
         */
        INTEGER(Integer.class, "int(11) NULL DEFAULT NULL"),
        INT(int.class, "int(11) NULL DEFAULT NULL"),
        DOUBLE(Double.class, "decimal(10, 2) NULL DEFAULT NULL"),
        Double(double.class, "decimal(10, 2) NULL DEFAULT NULL"),
        STRING(String.class, "varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL"),
        DATE(Date.class, "datetime(0) NULL DEFAULT NULL"),
        Date(Timestamp.class, "datetime(0) NULL DEFAULT NULL"),
        BOOLEAN(Boolean.class, "tinyint(1) NULL DEFAULT NULL"),
        Boolean(boolean.class, "tinyint(1) NULL DEFAULT NULL");

        private Class clazz;

        private String sql;

        TypeSqlEnum(Class clazz, String sql){
            this.clazz = clazz;
            this.sql = sql;
        }

        public static String getSqlByClass(Class clazz) {
            for (TypeSqlEnum typeSql : TypeSqlEnum.values()) {
                if (typeSql.getClazz().equals(clazz)) {
                    return typeSql.getSql();
                }
            }
            return STRING.getSql();
        }

        public Class getClazz() {
            return clazz;
        }

        public String getSql() {
            return sql;
        }
    }
}
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
Java实体生成SQL语句的方法有很多种,下面介绍两种比较常见的方法。 方法一:使用反射机制 使用反射机制可以获取Java实体类中的属性和对应的值,然后拼接成SQL语句。 示例代码: ``` public static String createInsertSql(Object obj, String tableName) throws IllegalAccessException { Field[] fields = obj.getClass().getDeclaredFields(); StringBuilder sb = new StringBuilder(); sb.append("insert into ").append(tableName).append("("); for (int i = 0; i < fields.length; i++) { if (i > 0) { sb.append(","); } String fieldName = fields[i].getName(); sb.append(fieldName); } sb.append(") values ("); for (int i = 0; i < fields.length; i++) { if (i > 0) { sb.append(","); } fields[i].setAccessible(true); Object value = fields[i].get(obj); if (value instanceof String) { sb.append("'").append(value).append("'"); } else { sb.append(value); } } sb.append(")"); return sb.toString(); } ``` 方法二:使用第三方库 可以使用第三方库,如MyBatis Generator,自动生成Java实体类对应的SQL语句。 示例代码: ``` <generatorConfiguration> <context id="Mysql" targetRuntime="MyBatis3"> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/test" userId="root" password="root"> </jdbcConnection> <javaModelGenerator targetPackage="com.example.model" targetProject="src/main/java"> <property name="enableSubPackages" value="true" /> <property name="trimStrings" value="true" /> </javaModelGenerator> <sqlMapGenerator targetPackage="com.example.mapper" targetProject="src/main/resources"> <property name="enableSubPackages" value="true" /> </sqlMapGenerator> <javaClientGenerator type="XMLMAPPER" targetPackage="com.example.mapper" targetProject="src/main/java"> <property name="enableSubPackages" value="true" /> </javaClientGenerator> <table tableName="user"></table> </context> </generatorConfiguration> ``` 以上是两种常见的Java实体生成SQL语句的方法,具体使用哪种方法可以根据实际情况选择。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值