Skip to content

Commit 4858310

Browse files
luop23luop23
committed
编写sql解析代码
1 parent 098ae53 commit 4858310

File tree

3 files changed

+274
-2
lines changed

3 files changed

+274
-2
lines changed

‎pom.xml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,16 +9,24 @@
99
<relativePath/> <!-- lookup parent from repository -->
1010
</parent>
1111
<groupId>com.luop</groupId>
12-
<artifactId>sql_analysis</artifactId>
12+
<artifactId>sql-blood-analysis</artifactId>
1313
<version>0.0.1-SNAPSHOT</version>
14-
<name>sql_analysis</name>
14+
<name>sql-blood-analysis</name>
1515
<description>Demo project for Spring Boot</description>
1616

1717
<properties>
1818
<java.version>1.8</java.version>
1919
</properties>
2020

2121
<dependencies>
22+
23+
<!--jsqlparser依赖-->
24+
<dependency>
25+
<groupId>com.github.jsqlparser</groupId>
26+
<artifactId>jsqlparser</artifactId>
27+
<version>0.9.1</version>
28+
</dependency>
29+
2230
<dependency>
2331
<groupId>org.springframework.boot</groupId>
2432
<artifactId>spring-boot-starter</artifactId>

‎src/main/java/com/luop/MySelectVisitor.java

Lines changed: 222 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,222 @@
1+
package com.luop;
2+
3+
import net.sf.jsqlparser.expression.*;
4+
import net.sf.jsqlparser.schema.Column;
5+
import net.sf.jsqlparser.schema.Table;
6+
import net.sf.jsqlparser.statement.select.*;
7+
import org.springframework.util.StringUtils;
8+
9+
import java.util.*;
10+
11+
/**
12+
* @Author: luoping
13+
* @Date: 2019/11/5 09:50
14+
* @Description:
15+
*/
16+
public class MySelectVisitor implements SelectVisitor {
17+
//用来存储查询SQL语句涉及到的表和字段
18+
@Override
19+
public void visit(PlainSelect pSelect) {
20+
processFromItem(pSelect, pSelect.getFromItem());
21+
//子查询处理,joins处理
22+
List<Join> joins = pSelect.getJoins();
23+
if (joins != null) {
24+
for (Join join : joins) {
25+
tableAliasName = ""; //重置表别名
26+
processFromItem(pSelect, join.getRightItem());
27+
}
28+
}
29+
}
30+
31+
/**
32+
* 处理fromItem，并将处理结果保存起来
33+
*
34+
* @param pSelect 当前from信息对应的select信息
35+
* @param fromItem from信息
36+
*/
37+
private Map<String, String> columnMappingMap = new HashMap<>(); //别名：真实字段名
38+
private Map<String, Set<String>> map = new HashMap<>(); //装查询字段
39+
private boolean state = true;
40+
private String tableAliasName;
41+
42+
//处理查询字段里的函数
43+
private Expression analysisFunction(Expression expression, Map<String, Set<String>> map, String aliasName) {
44+
while (judgeExpression(expression)) {
45+
while (expression instanceof CaseExpression) { //case when 函数
46+
Expression elseExpression = ((CaseExpression) expression).getElseExpression(); //获取else表达式
47+
if (judgeExpression(elseExpression))
48+
expression = elseExpression;
49+
else if (!Objects.isNull(elseExpression) && judgeValueExpression(elseExpression))
50+
putSelectMap(elseExpression, map, aliasName);
51+
52+
for (Expression whenClause : ((CaseExpression) expression).getWhenClauses()) { //when条件
53+
WhenClause clause = (WhenClause) whenClause;
54+
expression = analysisFunction(clause.getThenExpression(), map, aliasName); //获取then表达式
55+
}
56+
}
57+
while (expression instanceof Function) { //函数
58+
Function function = (Function) expression;
59+
List<Expression> expressions = function.getParameters().getExpressions();
60+
for (Expression ex : expressions) {
61+
if (judgeExpression(ex)) {
62+
expression = analysisFunction(ex, map, aliasName);
63+
} else {
64+
if (judgeValueExpression(ex))
65+
expression = analysisFunction(ex, map, aliasName);
66+
}
67+
}
68+
}
69+
while (expression instanceof CastExpression) { //cast函数
70+
Expression leftExpression = ((CastExpression) expression).getLeftExpression();
71+
if (judgeExpression(leftExpression))
72+
expression = leftExpression;
73+
}
74+
while (expression instanceof BinaryExpression) { //concat函数
75+
Expression leftExpression = ((BinaryExpression) expression).getLeftExpression(); //左侧表达式;
76+
if (judgeExpression(leftExpression)) {
77+
expression = leftExpression;
78+
} else {
79+
if (judgeValueExpression(leftExpression)) {
80+
putSelectMap(leftExpression, map, aliasName);
81+
}
82+
expression = ((BinaryExpression) expression).getRightExpression(); // 右侧表达式
83+
}
84+
}
85+
}
86+
if (judgeValueExpression(expression))
87+
putSelectMap(expression, map, aliasName);
88+
return expression;
89+
}
90+
91+
//保存查询字段
92+
private void putSelectMap(Expression expression, Map<String, Set<String>> map, String aliasName) {
93+
Set<String> set = new HashSet<>();
94+
if (!map.containsKey(aliasName)) {
95+
set.add(expression.toString());
96+
} else {
97+
set = map.get(aliasName);
98+
set.add(expression.toString());
99+
}
100+
map.put(aliasName, set);
101+
}
102+
103+
private void processFromItem(PlainSelect pSelect, FromItem fromItem) {
104+
if (state) { //将查询字段按 字段名：别名 封装到map中
105+
List<SelectItem> selectColumn = pSelect.getSelectItems();
106+
for (SelectItem selectItem : selectColumn) {
107+
SelectExpressionItem expressionItem = (SelectExpressionItem) selectItem;
108+
String aliasName;//字段别名
109+
if (expressionItem.getAlias() == null) { //没有别名
110+
aliasName = ((Column) expressionItem.getExpression()).getColumnName();
111+
} else { //有别名
112+
aliasName = expressionItem.getAlias().getName();
113+
}
114+
Expression expression = expressionItem.getExpression();
115+
analysisFunction(expression, map, aliasName);
116+
}
117+
}
118+
if (!state && fromItem.getAlias() != null) {
119+
if (!StringUtils.isEmpty(tableAliasName))
120+
columnMappingMap.put(tableAliasName, fromItem.getAlias().getName()); //外层子查询别名：子查询别名
121+
}
122+
if (fromItem instanceof Table) { //表
123+
String tableName = getTableName((Table) fromItem);
124+
if (fromItem.getAlias() != null) {
125+
tableAliasName = fromItem.getAlias().getName();
126+
}
127+
columnMappingMap.put(tableAliasName, tableName); //表别名：表真实名
128+
tableAliasName = Objects.isNull(tableAliasName) ? tableName : tableAliasName;
129+
} else if (fromItem instanceof SubSelect) { //子查询
130+
state = false;
131+
tableAliasName = Objects.requireNonNull(fromItem.getAlias()).getName(); //表别名
132+
((SubSelect) fromItem).getSelectBody().accept(this);
133+
}
134+
}
135+
136+
//获取表名（包含 数据库名，表空间名）
137+
private String getTableName(Table fromItem) {
138+
String name = fromItem.getName(); //表名
139+
String databaseName = fromItem.getDatabase().getDatabaseName(); //数据库名
140+
String schemaName = fromItem.getSchemaName(); //表空间名
141+
if (!Objects.isNull(databaseName) && !Objects.isNull(schemaName))
142+
return databaseName + "." + schemaName + "." + name;
143+
if (Objects.isNull(databaseName) && !Objects.isNull(schemaName))
144+
return schemaName + "." + name;
145+
return name;
146+
}
147+
148+
@Override
149+
public void visit(SetOperationList sOperationList) {
150+
//union all会用到
151+
// List<PlainSelect> pSelects = sOperationList.getPlainSelects();
152+
// for (PlainSelect pSelect : pSelects) {
153+
// this.visit(pSelect);
154+
// }
155+
}
156+
157+
@Override
158+
public void visit(WithItem wItem) {
159+
// System.out.println("WithItem=========" + wItem);
160+
}
161+
162+
/**
163+
* 返回SQL解析的最终结果
164+
* 最终结果为血缘关系（别名字段来源真实表字段）
165+
*
166+
* @return
167+
*/
168+
Map<String, Set<String>> getSqlParseResult() {
169+
// System.out.println("columnMappingMap: " + columnMappingMap);
170+
// System.out.println("map:" + map);
171+
return getColumnMapping();
172+
}
173+
174+
//封装血缘关系
175+
private Map<String, Set<String>> getColumnMapping() {
176+
Map<String, Set<String>> map2 = new HashMap<>();
177+
Set<String> keySet = map.keySet();
178+
for (String str : keySet) {
179+
Set<String> columnSet = new HashSet<>();
180+
Set<String> set = map.get(str);
181+
for (String s : set) {
182+
if (s.contains(".")) {
183+
String[] split = s.split("[.]");
184+
s = getMap(split[0]) + "." + split[1];
185+
}
186+
columnSet.add(s);
187+
map2.put(str, columnSet);
188+
}
189+
}
190+
return map2;
191+
}
192+
193+
//循环查询直到找到真实表名
194+
private String getMap(String str) {
195+
while (columnMappingMap.containsKey(str)) {
196+
str = columnMappingMap.get(str);
197+
}
198+
return str;
199+
}
200+
201+
//判断查询字段中是否包含函数
202+
private boolean judgeExpression(Expression expression) {
203+
if (!Objects.isNull(expression))
204+
return expression instanceof Function || expression instanceof CastExpression || expression instanceof CaseExpression || expression instanceof BinaryExpression;
205+
else
206+
return false;
207+
}
208+
209+
//判断expression是否为值类型
210+
private boolean judgeValueExpression(Expression expression) {
211+
return !(expression instanceof StringValue) && !(expression instanceof DoubleValue) && !(expression instanceof LongValue);
212+
}
213+
214+
/*
215+
* Function: isnull,ifnull,wm_concat,replace
216+
* CastExpression: cast,
217+
* CaseExpression: case when
218+
* StringValue: ""
219+
* LongValue: 11
220+
* DoubleValue: 2.35
221+
* */
222+
}

‎src/main/java/com/luop/SelectParseHelper.java

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
package com.luop;
2+
3+
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
4+
import net.sf.jsqlparser.statement.Statement;
5+
import net.sf.jsqlparser.statement.select.Select;
6+
import net.sf.jsqlparser.statement.select.SelectBody;
7+
8+
import java.util.Map;
9+
import java.util.Set;
10+
11+
/**
12+
* SQL解析帮助类
13+
* 约束：
14+
* 仅支持SELECT语句的解析
15+
* 查询字段不可以为*
16+
* 表必须要使用别名，查询字段必须使用表的别名(例：select t.id,t.address from userinfo t)
17+
* 说明：
18+
* 该方法的使用场景：控制用户不要越权访问表或者表的字段
19+
* 出于上述使用场景，SQL解析时只关心where之前的部分，包括子查询语句。解析结果返回所有查询涉及到的表和查询的字段。
20+
* SQL解析利用开源的JSQLParser来实现，参考网址：https://github.com/JSQLParser/JSqlParser
21+
*
22+
* @author "朱云山"
23+
*
24+
*/
25+
class SelectParseHelper {
26+
/**
27+
* 获取SQL语句中用到的所有表和查询字段名称(名称均为大写)
28+
*
29+
* @param sqltxt 要解析的SQL语句
30+
* @return
31+
* @throws Exception
32+
*/
33+
public static Map<String,Set<String>> getTableAndColumns(String sqltxt) throws Exception{
34+
Statement stmt = CCJSqlParserUtil.parse(sqltxt); //报错 说明sql语句错误
35+
Select selectStatement=(Select)stmt;
36+
37+
MySelectVisitor mySelectVisitor=new MySelectVisitor();
38+
SelectBody sBody=selectStatement.getSelectBody();
39+
sBody.accept(mySelectVisitor);
40+
return mySelectVisitor.getSqlParseResult();
41+
}
42+
}

0 commit comments

Comments
 (0)