Tencent
多表连接出现错误结果
环境信息
- 系统:Windows 10
- JDK: 1.8.0_17
- 数据库:MySQL 8.0.28
- APIJSON: 4.9.1
问题描述
在进行多表连接时，不知道为什么其它表的字段会加入那个表中。
数据库结构：
执行的apijson表达式：
{
"[]": {
"join": "&/Lrx_score:t2/sid@,&/Lrx_score:t3/sid@,&/Lrx_score:t4/sid@",
"Lrx_student": {
},
"Lrx_score:t2": {
"@column": "sid:t2_sid,cid:t2_cid,grade:t2_grade",
"sid@": "/Lrx_student/sid",
"cid": "01"
},
"Lrx_score:t3": {
"@column": "sid:t3_sid,cid:t3_cid,grade:t3_grade",
"sid@": "/Lrx_student/sid",
"cid": "02"
},
"Lrx_score:t4": {
"@column": "sid:t4_sid,cid:t4_cid,grade:t4_grade",
"sid@": "/Lrx_student/sid",
"cid": "03"
}
}
}
错误信息
红色框内出现其它表的数据
完整结果如下：
{
"[]": [
{
"Lrx_student": {
"sid": "01",
"name": "赵雷",
"age": "1990-01-01T00:00:00",
"sex": "男"
},
"Lrx_score:t2": {
"t2_sid": "01",
"t2_cid": "01",
"t2_grade": 80.0,
"t3_sid": "01",
"t3_cid": "02",
"t3_grade": 90.0,
"t4_sid": "01",
"t4_cid": "03",
"t4_grade": 99.0
},
"Lrx_score:t3": {
"t3_sid": "01",
"t3_cid": "02",
"t3_grade": 90.0
},
"Lrx_score:t4": {
"t4_sid": "01",
"t4_cid": "03",
"t4_grade": 99.0
}
},
{
"Lrx_student": {
"sid": "02",
"name": "钱电",
"age": "1990-12-21T00:00:00",
"sex": "男"
},
"Lrx_score:t2": {
"t2_sid": "02",
"t2_cid": "01",
"t2_grade": 70.0,
"t3_sid": "02",
"t3_cid": "02",
"t3_grade": 60.0,
"t4_sid": "02",
"t4_cid": "03",
"t4_grade": 80.0
},
"Lrx_score:t3": {
"t3_sid": "02",
"t3_cid": "02",
"t3_grade": 60.0
},
"Lrx_score:t4": {
"t4_sid": "02",
"t4_cid": "03",
"t4_grade": 80.0
}
},
{
"Lrx_student": {
"sid": "03",
"name": "孙风",
"age": "1990-05-20T00:00:00",
"sex": "男"
},
"Lrx_score:t2": {
"t2_sid": "03",
"t2_cid": "01",
"t2_grade": 80.0,
"t3_sid": "03",
"t3_cid": "02",
"t3_grade": 80.0,
"t4_sid": "03",
"t4_cid": "03",
"t4_grade": 80.0
},
"Lrx_score:t3": {
"t3_sid": "03",
"t3_cid": "02",
"t3_grade": 80.0
},
"Lrx_score:t4": {
"t4_sid": "03",
"t4_cid": "03",
"t4_grade": 80.0
}
},
{
"Lrx_student": {
"sid": "04",
"name": "李云",
"age": "1990-08-06T00:00:00",
"sex": "男"
},
"Lrx_score:t2": {
"t2_sid": "04",
"t2_cid": "01",
"t2_grade": 50.0,
"t3_sid": "04",
"t3_cid": "02",
"t3_grade": 30.0,
"t4_sid": "04",
"t4_cid": "03",
"t4_grade": 20.0
},
"Lrx_score:t3": {
"t3_sid": "04",
"t3_cid": "02",
"t3_grade": 30.0
},
"Lrx_score:t4": {
"t4_sid": "04",
"t4_cid": "03",
"t4_grade": 20.0
}
}
],
"ok": true,
"code": 200,
"msg": "success",
"debug:info|help": " \n **环境信息** \n 系统: Windows 10 10.0 \n 数据库: DEFAULT_DATABASE = MYSQL \n JDK: 12.0.1 amd64 \n APIJSON: 4.9.0 \n | \n 常见问题：https://github.com/Tencent/APIJSON/issues/36 \n 通用文档：https://github.com/Tencent/APIJSON/blob/master/Document.md \n 视频教程：https://search.bilibili.com/all?keyword=APIJSON",
"sql:generate|cache|execute|maxExecute": "13|4|9|200",
"depth:count|max": "3|5",
"time:start|duration|end|parse|sql": "1648708994731|48|1648708994779|11|37"
}
同时 SQL JOIN 多个同名副表确实有这个 bug，不过对你这个不影响功能，只是多返回了字段影响性能。 问题出现在 AbstractSQLExecutor.execute(@NotNull SQLConfig config, boolean unknowType) https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLExecutor.java#L442-L513
JDBC 不返回表别名，所以暂时还不好判断不带 @column 的情况。
不过像你这个需求，每个副表都有 @column，理论上根据这个可以准确计算出来每个字段所在表，不应该出现这个问题。
感谢反馈~
目前有个简单的解决方法： 分别把带别名的副表 key 名改为其它名称，通过 Access 表(apijson-framework) 或 注解+注册(直接用 APIJSON ORM) 配置表名映射为同一个真实数据库表名。
name: Lrx_score // Lrx_score -> Lrx_score
name: Lrx_score, alias: Lrx_score_t3 // Lrx_score_t3 -> Lrx_score
name: Lrx_score, alias: Lrx_score_t4 // Lrx_score_t4 -> Lrx_score
目前有个简单的解决方法： 分别把带别名的副表 key 名改为其它名称，通过 Access 表(apijson-framework) 或 注解+注册(直接用 APIJSON ORM) 配置表名映射为同一个真实数据库表名。
name: Lrx_score // Lrx_score -> Lrx_score
name: Lrx_score, alias: Lrx_score_t3 // Lrx_score_t3 -> Lrx_score
name: Lrx_score, alias: Lrx_score_t4 // Lrx_score_t4 -> Lrx_score
这个也不行，内部用的是真实表名 sqlTable 来判断。
得把 sqlTable 判断改为 sqlTable + alias 或者每条 SQL 语句中 FROM table_name 都加上 AS alias_name，这样从 JDBC 拿到的 tableName 或许是 alias_name，可以用来区分 Lrx_score:t2, Lrx_score:t3, Lrx_score:t4
关于 JDBC 对 SQL 函数拿不到表名的情况，可以解析 function(arg0, .. , table.column, .. , argN ..) 内部的参数 table.column，拿到 table 对比 config.getAlias 来判断。
也可以给对应 SQL 函数 AS table.column，这样在解析结果时通过点 . 来分割拿到 table 名确定把 column 分到哪个 JSONObject