Skip to content

Commit bb4896d

Browse files
TommyLemonTommyLemon
committed
通过减少不必要的 newSQLConfig 及 getSQL 等步骤来大幅提升数组内主表的查询性能
1 parent 893d3b3 commit bb4896d

4 files changed

Lines changed: 189 additions & 115 deletions

File tree

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

Lines changed: 147 additions & 101 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,8 @@ public AbstractObjectParser setParser(AbstractParser<?> parser) {
6060
protected final int type;
6161
protected final List<Join> joinList;
6262
protected final boolean isTable;
63+
protected final boolean isArrayMainTable;
64+
protected final boolean isReuse;
6365
protected final String path;
6466
protected final String name;
6567
protected final String table;
@@ -96,6 +98,8 @@ public AbstractObjectParser(@NotNull JSONObject request, String parentPath, Stri
9698
this.table = entry.getKey();
9799
this.alias = entry.getValue();
98100
this.isTable = apijson.JSONObject.isTableKey(table);
101+
this.isArrayMainTable = isSubquery == false && this.isTable && this.type == SQLConfig.TYPE_ITEM_CHILD_0 && RequestMethod.isGetMethod(method, true);
102+
this.isReuse = isArrayMainTable && arrayConfig != null && arrayConfig.getPosition() > 0;
99103

100104
this.objectCount = 0;
101105
this.arrayCount = 0;
@@ -118,6 +122,15 @@ public AbstractObjectParser(@NotNull JSONObject request, String parentPath, Stri
118122
Log.d(TAG, "AbstractObjectParser isEmpty = " + isEmpty + "; tri = " + tri + "; drop = " + drop);
119123
}
120124

125+
protected int position;
126+
public int getPosition() {
127+
return position;
128+
}
129+
public AbstractObjectParser setPosition(int position) {
130+
this.position = position;
131+
return this;
132+
}
133+
121134

122135
private boolean invalidate = false;
123136
public void invalidate() {
@@ -260,7 +273,7 @@ else if (method == PUT && value instanceof JSONArray
260273
// 非Table内的函数会被滞后在onChildParse后调用！ onFunctionResponse("-");
261274
}
262275

263-
if (isTable) {
276+
if (isReuse == false && isTable) {
264277
if (parser.getGlobleDatabase() != null && sqlRequest.get(JSONRequest.KEY_DATABASE) == null) {
265278
sqlRequest.put(JSONRequest.KEY_DATABASE, parser.getGlobleDatabase());
266279
}
@@ -297,96 +310,96 @@ else if (method == PUT && value instanceof JSONArray
297310
*/
298311
@Override
299312
public boolean onParse(@NotNull String key, @NotNull Object value) throws Exception {
300-
if (key.endsWith("@")) {//StringUtil.isPath((String) value)) {
301-
302-
if (value instanceof JSONObject) { // SQL 子查询对象，JSONObject -> SQLConfig.getSQL
303-
String replaceKey = key.substring(0, key.length() - 1);//key{}@ getRealKey
304-
305-
JSONObject subquery = (JSONObject) value;
306-
String range = subquery.getString(JSONRequest.KEY_SUBQUERY_RANGE);
307-
if (range != null && JSONRequest.SUBQUERY_RANGE_ALL.equals(range) == false && JSONRequest.SUBQUERY_RANGE_ANY.equals(range) == false) {
308-
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ range:value } 中 value 只能为 [" + JSONRequest.SUBQUERY_RANGE_ALL + ", " + JSONRequest.SUBQUERY_RANGE_ANY + "] 中的一个！");
309-
}
310-
313+
if (key.endsWith("@")) { // StringUtil.isPath((String) value)) {
314+
String replaceKey = key.substring(0, key.length() - 1);
315+
316+
// [] 内主表 position > 0 时，用来生成 SQLConfig 的键值对全都忽略，不解析
317+
// if (isReuse == false || replaceKey.endsWith("()") || (replaceKey.startsWith("@") && JSONRequest.TABLE_KEY_LIST.contains(replaceKey) == false)) {
318+
if (value instanceof JSONObject) { // key{}@ getRealKey, SQL 子查询对象，JSONObject -> SQLConfig.getSQL
319+
320+
JSONObject subquery = (JSONObject) value;
321+
String range = subquery.getString(JSONRequest.KEY_SUBQUERY_RANGE);
322+
if (range != null && JSONRequest.SUBQUERY_RANGE_ALL.equals(range) == false && JSONRequest.SUBQUERY_RANGE_ANY.equals(range) == false) {
323+
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ range:value } 中 value 只能为 [" + JSONRequest.SUBQUERY_RANGE_ALL + ", " + JSONRequest.SUBQUERY_RANGE_ANY + "] 中的一个！");
324+
}
311325

312-
JSONArray arr = parser.onArrayParse(subquery, path, key, true);
313326

314-
JSONObject obj = arr == null || arr.isEmpty() ? null : arr.getJSONObject(0);
315-
if (obj == null) {
316-
throw new Exception("服务器内部错误，解析子查询 " + path + "/" + key + ":{ } 为 Subquery 对象失败！");
317-
}
327+
JSONArray arr = parser.onArrayParse(subquery, path, key, true);
318328

319-
String from = subquery.getString(JSONRequest.KEY_SUBQUERY_FROM);
320-
JSONObject arrObj = from == null ? null : obj.getJSONObject(from);
321-
if (arrObj == null) {
322-
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ from:value } 中 value 对应的主表对象 " + from + ":{} 不存在！");
323-
}
324-
//
325-
SQLConfig cfg = (SQLConfig) arrObj.get(AbstractParser.KEY_CONFIG);
326-
if (cfg == null) {
327-
throw new NotExistException(TAG + ".onParse cfg == null");
328-
}
329-
330-
Subquery s = new Subquery();
331-
s.setPath(path);
332-
s.setOriginKey(key);
333-
s.setOriginValue(subquery);
334-
335-
s.setFrom(from);
336-
s.setRange(range);
337-
s.setKey(replaceKey);
338-
s.setConfig(cfg);
329+
JSONObject obj = arr == null || arr.isEmpty() ? null : arr.getJSONObject(0);
330+
if (obj == null) {
331+
throw new Exception("服务器内部错误，解析子查询 " + path + "/" + key + ":{ } 为 Subquery 对象失败！");
332+
}
339333

340-
key = replaceKey;
341-
value = s; //(range == null || range.isEmpty() ? "" : "range") + "(" + cfg.getSQL(false) + ") ";
334+
String from = subquery.getString(JSONRequest.KEY_SUBQUERY_FROM);
335+
JSONObject arrObj = from == null ? null : obj.getJSONObject(from);
336+
if (arrObj == null) {
337+
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ from:value } 中 value 对应的主表对象 " + from + ":{} 不存在！");
338+
}
339+
//
340+
SQLConfig cfg = (SQLConfig) arrObj.get(AbstractParser.KEY_CONFIG);
341+
if (cfg == null) {
342+
throw new NotExistException(TAG + ".onParse cfg == null");
343+
}
342344

343-
parser.putQueryResult(AbstractParser.getAbsPath(path, key), s); //字符串引用保证不了安全性 parser.getSQL(cfg));
344-
}
345-
else if (value instanceof String) { // 引用赋值路径
345+
Subquery s = new Subquery();
346+
s.setPath(path);
347+
s.setOriginKey(key);
348+
s.setOriginValue(subquery);
346349

347-
// System.out.println("getObject key.endsWith(@) >> parseRelation = " + parseRelation);
348-
String replaceKey = key.substring(0, key.length() - 1);//key{}@ getRealKey
349-
String targetPath = AbstractParser.getValuePath(type == TYPE_ITEM
350-
? path : parentPath, new String((String) value));
350+
s.setFrom(from);
351+
s.setRange(range);
352+
s.setKey(replaceKey);
353+
s.setConfig(cfg);
351354

352-
//先尝试获取，尽量保留缺省依赖路径，这样就不需要担心路径改变
353-
Object target = onReferenceParse(targetPath);
354-
Log.i(TAG, "onParse targetPath = " + targetPath + "; target = " + target);
355+
key = replaceKey;
356+
value = s; //(range == null || range.isEmpty() ? "" : "range") + "(" + cfg.getSQL(false) + ") ";
355357

356-
if (target == null) {//String#equals(null)会出错
357-
Log.d(TAG, "onParse target == null >> return true;");
358-
return true;
358+
parser.putQueryResult(AbstractParser.getAbsPath(path, key), s); //字符串引用保证不了安全性 parser.getSQL(cfg));
359359
}
360-
if (target instanceof Map) { //target可能是从requestObject里取出的 {}
361-
if (isTable || targetPath.endsWith("[]/" + JSONResponse.KEY_INFO) == false) {
362-
Log.d(TAG, "onParse target instanceof Map >> return false;");
363-
return false; //FIXME 这个判断现在来看是否还有必要？为啥不允许为 JSONObject ？以前可能因为防止二次遍历再解析，现在只有一次遍历
360+
else if (value instanceof String) { // //key{}@ getRealKey, 引用赋值路径
361+
// System.out.println("getObject key.endsWith(@) >> parseRelation = " + parseRelation);
362+
String targetPath = AbstractParser.getValuePath(type == TYPE_ITEM
363+
? path : parentPath, new String((String) value));
364+
365+
//先尝试获取，尽量保留缺省依赖路径，这样就不需要担心路径改变
366+
Object target = onReferenceParse(targetPath);
367+
Log.i(TAG, "onParse targetPath = " + targetPath + "; target = " + target);
368+
369+
if (target == null) {//String#equals(null)会出错
370+
Log.d(TAG, "onParse target == null >> return true;");
371+
return true;
364372
}
365-
}
366-
if (targetPath.equals(target)) {//必须valuePath和保证getValueByPath传进去的一致！
367-
Log.d(TAG, "onParse targetPath.equals(target) >>");
368-
369-
//非查询关键词 @key 不影响查询，直接跳过
370-
if (isTable && (key.startsWith("@") == false || JSONRequest.TABLE_KEY_LIST.contains(key))) {
371-
Log.e(TAG, "onParse isTable && (key.startsWith(@) == false"
372-
+ " || JSONRequest.TABLE_KEY_LIST.contains(key)) >> return null;");
373-
return false;//获取不到就不用再做无效的query了。不考虑 Table:{Table:{}}嵌套
374-
} else {
375-
Log.d(TAG, "onParse isTable(table) == false >> return true;");
376-
return true;//舍去，对Table无影响
373+
if (target instanceof Map) { //target可能是从requestObject里取出的 {}
374+
if (isTable || targetPath.endsWith("[]/" + JSONResponse.KEY_INFO) == false) {
375+
Log.d(TAG, "onParse target instanceof Map >> return false;");
376+
return false; //FIXME 这个判断现在来看是否还有必要？为啥不允许为 JSONObject ？以前可能因为防止二次遍历再解析，现在只有一次遍历
377+
}
377378
}
378-
}
379-
380-
//直接替换原来的key@:path为key:target
381-
Log.i(TAG, "onParse >> key = replaceKey; value = target;");
382-
key = replaceKey;
383-
value = target;
384-
Log.d(TAG, "onParse key = " + key + "; value = " + value);
385-
}
386-
else {
387-
throw new IllegalArgumentException(path + "/" + key + ":value 中 value 必须为 依赖路径String 或 SQL子查询JSONObject ！");
388-
}
379+
if (targetPath.equals(target)) {//必须valuePath和保证getValueByPath传进去的一致！
380+
Log.d(TAG, "onParse targetPath.equals(target) >>");
381+
382+
//非查询关键词 @key 不影响查询，直接跳过
383+
if (isTable && (key.startsWith("@") == false || JSONRequest.TABLE_KEY_LIST.contains(key))) {
384+
Log.e(TAG, "onParse isTable && (key.startsWith(@) == false"
385+
+ " || JSONRequest.TABLE_KEY_LIST.contains(key)) >> return null;");
386+
return false;//获取不到就不用再做无效的query了。不考虑 Table:{Table:{}}嵌套
387+
} else {
388+
Log.d(TAG, "onParse isTable(table) == false >> return true;");
389+
return true;//舍去，对Table无影响
390+
}
391+
}
389392

393+
//直接替换原来的key@:path为key:target
394+
Log.i(TAG, "onParse >> key = replaceKey; value = target;");
395+
key = replaceKey;
396+
value = target;
397+
Log.d(TAG, "onParse key = " + key + "; value = " + value);
398+
}
399+
else {
400+
throw new IllegalArgumentException(path + "/" + key + ":value 中 value 必须为 依赖路径String 或 SQL子查询JSONObject ！");
401+
}
402+
// }
390403
}
391404

392405
if (key.endsWith("()")) {
@@ -426,7 +439,9 @@ else if (isTable && key.startsWith("@") && JSONRequest.TABLE_KEY_LIST.contains(k
426439
customMap.put(key, value);
427440
}
428441
else {
442+
// 导致副表从 1 开始都不查了 if (isReuse == false) {
429443
sqlRequest.put(key, value);
444+
// }
430445
}
431446

432447
return true;
@@ -640,8 +655,8 @@ public AbstractObjectParser setSQLConfig() throws Exception {
640655

641656
@Override
642657
public AbstractObjectParser setSQLConfig(int count, int page, int position) throws Exception {
643-
if (isTable == false) {
644-
return this;
658+
if (isTable == false || isReuse) {
659+
return setPosition(position);
645660
}
646661

647662
if (sqlConfig == null) {
@@ -676,28 +691,30 @@ public AbstractObjectParser executeSQL() throws Exception {
676691
//执行SQL操作数据库
677692
if (isTable == false) {//提高性能
678693
sqlReponse = new JSONObject(sqlRequest);
679-
} else {
680-
try {
681-
sqlReponse = onSQLExecute();
682-
}
683-
catch (NotExistException e) {
684-
// Log.e(TAG, "getObject try { response = getSQLObject(config2); } catch (Exception e) {");
685-
// if (e instanceof NotExistException) {//非严重异常，有时候只是数据不存在
686-
// // e.printStackTrace();
687-
sqlReponse = null;//内部吃掉异常，put到最外层
688-
// requestObject.put(JSONResponse.KEY_MSG
689-
// , StringUtil.getString(requestObject.get(JSONResponse.KEY_MSG)
690-
// + "; query " + path + " cath NotExistException:"
691-
// + newErrorResult(e).getString(JSONResponse.KEY_MSG)));
692-
// } else {
693-
// throw e;
694-
// }
694+
}
695+
else {
696+
try {
697+
sqlReponse = onSQLExecute();
698+
699+
}
700+
catch (NotExistException e) {
701+
// Log.e(TAG, "getObject try { response = getSQLObject(config2); } catch (Exception e) {");
702+
// if (e instanceof NotExistException) {//非严重异常，有时候只是数据不存在
703+
// // e.printStackTrace();
704+
sqlReponse = null;//内部吃掉异常，put到最外层
705+
// requestObject.put(JSONResponse.KEY_MSG
706+
// , StringUtil.getString(requestObject.get(JSONResponse.KEY_MSG)
707+
// + "; query " + path + " cath NotExistException:"
708+
// + newErrorResult(e).getString(JSONResponse.KEY_MSG)));
709+
// } else {
710+
// throw e;
711+
// }
712+
}
695713
}
696714

697715
if (drop) {//丢弃Table，只为了向下提供条件
698716
sqlReponse = null;
699717
}
700-
}
701718

702719
return this;
703720
}
@@ -799,10 +816,39 @@ public Object onReferenceParse(@NotNull String path) {
799816

800817
@Override
801818
public JSONObject onSQLExecute() throws Exception {
802-
JSONObject result = parser.executeSQL(sqlConfig, isSubquery);
803-
if (isSubquery == false && result != null) {
804-
parser.putQueryResult(path, result);//解决获取关联数据时requestObject里不存在需要的关联数据
819+
int position = getPosition();
820+
821+
JSONObject result;
822+
if (isArrayMainTable && position > 0) { // 数组主表使用专门的缓存数据
823+
result = parser.getArrayMainCacheItem(parentPath.substring(0, parentPath.lastIndexOf("[]") + 2), position);
805824
}
825+
else {
826+
result = parser.executeSQL(sqlConfig, isSubquery);
827+
828+
if (isArrayMainTable && position == 0 && result != null) { // 提取并缓存数组主表的列表数据
829+
@SuppressWarnings("unchecked")
830+
List<JSONObject> list = (List<JSONObject>) result.remove(SQLExecutor.KEY_RAW_LIST);
831+
if (list != null) {
832+
String arrayPath = parentPath.substring(0, parentPath.lastIndexOf("[]") + 2);
833+
834+
for (int i = 1; i < list.size(); i++) { // 从 1 开始，0 已经处理过
835+
JSONObject obj = parser.parseCorrectResponse(table, list.get(i));
836+
list.set(i, obj);
837+
838+
if (obj != null) {
839+
parser.putQueryResult(arrayPath + "/" + i + "/" + table, obj); //解决获取关联数据时requestObject里不存在需要的关联数据
840+
}
841+
}
842+
843+
parser.putArrayMainCache(arrayPath, list);
844+
}
845+
}
846+
847+
if (isSubquery == false && result != null) {
848+
parser.putQueryResult(path, result);//解决获取关联数据时requestObject里不存在需要的关联数据
849+
}
850+
}
851+
806852
return result;
807853
}
808854

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

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,6 @@
1414
import java.sql.Savepoint;
1515
import java.util.ArrayList;
1616
import java.util.Arrays;
17-
import java.util.Comparator;
1817
import java.util.HashMap;
1918
import java.util.LinkedHashSet;
2019
import java.util.List;
@@ -792,8 +791,8 @@ public JSONObject onObjectParse(final JSONObject request
792791

793792

794793
JSONObject response = null;
795-
if (op != null) {//TODO SQL查询结果为空时，functionMap和customMap还有没有意义？
796-
if (arrayConfig == null) {//Common
794+
if (op != null) {//SQL查询结果为空时，functionMap和customMap没有意义
795+
if (arrayConfig == null) { //Common
797796
response = op.setSQLConfig().executeSQL().response();
798797
}
799798
else {//Array Item Child
@@ -1431,6 +1430,20 @@ public static JSONObject getJSONObject(JSONObject object, String key) {
14311430

14321431

14331432
public static final String KEY_CONFIG = "config";
1433+
1434+
protected Map<String, List<JSONObject>> arrayMainCacheMap = new HashMap<>();
1435+
public void putArrayMainCache(String arrayPath, List<JSONObject> mainTableDataList) {
1436+
arrayMainCacheMap.put(arrayPath, mainTableDataList);
1437+
}
1438+
public List<JSONObject> getArrayMainCache(String arrayPath) {
1439+
return arrayMainCacheMap.get(arrayPath);
1440+
}
1441+
public JSONObject getArrayMainCacheItem(String arrayPath, int position) {
1442+
List<JSONObject> list = getArrayMainCache(arrayPath);
1443+
return list == null || position >= list.size() ? null : list.get(position);
1444+
}
1445+
1446+
14341447

14351448
/**执行 SQL 并返回 JSONObject
14361449
* @param config
@@ -1451,8 +1464,9 @@ public JSONObject executeSQL(SQLConfig config, boolean isSubquery) throws Except
14511464
}
14521465

14531466
try {
1454-
boolean explain = config.isExplain();
14551467
JSONObject result;
1468+
1469+
boolean explain = config.isExplain();
14561470
if (explain) { //如果先执行 explain，则 execute 会死循环，所以只能先执行非 explain
14571471
config.setExplain(false); //对下面 config.getSQL(false); 生效
14581472
JSONObject res = getSQLExecutor().execute(config, false);

0 commit comments

Comments
 (0)