解决 JOIN 副表有 引用赋值 外的条件时因为缓存 SQL WHERE 中条件顺序不一致导致多余查询

TommyLemon · TommyLemon · commit 6f8001387833 · 2021-04-17T17:56:27.000+08:00
diff --git a/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java b/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java
@@ -1174,7 +1174,19 @@ else if (join != null){
 				throw new IllegalArgumentException("/" + path + ":'/targetTable/targetKey' 中路径对应的对象 '" + targetTableKey + "':{} 不存在或值为 null ！必须是 {} 这种 JSONObject 格式！");
 			}
 
-			tableObj.put(key, tableObj.remove(key)); //保证和SQLExcecutor缓存的Config里where顺序一致，生成的SQL也就一致
+			// 保证和 SQLExcecutor 缓存的 Config 里 where 顺序一致，生成的 SQL 也就一致 <<<<<<<<<
+			// AbstractSQLConfig.newSQLConfig 中强制把 id, id{}, userId, userId{} 放到了最前面		tableObj.put(key, tableObj.remove(key));
+			
+			if (tableObj.size() > 1) {  // 把 key 强制放最前，AbstractSQLExcecutor 中 config.putWhere 也是放尽可能最前
+				JSONObject newTableObj = new JSONObject(tableObj.size(), true);
+				newTableObj.put(key, tableObj.remove(key));
+				newTableObj.putAll(tableObj);
+				
+				tableObj = newTableObj;
+				request.put(tableKey, tableObj);
+			}
+			// 保证和 SQLExcecutor 缓存的 Config 里 where 顺序一致，生成的 SQL 也就一致 >>>>>>>>>
+			
 
 			Join j = new Join();
 			j.setPath(path);
diff --git a/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java b/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java
@@ -1570,7 +1570,9 @@ public AbstractSQLConfig putWhere(String key, Object value, boolean prior) {
 			combine = getCombine();
 			List<String> andList = combine.get("&");
 			if (value == null) {
-				andList.remove(key);
+				if (andList != null) {
+					andList.remove(key);
+				}
 			}
 			else if (andList == null || andList.contains(key) == false) {
 				int i = 0;
diff --git a/APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java b/APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java
@@ -408,8 +408,8 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
 
 
 				//替换为 "id{}": [userId1, userId2, userId3...]
-				jc.putWhere(j.getOriginKey(), null, false);
-				jc.putWhere(j.getKey() + "{}", targetValueList, false);
+				jc.putWhere(j.getOriginKey(), null, false);  // remove orginKey
+				jc.putWhere(j.getKey() + "{}", targetValueList, true);  // add orginKey{}
 
 				jc.setMain(true).setPreparedValueList(new ArrayList<>());
 
@@ -456,7 +456,7 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
 								+ "\n >>>>>>>>>>>>>>>>>>>>>>>>>>> \n\n");
 
 						//缓存到 childMap
-						cc.putWhere(j.getKey(), result.get(j.getKey()), false);
+						cc.putWhere(j.getKey(), result.get(j.getKey()), true);
 						cacheSql = cc.getSQL(false);
 						childMap.put(cacheSql, result);
 
@@ -531,7 +531,7 @@ protected JSONObject onPutColumn(@NotNull SQLConfig config, @NotNull ResultSet r
 
 					if (childConfig != null && childTable.equalsIgnoreCase(childConfig.getSQLTable())) {
 
-						childConfig.putWhere(j.getKey(), table.get(j.getTargetKey()), false);
+						childConfig.putWhere(j.getKey(), table.get(j.getTargetKey()), true);
 						childSql = childConfig.getSQL(false);
 
 						if (StringUtil.isEmpty(childSql, true)) {
