Skip to content

Commit 1e7f38e

Browse files
TommyLemonTommyLemon
committed
大幅提升 APP JOIN 一对多时子数组查询和缓存性能
1 parent 77db282 commit 1e7f38e

File tree

3 files changed

+115
-79
lines changed

3 files changed

+115
-79
lines changed

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

Lines changed: 80 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -172,9 +172,7 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknownType) throws
172172
long executedSQLStartTime = System.currentTimeMillis();
173173

174174
boolean isPrepared = config.isPrepared();
175-
176175
final String sql = config.getSQL(false);
177-
178176
config.setPrepared(isPrepared);
179177

180178
if (StringUtil.isEmpty(sql, true)) {
@@ -265,8 +263,9 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknownType) throws
265263
Log.i(TAG, ">>> execute result = getCache('" + sql + "', " + position + ") = " + result);
266264
if (result != null) {
267265
cachedSQLCount ++;
268-
if (getCache(sql,config).size() > 1) {
269-
result.put(KEY_RAW_LIST, getCache(sql,config));
266+
List<JSONObject> cache = getCache(sql, config);
267+
if (cache != null && cache.size() > 1) {
268+
result.put(KEY_RAW_LIST, cache);
270269
}
271270
Log.d(TAG, "\n\n execute result != null >> return result;" + "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n\n");
272271
return result;
@@ -282,7 +281,7 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknownType) throws
282281
}
283282
break;
284283

285-
default://OPTIONS, TRACE等
284+
default: //OPTIONS, TRACE等
286285
Log.e(TAG, "execute sql = " + sql + " ; method = " + config.getMethod() + " >> return null;");
287286
return null;
288287
}
@@ -663,47 +662,79 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
663662
SQLConfig jc = join.getJoinConfig();
664663

665664
List<On> onList = join.getOnList();
666-
int size = onList == null ? 0 : onList.size();
667-
if (size > 0) {
668-
for (int j = size - 1; j >= 0; j--) {
669-
On on = onList.get(j);
670-
String ok = on == null ? null : on.getOriginKey();
671-
if (ok == null) {
672-
throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[" + j + (on == null ? "] = null！" : ".getOriginKey() = null！"));
673-
}
674-
675-
// 取出 "id@": "@/User/userId" 中所有 userId 的值
676-
List<Object> targetValueList = new ArrayList<>();
677-
678-
for (int i = 0; i < resultList.size(); i++) {
679-
JSONObject mainTable = resultList.get(i);
680-
Object targetValue = mainTable == null ? null : mainTable.get(on.getTargetKey());
665+
On on = onList == null || onList.isEmpty() ? null : onList.get(0); // APP JOIN 应该有且只有一个 ON 条件
666+
String originKey = on == null ? null : on.getOriginKey();
667+
if (originKey == null) {
668+
throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[0" + (on == null ? "] = null！" : ".getOriginKey() = null！"));
669+
}
670+
String key = on.getKey();
671+
if (key == null) {
672+
throw new NullPointerException("服务器内部错误，List<Join> 中 Join.onList[0" + (on == null ? "] = null！" : ".getKey() = null！"));
673+
}
681674

682-
if (targetValue != null && targetValueList.contains(targetValue) == false) {
683-
targetValueList.add(targetValue);
684-
}
685-
}
675+
// 取出 "id@": "@/User/userId" 中所有 userId 的值
676+
List<Object> targetValueList = new ArrayList<>();
686677

687-
if (targetValueList.isEmpty() && config.isExplain() == false) {
688-
throw new NotExistException("targetValueList.isEmpty() && config.isExplain() == false");
689-
}
678+
for (int i = 0; i < resultList.size(); i++) {
679+
JSONObject mainTable = resultList.get(i);
680+
Object targetValue = mainTable == null ? null : mainTable.get(on.getTargetKey());
690681

691-
// 替换为 "id{}": [userId1, userId2, userId3...]
692-
jc.putWhere(ok, null, false); // remove originKey
693-
jc.putWhere(on.getKey() + "{}", targetValueList, true); // add originKey{} }
682+
if (targetValue != null && targetValueList.contains(targetValue) == false) {
683+
targetValueList.add(targetValue);
694684
}
695685
}
696686

687+
if (targetValueList.isEmpty() && config.isExplain() == false) {
688+
throw new NotExistException("targetValueList.isEmpty() && config.isExplain() == false");
689+
}
690+
691+
// 替换为 "id{}": [userId1, userId2, userId3...]
692+
jc.putWhere(originKey, null, false); // remove originKey
693+
jc.putWhere(key + "{}", targetValueList, true); // add originKey{} }
694+
697695
jc.setMain(true).setPreparedValueList(new ArrayList<>());
698696

697+
// 放一块逻辑更清晰，也避免解析 * 等不支持或性能开销
698+
// String q = jc.getQuote();
699+
// if (allChildCount > 0 && jc.getCount() <= 0) {
700+
// List<String> column = jc.getColumn();
701+
// if (column == null || column.isEmpty()) {
702+
// column = Arrays.asList("*;row_number()OVER(PARTITION BY " + q + key + q + " ORDER BY " + q + key + q + " ASC):_row_num_");
703+
// }
704+
// else {
705+
// column.add("row_number()OVER(PARTITION BY " + q + key + q + " ORDER BY " + q + key + q + " ASC):_row_num_");
706+
// }
707+
// jc.setColumn(column);
708+
// }
709+
699710
boolean prepared = jc.isPrepared();
700-
final String sql = jc.getSQL(false);
711+
String sql = jc.getSQL(false);
701712
jc.setPrepared(prepared);
702713

703714
if (StringUtil.isEmpty(sql, true)) {
704715
throw new NullPointerException(TAG + ".executeAppJoin StringUtil.isEmpty(sql, true) >> return null;");
705716
}
706717

718+
int childCount = cc.getCount();
719+
int allChildCount = childCount*config.getCount(); // 所有分组子项数量总和
720+
721+
String sql2 = null;
722+
if (childCount > 0 && (childCount != 1 || join.isOne2Many())) { // TODO 判断 MySQL >= 8.0
723+
String q = jc.getQuote();
724+
sql2 = prepared ? jc.getSQL(true) : sql;
725+
726+
String prefix = "SELECT * FROM(";
727+
String rnStr = ", row_number() OVER (PARTITION BY " + q + key + q + ((AbstractSQLConfig) jc).getOrderString(true) + ") _row_num_ FROM ";
728+
String suffix = ") _t WHERE ( (_row_num_ <= " + childCount + ") ) LIMIT " + allChildCount;
729+
730+
sql2 = prefix
731+
// 放一块逻辑更清晰，也避免解析 * 等不支持或性能开销 + sql
732+
+ sql2.replaceFirst(" FROM ", rnStr) // * 居然只能放在 row_number() 前面，放后面就报错 "SELECT ", rnStr)
733+
+ suffix;
734+
735+
sql = prepared ? (prefix + sql.replaceFirst(" FROM ", rnStr) + suffix) : sql2;
736+
}
737+
707738
boolean isExplain = jc.isExplain();
708739
if (isExplain == false) {
709740
generatedSQLCount ++;
@@ -723,13 +754,11 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
723754
executedSQLCount ++;
724755
executedSQLStartTime = System.currentTimeMillis();
725756
}
726-
rs = executeQuery(jc);
757+
rs = executeQuery(jc, sql2);
727758
if (isExplain == false) {
728759
executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;
729760
}
730761

731-
int childCount = cc.getCount();
732-
int allChildCount = childCount*config.getCount(); // 所有分组子项数量总和
733762
int count = 0;
734763

735764
int index = -1;
@@ -760,15 +789,8 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
760789
Log.d(TAG, "\n executeAppJoin while (rs.next()) { resultList.put( " + index + ", result); "
761790
+ "\n >>>>>>>>>>>>>>>>>>>>>>>>>>> \n\n");
762791

763-
if (onList != null) {
764-
for (On on : onList) { // APP JOIN 应该有且只有一个 ON 条件
765-
String ok = on.getOriginKey();
766-
String vk = ok.substring(0, ok.length() - 1);
767-
//TODO 兼容复杂关联
768-
cc.putWhere(on.getKey(), result.get(on.getKey()), true);
769-
}
770-
}
771-
792+
//TODO 兼容复杂关联
793+
cc.putWhere(key, result.get(key), true); // APP JOIN 应该有且只有一个 ON 条件
772794
String cacheSql = cc.getSQL(false);
773795
List<JSONObject> results = childMap.get(cacheSql);
774796

@@ -1010,22 +1032,25 @@ public boolean isJSONType(@NotNull SQLConfig config, ResultSetMetaData rsmd, int
10101032

10111033

10121034

1013-
/**
1014-
* @param config
1015-
* @return
1016-
* @throws Exception
1017-
*/
1018-
@Override
1035+
@Override // 重写是为了返回类型从 Statement 改为 PreparedStatement，避免其它方法出错
10191036
public PreparedStatement getStatement(@NotNull SQLConfig config) throws Exception {
1037+
return getStatement(config, null);
1038+
}
1039+
@Override
1040+
public PreparedStatement getStatement(@NotNull SQLConfig config, String sql) throws Exception {
1041+
if (StringUtil.isEmpty(sql)) {
1042+
sql = config.getSQL(config.isPrepared());
1043+
}
1044+
10201045
PreparedStatement statement; //创建Statement对象
10211046
if (config.getMethod() == RequestMethod.POST && config.getId() == null) { //自增id
1022-
statement = getConnection(config).prepareStatement(config.getSQL(config.isPrepared()), Statement.RETURN_GENERATED_KEYS);
1047+
statement = getConnection(config).prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
10231048
}
10241049
else if (RequestMethod.isGetMethod(config.getMethod(), true)) {
1025-
statement = getConnection(config).prepareStatement(config.getSQL(config.isPrepared()), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1050+
statement = getConnection(config).prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
10261051
}
10271052
else {
1028-
statement = getConnection(config).prepareStatement(config.getSQL(config.isPrepared()));
1053+
statement = getConnection(config).prepareStatement(sql);
10291054
}
10301055

10311056
List<Object> valueList = config.isPrepared() ? config.getPreparedValueList() : null;
@@ -1162,8 +1187,8 @@ public void close() {
11621187
}
11631188

11641189
@Override
1165-
public ResultSet executeQuery(@NotNull SQLConfig config) throws Exception {
1166-
PreparedStatement stt = getStatement(config);
1190+
public ResultSet executeQuery(@NotNull SQLConfig config, String sql) throws Exception {
1191+
PreparedStatement stt = getStatement(config, sql);
11671192
ResultSet rs = stt.executeQuery(); //PreparedStatement 不用传 SQL
11681193
// if (config.isExplain() && (config.isSQLServer() || config.isOracle())) {
11691194
// FIXME 返回的是 boolean 值 rs = stt.getMoreResults(Statement.CLOSE_CURRENT_RESULT);
@@ -1173,7 +1198,7 @@ public ResultSet executeQuery(@NotNull SQLConfig config) throws Exception {
11731198
}
11741199

11751200
@Override
1176-
public int executeUpdate(@NotNull SQLConfig config) throws Exception {
1201+
public int executeUpdate(@NotNull SQLConfig config, String sql) throws Exception {
11771202
PreparedStatement stt = getStatement(config);
11781203
int count = stt.executeUpdate(); // PreparedStatement 不用传 SQL
11791204

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

Lines changed: 23 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -16,15 +16,15 @@
1616
* @author Lemon
1717
*/
1818
public class Join {
19-
20-
private String path;
19+
20+
private String path; // /User/id@
2121

2222
private String joinType; // "@" - APP, "<" - LEFT, ">" - RIGHT, "*" - CROSS, "&" - INNER, "|" - FULL, "!" - OUTER, "^" - SIDE, "(" - ANTI, ")" - FOREIGN
2323
private String table; // User
2424
private String alias; // owner
2525
private int count = 1; // 当app join子表，需要返回子表的行数，默认1行；
2626
private List<On> onList; // ON User.id = Moment.userId AND ...
27-
27+
2828
private JSONObject request; // { "id@":"/Moment/userId" }
2929
private JSONObject outer; // "join": { "</User": { "@order":"id-", "@group":"id", "name~":"a", "tag$":"%a%", "@combine": "name~,tag$" } } 中的 </User 对应值
3030

@@ -72,7 +72,7 @@ public List<On> getOnList() {
7272
public void setOnList(List<On> onList) {
7373
this.onList = onList;
7474
}
75-
75+
7676
public JSONObject getRequest() {
7777
return request;
7878
}
@@ -105,6 +105,12 @@ public void setOuterConfig(SQLConfig outerConfig) {
105105
this.outerConfig = outerConfig;
106106
}
107107

108+
public boolean isOne2One() {
109+
return ! isOne2Many();
110+
}
111+
public boolean isOne2Many() {
112+
return path != null && path.contains("[]"); // TODO 必须保证一对一时不会传包含 [] 的 path
113+
}
108114

109115
public boolean isAppJoin() {
110116
return "@".equals(getJoinType());
@@ -165,7 +171,7 @@ public static boolean isLeftOrRightJoin(Join j) {
165171
return j != null && j.isLeftOrRightJoin();
166172
}
167173

168-
174+
169175

170176
public static class On {
171177

@@ -230,7 +236,7 @@ public String getTargetKey() {
230236
public void setTargetKey(String targetKey) {
231237
this.targetKey = targetKey;
232238
}
233-
239+
234240

235241
public void setKeyAndType(String joinType, String table, @NotNull String originKey) throws Exception { //id, id@, id{}@, contactIdList<>@ ...
236242
if (originKey.endsWith("@")) {
@@ -241,7 +247,7 @@ public void setKeyAndType(String joinType, String table, @NotNull String originK
241247
}
242248

243249
String k;
244-
250+
245251
if (originKey.endsWith("{}")) {
246252
setRelateType("{}");
247253
k = originKey.substring(0, originKey.length() - 2);
@@ -253,26 +259,26 @@ else if (originKey.endsWith("<>")) {
253259
else if (originKey.endsWith("$")) { // key%$:"a" -> key LIKE '%a%'; key?%$:"a" -> key LIKE 'a%'; key_?$:"a" -> key LIKE '_a'; key_%$:"a" -> key LIKE '_a%'
254260
k = originKey.substring(0, originKey.length() - 1);
255261
char c = k.isEmpty() ? 0 : k.charAt(k.length() - 1);
256-
262+
257263
String t = "$";
258264
if (c == '%' || c == '_' || c == '?') {
259265
t = c + t;
260266
k = k.substring(0, k.length() - 1);
261-
267+
262268
char c2 = k.isEmpty() ? 0 : k.charAt(k.length() - 1);
263269
if (c2 == '%' || c2 == '_' || c2 == '?') {
264270
if (c2 == c) {
265271
throw new IllegalArgumentException(originKey + ":value 中字符 " + k + " 不合法！key$:value 中不允许 key 中有连续相同的占位符！");
266272
}
267-
273+
268274
t = c2 + t;
269275
k = k.substring(0, k.length() - 1);
270276
}
271277
else if (c == '?') {
272278
throw new IllegalArgumentException(originKey + ":value 中字符 " + originKey + " 不合法！key$:value 中不允许只有单独的 '?'，必须和 '%', '_' 之一配合使用 ！");
273279
}
274280
}
275-
281+
276282
setRelateType(t);
277283
}
278284
else if (originKey.endsWith("~")) {
@@ -300,27 +306,23 @@ else if (originKey.endsWith("<")) {
300306
setRelateType("");
301307
k = originKey;
302308
}
303-
309+
304310
if (k != null && (k.contains("&") || k.contains("|"))) {
305311
throw new UnsupportedOperationException(joinType + "/.../" + table + "/" + originKey + " 中字符 " + k + " 不合法！与或非逻辑符仅支持 '!' 非逻辑符 ！");
306312
}
307-
313+
308314
//TODO if (c3 == '-') { // 表示 key 和 value 顺序反过来: value LIKE key
309-
315+
310316
Logic l = new Logic(k);
311317
setLogic(l);
312-
318+
313319
if (StringUtil.isName(l.getKey()) == false) {
314320
throw new IllegalArgumentException(joinType + "/.../" + table + "/" + originKey + " 中字符 " + l.getKey() + " 不合法！必须符合字段命名格式！");
315321
}
316-
322+
317323
setKey(l.getKey());
318324
}
319-
320-
321-
}
322-
323-
324325

326+
}
325327

326328
}

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

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -64,14 +64,20 @@ public interface SQLExecutor {
6464
* @return
6565
* @throws SQLException
6666
*/
67-
ResultSet executeQuery(@NotNull SQLConfig config) throws Exception;
67+
default ResultSet executeQuery(@NotNull SQLConfig config) throws Exception {
68+
return executeQuery(config, null);
69+
}
70+
ResultSet executeQuery(@NotNull SQLConfig config, String sql) throws Exception;
6871

6972
/**执行增、删、改
7073
* @param config
7174
* @return
7275
* @throws SQLException
7376
*/
74-
int executeUpdate(@NotNull SQLConfig config) throws Exception;
77+
default int executeUpdate(@NotNull SQLConfig config) throws Exception {
78+
return executeUpdate(config, null);
79+
}
80+
int executeUpdate(@NotNull SQLConfig config, String sql) throws Exception;
7581

7682

7783
/**判断是否为JSON类型
@@ -85,7 +91,10 @@ public interface SQLExecutor {
8591

8692

8793
Connection getConnection(@NotNull SQLConfig config) throws Exception;
88-
Statement getStatement(@NotNull SQLConfig config) throws Exception;
94+
default Statement getStatement(@NotNull SQLConfig config) throws Exception {
95+
return getStatement(config, null);
96+
}
97+
Statement getStatement(@NotNull SQLConfig config, String sql) throws Exception;
8998

9099
int getTransactionIsolation();
91100
void setTransactionIsolation(int transactionIsolation);

0 commit comments

Comments
 (0)