Skip to content

Commit cbffc9b

Browse files
TommyLemonTommyLemon
committed
新增子查询，初步完成 key{}@:{} 对应的 IN 类型子查询
1 parent 1774237 commit cbffc9b

File tree

11 files changed

+231
-82
lines changed

11 files changed

+231
-82
lines changed

‎APIJSON-Java-Server/APIJSONDemo/src/main/java/apijson/demo/server/DemoObjectParser.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -45,8 +45,8 @@ public abstract class DemoObjectParser extends AbstractObjectParser {
4545
* @param name
4646
* @throws Exception
4747
*/
48-
public DemoObjectParser(HttpSession session, @NotNull JSONObject request, String parentPath, String name, SQLConfig arrayConfig) throws Exception {
49-
super(request, parentPath, name, arrayConfig);
48+
public DemoObjectParser(HttpSession session, @NotNull JSONObject request, String parentPath, String name, SQLConfig arrayConfig, boolean isSubquery) throws Exception {
49+
super(request, parentPath, name, arrayConfig, isSubquery);
5050
}
5151

5252
@Override

‎APIJSON-Java-Server/APIJSONDemo/src/main/java/apijson/demo/server/DemoParser.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -84,9 +84,9 @@ public Object onFunctionParse(JSONObject json, String fun) throws Exception {
8484

8585

8686
@Override
87-
public DemoObjectParser createObjectParser(JSONObject request, String parentPath, String name, SQLConfig arrayConfig) throws Exception {
87+
public DemoObjectParser createObjectParser(JSONObject request, String parentPath, String name, SQLConfig arrayConfig, boolean isSubquery) throws Exception {
8888

89-
return new DemoObjectParser(session, request, parentPath, name, arrayConfig) {
89+
return new DemoObjectParser(session, request, parentPath, name, arrayConfig, isSubquery) {
9090

9191
//TODO 删除，onPUTArrayParse改用MySQL函数JSON_ADD, JSON_REMOVE等
9292
@Override

‎APIJSON-Java-Server/APIJSONDemo/src/main/java/apijson/demo/server/DemoSQLConfig.java‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@
2525
import zuo.biao.apijson.server.AbstractSQLConfig;
2626
import zuo.biao.apijson.server.Join;
2727
import zuo.biao.apijson.server.SQLConfig;
28+
import zuo.biao.apijson.server.Subquery;
2829

2930

3031
/**SQL配置
@@ -59,7 +60,13 @@ public String getSchema() {
5960
return StringUtil.isEmpty(s, true) ? "sys" : s; //TODO 改成你自己的
6061
}
6162

62-
63+
@Override
64+
public String getSubqueryString(Subquery subquery) throws Exception {
65+
//TODO 用 SQLExecutor 的 preparedStatement 返回的
66+
// String range = subquery.getRange();
67+
// return (range == null || range.isEmpty() ? "" : range) + "(" + subquery.getConfig().getSQL(false) + ") ";
68+
throw new UnsupportedOperationException("未解决 SQL 注入，暂不支持");
69+
}
6370

6471
public DemoSQLConfig() {
6572
this(RequestMethod.GET);
@@ -75,6 +82,7 @@ public DemoSQLConfig(RequestMethod method, int count, int page) {
7582
}
7683

7784

85+
7886
/**获取SQL配置
7987
* @param table
8088
* @param request

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

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -130,7 +130,6 @@ public JSONObject setUserIdIn(List<Object> list) {
130130
public static final String KEY_SCHEMA = "@schema"; //数据库，Table在非默认schema内时需要声明
131131
public static final String KEY_COLUMN = "@column"; //查询的Table字段或SQL函数
132132
public static final String KEY_FROM = "@from"; //FROM语句
133-
public static final String KEY_SQL = "@sql"; //SQL语句
134133
public static final String KEY_COMBINE = "@combine"; //条件组合，每个条件key前面可以放&,|,!逻辑关系 "id!{},&sex,!name&$"
135134
public static final String KEY_GROUP = "@group"; //分组方式
136135
public static final String KEY_HAVING = "@having"; //聚合函数条件，一般和@group一起用
@@ -144,7 +143,6 @@ public JSONObject setUserIdIn(List<Object> list) {
144143
TABLE_KEY_LIST.add(KEY_SCHEMA);
145144
TABLE_KEY_LIST.add(KEY_COLUMN);
146145
TABLE_KEY_LIST.add(KEY_FROM);
147-
TABLE_KEY_LIST.add(KEY_SQL);
148146
TABLE_KEY_LIST.add(KEY_COMBINE);
149147
TABLE_KEY_LIST.add(KEY_GROUP);
150148
TABLE_KEY_LIST.add(KEY_HAVING);

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

Lines changed: 88 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,6 @@
1515
package zuo.biao.apijson.server;
1616

1717
import static zuo.biao.apijson.JSONObject.KEY_COMBINE;
18-
import static zuo.biao.apijson.JSONObject.KEY_SQL;
1918
import static zuo.biao.apijson.JSONObject.KEY_CORRECT;
2019
import static zuo.biao.apijson.JSONObject.KEY_DROP;
2120
import static zuo.biao.apijson.JSONObject.KEY_TRY;
@@ -63,6 +62,7 @@ public AbstractObjectParser setParser(Parser<?> parser) {
6362
protected JSONObject request;//不用final是为了recycle
6463
protected String parentPath;//不用final是为了recycle
6564
protected SQLConfig arrayConfig;//不用final是为了recycle
65+
protected boolean isSubquery;
6666

6767
protected final int type;
6868
protected final List<Join> joinList;
@@ -77,21 +77,21 @@ public AbstractObjectParser setParser(Parser<?> parser) {
7777
*/
7878
protected final boolean drop;
7979
protected final JSONObject correct;
80-
protected final JSONObject sql;
8180

8281
/**for single object
8382
* @param parentPath
8483
* @param request
8584
* @param name
8685
* @throws Exception
8786
*/
88-
public AbstractObjectParser(@NotNull JSONObject request, String parentPath, String name, SQLConfig arrayConfig) throws Exception {
87+
public AbstractObjectParser(@NotNull JSONObject request, String parentPath, String name, SQLConfig arrayConfig, boolean isSubquery) throws Exception {
8988
if (request == null) {
9089
throw new IllegalArgumentException(TAG + ".ObjectParser request == null!!!");
9190
}
9291
this.request = request;
9392
this.parentPath = parentPath;
9493
this.arrayConfig = arrayConfig;
94+
this.isSubquery = isSubquery;
9595

9696
this.type = arrayConfig == null ? 0 : arrayConfig.getType();
9797
this.joinList = arrayConfig == null ? null : arrayConfig.getJoinList();
@@ -104,18 +104,15 @@ public AbstractObjectParser(@NotNull JSONObject request, String parentPath, Stri
104104
this.tri = false;
105105
this.drop = false;
106106
this.correct = null;
107-
this.sql = null;
108107
}
109108
else {
110109
this.tri = request.getBooleanValue(KEY_TRY);
111110
this.drop = request.getBooleanValue(KEY_DROP);
112111
this.correct = request.getJSONObject(KEY_CORRECT);
113-
this.sql = request.getJSONObject(KEY_SQL);
114112

115113
request.remove(KEY_TRY);
116114
request.remove(KEY_DROP);
117115
request.remove(KEY_CORRECT);
118-
request.remove(KEY_SQL);
119116

120117
try {
121118
parseCorrect();
@@ -281,7 +278,7 @@ public AbstractObjectParser parse() throws Exception {
281278
key = entry.getKey();
282279

283280
try {
284-
if (value instanceof JSONObject && key.startsWith("@") == false) {//JSONObject，往下一级提取
281+
if (value instanceof JSONObject && key.startsWith("@") == false && key.endsWith("@") == false) {//JSONObject，往下一级提取
285282
if (childMap != null) {//添加到childMap，最后再解析
286283
childMap.put(key, (JSONObject)value);
287284
}
@@ -331,51 +328,91 @@ else if (method == PUT && value instanceof JSONArray
331328
@Override
332329
public boolean onParse(@NotNull String key, @NotNull Object value) throws Exception {
333330
if (key.endsWith("@")) {//StringUtil.isPath((String) value)) {
334-
if (value instanceof String == false) {
335-
throw new IllegalArgumentException("\"key@\": 后面必须为依赖路径String！");
336-
}
337-
// System.out.println("getObject key.endsWith(@) >> parseRelation = " + parseRelation);
338-
String replaceKey = key.substring(0, key.length() - 1);//key{}@ getRealKey
339-
String targetPath = AbstractParser.getValuePath(type == TYPE_ITEM
340-
? path : parentPath, new String((String) value));
341-
342-
//先尝试获取，尽量保留缺省依赖路径，这样就不需要担心路径改变
343-
Object target = onReferenceParse(targetPath);
344-
Log.i(TAG, "onParse targetPath = " + targetPath + "; target = " + target);
345-
346-
if (target == null) {//String#equals(null)会出错
347-
Log.d(TAG, "onParse target == null >> continue;");
348-
return true;
349-
}
350-
if (target instanceof Map) { //target可能是从requestObject里取出的 {}
351-
Log.d(TAG, "onParse target instanceof Map >> continue;");
352-
return false;
331+
332+
if (value instanceof JSONObject) { // SQL 子查询对象，JSONObject -> SQLConfig.getSQL
333+
String replaceKey = key.substring(0, key.length() - 1);//key{}@ getRealKey
334+
335+
JSONObject subquery = (JSONObject) value;
336+
String range = subquery.getString("range");
337+
if (range != null && "any".equals(range) == false && "all".equals(range) == false) {
338+
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ range:value } 中 value 只能为 [any, all] 中的一个！");
339+
}
340+
341+
342+
JSONArray arr = parser.onArrayParse(subquery, AbstractParser.getAbsPath(path, replaceKey), replaceKey, true);
343+
344+
JSONObject obj = arr == null || arr.isEmpty() ? null : arr.getJSONObject(0);
345+
346+
String from = subquery.getString("from");
347+
JSONObject arrObj = obj.getJSONObject(from);
348+
if (arrObj == null) {
349+
throw new IllegalArgumentException("子查询 " + path + "/" + key + ":{ from:value } 中 value 对应的数组对象不存在！");
350+
}
351+
//
352+
SQLConfig cfg = arrObj == null ? null : (SQLConfig) arrObj.get(AbstractParser.KEY_CONFIG);
353+
354+
Subquery s = new Subquery();
355+
s.setPath(parentPath);
356+
s.setOriginKey(key);
357+
s.setOriginValue(subquery);
358+
359+
s.setRange(range);
360+
s.setKey(replaceKey);
361+
s.setConfig(cfg);
362+
363+
parser.putQueryResult(AbstractParser.getAbsPath(path, key), s); //字符串引用保证不了安全性 parser.getSQL(cfg));
364+
365+
key = replaceKey;
366+
value = s; //(range == null || range.isEmpty() ? "" : "range") + "(" + cfg.getSQL(false) + ") ";
353367
}
354-
if (targetPath.equals(target)) {//必须valuePath和保证getValueByPath传进去的一致！
355-
Log.d(TAG, "onParse targetPath.equals(target) >>");
356-
357-
//非查询关键词 @key 不影响查询，直接跳过
358-
if (isTable && (key.startsWith("@") == false || JSONRequest.TABLE_KEY_LIST.contains(key))) {
359-
Log.e(TAG, "onParse isTable && (key.startsWith(@) == false"
360-
+ " || JSONRequest.TABLE_KEY_LIST.contains(key)) >> return null;");
361-
return false;//获取不到就不用再做无效的query了。不考虑 Table:{Table:{}}嵌套
362-
} else {
363-
Log.d(TAG, "onParse isTable(table) == false >> continue;");
364-
return true;//舍去，对Table无影响
368+
else if (value instanceof String) { // 引用赋值路径
369+
370+
// System.out.println("getObject key.endsWith(@) >> parseRelation = " + parseRelation);
371+
String replaceKey = key.substring(0, key.length() - 1);//key{}@ getRealKey
372+
String targetPath = AbstractParser.getValuePath(type == TYPE_ITEM
373+
? path : parentPath, new String((String) value));
374+
375+
//先尝试获取，尽量保留缺省依赖路径，这样就不需要担心路径改变
376+
Object target = onReferenceParse(targetPath);
377+
Log.i(TAG, "onParse targetPath = " + targetPath + "; target = " + target);
378+
379+
if (target == null) {//String#equals(null)会出错
380+
Log.d(TAG, "onParse target == null >> continue;");
381+
return true;
365382
}
366-
}
383+
if (target instanceof Map) { //target可能是从requestObject里取出的 {}
384+
Log.d(TAG, "onParse target instanceof Map >> continue;");
385+
return false;
386+
}
387+
if (targetPath.equals(target)) {//必须valuePath和保证getValueByPath传进去的一致！
388+
Log.d(TAG, "onParse targetPath.equals(target) >>");
389+
390+
//非查询关键词 @key 不影响查询，直接跳过
391+
if (isTable && (key.startsWith("@") == false || JSONRequest.TABLE_KEY_LIST.contains(key))) {
392+
Log.e(TAG, "onParse isTable && (key.startsWith(@) == false"
393+
+ " || JSONRequest.TABLE_KEY_LIST.contains(key)) >> return null;");
394+
return false;//获取不到就不用再做无效的query了。不考虑 Table:{Table:{}}嵌套
395+
} else {
396+
Log.d(TAG, "onParse isTable(table) == false >> continue;");
397+
return true;//舍去，对Table无影响
398+
}
399+
}
367400

401+
//直接替换原来的key@:path为key:target
402+
Log.i(TAG, "onParse >> key = replaceKey; value = target;");
403+
key = replaceKey;
404+
value = target;
405+
Log.d(TAG, "onParse key = " + key + "; value = " + value);
406+
}
407+
else {
408+
throw new IllegalArgumentException(path + "/" + key + ":value 中 value 必须为 依赖路径String 或 SQL子查询JSONObject ！");
409+
}
368410

369-
//直接替换原来的key@:path为key:target
370-
Log.i(TAG, "onParse >> key = replaceKey; value = target;");
371-
key = replaceKey;
372-
value = target;
373-
Log.d(TAG, "onParse key = " + key + "; value = " + value);
374411
}
375412

376413
if (key.endsWith("()")) {
377414
if (value instanceof String == false) {
378-
throw new IllegalArgumentException(path + "/" + key + ":function() 后面必须为函数String！");
415+
throw new IllegalArgumentException(path + "/" + key + ":value 中 value 必须为函数String！");
379416
}
380417

381418
String k = key.substring(0, key.length() - 2);
@@ -439,7 +476,7 @@ public JSON onChildParse(int index, String key, JSONObject value) throws Excepti
439476
+ "数组 []:{} 中第一个 key:{} 必须是主表 TableKey:{} ！不能为 arrayKey[]:{} ！");
440477
}
441478

442-
child = parser.onArrayParse(value, path, key);
479+
child = parser.onArrayParse(value, path, key, isSubquery);
443480
isEmpty = child == null || ((JSONArray) child).isEmpty();
444481
}
445482
else {//APIJSON Object
@@ -448,7 +485,7 @@ public JSON onChildParse(int index, String key, JSONObject value) throws Excepti
448485
+ "数组 []:{} 中每个 key:{} 都必须是表 TableKey:{} 或 数组 arrayKey[]:{} ！");
449486
}
450487

451-
child = parser.onObjectParse(value, path, key, isMain ? arrayConfig.setType(SQLConfig.TYPE_ITEM_CHILD_0) : null);
488+
child = parser.onObjectParse(value, path, key, isMain ? arrayConfig.setType(SQLConfig.TYPE_ITEM_CHILD_0) : null, isSubquery);
452489

453490
isEmpty = child == null || ((JSONObject) child).isEmpty();
454491
if (isFirst && isEmpty) {
@@ -543,14 +580,14 @@ public AbstractObjectParser setSQLConfig(int count, int page, int position) thro
543580
if (isTable == false) {
544581
return this;
545582
}
546-
583+
547584
if (sqlConfig == null) {
548585
sqlConfig = newSQLConfig();
549586
}
550587
sqlConfig.setCount(count).setPage(page).setPosition(position);
551-
588+
552589
parser.onVerifyRole(sqlConfig);
553-
590+
554591
return this;
555592
}
556593

@@ -683,8 +720,8 @@ public Object onReferenceParse(@NotNull String path) {
683720

684721
@Override
685722
public JSONObject onSQLExecute() throws Exception {
686-
JSONObject result = parser.executeSQL(sqlConfig);
687-
if (result != null) {
723+
JSONObject result = parser.executeSQL(sqlConfig, isSubquery);
724+
if (isSubquery == false && result != null) {
688725
parser.putQueryResult(path, result);//解决获取关联数据时requestObject里不存在需要的关联数据
689726
}
690727
return result;
@@ -713,9 +750,6 @@ public void recycle() {
713750
if (correct != null) {
714751
request.put(KEY_CORRECT, correct);
715752
}
716-
if (sql != null) {
717-
request.put(KEY_SQL, sql);
718-
}
719753

720754

721755
corrected = null;

0 commit comments

Comments
 (0)