Java解析SQL中的表名：使用Druid解析SQL中的表名，使用Alibaba Druid解析SQL中的数据库类型、字段、表名、条件、聚合类型、排序类型。

在工作中，我们有时候需要根据前端传来的SQL解析出SQL中的表名，使用网上各种通过正则表达式解析SQL的代码不仅麻烦，其中或多或少都会有各种Bug，现在使用Druid解析SQL，能快速解决问题。

步骤如下：

第一步、pom文件中添加druid依赖：

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

第二步、写方法，代码如下（以MySQL为例）：

    private static List<String> getAllTableNameBySQL(String sql) {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        // 使用Parser解析生成AST，这里SQLStatement就是AST
        SQLStatement sqlStatement = parser.parseStatement();
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        sqlStatement.accept(visitor);
        Map<TableStat.Name, TableStat> tables = visitor.getTables();
        List<String> allTableName = new ArrayList<>();
        for (TableStat.Name t : tables.keySet()) {
            allTableName.add(t.getName());
        }
        return allTableName;
    }

测试代码：

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.stat.TableStat;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 使用Druid解析SQL表名
 */
public class Main {
    public static void main(String[] args) {
        //测试内连接，相当于inner join
//        String sql = "select * from a,b where a.c=b.c";
        //测试复杂的SQL
        String sql =
                "select \n" +
                "  t1.*\n" +
                "  ,t2.*\n" +
                "from\n" +
                "(\n" +
                "  select \n" +
                "    shop_id 门店id\n" +
                "    ,shop_name 门店名称\n" +
                "  from tableA\n" +
                "  where pt=20201130\n" +
                "  and shop_level>=8          --注释\n" +
                ") t1\n" +
                "left join\n" +
                "(\n" +
                "  SELECT \n" +
                "    shop_id\n" +
                "    ,sum(create_orders) AS 创建订单数\n" +
                "    ,sum(payment_amount) AS 支付订单金额\n" +
                "    ,sum(pay_orders) AS 成功订单数\n" +
                "    ,sum(pay_amount) AS 成功订单金额\n" +
                "    ,sum(coalesce(ninety_orders,0) -coalesce(ninety_refund_orders,0)) AS XX元订单数\n" +
                "    ,sum(coalesce(ninety_order_amount,0) - coalesce(ninety_refund_amount,0)) AS XX元订单金额\n" +
                "    ,sum(zero_orders) AS 0元订单数\n" +
                "    ,sum(coalesce(pay_orders,0) - coalesce(zero_orders,0)) AS 付费订单数\n" +
                "  FROM tableB\n" +
                "  WHERE substr(pt,1,6) = '202011'\n" +
                "  GROUP BY \n" +
                "    shop_id\n" +
                ") t2 on t1.门店id = t2.shop_id\n" +
                ";";
        getAllTableNameBySQL(sql).forEach(System.out::println);
    }

    private static List<String> getAllTableNameBySQL(String sql) {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        // 使用Parser解析生成AST，这里SQLStatement就是AST
        SQLStatement sqlStatement = parser.parseStatement();
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        sqlStatement.accept(visitor);
        Map<TableStat.Name, TableStat> tables = visitor.getTables();
        List<String> allTableName = new ArrayList<>();
        for (TableStat.Name t : tables.keySet()) {
            allTableName.add(t.getName());
        }
        return allTableName;
    }
}

运行发现，简单和复杂的SQL都能被解析出来：

Alibaba Druid的GitHub网址：https://github.com/alibaba/druid/

MySQLSchemaStatVisitor的其他API如下：

数据库类型：getDbType());
查询的字段：getColumns());
表名：getTables().keySet());
条件：visitor.getConditions());
group by：visitor.getGroupByColumns());
order by：getOrderByColumns());

测试结果：