8000 Server:给JOIN的ORDER BY和GROUP BY字段加表前缀 · coder-caicai/APIJSON@7bcad30 · GitHub
[go: up one dir, main page]
Skip to content

Commit 7bcad30

Browse files
TommyLemonTommyLemon
committed
Server:给JOIN的ORDER BY和GROUP BY字段加表前缀
1 parent 9b5b32c commit 7bcad30

File tree

1 file changed

+48
-33
lines changed

1 file changed

+48
-33
lines changed

‎APIJSON-Java-Server/APIJSONLibrary/src/main/java/zuo/biao/apijson/server/AbstractSQLConfig.java

Lines changed: 48 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -254,23 +254,29 @@ public AbstractSQLConfig setGroup(String group) {
254254
}
255255
@JSONField(serialize = false)
256256
public String getGroupString() {
257+
//TODO 加上子表的group
258+
257259
group = StringUtil.getTrimedString(group);
258260
if (group.isEmpty()) {
259261
return "";
260262
}
261263

262-
if (isPrepared()) { //不能通过 ? 来代替，因为SQLExecutor statement.setString后 GROUP BY 'userId' 有单引号，只能返回一条数据，必须去掉单引号才行！
263-
String[] keys = StringUtil.split(group);
264-
if (keys != null && keys.length > 0) {
265-
for (int i = 0; i < keys.length; i++) {
266-
if (StringUtil.isName(keys[i]) == false) {
267-
throw new IllegalArgumentException("@group:value 中 value里面用 , 分割的每一项都必须是1个单词！并且不要有空格！");
268-
}
264+
String[] keys = StringUtil.split(group);
265+
if (keys == null || keys.length <= 0) {
266+
return "";
267+
}
268+
269+
for (int i = 0; i < keys.length; i++) {
270+
if (isPrepared()) { //不能通过 ? 来代替，因为SQLExecutor statement.setString后 GROUP BY 'userId' 有单引号，只能返回一条数据，必须去掉单引号才行！
271+
if (StringUtil.isName(keys[i]) == false) {
272+
throw new IllegalArgumentException("@group:value 中 value里面用 , 分割的每一项都必须是1个单词！并且不要有空格！");
269273
}
270274
}
275+
276+
keys[i] = getKey(keys[i]);
271277
}
272278

273-
return " GROUP BY " + group;
279+
return " GROUP BY " + StringUtil.getString(keys);
274280
}
275281

276282
@Override
@@ -311,6 +317,8 @@ public AbstractSQLConfig setOrder(String order) {
311317
}
312318
@JSONField(serialize = false)
313319
public String getOrderString() {
320+
//TODO 加上子表的order
321+
314322
order = StringUtil.getTrimedString(order);
315323
if (order.isEmpty()) {
316324
return "";
@@ -321,30 +329,37 @@ public String getOrderString() {
321329
if (order.contains("-")) {
322330
order = order.replaceAll("-", " DESC ");
323331
}
324-
332+
325333
//TODO column, order, group 都改用 List<String> 存储！！！，并且每个字段都要加 Table. 前缀！
326-
if (isPrepared()) { //不能通过 ? 来代替，SELECT 'id','name' 返回的就是 id:"id", name:"name"，而不是数据库里的值！
327-
String[] keys = StringUtil.split(order);
328-
if (keys != null && keys.length > 0) {
329-
String origin;
330-
int index;
331-
for (int i = 0; i < keys.length; i++) {
332-
index = keys[i].trim().endsWith(" ASC") ? keys[i].lastIndexOf(" ASC") : -1; //StringUtil.split返回数组中，子项不会有null
333-
if (index < 0) {
334-
index = keys[i].trim().endsWith(" DESC") ? keys[i].lastIndexOf(" DESC") : -1;
335-
}
336-
origin = index < 0 ? keys[i] : keys[i].substring(0, index);
334+
String[] keys = StringUtil.split(order);
335+
if (keys == null || keys.length <= 0) {
336+
return "";
337+
}
337338

338-
//这里既不对origin trim，也不对 ASC/DESC ignoreCase，希望前端严格传没有任何空格的字符串过来，减少传输数据量，节约服务器性能
339-
if (StringUtil.isName(origin) == false) {
340-
throw new IllegalArgumentException("预编译模式下 @order:value 中 value里面用 , 分割的每一项"
341-
+ " column+ / column- 中 column必须是1个单词！并且不要有多余的空格！");
342-
}
339+
String origin;
340+
String sort;
341+
int index;
342+
for (int i = 0; i < keys.length; i++) {
343+
index = keys[i].trim().endsWith(" ASC") ? keys[i].lastIndexOf(" ASC") : -1; //StringUtil.split返回数组中，子项不会有null
344+
if (index < 0) {
345+
index = keys[i].trim().endsWith(" DESC") ? keys[i].lastIndexOf(" DESC") : -1;
346+
sort = index <= 0 ? "" : " DESC ";
347+
} else {
348+
sort = " ASC ";
349+
}
350+
origin = index < 0 ? keys[i] : keys[i].substring(0, index);
351+
352+
if (isPrepared()) { //不能通过 ? 来代替，SELECT 'id','name' 返回的就是 id:"id", name:"name"，而不是数据库里的值！
353+
//这里既不对origin trim，也不对 ASC/DESC ignoreCase，希望前端严格传没有任何空格的字符串过来，减少传输数据量，节约服务器性能
354+
if (StringUtil.isName(origin) == false) {
355+
throw new IllegalArgumentException("预编译模式下 @order:value 中 value里面用 , 分割的每一项"
356+
+ " column+ / column- 中 column必须是1个单词！并且不要有多余的空格！");
343357
}
344358
}
359+
keys[i] = getKey(origin) + sort;
345360
}
346-
347-
return " ORDER BY " + order;
361+
362+
return " ORDER BY " + StringUtil.getString(keys);
348363
}
349364

350365

@@ -699,7 +714,7 @@ else if (prior && andList.isEmpty() == false) {
699714
i ++;
700715
}
701716
}
702-
717+
703718
if (prior) {
704719
andList.add(i, key); //userId的优先级不能比id高 0, key);
705720
} else {
@@ -800,11 +815,11 @@ else if ("!".equals(ce.getKey())) {
800815
jc.setMain(false).setPrepared(isPrepared()).setPreparedValueList(new ArrayList<Object>());
801816
js = jc.getWhereString(false);
802817
jc.setMain(isMain);
803-
818+
804819
if (StringUtil.isEmpty(js, true)) {
805820
continue;
806821
}
807-
822+
808823
whereString = " ( "
809824
+ getCondition(
810825
Logic.isNot(logic),
@@ -813,7 +828,7 @@ else if ("!".equals(ce.getKey())) {
813828
+ " ( " + js + " ) "
814829
)
815830
+ " ) ";
816-
831+
817832
preparedValueList.addAll(jc.getPreparedValueList());
818833
break;
819834
//可能 LEFT JOIN 和 INNER JOIN 同时存在 default:
@@ -915,7 +930,7 @@ public AbstractSQLConfig setPreparedValueList(List<Object> preparedValueList) {
915930
this.preparedValueList = preparedValueList;
916931
return this;
917932
}
918-
933+
919934
//$ search <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
920935
/**search key match value
921936
* @param in
@@ -1412,7 +1427,7 @@ public String getJoinString() throws Exception {
14121427
+ j.getName() + " ON " + jc.getTable() + "." + j.getKey() + " = "
14131428
+ j.getTargetName() + "." + j.getTargetKey();
14141429
jc.setMain(false).setKeyPrefix(true);
1415-
1430+
14161431
preparedValueList.addAll(jc.getPreparedValueList());
14171432
break;
14181433
default:

0 commit comments

Comments
 (0)
0