DruidParser解析SQL-校验重复SQL
添加maven依赖
<dependency>
<groupId>com.alibaba
</groupId>
<artifactId>druid
</artifactId>
<version>1.0.26
</version>
</dependency>
druidParser解析SQL工具类
package druid
;
import com
.alibaba
.druid
.sql
.ast
.SQLStatement
;
import com
.alibaba
.druid
.sql
.dialect
.mysql
.visitor
.MySqlSchemaStatVisitor
;
import com
.alibaba
.druid
.sql
.parser
.SQLParserUtils
;
import com
.alibaba
.druid
.sql
.parser
.SQLStatementParser
;
import com
.alibaba
.druid
.stat
.TableStat
;
import java
.util
.ArrayList
;
import java
.util
.HashMap
;
import java
.util
.List
;
import java
.util
.Map
;
public class DruidSqlParse {
public static Map
<String
, List
<String>> parse(String sql
, String dbType
) throws Exception
{
Map
<String
, List
<String>> visitorMap
= new HashMap<>();
SQLStatementParser statementParser
= SQLParserUtils
.createSQLStatementParser(sql
, dbType
);
SQLStatement sqlStatement
= statementParser
.parseStatement();
MySqlSchemaStatVisitor visitor
= new MySqlSchemaStatVisitor();
sqlStatement
.accept(visitor
);
if (!visitor
.getTables().isEmpty()) {
List
<String> tables
= new ArrayList<>();
for (Map
.Entry
<TableStat.Name, TableStat> nameTableStatEntry
: visitor
.getTables().entrySet()) {
tables
.add(nameTableStatEntry
.getKey().getName());
}
visitorMap
.put("tables", tables
);
}
if (!visitor
.getColumns().isEmpty()) {
List
<String> columns
= new ArrayList<>();
for (TableStat
.Column column
: visitor
.getColumns()) {
columns
.add(column
.getTable() + "." + column
.getName());
}
visitorMap
.put("columns", columns
);
}
if (!visitor
.getConditions().isEmpty()) {
List
<String> conditions
= new ArrayList<>();
for (TableStat
.Condition condition
: visitor
.getConditions()) {
conditions
.add(condition
.getColumn() + condition
.getOperator());
}
visitorMap
.put("conditions", conditions
);
}
if (!visitor
.getGroupByColumns().isEmpty()) {
List
<String> groupByColumns
= new ArrayList<>();
for (TableStat
.Column groupByColumn
: visitor
.getGroupByColumns()) {
groupByColumns
.add(groupByColumn
.getTable() + "." + groupByColumn
.getName());
}
visitorMap
.put("groupByColumns", groupByColumns
);
}
if (!visitor
.getOrderByColumns().isEmpty()) {
List
<String> orderByColumns
= new ArrayList<>();
for (TableStat
.Column orderByColumn
: visitor
.getOrderByColumns()) {
orderByColumns
.add(orderByColumn
.getTable() + "." + orderByColumn
.getTable());
}
visitorMap
.put("orderByColumns", orderByColumns
);
}
return visitorMap
;
}
}
测试方法
public static void main(String
[] args
) throws Exception
{
String dbType
= JdbcUtils
.MYSQL
;
String sql1
= "select * from user where id = '1' and password = 'xxx'";
String sql2
= "select * from user where id = '2' and password = 'bbb'";
String sql3
= "select * from user where id = '2' and username = 'xiaoming'";
Map
<String
, List
<String>> sql1Map
= DruidSqlParse
.parse(sql1
, dbType
);
Map
<String
, List
<String>> sql2Map
= DruidSqlParse
.parse(sql2
, dbType
);
Map
<String
, List
<String>> sql3Map
= DruidSqlParse
.parse(sql3
, dbType
);
System
.out
.println("sql1与sql2为同一SQL：" + sql1Map
.equals(sql2Map
));
System
.out
.println("sql1与sql3为同一SQL：" + sql1Map
.equals(sql3Map
));
}
