Java解析SQL中的表名：使⽤Druid解析SQL中的表名，使⽤
AlibabaDruid。。。
在⼯作中，我们有时候需要根据前端传来的SQL解析出SQL中的表名，使⽤⽹上各种通过正则表达式解析SQL的代码不仅⿇烦，其中或多或少都会有各种Bug，现在使⽤Druid解析SQL，能快速解决问题。
步骤如下：
第⼀步、pom⽂件中添加druid依赖：
<dependency>
<groupId>com.alibaba</groupId>
sql replace函数用法<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
roundif函数
SQLStatement sqlStatement = parser.parseStatement();
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
sqlStatement.accept(visitor);
Map<TableStat.Name, TableStat> tables = Tables();
List<String> allTableName = new ArrayList<>();
for (TableStat.Name t : tables.keySet()) {
tabletpc组件修复allTableName.Name());美食网页设计主页和子页
}
return allTableName;
}
测试代码：
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.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 =
mysqlschema作用"select \n" +
simulink仿真总结"  t1.*\n" +
"  ,t2.*\n" +
"from\n" +
"(\n" +
"  select \n" +
"    shop_id 门店id\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 = Tables();
List<String> allTableName = new ArrayList<>();
for (TableStat.Name t : tables.keySet()) {
allTableName.Name());
}
return allTableName;
}
}
运⾏发现，简单和复杂的SQL都能被解析出来：
MySQLSchemaStatVisitor的其他API如下：
数据库类型：getDbType());
查询的字段：getColumns());
表名：getTables().keySet());
条件：Conditions());
group by：GroupByColumns());
order by：getOrderByColumns());
测试结果：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。