Druid解析SQL获取注释、表明

ha_lydms

发布于 2023-08-10 08:54:12

7370

发布于 2023-08-10 08:54:12

文章被收录于专栏：学习内容

一、简介

Druid是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。文档地址

https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

参考地址：

https://github.com/alibaba/druid/issues/2457
https://github.com/alibaba/druid/issues/2426

二、相应API

1、格式化SQL

String sql = "CREATE TABLE `my_user` (\n" +
        "  `id` int(11) DEFAULT NULL COMMENT 'ID',\n" +
        "  `name` varchar(255) DEFAULT NULL COMMENT '名称',\n" +
        "  `sex` int(11) DEFAULT NULL COMMENT '性别(1:男。2:女)',\n" +
        "  `address` varchar(64) DEFAULT NULL COMMENT '地址',\n" +
        "  `create_time` datetime DEFAULT NULL COMMENT '创建时间',\n" +
        "  `update_time` datetime DEFAULT NULL COMMENT '更新时间'\n" +
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'";
//  格式化SQL(假如SQL为多个SQL拼接，则list数量会大于0)
List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
SQLStatement sqlStatement = sqlStatements.get(0);

2、解析SQL，获取表名

SQLCreateTableStatement sqlCreateTableStatement = (SQLCreateTableStatement) SQLUtils.parseStatements(sql, JdbcConstants.MYSQL).get(0);
String tableName = sqlCreateTableStatement.getTableName();
SQLName name = sqlCreateTableStatement.getName();

3、解析SQL，获取列名称

SQLCreateTableStatement sqlCreateTableStatement = (SQLCreateTableStatement) SQLUtils.parseStatements(sql, JdbcConstants.MYSQL).get(0);

List<SQLColumnDefinition> columnDefinitions = sqlCreateTableStatement.getColumnDefinitions();
for (SQLColumnDefinition column : columnDefinitions) {
    String name = column.getName().toString();
    String comment = column.getComment().toString();
}

4、替换表名

List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);

HashMap<String, String> hashMap = new HashMap<>();
hashMap.put("old_table", "new_table");
String changeSQL = SQLUtils.toSQLString(stmtList, JdbcConstants.MYSQL, null, new SQLUtils.FormatOption(true, false), hashMap);

三、支持数据源类型

case oracle:
case oceanbase_oracle:
case mysql:
case mariadb:
case drds: 
case elastic_search: 
case postgresql:
case edb:
case sqlserver:
case jtds:
case h2:
case blink:
case db2:
case odps:
case phoenix:
case hive:
case presto:
case trino:
case ads:
case antspark:
case clickhouse:

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-04-25，如有侵权请联系 cloudcommunity@tencent.com 删除

sql