16-SQL解析-DruidSQL

依赖

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

解析CREATE语句

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLColumnDefinition;
import com.alibaba.druid.sql.ast.statement.SQLCreateTableStatement;
import com.alibaba.druid.sql.ast.statement.SQLTableElement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;

import java.util.List;

public class XmlTest {

    @org.junit.Test
    public void testCreateTable2() {
        String createTableSql = "CREATE TABLE example (" +
                "id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键', " +
                "name VARCHAR(100) NOT NULL COMMENT '姓名', " +
                "price decimal(10,2) NOT NULL COMMENT '价格', " +
                "age INT COMMENT '年龄'" +
                ");";

        // 使用 MySQL 语法解析器
        MySqlStatementParser parser = new MySqlStatementParser(createTableSql);
        List<SQLStatement> statements = parser.parseStatementList();

        for (SQLStatement stmt : statements) {
            if (stmt instanceof SQLCreateTableStatement) {
                SQLCreateTableStatement createTableStatement = (SQLCreateTableStatement) stmt;
                System.out.println("Table Name: " + createTableStatement.getTableName());

                List<SQLTableElement> tableElements = createTableStatement.getTableElementList();
                for (SQLTableElement element : tableElements) {
                    if (element instanceof SQLColumnDefinition) {
                        SQLColumnDefinition column = (SQLColumnDefinition) element;

                        // 获取字段名称
                        String columnName = column.getName().getSimpleName();
                        // 获取字段类型
                        String columnType = column.getDataType().getName();
                        // 获取字段备注
                        String columnComment = column.getComment().toString().replaceAll("'", "");
                        // 判断是否为自增主键
                        boolean isAutoIncrement = column.isAutoIncrement();
                        boolean isPrimaryKey = column.getConstraints().stream()
                                .anyMatch(constraint ->  constraint.toString().contains("PRIMARY"));

                        System.out.println("Column Name: " + columnName);
                        System.out.println("Column Type: " + columnType);
                        System.out.println("Column Comment: " + columnComment);
                        System.out.println("Is Auto Increment: " + isAutoIncrement);
                        System.out.println("Is Primary Key: " + isPrimaryKey);
                        System.out.println("---------------");
                    }
                }
            }
        }
    }
}

结果

Table Name: example
Column Name: id
Column Type: INT
Column Comment: 主键
Is Auto Increment: true
Is Primary Key: true
---------------
Column Name: name
Column Type: VARCHAR
Column Comment: 姓名
Is Auto Increment: false
Is Primary Key: false
---------------
Column Name: price
Column Type: decimal
Column Comment: 价格
Is Auto Increment: false
Is Primary Key: false
---------------
Column Name: age
Column Type: INT
Column Comment: 年龄
Is Auto Increment: false
Is Primary Key: false
---------------

解析ALTER语句

@Test
public void testAlterTable() {
    String ddl3 = "ALTER TABLE tm_app_banner\n" +
            "MODIFY `package_name` VARCHAR(100) DEFAULT NULL COMMENT '包名xx',\n" +
            "DROP COLUMN `image1`,\n" +
            "DROP COLUMN `image2`,\n" +
            "ADD COLUMN `images` VARCHAR(255) DEFAULT NULL COMMENT 'URL: xx',\n" +
            "MODIFY `status` TINYINT(1) DEFAULT '2' COMMENT '0:很好的,1:y,不好的:好的 heiei',\n" +
            "ADD COLUMN `white_list_flag` TINYINT(1) DEFAULT '1' COMMENT '好的嘛 OK',\n" +
            "ADD COLUMN `white_list_categorys` VARCHAR(11) DEFAULT NULL COMMENT '白名单状态 并且',\n" +
            "ADD COLUMN `hu_software_version` INT(11) DEFAULT '0' COMMENT 'hu_software_version and other'";
    try {
        MySqlStatementParser parser = new MySqlStatementParser(ddl3);
        // 使用Parser解析生成AST，这里SQLStatement就是AST
        SQLAlterTableStatement statement = (SQLAlterTableStatement) parser.parseStatement();

        List<SQLAlterTableItem> items = statement.getItems();
        HashMap<String, String> colAndComment = new HashMap<>();
        items.forEach(
                x -> {
                    //如果是ADD语句
                    if (x instanceof SQLAlterTableAddColumn) {
                        for (SQLColumnDefinition column : ((SQLAlterTableAddColumn) x).getColumns()) {
                            String colName = column.getName().getSimpleName().replaceAll("`", "");
                            String comment = column.getComment().toString().replaceAll("'", "");
                            colAndComment.put(colName, comment);
                        }
                    }
                    //如果MODIFY语句
                    if (x instanceof MySqlAlterTableModifyColumn) {
                        SQLColumnDefinition newColumnDefinition = ((MySqlAlterTableModifyColumn) x).getNewColumnDefinition();
                        String colName = newColumnDefinition.getName().getSimpleName();
                        String comment = newColumnDefinition.getComment().toString();
                        colAndComment.put(colName, comment);
                    }
                }
        );
        System.out.println(colAndComment);
    } catch (Exception e) {
        throw new IllegalArgumentException("DDL语法不正确");

    }
}

控制台输出:

{images=URL: xx, `status`='0:很好的,1:y,不好的:好的 heiei', white_list_flag=好的嘛 OK, white_list_categorys=白名单状态 并且, `package_name`='包名xx', hu_software_version=hu_software_version and other}