SqlWhereHelper.java
4.77 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
package com.xly.util;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
@Slf4j
public class SqlWhereHelper {
/**
* 判断 SQL 是否包含 WHERE 条件（使用 JSqlParser）
*/
public static boolean hasWhereCondition(String sql) {
if (StrUtil.isBlank(sql)) {
return false;
}
try {
Statement statement = CCJSqlParserUtil.parse(sql);
if (statement instanceof Select) {
Select select = (Select) statement;
return select.getPlainSelect() != null && select.getPlainSelect().getWhere() != null;
}
if (statement instanceof Update) {
Update update = (Update) statement;
return update.getWhere() != null;
}
if (statement instanceof Delete) {
Delete delete = (Delete) statement;
return delete.getWhere() != null;
}
} catch (JSQLParserException e) {
log.warn("SQL 解析失败，回退到简单匹配: {}", sql, e);
return hasWhereConditionSimple(sql);
}
return false;
}
/**
* 简单判断是否有 WHERE
*/
public static boolean hasWhereConditionSimple(String sql) {
if (StrUtil.isBlank(sql)) return false;
String s = sql.toUpperCase();
int whereIdx = s.indexOf("WHERE");
if (whereIdx == -1) return false;
String before = s.substring(0, whereIdx).trim();
if (!before.matches(".*\\b(SELECT|UPDATE|DELETE)\\b.*")) return false;
String after = s.substring(whereIdx + 5).trim();
return !after.isEmpty() && !after.matches("^(GROUP BY|ORDER BY|LIMIT).*");
}
// ===================== 你要的新方法 =====================
/**
* 判断：
* 1. 有 WHERE
* 2. 条件中 不包含 = > < >= <= IN EXISTS
* → 满足返回 true
*/
public static boolean hasWhereButNoCompareOperators(String sql) {
if (!hasWhereCondition(sql)) {
return false;
}
String upper = sql.toUpperCase();
// 禁止出现的条件符号/关键字
boolean hasEq = upper.contains("=");
boolean hasGt = upper.contains(">");
boolean hasLt = upper.contains("<");
boolean hasIn = upper.contains(" IN ");
boolean hasExists = upper.contains(" EXISTS ");
// 只要有任何一个，就返回 false
if (hasEq || hasGt || hasLt || hasIn || hasExists) {
return false;
}
// 有 WHERE 且 无等值/区间/大小/IN/EXISTS → 返回 true
return true;
}
// ===================== 你原来的方法不动 =====================
public static int getWhereInsertPosition(String sql) {
if (hasWhereCondition(sql)) {
return -1;
}
String upperSql = sql.toUpperCase();
int fromIndex = upperSql.indexOf("FROM");
if (fromIndex == -1) return sql.length();
int tableEndIndex = findTableEnd(sql, fromIndex + 4);
int groupIndex = upperSql.indexOf("GROUP BY", tableEndIndex);
int orderIndex = upperSql.indexOf("ORDER BY", tableEndIndex);
int limitIndex = upperSql.indexOf("LIMIT", tableEndIndex);
int nextKeyword = sql.length();
if (groupIndex != -1) nextKeyword = Math.min(nextKeyword, groupIndex);
if (orderIndex != -1) nextKeyword = Math.min(nextKeyword, orderIndex);
if (limitIndex != -1) nextKeyword = Math.min(nextKeyword, limitIndex);
return nextKeyword;
}
private static int findTableEnd(String sql, int startIndex) {
if (startIndex >= sql.length()) return sql.length();
String afterFrom = sql.substring(startIndex);
int endIndex = startIndex;
for (int i = 0; i < afterFrom.length(); i++) {
char c = afterFrom.charAt(i);
if (Character.isWhitespace(c) || c == ',' || c == '(' || c == ')') {
endIndex = startIndex + i;
break;
}
endIndex = startIndex + i + 1;
}
return endIndex;
}
public static String addWhereCondition(String sql, String condition) {
if (StrUtil.isBlank(sql) || StrUtil.isBlank(condition)) return sql;
if (hasWhereCondition(sql)) {
return sql + " AND " + condition;
} else {
int insertPos = getWhereInsertPosition(sql);
return sql.substring(0, insertPos) + " WHERE " + condition + sql.substring(insertPos);
}
}
}