Repository navigation
Expand file tree
/
Copy pathSQL.java
More file actions
executable file
·392 lines (360 loc) · 9.67 KB
/
Copy pathSQL.java
File metadata and controls
executable file
·392 lines (360 loc) · 9.67 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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
/*Copyright (C) 2020 THL A29 Limited, a Tencent company. All rights reserved.
This source code is licensed under the Apache License Version 2.0.*/
package apijson;
/**SQL语句，函数名尽量和JDK中相同或类似功能的函数的名称一致
* @author Lemon
*/
public class SQL {
public static final String OR = " OR ";
public static final String AND = " AND ";
public static final String NOT = " NOT ";
public static final String AS = " AS ";
public static final String IS = " IS ";
public static final String NULL = " NULL ";
public static final String IS_NOT = " IS NOT ";
public static final String IS_NULL = " IS NULL ";
public static final String IS_NOT_NULL = " IS NOT NULL ";
//括号必须紧跟函数名！ count (...) 报错！
public static final String COUNT = "count";
public static final String SUM = "sum";
public static final String MAX = "max";
public static final String MIN = "min";
public static final String AVG = "avg";
/**
* isNull = true
* @return {@link #isNull(boolean)}
*/
public static String isNull() {
return isNull(true);
}
/**
* @param isNull
* @return {@link #IS} + (isNull ? "" : {@link #NOT}) + {@link #NULL};
*/
public static String isNull(boolean isNull) {
return IS + (isNull ? "" : NOT) + NULL;
}
/**
* isNull = true
* @param s
* @return {@link #isNull(String, boolean)}
*/
public static String isNull(String s) {
return isNull(s, true);
}
/**
* @param s
* @param isNull
* @return s + {@link #isNull(boolean)}
*/
public static String isNull(String s, boolean isNull) {
return s + isNull(isNull);
}
/**
* isEmpty = true
* @param s
* @return {@link #isEmpty(String, boolean)}
*/
public static String isEmpty(String s) {
return isEmpty(s, true);
}
/**
* trim = false
* @param s
* @param isEmpty
* @return {@link #isEmpty(String, boolean, boolean)}
*/
public static String isEmpty(String s, boolean isEmpty) {
return isEmpty(s, isEmpty, false);
}
/**
* nullable = true
* @param s
* @param isEmpty <=0
* @param trim s = trim(s);
* @return {@link #isEmpty(String, boolean, boolean, boolean)}
*/
public static String isEmpty(String s, boolean isEmpty, boolean trim) {
return isEmpty(s, isEmpty, trim, true);
}
/**
* @param s
* @param isEmpty <=0
* @param trim s = trim(s);
* @param nullable isNull(s, true) + {@link #OR} +
* @return {@link #lengthCompare(String, String)}
*/
public static String isEmpty(String s, boolean isEmpty, boolean trim, boolean nullable) {
if (trim) {
s = trim(s);
}
return (nullable ? isNull(s, true) + OR : "") + lengthCompare(s, (isEmpty ? "<=" : ">") + "0");
}
/**
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
* @return {@link #length(String)} + compare
*/
public static String lengthCompare(String s, String compare) {
return length(s) + compare;
}
/**
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
* @return "length(" + s + ")"
*/
public static String length(String s) {
return "length(" + s + ")";
}
/**
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
* @return "char_length(" + s + ")"
*/
public static String charLength(String s) {
return "char_length(" + s + ")";
}
/**
* @param s
* @return "trim(" + s + ")"
*/
public static String trim(String s) {
return "trim(" + s + ")";
}
/**
* @param s
* @return "ltrim(" + s + ")"
*/
public static String trimLeft(String s) {
return "ltrim(" + s + ")";
}
/**
* @param s
* @return "rtrim(" + s + ")"
*/
public static String trimRight(String s) {
return "rtrim(" + s + ")";
}
/**
* @param s
* @param n
* @return "left(" + s + "," + n + ")"
*/
public static String left(String s, int n) {
return "left(" + s + "," + n + ")";
}
/**
* @param s
* @param n
* @return "right(" + s + "," + n + ")"
*/
public static String right(String s, int n) {
return "right(" + s + "," + n + ")";
}
/**
* @param s
* @param start
* @param end
* @return "substring(" + s + "," + start + "," + (end-start) + ")"
*/
public static String subString(String s, int start, int end) {
return "substring(" + s + "," + start + "," + (end-start) + ")";
}
/**
* @param s
* @param c
* @return "instr(" + s + ", " + c + ")"
*/
public static String indexOf(String s, String c) {
return "instr(" + s + ", " + c + ")";
}
/**
* @param s
* @param c1
* @param c2
* @return "replace(" + s + ", " + c1 + ", " + c2 + ")"
*/
public static String replace(String s, String c1, String c2) {
return "replace(" + s + ", " + c1 + ", " + c2 + ")";
}
/**
* @param s1
* @param s2
* @return "concat(" + s1 + ", " + s2 + ")"
*/
public static String concat(String s1, String s2) {
return "concat(" + s1 + ", " + s2 + ")";
}
/**
* @param s1
* @param s2
* @return "strcmp(" + s1 + ", " + s2 + ")"
*/
public static String equals(String s1, String s2) {
return "strcmp(" + s1 + ", " + s2 + ")";
}
/**
* @param s
* @return "upper(" + s + ")"
*/
public static String toUpperCase(String s) {
return "upper(" + s + ")";
}
/**
* @param s
* @return "lower(" + s + ")"
*/
public static String toLowerCase(String s) {
return "lower(" + s + ")";
}
//column and function<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
/**字段
* @param column
* @return column.isEmpty() ? "*" : column;
*/
public static String column(String column) {
column = StringUtil.getTrimedString(column);
return column.isEmpty() ? "*" : column;
}
/**有别名的字段
* @param column
* @return {@link #count(String)} + {@link #AS};
*/
public static String columnAs(String column) {
return count(column) + AS;
}
/**函数
* @param column if (StringUtil.isEmpty(column, true) || column.contains(",")) -> column = null;
* @return " " + fun + "(" + {@link #column(String)} + ") ";
*/
public static String function(String fun, String column) {
if (StringUtil.isEmpty(column, true) || column.contains(",")) {
column = null; //解决 count(id,name) 这种多个字段导致的SQL异常
}
return " " + fun + "(" + column(column) + ") ";
}
/**有别名的函数
* @param column
* @return {@link #function(String, String)} + {@link #AS} + fun;
*/
public static String functionAs(String fun, String column) {
return function(fun, column) + AS + fun + " ";
}
/**计数
* column = null
* @return {@link #count(String)}
*/
public static String count() {
return count(null);
}
/**计数
* fun = {@link #COUNT}
* @param column
* @return {@link #functionAs(String, String)}
*/
public static String count(String column) {
return functionAs(COUNT, column);
}
/**求和
* fun = {@link #SUM}
* @param column
* @return {@link #functionAs(String, String)}
*/
public static String sum(String column) {
return functionAs(SUM, column);
}
/**最大值
* fun = {@link #MAX}
* @param column
* @return {@link #functionAs(String, String)}
*/
public static String max(String column) {
return functionAs(MAX, column);
}
/**最小值
* fun = {@link #MIN}
* @param column
* @return {@link #functionAs(String, String)}
*/
public static String min(String column) {
return functionAs(MIN, column);
}
/**平均值
* fun = {@link #AVG}
* @param column
* @return {@link #functionAs(String, String)}
*/
public static String avg(String column) {
return functionAs(AVG, column);
}
//column and function>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
//search<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
public static final int SEARCH_TYPE_CONTAIN_FULL = 0;
public static final int SEARCH_TYPE_CONTAIN_ORDER = 1;
public static final int SEARCH_TYPE_CONTAIN_SINGLE = 2;
public static final int SEARCH_TYPE_CONTAIN_ANY = 3;
public static final int SEARCH_TYPE_START = 4;
public static final int SEARCH_TYPE_END = 5;
public static final int SEARCH_TYPE_START_SINGLE = 6;
public static final int SEARCH_TYPE_END_SINGLE = 7;
public static final int SEARCH_TYPE_PART_MATCH = 8;
/**获取搜索值
* @param s
* @return
*/
public static String search(String s) {
return search(s, SEARCH_TYPE_CONTAIN_FULL);
}
/**获取搜索值
* @param s
* @param type
* @return
*/
public static String search(String s, int type) {
return search(s, type, true);
}
/**获取搜索值
* @param s
* @param type
* @param ignoreCase
* @return default SEARCH_TYPE_CONTAIN_FULL
*/
public static String search(String s, int type, boolean ignoreCase) {
if (s == null) {
return null;
}
switch (type) {
case SEARCH_TYPE_CONTAIN_SINGLE:
return "_" + s + "_";
case SEARCH_TYPE_CONTAIN_ORDER:
char[] cs = s.toCharArray();
if (cs == null) {
return null;
}
String value = "%";
for (int i = 0; i < cs.length; i++) {
value += cs[i] + "%";
}
return value;
case SEARCH_TYPE_START:
return s + "%";
case SEARCH_TYPE_END:
return "%" + s;
case SEARCH_TYPE_START_SINGLE:
return s + "_";
case SEARCH_TYPE_END_SINGLE:
return "_" + s;
case SEARCH_TYPE_CONTAIN_ANY:
case SEARCH_TYPE_PART_MATCH:
cs = s.toCharArray();
if (cs == null) {
return null;
}
value = "";
for (int i = 0; i < cs.length; i++) {
value += search("" + cs[i], SEARCH_TYPE_CONTAIN_FULL, ignoreCase);
}
return value;
default://SEARCH_TYPE_CONTAIN_FULL
return "%" + s + "%";
}
}
//search>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
}