Druid解析Mysql DDL

最新推荐文章于 2024-03-26 17:34:44 发布

在爪洼山玩泥巴

最新推荐文章于 2024-03-26 17:34:44 发布

阅读量753

点赞数

分类专栏： Java 文章标签： mysql java sql mysql表到javabean映射生成mybatis的接口和sql语句

本文链接：https://blog.csdn.net/lrcxl/article/details/129736723

版权

Java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

目的

通过Mysql的DDL直接解析出表的信息（表名、备注、字段信息、索引信息），就可以做代码生成了。

依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.16</version>
 </dependency>

代码

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLDataTypeImpl;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLIndexDefinition;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLNotNullConstraint;
import com.alibaba.druid.sql.ast.statement.SQLNullConstraint;
import com.alibaba.druid.sql.ast.statement.SQLPrimaryKey;
import com.alibaba.druid.sql.ast.statement.SQLUnique;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlUnique;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;


public class MySQLColumnVisitor extends MySqlSchemaStatVisitor {

	//保存索引信息
    private List<Index> indices = new ArrayList<>();

    @Override
    public boolean visit(SQLColumnDefinition x) {
        String tableName = null;
        SQLObject parent = x.getParent();
        if (parent instanceof SQLCreateTableStatement) {
            tableName = SQLUtils.normalize(((SQLCreateTableStatement) parent).getTableName());
        }
        if (Objects.isNull(tableName)) {
            return true;
        }
        String columnName = SQLUtils.normalize(x.getName().toString());
        TableStat.Column column = this.addColumn(tableName, columnName);
        column.setDataType(x.getDataType().getName());
        Map<String, Object> attr = column.getAttributes();
        if (Objects.isNull(attr)) {
            attr = new HashMap<>();
            column.setAttributes(attr);
        }
        //其他属性
        //attr.put("sqlSegment", x.toString());
        if (Objects.nonNull(x.getComment())) {
            attr.put("comment", SQLUtils.normalize(x.getComment().toString()));
        }
        attr.put("unsigned", ((SQLDataTypeImpl) x.getDataType()).isUnsigned());
        if (Objects.nonNull(x.getDefaultExpr())) {
            attr.put("defaultValue", SQLUtils.normalize(x.getDefaultExpr().toString()));
        }
        List<Object> typeArgs = new ArrayList<>();
        attr.put("typeArgs", typeArgs);
        for (SQLExpr argument : x.getDataType().getArguments()) {
            if (argument instanceof SQLIntegerExpr) {
                Number number = ((SQLIntegerExpr) argument).getNumber();
                typeArgs.add(number);
            }
        }
        for (Object item : x.getConstraints()) {
            if (item instanceof SQLPrimaryKey) {
                column.setPrimaryKey(true);
            } else if (item instanceof SQLUnique) {
                column.setUnique(true);
            } else if (item instanceof SQLNotNullConstraint) {
                attr.put("notnull", true);
            } else if (item instanceof SQLNullConstraint) {
                attr.put("notnull", false);
            }
        }
        return false;
    }

    @Override
    public boolean visit(MySqlKey x) {
        addIndex(x);
        return false;
    }

    @Override
    public boolean visit(MySqlUnique x) {
        addIndex(x);
        return false;
    }

    @Override
    public boolean visit(MySqlPrimaryKey x) {
        addIndex(x);
        return false;
    }


    private void addIndex(MySqlKey x) {
        SQLIndexDefinition indexDefinition = x.getIndexDefinition();
        List<String> indexColumns = indexDefinition.getColumns().stream().map(v -> SQLUtils.normalize(v.getExpr().toString())).collect(Collectors.toList());
        Index index = new Index(
                getOrDef(indexDefinition.getName(), "")
                , getOrDef(indexDefinition.getType(), "")
                , getOrDef(indexDefinition.getOptions().getComment(), "")
                , indexColumns);
        this.indices.add(index);
    }

    private String getOrDef(Object obj, String def) {
        return Objects.isNull(obj) ? def : SQLUtils.normalize(String.valueOf(obj));
    }

    /**
     * 获取索引信息
     *
     * @return
     */
    public List<Index> getIndices() {
        return new ArrayList<>(indices);
    }

    public static class Index {
        private String name;
        private String type;
        private String comment;
        private List<String> columns;

        private Index(String name, String type, String comment, List<String> columns) {
            this.name = name;
            this.type = type;
            this.comment = comment;
            this.columns = columns;
        }

        public String getName() {
            return name;
        }

        public String getType() {
            return type;
        }

        public String getComment() {
            return comment;
        }

        public List<String> getColumns() {
            return columns;
        }
    }
}

测试用例

DDL

CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `cert_no` varchar(32) NOT NULL DEFAULT '' COMMENT '证件号',
  `phone` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `birth_date` date  default NULL COMMENT '出生日期',
  `grade_num` int(8)  NOT NULL DEFAULT '0' COMMENT '年级号',
  `class_num` int(8)  NOT NULL DEFAULT '0' COMMENT '班级号',
  `state` tinyint(8)  NOT NULL DEFAULT '0' COMMENT '状态',
  `balance` decimal(16,2)  NOT NULL DEFAULT '0.00' COMMENT '余额',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `k_name`(`name`) USING BTREE comment '姓名搜索',
  KEY `k_birth_date`(`birth_date`) USING BTREE comment '出生日期',
  UNIQUE KEY `uk_cert_no` (`cert_no`) USING BTREE comment '证件号唯一',
  UNIQUE KEY `uk_grade_class` (`grade_num`,`class_num`) USING BTREE comment '年级+班级唯一'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

测试代码

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.fastjson.JSON;

public class SqlParserTest {
    public static void main(String[] args) {
    	String ddl = "替换DDL";
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, DbType.mysql);
        SQLStatement sqlStatement = parser.parseStatement();
        if (!(sqlStatement instanceof MySqlCreateTableStatement)) {
            System.out.println("not create table statement");
            return;
        }
        MySqlCreateTableStatement mySqlCreateTableStatement = (MySqlCreateTableStatement) sqlStatement;
        MySQLColumnVisitor mySQLColumnVisitor = new MySQLColumnVisitor();
        mySqlCreateTableStatement.accept(mySQLColumnVisitor);

        System.out.println("--------------table------------------");
        System.out.println("表名 => " + SQLUtils.normalize(mySqlCreateTableStatement.getTableName()));
        System.out.println("表备注 => " + SQLUtils.normalize(mySqlCreateTableStatement.getComment().toString()));
        for (SQLAssignItem tableOption : mySqlCreateTableStatement.getTableOptions()) {
            System.out.println("表配置信息 => " + tableOption.getTarget().toString() + "=" + tableOption.getValue().toString());
        }

        System.out.println("\n------------------columns----------------");
        mySQLColumnVisitor.getColumns().forEach(c -> {
            System.out.println(c.getName() + ":" + c.getDataType() + " => " + JSON.toJSONString(c.getAttributes()));
        });
        System.out.println("\n------------------index----------------");
        for (MySQLColumnVisitor.Index index : mySQLColumnVisitor.getIndices()) {
            System.out.println(JSON.toJSONString(index));
        }
	}
}

测试结果

--------------table------------------
表名 => student
表备注 => 学生信息表
表配置信息 => ENGINE=InnoDB
表配置信息 => CHARSET=utf8mb4

------------------columns----------------
id:bigint => {"typeArgs":[20],"notnull":true,"unsigned":true,"comment":"主键"}
name:varchar => {"typeArgs":[32],"defaultValue":"''","notnull":true,"unsigned":false,"comment":"姓名"}
cert_no:varchar => {"typeArgs":[32],"defaultValue":"''","notnull":true,"unsigned":false,"comment":"证件号"}
phone:varchar => {"typeArgs":[11],"defaultValue":"''","notnull":true,"unsigned":false,"comment":"手机号"}
birth_date:date => {"typeArgs":[],"defaultValue":"NULL","unsigned":false,"comment":"出生日期"}
grade_num:int => {"typeArgs":[8],"defaultValue":"0","notnull":true,"unsigned":false,"comment":"年级号"}
class_num:int => {"typeArgs":[8],"defaultValue":"0","notnull":true,"unsigned":false,"comment":"班级号"}
state:tinyint => {"typeArgs":[8],"defaultValue":"0","notnull":true,"unsigned":false,"comment":"状态"}
balance:decimal => {"typeArgs":[16,2],"defaultValue":"0.00","notnull":true,"unsigned":false,"comment":"余额"}
create_time:timestamp => {"typeArgs":[],"defaultValue":"CURRENT_TIMESTAMP","notnull":true,"unsigned":false,"comment":"创建时间"}
update_time:timestamp => {"typeArgs":[],"defaultValue":"CURRENT_TIMESTAMP","notnull":true,"unsigned":false,"comment":"修改时间"}

------------------index----------------
{"columns":["id"],"comment":"","name":"","type":"PRIMARY"}
{"columns":["name"],"comment":"姓名搜索","name":"k_name","type":""}
{"columns":["birth_date"],"comment":"出生日期","name":"k_birth_date","type":""}
{"columns":["cert_no"],"comment":"证件号唯一","name":"uk_cert_no","type":"UNIQUE"}
{"columns":["grade_num","class_num"],"comment":"年级+班级唯一","name":"uk_grade_class","type":"UNIQUE"}