【工作记录】数据库被黑，Java实体类反向生成Sql语句

最新推荐文章于 2023-08-29 13:29:19 发布

文艺倾年

最新推荐文章于 2023-08-29 13:29:19 发布

阅读量457

点赞数 1

分类专栏： # SpringBoot 文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/m0_51517236/article/details/130675354

版权

SpringBoot 专栏收录该内容

14 篇文章 2 订阅

订阅专栏

前言

😊你好，我是小航，一个正在变秃、变强的文艺倾年。
🔔本文讲解Java实体类反向生成Sql语句，一起卷起来叭！

目录

前言
代码思路
核心代码

代码思路

1.将Entity文件夹和代码导入SpringBoot文件，需要引入：Lombok、Mysql等相关依赖

在这里插入图片描述

2.检查主键是否有@TableId注解，含@TableField（exist=false）注解字段会被过滤

在这里插入图片描述

3.默认引擎：InnoDB、AUTO_INCREMENT = 1、CHARACTER SET = utf8、COLLATE = utf8_general_ci、Mysql版本：8

核心代码

💬 参考代码：SqlGenerator.java

package com.artboy.common.utils;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xh
 * @Date 2023/5/14
 */
@Slf4j
public class SqlGenerator {

    //-1小写,1大写
    public static final int format = -1;
    /**
     * 用来存储Java等属性类型与sql中属性类型的对照
     * </br>
     * 例如：java.lang.Integer 对应 integer
     */
    public static Map<String, String> map = new HashMap<>();

    public static void main(String[] args) {
        map.put("boolean", "tinyint(1)");
        map.put("java.lang.Object", "varchar(255)");
        map.put("int", "int");
        map.put("short", "int");
        map.put("long", "bigint");
        map.put("float", "float");
        map.put("double", "double");
        map.put("char", "varchar(255)");
        map.put("java.lang.String", "varchar(255)");
        map.put("java.lang.Integer", "int");
        map.put("java.lang.byte[]", "blob");
        map.put("java.lang.Boolean", "bit");
        map.put("java.lang.Long", "integer unsigned");
        map.put("java.math.BigInteger", "bigint unsigned");
        map.put("java.lang.Float", "float");
        map.put("java.lang.Double", "float");
        map.put("java.sql.Date", "datetime");
        map.put("java.sql.Time", "time");
        map.put("java.sql.Timestamp", "datetime");
        map.put("java.util.Date", "datetime");
        map.put("java.lang.Byte", "tinyint");
        sqlConstruction();
    }

    /**
     * 生成sql建库语句
     */
    private static void sqlConstruction() {
        //实体类所在的package在磁盘上的绝对路径
        String packageName = "F:\\快速开发模板\\springboot-init-all\\springboot-common\\src\\main\\java\\com\\artboy\\common\\utils\\entity";
        //生成sql的文件夹
        String filePath = "F:\\sqlGen";
        //项目中实体类的路径
        String prefix = "com.artboy.common.utils.entity.";
        String className = "";

        StringBuffer sqls = new StringBuffer();
        //获取包下的所有类名称
        List<String> list = getAllClasses(packageName);
        for (String str : list) {
            className = prefix + str.substring(0, str.lastIndexOf("."));
            String sql = generateSql(className, filePath);
            sqls.append(sql);
        }
        StringToSql(sqls.toString(), filePath + "\\entityToSql.sql");
    }

    /**
     * 根据实体类生成建表语句
     *
     * @param className 全类名
     * @param filePath  磁盘路径
     * @return
     */
    public static String generateSql(String className, String filePath) {
        try {
            Class<?> clz = Class.forName(className);
            className = clz.getSimpleName();
            // 通过@TableName注解获取表名
            String value = clz.getAnnotation(TableName.class).value();
            if (!StringUtils.isEmpty(value)) {
                log.info("读取注解成功：{}", value);
                className = value;
            } else {
                // 表表名adminUser → tb_admin_user
                className = "tb_" + getStandardFields(className).toLowerCase();
            }
            Field[] fields = clz.getDeclaredFields();
            StringBuffer column = new StringBuffer();
            String varchar = " CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,";
            for (Field f : fields) {
                // 去掉包含TableField字段
                if(f.getAnnotation(TableField.class) != null && !f.getAnnotation(TableField.class).exist()) {
                    continue;
                }
                String typeName = f.getType().getName();
                // serialVersionUID
                if (f.getName().equals("serialVersionUID")) {
                    continue;
                }
                if (f.getAnnotation(TableId.class) != null) {
                    log.info("typeName : {}, filedName: {}", typeName, getStandardFields(f.getName()));
                    if("java.lang.Long".equals(typeName)) { // 默认主键自增
                        column.append(" \n `" + getStandardFields(f.getName()) + "` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,");
                    }
                    continue;
                }
                if ("java.lang.String".equals(typeName)) {
                    column.append(" \n `" + getStandardFields(f.getName()) + "`" + " " + map.get(typeName)).append(varchar);
                } else {
                    column.append(" \n `" + getStandardFields(f.getName()) + "`" + " " + map.get(typeName)).append(",");
                }
            }
            // 删除最后一个，
            column.deleteCharAt(column.lastIndexOf(","));
            StringBuffer sql = new StringBuffer();
            sql.append("\n DROP TABLE IF EXISTS `" + className + "`; ")
                    .append(" \n CREATE TABLE `" + className + "`  (")
                    .append(column)
                    .append(" \n ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci;\n");
            return sql.toString();
        } catch (ClassNotFoundException e) {
            log.info("该类未找到！");
            return null;
        }

    }

    /**
     * 转换为标准等sql字段 例如 adminUser → admin_user
     *
     * @param str 转换为字符串的字段名
     * @return
     */
    public static String getStandardFields(String str) {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (i != 0 && (c > 'A' && c < 'Z')) {
                sb.append("_");
            }
            sb.append(c);
        }
        return sb.toString();
    }

    /**
     * 获取包下面等所有实体类名称，类似于获取 XXX.java
     *
     * @param packageName 全类名
     * @return
     */
    public static List<String> getAllClasses(String packageName) {
        List<String> classList = new ArrayList();
        String className = "";
        File f = new File(packageName);
        if (f.exists() && f.isDirectory()) {
            File[] files = f.listFiles();
            // 遍历实体类下面等所有.java文件 获取其类名
            for (File file : files) {
                className = file.getName();
                classList.add(className);
            }
            return classList;
        } else {
            log.info("包路径未找到！");
            return null;
        }
    }

    /**
     * 将生成等String字符串 写进sql文件
     *
     * @param str  String字符串
     * @param path sql文件路径路径
     */
    public static void StringToSql(String str, String path) {
        if (format < 0) {
            str = str.toLowerCase();
        } else {
            str = str.toUpperCase();
        }
        System.out.println(str);
        byte[] sourceByte = str.getBytes();
        FileOutputStream os = null;
        if (null != sourceByte) {
            try {
                //文件路径（路径+文件名）
                File file = new File(path);
                //文件不存在则创建文件，先创建目录
                if (!file.exists()) {
                    File dir = new File(file.getParent());
                    dir.mkdirs();
                    file.createNewFile();
                }
                //文件输出流用于将数据写入文件
                os = new FileOutputStream(file);
                os.write(sourceByte);
                os.flush();
                System.out.println("生成成功!!");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 关闭文件输出流
                try {
                    if (os != null) {
                        os.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

🚩 运行结果：

drop table if exists `district`;  
 create table `district`  ( 
 `ch_name` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `en_name` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `alias` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `type` int, 
 `location` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `area` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `governsarea` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `government` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `phone` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `code` int, 
 `climate` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `scenery` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `train` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `car` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `gdp` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `person` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `admin_code` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `official` varchar(255) character set utf8 collate utf8_general_ci default null, 
 `p_id` int 
 ) engine = innodb auto_increment = 1 character set = utf8 collate = utf8_general_ci;