Skip to content

Commit 89fa184

Browse files
TommyLemonTommyLemon
committed
LEFT/RIGHT JOIN 新增支持在外层WHERE/GROUP BY/HAVING 加条件/排序(待解决引入 bug) #824 
1 parent 4fe61af commit 89fa184

4 files changed

Lines changed: 105 additions & 39 deletions

File tree

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

Lines changed: 33 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1563,20 +1563,34 @@ else if (join != null){
15631563
throw new UnsupportedDataTypeException(TAG + ".onJoinParse join 只能是 String 或 Map<String, Object> 类型！");
15641564
}
15651565

1566-
List<Entry<String, Object>> slashKeys = new ArrayList<>();
1567-
List<Entry<String, Object>> nonSlashKeys = new ArrayList<>();
15681566
Set<Entry<String, Object>> entries = joinMap == null ? null : joinMap.entrySet();
15691567

15701568
if (entries == null || entries.isEmpty()) {
15711569
Log.e(TAG, "onJoinParse set == null || set.isEmpty() >> return null;");
15721570
return null;
15731571
}
1574-
for (Entry<String, Object> e : entries) {
1572+
1573+
M mainOuter = JSON.createJSONObject();
1574+
Join<T, M, L> mainJoin = new Join<>();
1575+
mainJoin.setRequest(mainOuter);
1576+
1577+
List<Entry<String, Object>> slashKeys = new ArrayList<>();
1578+
List<Entry<String, Object>> nonSlashKeys = new ArrayList<>();
1579+
// https://github.com/Tencent/APIJSON/issues/824#issuecomment-3038316490
1580+
for (Entry<String, Object> e : entries) { // https://github.com/Tencent/APIJSON/pull/829
15751581
String path = e.getKey();
1576-
if (path != null && path.indexOf("/") > 0) {
1577-
slashKeys.add(e); // 以 / 开头的 key，例如 </Table/key@
1582+
Object value = e.getValue();
1583+
if (value instanceof Map<?, ?>) {
1584+
if (path != null && path.indexOf("/") > 0) {
1585+
slashKeys.add(e); // 以 / 开头的 key，例如 </Table/key@
1586+
} else if (isTableKey(path)) {
1587+
nonSlashKeys.add(e); // 普通 key，例如 Table: {}
1588+
} else {
1589+
throw new IllegalArgumentException(apijson.JSONRequest.KEY_JOIN + ":value 中value不合法！"
1590+
+ "必须为 &/Table0/key0,</Table1/key1,... 或 { '&/Table0/key0':{}, '</Table1/key1':{},... } 这种形式！");
1591+
}
15781592
} else {
1579-
nonSlashKeys.add(e); // 普通 key，例如 Table: {}
1593+
mainOuter.put(path, value);
15801594
}
15811595
}
15821596

@@ -1586,23 +1600,23 @@ else if (join != null){
15861600
String tableKey = e.getKey(); // 如 "Location_info"
15871601
Object tableObj = e.getValue(); // value 是 Map
15881602

1589-
if (request.containsKey(tableKey)) {
1603+
if (request.get(tableKey) instanceof Map<?, ?>) {
15901604
whereJoinMap.put(tableKey, tableObj);
15911605
} else {
1592-
Log.w(TAG, "跳过 join 中 key = " + tableKey + "，因为它不在 request 中");
1606+
throw new IllegalArgumentException(apijson.JSONRequest.KEY_JOIN + ":'" + tableKey + "' 不是 JOIN 副表的名称 ！");
15931607
}
15941608
}
15951609

1610+
List<Join<T, M, L>> joinList = new ArrayList<>();
1611+
joinList.add(mainJoin);
15961612

15971613
Set<Entry<String, Object>> set = joinMap == null ? null : new LinkedHashSet<>(slashKeys);
15981614

15991615
if (set == null || set.isEmpty()) {
16001616
Log.e(TAG, "onJoinParse set == null || set.isEmpty() >> return null;");
1601-
return null;
1617+
return joinList;
16021618
}
16031619

1604-
List<Join<T, M, L>> joinList = new ArrayList<>();
1605-
16061620
for (Entry<String, Object> e : set) { // { &/User:{}, </Moment/id@":{}, @/Comment/toId@:{} }
16071621
// 分割 /Table/key
16081622
String path = e == null ? null : e.getKey();
@@ -1611,10 +1625,14 @@ else if (join != null){
16111625
if (outer instanceof Map<?, ?> == false) {
16121626
throw new IllegalArgumentException(apijson.JSONRequest.KEY_JOIN + ":value 中value不合法！"
16131627
+ "必须为 &/Table0/key0,</Table1/key1,... 或 { '&/Table0/key0':{}, '</Table1/key1':{},... } 这种形式！");
1628+
//mainOuter.put(path, outer);
1629+
//continue;
16141630
}
16151631

16161632
int index = path == null ? -1 : path.indexOf("/");
16171633
if (index < 0) {
1634+
//if (isTableKey(path)) {
1635+
//}
16181636
throw new IllegalArgumentException(apijson.JSONRequest.KEY_JOIN + ":value 中 value 值 " + path + " 不合法！"
16191637
+ "必须为 &/Table0,</Table1/key1,@/Table1:alias2/key2,... 或 { '&/Table0':{}, '</Table1/key1':{},... } 这种形式！");
16201638
}
@@ -1790,13 +1808,13 @@ else if (join != null){
17901808
j.setTable(table);
17911809
j.setAlias(alias);
17921810

1793-
M outerObj = (M) JSON.createJSONObject((Map<String, Object>) outer);
1811+
M outerObj = JSON.createJSONObject((Map<String, Object>) outer);
17941812
j.setOn(outerObj);
17951813
j.setRequest(requestObj);
17961814

1797-
if (whereJoinMap.containsKey(table)) {
1798-
Object rawOuter = whereJoinMap.get(table);
1799-
M outerObj1 = (M) JSON.createJSONObject((Map<String, Object>) rawOuter);
1815+
Object rawOuter = whereJoinMap.get(table);
1816+
if (rawOuter instanceof Map<?, ?>) {
1817+
M outerObj1 = JSON.createJSONObject((Map<String, Object>) rawOuter);
18001818
j.setOuter(outerObj1);
18011819
}
18021820

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

Lines changed: 62 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -2347,6 +2347,16 @@ public String gainColumnString(boolean inSQLJoin) throws Exception {
23472347
if (joinList != null) {
23482348
boolean first = true;
23492349
for (Join<T, M, L> join : joinList) {
2350+
if (join == null) {
2351+
continue;
2352+
}
2353+
if (join.getJoinType() == null) {
2354+
SQLConfig<T, M, L> cfg = join.getJoinConfig();
2355+
List<String> col = cfg == null ? null : cfg.getColumn();
2356+
column = col == null ? column : col;
2357+
continue;
2358+
}
2359+
23502360
if (join.isAppJoin()) {
23512361
continue;
23522362
}
@@ -3315,11 +3325,23 @@ else if (key.equals(userIdInKey)) {
33153325
*/
33163326
@Override
33173327
public String gainWhereString(boolean hasPrefix) throws Exception {
3318-
String combineExpr = getCombine();
3328+
Join<T, M, L> join = joinList == null || joinList.isEmpty() ? null : joinList.get(0);
3329+
SQLConfig<T, M, L> cfg = join == null || join.getJoinType() != null ? null : join.getJoinConfig();
3330+
Map<String, Object> joinWhere = cfg == null ? null : cfg.getWhere();
3331+
//String ws = cfg == null ? null : cfg.gainWhereString(hasPrefix);
3332+
//if (StringUtil.isNotEmpty(ws, true)) {
3333+
// return ws;
3334+
//}
3335+
3336+
String joinCombine = cfg == null ? null : cfg.getCombine();
3337+
Map<String, Object> where = joinWhere == null ? getWhere() : joinWhere;
3338+
String combineExpr = StringUtil.isEmpty(joinCombine, true) ? getCombine() : joinCombine;
33193339
if (StringUtil.isEmpty(combineExpr, false)) {
3320-
return getWhereString(hasPrefix, getMethod(), getWhere(), getCombineMap(), getJoinList(), ! isTest());
3340+
Map<String, List<String>> joinCombineMap = cfg == null ? null : cfg.getCombineMap();
3341+
Map<String, List<String>> combineMap = joinCombineMap == null ? getCombineMap() : joinCombineMap;
3342+
return getWhereString(hasPrefix, getMethod(), where, combineMap, getJoinList(), ! isTest());
33213343
}
3322-
return getWhereString(hasPrefix, getMethod(), getWhere(), combineExpr, getJoinList(), ! isTest());
3344+
return getWhereString(hasPrefix, getMethod(), where, combineExpr, getJoinList(), ! isTest());
33233345
}
33243346
/**获取WHERE
33253347
* @param method
@@ -3741,7 +3763,10 @@ protected String concatJoinWhereString(String whereString) throws Exception {
37413763
boolean changed = false;
37423764
// 各种 JOIN 没办法统一用 & | ！连接，只能按优先级，和 @combine 一样?
37433765
for (Join<T, M, L> j : joinList) {
3744-
String jt = j.getJoinType();
3766+
String jt = j == null ? null : j.getJoinType();
3767+
if (jt == null) {
3768+
continue;
3769+
}
37453770

37463771
switch (jt) {
37473772
case "*": // CROSS JOIN
@@ -3752,7 +3777,7 @@ protected String concatJoinWhereString(String whereString) throws Exception {
37523777
if (outerConfig == null){
37533778
break;
37543779
}
3755-
boolean isMain1 = outerConfig.isMain();
3780+
37563781
outerConfig.setMain(false).setPrepared(isPrepared()).setPreparedValueList(new ArrayList<Object>());
37573782
String outerWhere = outerConfig.gainWhereString(false);
37583783

@@ -4021,7 +4046,10 @@ else if (isTest()) {
40214046
return gainSQLValue(key).toString();
40224047
}
40234048

4024-
Map<String, String> keyMap = getKeyMap();
4049+
Join<T, M, L> join = joinList == null || joinList.isEmpty() ? null : joinList.get(0);
4050+
SQLConfig<T, M, L> cfg = join == null || join.getJoinType() != null ? null : join.getJoinConfig();
4051+
Map<String, String> joinKeyMap = cfg == null ? null : cfg.getKeyMap();
4052+
Map<String, String> keyMap = joinKeyMap == null ? getKeyMap() : joinKeyMap;
40254053
String expression = keyMap == null ? null : keyMap.get(key);
40264054
if (expression == null) {
40274055
expression = COLUMN_KEY_MAP == null ? null : COLUMN_KEY_MAP.get(key);
@@ -5162,13 +5190,23 @@ public String gainJoinString() throws Exception {
51625190
List<Object> pvl = getPreparedValueList(); // new ArrayList<>();
51635191
//boolean changed = false;
51645192

5165-
// 主表不用别名 String ta;
5166-
for (Join j : joinList) {
5193+
// 主表不用别名 String ta;
5194+
boolean first = true;
5195+
for (Join<T, M, L> j : joinList) {
5196+
if (j == null) {
5197+
continue;
5198+
}
5199+
if (first) {
5200+
first = false;
5201+
continue;
5202+
}
5203+
51675204
onGainJoinString(j);
51685205

51695206
if (j.isAppJoin()) { // APP JOIN，只是作为一个标记，执行完主表的查询后自动执行副表的查询 User.id IN($commentIdList)
51705207
continue;
51715208
}
5209+
51725210
String type = j.getJoinType();
51735211

51745212
//LEFT JOIN sys.apijson_user AS User ON User.id = Moment.userId， 都是用 = ，通过relateType处理缓存
@@ -5490,7 +5528,7 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> SQLConf
54905528
String catalog = getString(request, KEY_CATALOG);
54915529
String schema = getString(request, KEY_SCHEMA);
54925530

5493-
SQLConfig<T, M, L> config = (SQLConfig<T, M, L>) callback.getSQLConfig(method, database, schema, datasource, table);
5531+
SQLConfig<T, M, L> config = callback.getSQLConfig(method, database, schema, datasource, table);
54945532
config.setAlias(alias);
54955533

54965534
config.setDatabase(database); // 不删，后面表对象还要用的，必须放在 parseJoin 前
@@ -6306,6 +6344,11 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> SQLConf
63066344
for (Join<T, M, L> join : joinList) {
63076345
table = join.getTable();
63086346
alias = join.getAlias();
6347+
6348+
if (table == null && join.getJoinType() == null) {
6349+
table = config.getTable();
6350+
}
6351+
63096352
//JOIN子查询不能设置LIMIT，因为ON关系是在子查询后处理的，会导致结果会错误
63106353
SQLConfig<T, M, L> joinConfig = newSQLConfig(method, table, alias, join.getRequest(), null, false, callback);
63116354
SQLConfig<T, M, L> cacheConfig = join.canCacheViceTable() == false ? null : newSQLConfig(method, table, alias
@@ -6319,6 +6362,7 @@ else if (joinConfig.getDatabase().equals(config.getDatabase()) == false) {
63196362
throw new IllegalArgumentException("主表 " + config.getTable() + " 的 @database:" + config.getDatabase()
63206363
+ " 和它 SQL JOIN 的副表 " + table + " 的 @database:" + joinConfig.getDatabase() + " 不一致！");
63216364
}
6365+
63226366
if (joinConfig.getSchema() == null) {
63236367
joinConfig.setSchema(config.getSchema()); //主表 JOIN 副表，默认 schema 一致
63246368
}
@@ -6327,16 +6371,17 @@ else if (joinConfig.getDatabase().equals(config.getDatabase()) == false) {
63276371
cacheConfig.setDatabase(joinConfig.getDatabase()).setSchema(joinConfig.getSchema()); //解决主表 JOIN 副表，引号不一致
63286372
}
63296373

6330-
if (isQuery) {
6331-
config.setKeyPrefix(true);
6374+
if (join.getJoinType() != null) {
6375+
if (isQuery) {
6376+
config.setKeyPrefix(true);
6377+
}
6378+
joinConfig.setMain(false).setKeyPrefix(true);
63326379
}
63336380

6334-
joinConfig.setMain(false).setKeyPrefix(true);
6335-
63366381
if (join.getOn() != null) {
63376382
SQLConfig<T, M, L> onConfig = newSQLConfig(method, table, alias, join.getOn(), null, false, callback);
6338-
onConfig.setMain(false)
6339-
.setKeyPrefix(true)
6383+
onConfig.setMain(joinConfig.isMain())
6384+
.setKeyPrefix(joinConfig.isKeyPrefix())
63406385
.setDatabase(joinConfig.getDatabase())
63416386
.setSchema(joinConfig.getSchema()); //解决主表 JOIN 副表，引号不一致
63426387

@@ -6345,8 +6390,8 @@ else if (joinConfig.getDatabase().equals(config.getDatabase()) == false) {
63456390

63466391
if (join.getOuter() != null) {
63476392
SQLConfig<T, M, L> outerConfig = newSQLConfig(method, table, alias, join.getOuter(), null, false, callback);
6348-
outerConfig.setMain(false)
6349-
.setKeyPrefix(true)
6393+
outerConfig.setMain(joinConfig.isMain())
6394+
.setKeyPrefix(joinConfig.isKeyPrefix())
63506395
.setDatabase(joinConfig.getDatabase())
63516396
.setSchema(joinConfig.getSchema()); //解决主表 JOIN 副表，引号不一致
63526397
join.setOuterConfig(outerConfig);

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

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -426,7 +426,7 @@ public M execute(@NotNull SQLConfig<T, M, L> config, boolean unknownType) throws
426426
// 为什么 isExplain == false 不用判断？因为所有字段都在一张 Query Plan 表
427427
if (index <= 0 && columnIndexAndJoinMap != null) { // && viceColumnStart > length) {
428428

429-
SQLConfig<T, M, L> curConfig = curJoin == null || ! curJoin.isSQLJoin() ? null : curJoin.getCacheConfig();
429+
SQLConfig<T, M, L> curConfig = curJoin == null || curJoin.getJoinType() == null || ! curJoin.isSQLJoin() ? null : curJoin.getCacheConfig();
430430
List<String> curColumn = curConfig == null ? null : curConfig.getColumn();
431431
String sqlTable = curConfig == null ? null : curConfig.gainSQLTable();
432432
String sqlAlias = curConfig == null ? null : curConfig.getAlias();
@@ -469,7 +469,7 @@ public M execute(@NotNull SQLConfig<T, M, L> config, boolean unknownType) throws
469469
else if (isMain) {
470470
for (int j = 0; j < joinList.size(); j++) {
471471
Join<T, M, L> join = joinList.get(j);
472-
SQLConfig<T, M, L> cfg = join == null || ! join.isSQLJoin() ? null : join.getJoinConfig();
472+
SQLConfig<T, M, L> cfg = join == null || join.getJoinType() == null || ! join.isSQLJoin() ? null : join.getJoinConfig();
473473
List<String> c = cfg == null ? null : cfg.getColumn();
474474

475475
if (cfg != null) {
@@ -501,7 +501,7 @@ else if (isMain) {
501501
int joinCount = joinList.size();
502502
for (int j = lastViceTableStart; j < joinCount; j++) { // 查找副表 @column，定位字段所在表
503503
Join<T, M, L> join = joinList.get(j);
504-
SQLConfig<T, M, L> cfg = join == null || ! join.isSQLJoin() ? null : join.getJoinConfig();
504+
SQLConfig<T, M, L> cfg = join == null || join.getJoinType() == null || ! join.isSQLJoin() ? null : join.getJoinConfig();
505505
List<String> c = cfg == null ? null : cfg.getColumn();
506506

507507
nextViceColumnStart += (c != null && ! c.isEmpty() ?
@@ -574,7 +574,7 @@ else if (isMain) {
574574
}
575575

576576
if (curColumn == null) {
577-
curConfig = curJoin == null || ! curJoin.isSQLJoin() ? null : curJoin.getJoinConfig();
577+
curConfig = curJoin == null || curJoin.getJoinType() == null || ! curJoin.isSQLJoin() ? null : curJoin.getJoinConfig();
578578
curColumn = curConfig == null ? null : curConfig.getColumn();
579579
}
580580

@@ -590,7 +590,7 @@ else if (isMain) {
590590
// 如果是主表则直接用主表对应的 item，否则缓存副表数据到 childMap
591591
Join prevJoin = columnIndexAndJoinMap == null || i < 2 ? null : columnIndexAndJoinMap[i - 2];
592592
if (curJoin != prevJoin) { // 前后字段不在同一个表对象，即便后面出现 null，也不该是主表数据，而是逻辑 bug 导致
593-
SQLConfig<T, M, L> viceConfig = curJoin != null && curJoin.isSQLJoin() ? curJoin.getCacheConfig() : null;
593+
SQLConfig<T, M, L> viceConfig = curJoin != null && curJoin.isSQLJoin() && curJoin.getJoinType() == null ? curJoin.getCacheConfig() : null;
594594
boolean hasPK = false;
595595
if (viceConfig != null) { //FIXME 只有和主表关联才能用 item，否则应该从 childMap 查其它副表数据
596596
List<On> onList = curJoin.getOnList();
@@ -991,7 +991,7 @@ protected M onPutColumn(@NotNull SQLConfig<T, M, L> config, @NotNull ResultSet r
991991
Object value = getValue(config, rs, rsmd, row, table, columnIndex, label, childMap, keyMap);
992992

993993
// 主表必须 put 至少一个 null 进去，否则全部字段为 null 都不 put 会导致中断后续正常返回值
994-
if (value != null || ENABLE_OUTPUT_NULL_COLUMN || (join == null && table.isEmpty())) {
994+
if (value != null || ENABLE_OUTPUT_NULL_COLUMN || ((join == null || join.getJoinType() == null) && table.isEmpty())) {
995995
table.put(label, value);
996996
}
997997

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -392,7 +392,10 @@ static String gainSQLAlias(@NotNull String table, String alias) {
392392
return StringUtil.isEmpty(alias) ? table : table + "__" + alias; // 带上原表名，避免 alias 和其它表名/字段名冲突
393393
}
394394

395-
395+
default String gainColumnString() throws Exception {
396+
return gainColumnString(false);
397+
}
398+
String gainColumnString(boolean inSQLJoin) throws Exception;
396399
String gainWhereString(boolean hasPrefix) throws Exception;
397400

398401
String gainRawSQL(String key, Object value) throws Exception;

0 commit comments

Comments
 (0)