Skip to content

Commit f068a21

Browse files
luop23luop23
committed
增加union解析以及表之间关系解析
1 parent f0a70ea commit f068a21

File tree

3 files changed

+145
-7
lines changed

3 files changed

+145
-7
lines changed

‎README.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
工具说明：
55
-
66
1、本工具只解析select语句
7-
2、支持子查询
7+
2、支持子查询和union查询
88
3、支持常用数据库的常用函数，如concat、case when、nvl、isnull、cast、ifnull等等
99
4、工具可能对某些sql无法解析，有待完善
1010

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

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -138,20 +138,21 @@ private String getTableName(Table fromItem) {
138138
String name = fromItem.getName(); //表名
139139
String databaseName = fromItem.getDatabase().getDatabaseName(); //数据库名
140140
String schemaName = fromItem.getSchemaName(); //表空间名
141-
if (!Objects.isNull(databaseName) && !Objects.isNull(schemaName))
142-
return databaseName + "." + schemaName + "." + name;
143141
if (Objects.isNull(databaseName) && !Objects.isNull(schemaName))
144142
return schemaName + "." + name;
143+
if (!Objects.isNull(databaseName)) {
144+
return databaseName + "." + (schemaName == null ? "" : schemaName) + "." + name;
145+
}
145146
return name;
146147
}
147148

148149
@Override
149150
public void visit(SetOperationList sOperationList) {
150151
//union all会用到
151-
// List<PlainSelect> pSelects = sOperationList.getPlainSelects();
152-
// for (PlainSelect pSelect : pSelects) {
153-
// this.visit(pSelect);
154-
// }
152+
List<PlainSelect> pSelects = sOperationList.getPlainSelects();
153+
for (PlainSelect pSelect : pSelects) {
154+
this.visit(pSelect);
155+
}
155156
}
156157

157158
@Override

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

Lines changed: 137 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,137 @@
1+
package com.luop;
2+
3+
import net.sf.jsqlparser.expression.Expression;
4+
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
5+
import net.sf.jsqlparser.expression.operators.relational.OldOracleJoinBinaryExpression;
6+
import net.sf.jsqlparser.schema.Table;
7+
import net.sf.jsqlparser.statement.select.*;
8+
import org.springframework.util.CollectionUtils;
9+
10+
import java.util.*;
11+
12+
/**
13+
* @Author: luoping
14+
* @Date: 2019/11/5 09:50
15+
* @Description: 表之间的关系
16+
*/
17+
public class MySelectVisitor2 implements SelectVisitor {
18+
//用来存储查询SQL语句涉及到的表和字段
19+
@Override
20+
public void visit(PlainSelect pSelect) {
21+
processFromItem(pSelect, pSelect.getFromItem());
22+
//子查询处理,joins处理
23+
List<Join> joins = pSelect.getJoins();
24+
if (joins != null) {
25+
for (Join join : joins) {
26+
processFromItem(pSelect, join.getRightItem());
27+
}
28+
}
29+
}
30+
31+
private Map<String, String> tableMap = new HashMap<>();
32+
private Map<String, Set<String>> mappingMap = new HashMap<>();
33+
34+
private void processFromItem(PlainSelect pSelect, FromItem fromItem) {
35+
List<Join> joins = pSelect.getJoins();
36+
if (!CollectionUtils.isEmpty(joins) && joins.size() > 0) {
37+
putTableMap(fromItem);
38+
39+
//解析join
40+
for (Join join : joins) {
41+
putTableMap(join.getRightItem());
42+
Expression onExpression = join.getOnExpression();
43+
analysisCondition(onExpression);
44+
}
45+
}
46+
}
47+
48+
//解析表间字段映射关系
49+
private Expression analysisCondition(Expression expression) {
50+
while (expression instanceof AndExpression) {
51+
AndExpression andExpression = (AndExpression) expression;
52+
analysisCondition(andExpression.getRightExpression());
53+
Expression leftExpression = andExpression.getLeftExpression();
54+
if (leftExpression instanceof OldOracleJoinBinaryExpression) {
55+
expression = leftExpression;
56+
} else {
57+
expression = analysisCondition(leftExpression);
58+
}
59+
}
60+
if (expression instanceof OldOracleJoinBinaryExpression) {
61+
String leftStr = ((OldOracleJoinBinaryExpression) expression).getLeftExpression().toString();
62+
String rightStr = ((OldOracleJoinBinaryExpression) expression).getRightExpression().toString();
63+
if (mappingMap.containsKey(leftStr)) {
64+
mappingMap.get(leftStr).add(rightStr);
65+
} else {
66+
Set<String> set = new HashSet<>();
67+
set.add(rightStr);
68+
mappingMap.put(leftStr, set);
69+
}
70+
}
71+
return expression;
72+
}
73+
74+
75+
//表之间的映射关系
76+
Map<String, Set<String>> tableMapping() {
77+
Map<String, Set<String>> map = new HashMap<>();
78+
if (!CollectionUtils.isEmpty(mappingMap)) {
79+
Set<String> keys = mappingMap.keySet();
80+
for (String key : keys) {
81+
Set<String> set = new HashSet<>();
82+
Set<String> values = mappingMap.get(key);
83+
if (!CollectionUtils.isEmpty(values)) {
84+
for (String value : values) {
85+
set.add(replaceAliasName(value));
86+
}
87+
}
88+
map.put(replaceAliasName(key), set);
89+
}
90+
}
91+
return map;
92+
}
93+
94+
//替换表别名
95+
private String replaceAliasName(String value) {
96+
if (value.contains(".")) {
97+
String[] target = value.split("[.]");
98+
return value.replace(target[0], tableMap.get(target[0]));
99+
}
100+
return value;
101+
}
102+
103+
104+
//将表按 别名：真实名 存进map中
105+
private void putTableMap(FromItem fromItem) {
106+
String tableName = getTableName((Table) fromItem);
107+
String tableAliasName = fromItem.getAlias().getName();
108+
tableMap.put(tableAliasName, tableName);
109+
}
110+
111+
//获取表名（包含 数据库名，表空间名）
112+
private String getTableName(Table fromItem) {
113+
String name = fromItem.getName(); //表名
114+
String databaseName = fromItem.getDatabase().getDatabaseName(); //数据库名
115+
String schemaName = fromItem.getSchemaName(); //表空间名
116+
if (Objects.isNull(databaseName) && !Objects.isNull(schemaName))
117+
return schemaName + "." + name;
118+
if (!Objects.isNull(databaseName)) {
119+
return databaseName + "." + (schemaName == null ? "" : schemaName) + "." + name;
120+
}
121+
return name;
122+
}
123+
124+
@Override
125+
public void visit(SetOperationList sOperationList) {
126+
//union all会用到
127+
List<PlainSelect> pSelects = sOperationList.getPlainSelects();
128+
for (PlainSelect pSelect : pSelects) {
129+
this.visit(pSelect);
130+
}
131+
}
132+
133+
@Override
134+
public void visit(WithItem wItem) {
135+
// System.out.println("WithItem=========" + wItem);
136+
}
137+
}

0 commit comments

Comments
 (0)