Skip to content

Commit 7b5ec60

Browse files
TommyLemonTommyLemon
committed
Server:新增支持key<>:[]表示key对应值没有内容；优化key<>:[Object]中Object类型处理；优化SQL注释并新增isNull,isEmpty方法
1 parent 8174026 commit 7b5ec60

2 files changed

Lines changed: 95 additions & 42 deletions

File tree

‎APIJSON(Server)/APIJSON(Eclipse_JEE)/src/main/java/zuo/biao/apijson/SQL.java‎

Lines changed: 81 additions & 38 deletions
Original file line numberDiff line numberDiff line change
@@ -22,119 +22,162 @@ public class SQL {
2222
public static final String OR = " OR ";
2323
public static final String AND = " AND ";
2424
public static final String NOT = " NOT ";
25-
public static final String IS = " IS ";
26-
public static final String NULL = " NULL ";
27-
25+
public static final String IS = " is ";
26+
public static final String NULL = " null ";
27+
28+
/**
29+
* isNull = true
30+
* @return {@link #isNull(boolean)}
31+
*/
32+
public static String isNull() {
33+
return isNull(true);
34+
}
2835
/**
2936
* @param isNull
30-
* @return
37+
* @return IS + (isNull ? "" : NOT) + NULL;
3138
*/
3239
public static String isNull(boolean isNull) {
33-
return "is" + (isNull ? "" : " not") + " null";
40+
return IS + (isNull ? "" : NOT) + NULL;
41+
}
42+
/**
43+
* isNull = true
44+
* @param s
45+
* @return {@link #isNull(String, boolean)}
46+
*/
47+
public static String isNull(String s) {
48+
return isNull(s, true);
49+
}
50+
/**
51+
* @param s
52+
* @param isNull
53+
* @return s + {@link #isNull(boolean)}
54+
*/
55+
public static String isNull(String s, boolean isNull) {
56+
return s + isNull(isNull);
57+
}
58+
59+
/**
60+
* isEmpty = true
61+
* @param s
62+
* @return {@link #isEmpty(String, boolean)}
63+
*/
64+
public static String isEmpty(String s) {
65+
return isEmpty(s, true);
3466
}
3567
/**
3668
* trim = false
3769
* @param s
3870
* @param isEmpty
39-
* @return
71+
* @return {@link #isEmpty(String, boolean, boolean)}
4072
*/
4173
public static String isEmpty(String s, boolean isEmpty) {
4274
return isEmpty(s, isEmpty, false);
4375
}
4476
/**
77+
* nullable = true
4578
* @param s
46-
* @param isEmpty
47-
* @param trim
48-
* @return
79+
* @param isEmpty <=0
80+
* @param trim s = trim(s);
81+
* @return {@link #isEmpty(String, boolean, boolean, boolean)}
4982
*/
5083
public static String isEmpty(String s, boolean isEmpty, boolean trim) {
84+
return isEmpty(s, isEmpty, trim, true);
85+
}
86+
/**
87+
* @param s
88+
* @param isEmpty <=0
89+
* @param trim s = trim(s);
90+
* @param nullable isNull(s, true) + OR +
91+
* @return {@link #lengthCompare(String, String)}
92+
*/
93+
public static String isEmpty(String s, boolean isEmpty, boolean trim, boolean nullable) {
5194
if (trim) {
5295
s = trim(s);
5396
}
54-
return lengthCompare(s, (isEmpty ? ">" : "<=") + "0");
97+
return (nullable ? isNull(s, true) + OR : "") + lengthCompare(s, (isEmpty ? "<=" : ">") + "0");
5598
}
5699
/**
57100
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
58-
* @return
101+
* @return {@link #length(String)} + compare
59102
*/
60103
public static String lengthCompare(String s, String compare) {
61104
return length(s) + compare;
62105
}
63-
64-
106+
107+
65108
/**
66109
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
67-
* @return
110+
* @return "length(" + s + ")"
68111
*/
69112
public static String length(String s) {
70113
return "length(" + s + ")";
71114
}
72115
/**
73116
* @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
74-
* @return
117+
* @return "char_length(" + s + ")"
75118
*/
76119
public static String charLength(String s) {
77120
return "char_length(" + s + ")";
78121
}
79-
122+
80123
/**
81124
* @param s
82-
* @return
125+
* @return "trim(" + s + ")"
83126
*/
84127
public static String trim(String s) {
85128
return "trim(" + s + ")";
86129
}
87130
/**
88131
* @param s
89-
* @return
132+
* @return "ltrim(" + s + ")"
90133
*/
91134
public static String trimLeft(String s) {
92135
return "ltrim(" + s + ")";
93136
}
94137
/**
95138
* @param s
96-
* @return
139+
* @return "rtrim(" + s + ")"
97140
*/
98141
public static String trimRight(String s) {
99142
return "rtrim(" + s + ")";
100143
}
101-
144+
102145
/**
103146
* @param s
104147
* @param n
105-
* @return
148+
* @return "left(" + s + "," + n + ")"
106149
*/
107150
public static String left(String s, int n) {
108151
return "left(" + s + "," + n + ")";
109152
}
110153
/**
111154
* @param s
112155
* @param n
113-
* @return
156+
* @return "right(" + s + "," + n + ")"
114157
*/
115158
public static String right(String s, int n) {
116159
return "right(" + s + "," + n + ")";
117160
}
118-
161+
119162
/**
120163
* @param s
121164
* @param start
122165
* @param end
123-
* @return
166+
* @return "substring(" + s + "," + start + "," + (end-start) + ")"
124167
*/
125168
public static String subString(String s, int start, int end) {
126169
return "substring(" + s + "," + start + "," + (end-start) + ")";
127170
}
128-
171+
129172
/**
130173
* @param s
131174
* @param c
132-
* @return
175+
* @return "instr(" + s + "," + c + ")"
133176
*/
134177
public static String indexOf(String s, String c) {
135178
return "instr(" + s + "," + c + ")";
136179
}
137-
180+
138181
/**
139182
* @param s
140183
* @param c1
@@ -144,35 +187,35 @@ public static String indexOf(String s, String c) {
144187
public static String replace(String s, String c1, String c2) {
145188
return "replace(" + s + "," + c1 + "," + c2 + ")";
146189
}
147-
190+
148191
/**
149192
* @param s1
150193
* @param s2
151-
* @return
194+
* @return "strcmp(" + s1 + "," + s2 + ")"
152195
*/
153196
public static String equals(String s1, String s2) {
154197
return "strcmp(" + s1 + "," + s2 + ")";
155198
}
156-
199+
157200
/**
158201
* @param s
159-
* @return
202+
* @return "upper(" + s + ")"
160203
*/
161204
public static String toUpperCase(String s) {
162205
return "upper(" + s + ")";
163206
}
164207
/**
165208
* @param s
166-
* @return
209+
* @return "lower(" + s + ")"
167210
*/
168211
public static String toLowerCase(String s) {
169212
return "lower(" + s + ")";
170213
}
171-
172-
173-
174-
175-
214+
215+
216+
217+
218+
176219
public static final int SEARCH_TYPE_CONTAIN_FULL = 0;
177220
public static final int SEARCH_TYPE_CONTAIN_ORDER = 1;
178221
public static final int SEARCH_TYPE_CONTAIN_SINGLE = 2;
@@ -243,5 +286,5 @@ public static String search(String s, int type, boolean ignoreCase) {
243286
return "%" + s + "%";
244287
}
245288
}
246-
289+
247290
}

‎APIJSON(Server)/APIJSON(Eclipse_JEE)/src/main/java/zuo/biao/apijson/server/sql/QueryConfig.java‎

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
import java.util.Map;
2626
import java.util.Set;
2727

28+
import com.alibaba.fastjson.JSON;
2829
import com.alibaba.fastjson.JSONArray;
2930
import com.alibaba.fastjson.JSONObject;
3031

@@ -348,6 +349,10 @@ public static String getWhereString(RequestMethod method, Map<String, Object> wh
348349
condition = (key + "='" + value + "'");
349350
break;
350351
}
352+
if (StringUtil.isEmpty(condition, true)) {//避免SQL条件连接错误
353+
continue;
354+
}
355+
351356
whereString += (isFirst ? "" : AND) + condition;
352357

353358
isFirst = false;
@@ -494,9 +499,6 @@ public static String getContainString(String key, Object value) throws NotExistE
494499
if (value == null) {
495500
return "";
496501
}
497-
if (value instanceof JSONObject) {
498-
throw new IllegalArgumentException(key + "<>\":value 中value不能为JSONObject！");
499-
}
500502

501503
Logic logic = new Logic(key);
502504
key = logic.getKey();
@@ -510,7 +512,9 @@ public static String getContainString(String key, Object value) throws NotExistE
510512
return getContainString(key, ((JSONArray) value).toArray(), logic.getType());
511513
}
512514
/**WHERE key contains childs
513-
* @param in
515+
* @param key
516+
* @param childs null ? "" : (empty ? no child : contains childs)
517+
* @param type |, &, !
514518
* @return LOGIC [ ( key LIKE '[" + childs[i] + "]' OR key LIKE '[" + childs[i] + ", %'
515519
* OR key LIKE '%, " + childs[i] + ", %' OR key LIKE '%, " + childs[i] + "]' ) ]
516520
* @throws IllegalArgumentException
@@ -520,6 +524,9 @@ public static String getContainString(String key, Object[] childs, int type) thr
520524
if (childs != null) {
521525
for (int i = 0; i < childs.length; i++) {
522526
if (childs[i] != null) {
527+
if (childs[i] instanceof JSON) {
528+
throw new IllegalArgumentException(key + "<>\":value 中value类型不能为JSON！");
529+
}
523530
if (childs[i] instanceof String) {
524531
childs[i] = "\"" + childs[i] + "\"";
525532
}
@@ -536,6 +543,9 @@ public static String getContainString(String key, Object[] childs, int type) thr
536543
);
537544
}
538545
}
546+
if (condition.isEmpty()) {
547+
condition = (SQL.isEmpty(key, true) + OR + getLikeString(key, "[]"));
548+
}
539549
}
540550
if (condition.isEmpty()) {
541551
return "";

0 commit comments

Comments
 (0)