java:Druid工具类解析sql获取表名

java:Druid工具类解析sql获取表名

1 前言

alibaba的druid连接池除了sql执行的功能外,还有sql语法解析的工具提供,参考依赖如下:

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

2 使用

参考druid的工具类:com.alibaba.druid.sql.parser.SQLParserUtils#getTables(String sql, DbType dbType)方法,可以用于获取sql的表名:

比如针对mysql的select语句:

package com.xiaoxu.parser;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.parser.SQLParserUtils;


import java.util.List;

/**
 * @author xiaoxu
 * @date 2024-03-11
 * java_demo2:com.xiaoxu.parser.SQLParserTest
 */
public class SQLParserTest {

    public static void main(String[] args) {
        List<String> tables = SQLParserUtils.getTables("select * from my_fuitrs_99 where id = ?", DbType.mysql);
        System.out.println(tables);
        String nihao_99 = "select * from my_fuitrs_99 where id = ?".replace(tables.get(0), "my_fuitrs");
        System.out.println(nihao_99);
    }

}

执行结果如下:
在这里插入图片描述

该方法的本质是根据自定义的hash值,对于每个标识符,能唯一生成一个hashCode,将标识符与存入map中的标识符来比对。比如当lexer.token获取的是com.alibaba.druid.sql.parser.Token类中的FROM(“FROM”)时,先执行lexer.nextToken()方法调用,获取到用户自定义的from后续的标识符(from关键字后续跟着的空格等字符会跳过),判断在没有获取到,则将其置为:Token.IDENTIFIER,表示用户自定义的identifier标识符。如下 keywords.getKeyword(hashLCase) 如果获取的标识符不存在,则将判断为用户自定义的标识符。

参考com.alibaba.druid.sql.parser.Lexer的scanIdentifier()方法:

public void scanIdentifier() {
    this.hashLCase = 0;
    this.hash = 0;

    final char first = ch;

    if (ch == '`') {
        mark = pos;
        bufPos = 1;
        char ch;

        int startPos = pos + 1;
        int quoteIndex = text.indexOf('`', startPos);
        if (quoteIndex == -1) {
            throw new ParserException("illegal identifier. " + info());
        }

        hashLCase = 0xcbf29ce484222325L;
        hash = 0xcbf29ce484222325L;

        for (int i = startPos; i < quoteIndex; ++i) {
            ch = text.charAt(i);

            hashLCase ^= ((ch >= 'A' && ch <= 'Z') ? (ch + 32) : ch);
            hashLCase *= 0x100000001b3L;

            hash ^= ch;
            hash *= 0x100000001b3L;
        }

        stringVal = MySqlLexer.quoteTable.addSymbol(text, pos, quoteIndex + 1 - pos, hash);
        //stringVal = text.substring(mark, pos);
        pos = quoteIndex + 1;
        this.ch = charAt(pos);
        token = Token.IDENTIFIER;
        return;
    }

    final boolean firstFlag = isFirstIdentifierChar(first);
    if (!firstFlag) {
        throw new ParserException("illegal identifier. " + info());
    }

    hashLCase = 0xcbf29ce484222325L;
    hash = 0xcbf29ce484222325L;

    hashLCase ^= ((ch >= 'A' && ch <= 'Z') ? (ch + 32) : ch);
    hashLCase *= 0x100000001b3L;

    hash ^= ch;
    hash *= 0x100000001b3L;

    mark = pos;
    bufPos = 1;
    char ch = 0;
    for (; ; ) {
        char c0 = ch;
        ch = charAt(++pos);

        if (!isIdentifierChar(ch)) {
            if ((ch == '(' || ch == ')') && c0 > 256) {
                bufPos++;
                continue;
            }
            break;
        }

        hashLCase ^= ((ch >= 'A' && ch <= 'Z') ? (ch + 32) : ch);
        hashLCase *= 0x100000001b3L;

        hash ^= ch;
        hash *= 0x100000001b3L;

        bufPos++;
        continue;
    }

    this.ch = charAt(pos);

    if (bufPos == 1) {
        switch (first) {
            case '(':
                token = Token.LPAREN;
                return;
            case ')':
                token = Token.RPAREN;
                return;
            default:
                break;
        }
        token = Token.IDENTIFIER;
        stringVal = CharTypes.valueOf(first);
        if (stringVal == null) {
            stringVal = Character.toString(first);
        }
        return;
    }

    Token tok = keywords.getKeyword(hashLCase);
    if (tok != null) {
        token = tok;
        if (token == Token.IDENTIFIER) {
            stringVal = SymbolTable.global.addSymbol(text, mark, bufPos, hash);
        } else {
            stringVal = null;
        }
    } else {
        token = Token.IDENTIFIER;
        stringVal = SymbolTable.global.addSymbol(text, mark, bufPos, hash);
    }
}

获取表名的工具类方法:

public static List<String> getTables(String sql, DbType dbType) {
    Set<String> tables = new LinkedHashSet<>();

    boolean set = false;
    Lexer lexer = createLexer(sql, dbType);
    lexer.nextToken();

    SQLExprParser exprParser;
    switch (dbType) {
        case odps:
            exprParser = new OdpsExprParser(lexer);
            break;
        case mysql:
            exprParser = new MySqlExprParser(lexer);
            break;
        default:
            exprParser = new SQLExprParser(lexer);
            break;
    }

    for_:
    for (; lexer.token != Token.EOF; ) {
        switch (lexer.token) {
            case CREATE:
            case DROP:
            case ALTER:
                set = false;
                lexer.nextToken();

                if (lexer.token == Token.TABLE) {
                    lexer.nextToken();

                    if (lexer.token == Token.IF) {
                        lexer.nextToken();

                        if (lexer.token == Token.NOT) {
                            lexer.nextToken();
                        }

                        if (lexer.token == Token.EXISTS) {
                            lexer.nextToken();
                        }
                    }

                    SQLName name = exprParser.name();
                    tables.add(name.toString());

                    if (lexer.token == Token.AS) {
                        lexer.nextToken();
                    }
                }
                continue for_;
            case FROM:
            case JOIN:
                lexer.nextToken();
                if (lexer.token != Token.LPAREN
                        && lexer.token != Token.VALUES
                ) {
                    SQLName name = exprParser.name();
                    tables.add(name.toString());
                }
                continue for_;
            case SEMI:
                set = false;
                break;
            case SET:
                set = true;
                break;
            case EQ:
                if (set && dbType == DbType.odps) {
                    lexer.nextTokenForSet();
                    continue for_;
                }
                break;
            default:
                break;
        }

        lexer.nextToken();

    }

    return new ArrayList<>(tables);
}

比如上述的from关键字,当执行完lexer.nextToken()方法后,lexer.stringVal()方法即可以通过字符串的头尾下标切割字符串并返回该标识符,比如表名,也就是我们自定义的标识符。同时关注源码逻辑可知,数据库的表名,druid工具类会将大写字符、小写字符(大写字母A-Z的ASCII码值范围是65-90,而小写字母的ASCII码值范围是97-122,在大写字母的ASCII码值上+32即可转换成小写字母)判定为标识符,同时druid在处理时,除了大小写字母外,下划线(_)、美元符号($)、数字(0-9,ASCII码值范围是48-57)等等,均可判定为标识符。

修改表名含有大写字母,如下获取sql的表名:

List<String> tables = SQLParserUtils.getTables("select * from my_fuitrs_99 where id = ?", DbType.mysql);
System.out.println(tables);
String nihao_99 = "select * from my_fuitrs_99 where id = ?".replace(tables.get(0), "my_fuitrs");
System.out.println(nihao_99);
List<String> tables2 = SQLParserUtils.getTables("select * from MY_fuitrs_99 where id = ?", DbType.mysql);
System.out.println(tables2);

重新执行执行结果如下:

[my_fuitrs_99]
select * from my_fuitrs where id = ?
[MY_fuitrs_99]

3 举一反三

那么我们可以根据上面的工具,简单自定义实现一个替换sql表名的工具,工具类如下:

SQLParserUtil:

package com.xiaoxu.parser;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlLexer;
import com.alibaba.druid.sql.parser.Lexer;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserFeature;
import com.alibaba.druid.sql.parser.Token;
import org.springframework.lang.Nullable;
import org.springframework.util.StringUtils;

/**
 * @author xiaoxu
 * @date 2024-03-12
 * java_demo2:com.xiaoxu.parser.SQLParserUtil
 */
@SuppressWarnings("all")
public class SQLParserUtil {

    public static String getSqlFromReplaceNameIfNeccessary(String sql, DbType dbType, @Nullable String replaceTableName) {
        String table = null;
        String newSql = sql;
        Lexer lexer = createLexer(sql, dbType);
        lexer.nextToken();

        SQLExprParser exprParser;
        switch (dbType) {
            case mysql:
                exprParser = new MySqlExprParser(lexer);
                break;
            default:
                exprParser = new SQLExprParser(lexer);
                break;
        }

        for_:
        for (; lexer.token() != Token.EOF; ) {
            switch (lexer.token()) {
                case FROM:
                case INTO:
                    lexer.nextToken();
                    if (lexer.token() != Token.LPAREN && lexer.token() != Token.VALUES) {
                        if (StringUtils.hasText(replaceTableName)) {
                            int mark = ((MLexer) lexer).getMark();
                            int bufPoss = ((MLexer) lexer).getBufPos();
                            StringBuilder sbd = new StringBuilder();
                            sbd.append(sql.substring(0, mark));
                            sbd.append(replaceTableName);
                            sbd.append(sql.substring(mark + bufPoss));
                            newSql = sbd.toString();
                        }
                        SQLName name = exprParser.name();
                        table = name.toString();
                    }
                    break for_;
                default:
                    break;
            }

            lexer.nextToken();
        }

        System.out.println("原本的表名:" + table);
        System.out.println("替换表名为:" + replaceTableName + "后的sql:" + newSql);
        return newSql;
    }

    public static Lexer createLexer(String sql, DbType dbType) {
        return createLexer(sql, dbType, new SQLParserFeature[0]);
    }

    public static Lexer createLexer(String sql, DbType dbType, SQLParserFeature... features) {
        if (dbType == null) {
            dbType = DbType.mysql;
        }

        switch (dbType) {
            case mysql:
                return new MLexer(sql);
            default:
                return new Lexer(sql, null, dbType);
        }
    }

    private static class MLexer extends MySqlLexer {
        public MLexer(char[] input, int inputLength, boolean skipComment) {
            super(input, inputLength, skipComment);
        }

        public MLexer(String input) {
            super(input);
        }

        public MLexer(String input, SQLParserFeature... features) {
            super(input, features);
        }

        public MLexer(String input, boolean skipComment, boolean keepComments) {
            super(input, skipComment, keepComments);
        }

        public int getBufPos() {
            return this.bufPos;
        }

        public int getMark() {
            return this.mark;
        }
    }

}

测试下我们自定义的SQLParserUtil工具类:

package com.xiaoxu.parser;

import com.alibaba.druid.DbType;

/**
 * @author xiaoxu
 * @date 2024-03-12
 * java_demo2:com.xiaoxu.parser.SQLParserTest2
 */
public class SQLParserTest2 {

    public static void main(String[] args) {
        String sql = SQLParserUtil.getSqlFromReplaceNameIfNeccessary("select * from my_fruits_99 where id = ?",
                DbType.mysql, "xiaoxu_88");
        System.out.println(sql);

        System.out.println("\n");
        String sql2 = SQLParserUtil.getSqlFromReplaceNameIfNeccessary("insert into apple_$66 values()",
                DbType.mysql, "Pear$_88");
        System.out.println(sql2);
    }

}

执行结果如下:

在这里插入图片描述

可以看到,上面工具针对扫描到标识符为FROM(比如select * from语句)或者INTO(比如insert into语句)时,可以实现替换sql的表名功能,其余类似功能参考druid的工具类自行实现即可。

再来举个栗子,新增方法getSqlInHoldCountIfNeccessary如下:

package com.xiaoxu.parser;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlLexer;
import com.alibaba.druid.sql.parser.Lexer;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.alibaba.druid.sql.parser.SQLParserFeature;
import com.alibaba.druid.sql.parser.Token;
import com.google.common.collect.Lists;
import org.springframework.lang.Nullable;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * @author xiaoxu
 * @date 2024-03-12
 * java_demo2:com.xiaoxu.parser.SQLParserUtil
 */
@SuppressWarnings("all")
public class SQLParserUtil {

    public static String getSqlInHoldCountIfNeccessary(String sql, DbType dbType, @Nullable Integer[] count) {
        StringBuilder tempSql = new StringBuilder();
        Lexer lexer = createLexer(sql, dbType);
        lexer.nextToken();

        int posInc = 0;
        int startPos = 0;
        int endPos = 0;
        int subStartPos = 0;

        for_:
        for (; lexer.token() != Token.EOF; ) {
            switch (lexer.token()) {
                case IN:
                    lexer.nextToken();
                    startPos = endPos;
                    endPos = ((MLexer) lexer).getPos();

                    if (lexer.token() == Token.LPAREN) {
                        tempSql.append(sql, startPos, endPos);
                        subStartPos = endPos;
                        do {
                            endPos = ((MLexer) lexer).getPos();
                            lexer.nextToken();
                        } while (lexer.token() != Token.RPAREN);

                        String replaceMent = sql.substring(subStartPos, endPos);

                        if (count != null && count.length > posInc && count[posInc] != null && count[posInc] > 0) {
                            List<String> incStrs = Lists.newArrayList();
                            for (int i = 0; i < count[posInc]; i++) {
                                incStrs.add("?");
                            }
                            tempSql.append(String.join(",", incStrs));

                        } else {
                            tempSql.append(replaceMent);
                        }
                        posInc++;
                    }
                    continue for_;
                default:
                    break;
            }

            lexer.nextToken();
        }

        tempSql.append(sql.substring(endPos));
        return tempSql.toString();
    }

    public static String getSqlFromReplaceNameIfNeccessary(String sql, DbType dbType, @Nullable String replaceTableName) {
        String table = null;
        String newSql = sql;
        Lexer lexer = createLexer(sql, dbType);
        lexer.nextToken();

        SQLExprParser exprParser;
        switch (dbType) {
            case mysql:
                exprParser = new MySqlExprParser(lexer);
                break;
            default:
                exprParser = new SQLExprParser(lexer);
                break;
        }

        for_:
        for (; lexer.token() != Token.EOF; ) {
            switch (lexer.token()) {
                case FROM:
                case INTO:
                    lexer.nextToken();
                    if (lexer.token() != Token.LPAREN && lexer.token() != Token.VALUES) {
                        if (StringUtils.hasText(replaceTableName)) {
                            int mark = ((MLexer) lexer).getMark();
                            int bufPoss = ((MLexer) lexer).getBufPos();
                            StringBuilder sbd = new StringBuilder();
                            sbd.append(sql.substring(0, mark));
                            sbd.append(replaceTableName);
                            sbd.append(sql.substring(mark + bufPoss));
                            newSql = sbd.toString();
                        }
                        SQLName name = exprParser.name();
                        table = name.toString();
                    }
                    break for_;
                default:
                    break;
            }

            lexer.nextToken();
        }

        System.out.println("原本的表名:" + table);
        System.out.println("替换表名为:" + replaceTableName + "后的sql:" + newSql);
        return newSql;
    }

    public static Lexer createLexer(String sql, DbType dbType) {
        return createLexer(sql, dbType, new SQLParserFeature[0]);
    }

    public static Lexer createLexer(String sql, DbType dbType, SQLParserFeature... features) {
        if (dbType == null) {
            dbType = DbType.mysql;
        }

        switch (dbType) {
            case mysql:
                return new MLexer(sql);
            default:
                return new Lexer(sql, null, dbType);
        }
    }

    private static class MLexer extends MySqlLexer {
        public MLexer(char[] input, int inputLength, boolean skipComment) {
            super(input, inputLength, skipComment);
        }

        public MLexer(String input) {
            super(input);
        }

        public MLexer(String input, SQLParserFeature... features) {
            super(input, features);
        }

        public MLexer(String input, boolean skipComment, boolean keepComments) {
            super(input, skipComment, keepComments);
        }

        public int getBufPos() {
            return this.bufPos;
        }

        public int getMark() {
            return this.mark;
        }

        public int getPos() {
            return this.pos;
        }
    }

}

getSqlInHoldCountIfNeccessary方法的效果是,我们知道在mysql的子查询IN中,假设一个sql有多处具有子查询IN,假定为:in (?)。但是我们需要自定义IN子查询后续的参数个数,意即类似更新sql的子查询参数数目为in (?,?,?),同时不改变原有sql的语句,那么通过我们自定义的该方法可以达到效果,测试如下:

package com.xiaoxu.parser;

import com.alibaba.druid.DbType;

/**
 * @author xiaoxu
 * @date 2024-03-12
 * java_demo2:com.xiaoxu.parser.SQLParserTest2
 */
public class SQLParserTest2 {

    public static void main(String[] args) {
        String sql = SQLParserUtil.getSqlInHoldCountIfNeccessary("select * from my where id in( ?,?) and status is not null and name in (?) and ot ='N' and pr in ()",
                DbType.mysql, new Integer[]{null, 4});
        System.out.println("最终结果是:");
        System.out.println(sql);

        String sql2 = SQLParserUtil.getSqlInHoldCountIfNeccessary("select * from my where id in (?)",
                DbType.mysql, new Integer[]{3});
        System.out.println(sql2);

        String sql3 = SQLParserUtil.getSqlInHoldCountIfNeccessary("select * from my where id = ?",
                DbType.mysql, new Integer[]{3});
        System.out.println(sql3);
    }

}

执行结果如下:

在这里插入图片描述
其中参数new Integer[]{null, 4}的效果是,第一个IN子查询不变,第二个子查询更新为in (?,?,?,?)。该逻辑是按照顺序更新IN的后续参数数目,同时不改变原有的sql。

### 关于SQL语句中 `::` 和 `:: AS tablename` 的解释 在标准的SQL语法中,`::` 并不是一个常见的达形式。然而,在某些特定场景下,这种示可能具有特殊的意义,通常与数据库的具体实现有关。 #### 1. **关于 `::`** 如果看到这样的语法,可能是某种方言或扩展功能的一部分。例如: - 在PostgreSQL中,`::` 是一种类型转换操作符[^4]。因此,`::` 可能被误解为尝试将某个对象强制转换为目标数据类型。但实际上,它并不适用于本身。 - 如果这是自定义逻辑的一部分,则可能是在应用程序层面对进行了字符串拼接或其他处理方式。例如: ```java String query = "SELECT * FROM " + tableName + "::" + aliasName; ``` 在这种情况下,具体含义需依赖上下文环境来判断。 --- #### 2. **关于 `:: AS tablename`** 当涉及到 `AS` 子句时,其主要目的是为列、达式指定别。对于 `:: AS tablename` 结构来说: - 假设第一个 `` 实际代原始称; - 第二个 `` 则作为中间过渡标识(尽管不常见),最终通过 `AS` 定义新的别 `tablename`. 此模式更接近于一些高级解析库的行为,比如阿里巴巴 Druid 或者其他第三方 SQL 解析工具可能会生成类似的 AST(Abstract Syntax Tree),用于内部跟踪源及其映射关系[^5]. 下面是一个简化版例子展示如何利用 Java 配合 Druid获取并重命: ```java import com.alibaba.druid.sql.parser.*; import com.alibaba.druid.sql.visitor.SchemaStatVisitor; public class TableNameExtractor { public static void main(String[] args){ String sql = "SELECT id,name FROM users u WHERE u.id > ?"; // 创建 MySQL 专用解析器实例 SQLStatementParser parser = new MySqlStatementParser(sql); SQLStatement stmt = parser.parseStatement(); // 初始化访问器类 SchemaStatVisitor visitor = new MySqlSchemaStatVisitor(); stmt.accept(visitor); System.out.println("Tables: "+visitor.getTables()); } } ``` 上述代码片段展示了如何提取 SQL 中涉及的所有格,并可通过进一步定制化逻辑完成诸如替换原生为新别的任务。 --- #### 总结说明 虽然传统意义上不存在严格意义上的 `::`,但在实际开发过程中由于不同框架的支持或者个人习惯等原因会出现类似现象;而带有 `AS` 的情况则更多体现了一种重新赋予外部可读性强的字给内部复杂计算结果的过程。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值