Skip to content

Commit c39cd1e

Browse files
TommyLemonTommyLemon
committed
新增数组关键词 compat 解决对聚合函数字段通过 query:2 分页查总数返回值错误
1 parent 38c1997 commit c39cd1e

File tree

5 files changed

+120
-34
lines changed

5 files changed

+120
-34
lines changed

‎APIJSONORM/src/main/java/apijson/JSONRequest.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,7 @@ public JSONRequest setFormat(Boolean format) {
8787
public static final String SUBQUERY_RANGE_ANY = "ANY";
8888

8989
public static final String KEY_QUERY = "query";
90+
public static final String KEY_COMPAT = "compat";
9091
public static final String KEY_COUNT = "count";
9192
public static final String KEY_PAGE = "page";
9293
public static final String KEY_JOIN = "join";
@@ -97,6 +98,7 @@ public JSONRequest setFormat(Boolean format) {
9798
static {
9899
ARRAY_KEY_LIST = new ArrayList<String>();
99100
ARRAY_KEY_LIST.add(KEY_QUERY);
101+
ARRAY_KEY_LIST.add(KEY_COMPAT);
100102
ARRAY_KEY_LIST.add(KEY_COUNT);
101103
ARRAY_KEY_LIST.add(KEY_PAGE);
102104
ARRAY_KEY_LIST.add(KEY_JOIN);

‎APIJSONORM/src/main/java/apijson/SQL.java‎

Lines changed: 17 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ public class SQL {
2121
public static final String IS_NOT = " IS NOT ";
2222
public static final String IS_NULL = " IS NULL ";
2323
public static final String IS_NOT_NULL = " IS NOT NULL ";
24-
24+
2525
//括号必须紧跟函数名！ count (...) 报错！
2626
public static final String COUNT = "count";
2727
public static final String SUM = "sum";
@@ -191,7 +191,7 @@ public static String indexOf(String s, String c) {
191191
public static String replace(String s, String c1, String c2) {
192192
return "replace(" + s + ", " + c1 + ", " + c2 + ")";
193193
}
194-
194+
195195
/**
196196
* @param s1
197197
* @param s2
@@ -225,11 +225,11 @@ public static String toLowerCase(String s) {
225225
return "lower(" + s + ")";
226226
}
227227

228-
229-
228+
229+
230230

231231
//column and function<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
232-
232+
233233
/**字段
234234
* @param column
235235
* @return column.isEmpty() ? "*" : column;
@@ -245,15 +245,16 @@ public static String column(String column) {
245245
public static String columnAs(String column) {
246246
return count(column) + AS;
247247
}
248-
248+
249249
/**函数
250250
* @param column if (StringUtil.isEmpty(column, true) || column.contains(",")) -> column = null;
251251
* @return " " + fun + "(" + {@link #column(String)} + ") ";
252252
*/
253253
public static String function(String fun, String column) {
254-
if (StringUtil.isEmpty(column, true) || column.contains(",")) {
255-
column = null; //解决 count(id,name) 这种多个字段导致的SQL异常
256-
}
254+
// 支持 fun(col1,col2..)
255+
// if (StringUtil.isEmpty(column, true) || column.contains(",")) {
256+
// column = null; //解决 count(id,name) 这种多个字段导致的SQL异常
257+
// }
257258
return " " + fun + "(" + column(column) + ") ";
258259
}
259260
/**有别名的函数
@@ -263,7 +264,7 @@ public static String function(String fun, String column) {
263264
public static String functionAs(String fun, String column) {
264265
return function(fun, column) + AS + fun + " ";
265266
}
266-
267+
267268
/**计数
268269
* column = null
269270
* @return {@link #count(String)}
@@ -313,9 +314,9 @@ public static String avg(String column) {
313314
}
314315

315316
//column and function>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
316-
317-
318-
317+
318+
319+
319320
//search<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
320321

321322
public static final int SEARCH_TYPE_CONTAIN_FULL = 0;
@@ -391,13 +392,13 @@ public static String search(String s, int type, boolean ignoreCase) {
391392

392393
//search>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
393394

394-
395+
395396
public static boolean isBooleanOrNumber(String type) {
396397
type = StringUtil.toUpperCase(type, true);
397398
return type.isEmpty() || (type.endsWith("INT") && type.endsWith("POINT") == false)
398399
|| type.endsWith("BOOLEAN") || type.endsWith("ENUM")
399400
|| type.endsWith("FLOAT") || type.endsWith("DOUBLE") || type.endsWith("DECIMAL");
400401
}
401-
402-
402+
403+
403404
}

‎APIJSONORM/src/main/java/apijson/orm/AbstractParser.java‎

Lines changed: 32 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@
4242
import apijson.Log;
4343
import apijson.NotNull;
4444
import apijson.RequestMethod;
45+
import apijson.SQL;
4546
import apijson.StringUtil;
4647
import apijson.orm.exception.ConditionErrorException;
4748
import apijson.orm.exception.ConflictException;
@@ -1051,9 +1052,33 @@ public JSONObject onObjectParse(final JSONObject request
10511052
//total 这里不能用arrayConfig.getType()，因为在createObjectParser.onChildParse传到onObjectParse时已被改掉
10521053
if (type == SQLConfig.TYPE_ITEM_CHILD_0 && query != JSONRequest.QUERY_TABLE && position == 0) {
10531054

1054-
RequestMethod method = op.getMethod();
1055-
JSONObject rp = op.setMethod(RequestMethod.HEAD).setSQLConfig().executeSQL().getSqlReponse();
1056-
op.setMethod(method);
1055+
JSONObject rp;
1056+
Boolean compat = arrayConfig.getCompat();
1057+
if (compat != null && compat) {
1058+
// 解决对聚合函数字段通过 query:2 分页查总数返回值错误
1059+
// 这里可能改变了内部的一些数据，下方通过 arrayConfig 还原
1060+
SQLConfig cfg = op.setSQLConfig(0, 0, 0).getSQLConfig();
1061+
boolean isExplain = cfg.isExplain();
1062+
cfg.setExplain(false);
1063+
1064+
Subquery subq = new Subquery();
1065+
subq.setFrom(cfg.getTable());
1066+
subq.setConfig(cfg);
1067+
1068+
SQLConfig countSQLCfg = createSQLConfig();
1069+
countSQLCfg.setColumn(Arrays.asList("count(*):count"));
1070+
countSQLCfg.setFrom(subq);
1071+
1072+
rp = executeSQL(countSQLCfg, false);
1073+
1074+
cfg.setExplain(isExplain);
1075+
}
1076+
else {
1077+
// 对聚合函数字段通过 query:2 分页查总数返回值错误
1078+
RequestMethod method = op.getMethod();
1079+
rp = op.setMethod(RequestMethod.HEAD).setSQLConfig().executeSQL().getSqlReponse();
1080+
op.setMethod(method);
1081+
}
10571082

10581083
if (rp != null) {
10591084
int index = parentPath.lastIndexOf("]/");
@@ -1147,6 +1172,7 @@ public JSONArray onArrayParse(JSONObject request, String parentPath, String name
11471172

11481173
//不能改变，因为后面可能继续用到，导致1以上都改变 []:{0:{Comment[]:{0:{Comment:{}},1:{...},...}},1:{...},...}
11491174
final String query = request.getString(JSONRequest.KEY_QUERY);
1175+
final Boolean compat = request.getBoolean(JSONRequest.KEY_COMPAT);
11501176
final Integer count = request.getInteger(JSONRequest.KEY_COUNT); //TODO 如果不想用默认数量可以改成 getIntValue(JSONRequest.KEY_COUNT);
11511177
final Integer page = request.getInteger(JSONRequest.KEY_PAGE);
11521178
final Object join = request.get(JSONRequest.KEY_JOIN);
@@ -1189,6 +1215,7 @@ public JSONArray onArrayParse(JSONObject request, String parentPath, String name
11891215
}
11901216

11911217
request.remove(JSONRequest.KEY_QUERY);
1218+
request.remove(JSONRequest.KEY_COMPAT);
11921219
request.remove(JSONRequest.KEY_COUNT);
11931220
request.remove(JSONRequest.KEY_PAGE);
11941221
request.remove(JSONRequest.KEY_JOIN);
@@ -1227,6 +1254,7 @@ else if (childKeys.length == 1 && JSONRequest.isTableKey(childKeys[0])) { //
12271254
.setCount(size)
12281255
.setPage(page2)
12291256
.setQuery(query2)
1257+
.setCompat(compat)
12301258
.setTable(arrTableKey)
12311259
.setJoinList(onJoinParse(join, request));
12321260

@@ -1301,6 +1329,7 @@ else if (childKeys.length == 1 && JSONRequest.isTableKey(childKeys[0])) { //
13011329
} finally {
13021330
//后面还可能用到，要还原
13031331
request.put(JSONRequest.KEY_QUERY, query);
1332+
request.put(JSONRequest.KEY_COMPAT, compat);
13041333
request.put(JSONRequest.KEY_COUNT, count);
13051334
request.put(JSONRequest.KEY_PAGE, page);
13061335
request.put(JSONRequest.KEY_JOIN, join);

‎APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java‎

Lines changed: 66 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@ public abstract class AbstractSQLConfig implements SQLConfig {
104104
// 自定义原始 SQL 片段 Map<key, substring>：当 substring 为 null 时忽略；当 substring 为 "" 时整个 value 是 raw SQL；其它情况则只是 substring 这段为 raw SQL
105105
public static final Map<String, String> RAW_MAP;
106106
// 允许调用的 SQL 函数：当 substring 为 null 时忽略；当 substring 为 "" 时整个 value 是 raw SQL；其它情况则只是 substring 这段为 raw SQL
107+
public static final Map<String, String> SQL_AGGREGATE_FUNCTION_MAP;
107108
public static final Map<String, String> SQL_FUNCTION_MAP;
108109

109110

@@ -242,6 +243,13 @@ public abstract class AbstractSQLConfig implements SQLConfig {
242243

243244

244245

246+
SQL_AGGREGATE_FUNCTION_MAP = new LinkedHashMap<>(); // 保证顺序，避免配置冲突等意外情况
247+
SQL_AGGREGATE_FUNCTION_MAP.put("max", "");
248+
SQL_AGGREGATE_FUNCTION_MAP.put("min", "");
249+
SQL_AGGREGATE_FUNCTION_MAP.put("avg", "");
250+
SQL_AGGREGATE_FUNCTION_MAP.put("count", "");
251+
SQL_AGGREGATE_FUNCTION_MAP.put("sum", "");
252+
245253
SQL_FUNCTION_MAP = new LinkedHashMap<>(); // 保证顺序，避免配置冲突等意外情况
246254

247255
//窗口函数
@@ -761,6 +769,7 @@ public String getUserIdKey() {
761769
private int page; //Table所在页码
762770
private int position; //Table在[]中的位置
763771
private int query; //JSONRequest.query
772+
private Boolean compat; //JSONRequest.compat query total
764773
private int type; //ObjectParser.type
765774
private int cache;
766775
private boolean explain;
@@ -1458,14 +1467,9 @@ public String getColumnString(boolean inSQLJoin) throws Exception {
14581467
case HEAD:
14591468
case HEADS: //StringUtil.isEmpty(column, true) || column.contains(",") 时SQL.count(column)会return "*"
14601469
if (isPrepared() && column != null) {
1461-
14621470
List<String> raw = getRaw();
14631471
boolean containRaw = raw != null && raw.contains(KEY_COLUMN);
1464-
1465-
String origin;
1466-
String alias;
1467-
int index;
1468-
1472+
14691473
for (String c : column) {
14701474
if (containRaw) {
14711475
// 由于 HashMap 对 key 做了 hash 处理，所以 get 比 containsValue 更快
@@ -1476,9 +1480,9 @@ public String getColumnString(boolean inSQLJoin) throws Exception {
14761480
}
14771481
}
14781482

1479-
index = c.lastIndexOf(":"); //StringUtil.split返回数组中，子项不会有null
1480-
origin = index < 0 ? c : c.substring(0, index);
1481-
alias = index < 0 ? null : c.substring(index + 1);
1483+
int index = c.lastIndexOf(":"); //StringUtil.split返回数组中，子项不会有null
1484+
String origin = index < 0 ? c : c.substring(0, index);
1485+
String alias = index < 0 ? null : c.substring(index + 1);
14821486

14831487
if (alias != null && StringUtil.isName(alias) == false) {
14841488
throw new IllegalArgumentException("HEAD请求: 字符 " + alias + " 不合法！预编译模式下 @column:value 中 value里面用 , 分割的每一项"
@@ -1499,8 +1503,41 @@ public String getColumnString(boolean inSQLJoin) throws Exception {
14991503
}
15001504
}
15011505
}
1506+
1507+
boolean onlyOne = column != null && column.size() == 1;
1508+
String c0 = onlyOne ? column.get(0) : null;
1509+
1510+
if (onlyOne) {
1511+
int index = c0 == null ? -1 : c0.lastIndexOf(":");
1512+
if (index > 0) {
1513+
c0 = c0.substring(0, index);
1514+
}
1515+
1516+
int start = c0 == null ? -1 : c0.indexOf("(");
1517+
int end = start <= 0 ? -1 : c0.lastIndexOf(")");
1518+
if (start > 0 && end > start) {
1519+
String fun = c0.substring(0, start);
1520+
1521+
// Invalid use of group function SELECT count(max(`id`)) AS count FROM `sys`.`Comment`
1522+
if (SQL_AGGREGATE_FUNCTION_MAP.containsKey(fun)) {
1523+
String group = getGroup(); // TODO 唯一 100% 兼容的可能只有 SELECT count(*) FROM (原语句) AS table
1524+
return StringUtil.isEmpty(group, true) ? "1" : "count(DISTINCT " + group + ")";
1525+
}
1526+
1527+
String[] args = start == end - 1 ? null : StringUtil.split(c0.substring(start + 1, end));
1528+
if (args == null || args.length <= 0) {
1529+
return SQL.count(c0);
1530+
}
15021531

1503-
return SQL.count(column != null && column.size() == 1 && StringUtil.isName(column.get(0)) ? getKey(column.get(0)) : "*");
1532+
List<String> raw = getRaw();
1533+
boolean containRaw = raw != null && raw.contains(KEY_COLUMN);
1534+
1535+
return SQL.count(parseColumn(c0, containRaw));
1536+
}
1537+
}
1538+
1539+
return SQL.count(onlyOne ? getKey(c0) : "*");
1540+
// return SQL.count(onlyOne && StringUtil.isName(column.get(0)) ? getKey(column.get(0)) : "*");
15041541
case POST:
15051542
if (column == null || column.isEmpty()) {
15061543
throw new IllegalArgumentException("POST 请求必须在Table内设置要保存的 key:value ！");
@@ -1997,6 +2034,16 @@ public AbstractSQLConfig setQuery(int query) {
19972034
this.query = query;
19982035
return this;
19992036
}
2037+
@Override
2038+
public Boolean getCompat() {
2039+
return compat;
2040+
}
2041+
@Override
2042+
public AbstractSQLConfig setCompat(Boolean compat) {
2043+
this.compat = compat;
2044+
return this;
2045+
}
2046+
20002047
@Override
20012048
public int getType() {
20022049
return type;
@@ -3753,14 +3800,13 @@ private static String getConditionString(String column, String table, AbstractSQ
37533800

37543801
//根据方法不同，聚合语句不同。GROUP BY 和 HAVING 可以加在 HEAD 上, HAVING 可以加在 PUT, DELETE 上，GET 全加，POST 全都不加
37553802
String aggregation = "";
3756-
if (RequestMethod.isGetMethod(config.getMethod(), true)){
3757-
aggregation = config.getGroupString(true) + config.getHavingString(true) +
3758-
config.getOrderString(true);
3803+
if (RequestMethod.isGetMethod(config.getMethod(), true)) {
3804+
aggregation = config.getGroupString(true) + config.getHavingString(true) + config.getOrderString(true);
37593805
}
3760-
if (RequestMethod.isHeadMethod(config.getMethod(), true)){
3806+
if (RequestMethod.isHeadMethod(config.getMethod(), true)) { // TODO 加参数 isPagenation 判断是 GET 内分页 query:2 查总数，不用加这些条件
37613807
aggregation = config.getGroupString(true) + config.getHavingString(true) ;
37623808
}
3763-
if (config.getMethod() == PUT || config.getMethod() == DELETE){
3809+
if (config.getMethod() == PUT || config.getMethod() == DELETE) {
37643810
aggregation = config.getHavingString(true) ;
37653811
}
37663812

@@ -3825,6 +3871,8 @@ public String getJoinString() throws Exception {
38253871

38263872
// 主表不用别名 String ta;
38273873
for (Join j : joinList) {
3874+
onGetJoinString(j);
3875+
38283876
if (j.isAppJoin()) { // APP JOIN，只是作为一个标记，执行完主表的查询后自动执行副表的查询 User.id IN($commentIdList)
38293877
continue;
38303878
}
@@ -4089,6 +4137,9 @@ protected void onJoinNotRelation(String sql, String quote, Join j, String jt, Li
40894137
protected void onJoinComplextRelation(String sql, String quote, Join j, String jt, List<On> onList, On on) {
40904138
throw new UnsupportedOperationException("JOIN 已禁用 $, ~, {}, <>, >, <, >=, <= 等复杂关联 ！性能很差、需求极少，默认只允许 = 等价关联，如要取消禁用可在后端重写相关方法！");
40914139
}
4140+
4141+
protected void onGetJoinString(Join j) throws UnsupportedOperationException {
4142+
}
40924143
protected void onGetCrossJoinString(Join j) throws UnsupportedOperationException {
40934144
throw new UnsupportedOperationException("已禁用 * CROSS JOIN ！性能很差、需求极少，如要取消禁用可在后端重写相关方法！");
40944145
}

‎APIJSONORM/src/main/java/apijson/orm/SQLConfig.java‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -98,6 +98,9 @@ public interface SQLConfig {
9898
int getQuery();
9999
SQLConfig setQuery(int query);
100100

101+
Boolean getCompat();
102+
SQLConfig setCompat(Boolean compat);
103+
101104
int getPosition();
102105
SQLConfig setPosition(int position);
103106

0 commit comments

Comments
 (0)