Skip to content

Commit c445c71

Browse files
TommyLemonTommyLemon
committed
完善对 Presto, Trino 的支持；解决 SQL JOIN 当部分 JDBC rsmd.getTableName 返回空值导致最终主表字段插到副表对象导致 最终返回 JSON 为空或者副表字段写进主表覆盖同名字段等
1 parent 305d12a commit c445c71

File tree

3 files changed

+41
-8
lines changed

3 files changed

+41
-8
lines changed

‎APIJSONORM/pom.xml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55

66
<groupId>com.github.Tencent</groupId>
77
<artifactId>APIJSON</artifactId>
8-
<version>5.3.0</version>
8+
<version>5.4.0</version>
99
<packaging>jar</packaging>
1010

1111
<name>APIJSONORM</name>

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

Lines changed: 26 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -423,13 +423,20 @@ public abstract class AbstractSQLConfig implements SQLConfig {
423423
SQL_FUNCTION_MAP.put("json_array", ""); // JSON_ARRAY(val1, val2...) 创建JSON数组
424424
SQL_FUNCTION_MAP.put("json_array_append", ""); // JSON_ARRAY_APPEND(json_doc, val) 将数据附加到JSON文档
425425
SQL_FUNCTION_MAP.put("json_array_insert", ""); // JSON_ARRAY_INSERT(json_doc, val) 插入JSON数组
426+
SQL_FUNCTION_MAP.put("json_array_get", ""); // JSON_ARRAY_GET(json_doc, position) 从JSON数组提取指定位置的元素
426427
SQL_FUNCTION_MAP.put("json_contains", ""); // JSON_CONTAINS(json_doc, val) JSON文档是否在路径中包含特定对象
427-
SQL_FUNCTION_MAP.put("json_contains_path", ""); // JSON_CONTAINS_PATH(json_doc, path) JSON文档是否在路径中包含任何数据
428+
SQL_FUNCTION_MAP.put("json_array_contains", ""); // JSON_ARRAY_CONTAINS(json_doc, path) JSON文档是否在路径中包含特定对象
429+
SQL_FUNCTION_MAP.put("json_contains_path", ""); // JSON_CONTAINS_PATH(json_doc, path) JSON文档是否在路径中包含任何数据
428430
SQL_FUNCTION_MAP.put("json_depth", ""); // JSON_DEPTH(json_doc) JSON文档的最大深度
429431
SQL_FUNCTION_MAP.put("json_extract", ""); // JSON_EXTRACT(json_doc, path) 从JSON文档返回数据
432+
SQL_FUNCTION_MAP.put("json_extract_scalar", ""); // JSON_EXTRACT_SCALAR(json_doc, path) 从JSON文档返回基础类型数据，例如 Boolean, Number, String
430433
SQL_FUNCTION_MAP.put("json_insert", ""); // JSON_INSERT(json_doc, val) 将数据插入JSON文档
431434
SQL_FUNCTION_MAP.put("json_keys", ""); // JSON_KEYS(json_doc[, path]) JSON文档中的键数组
432435
SQL_FUNCTION_MAP.put("json_length", ""); // JSON_LENGTH(json_doc) JSON文档中的元素数
436+
SQL_FUNCTION_MAP.put("json_size", ""); // JSON_SIZE(json_doc) JSON文档中的元素数
437+
SQL_FUNCTION_MAP.put("json_array_length", ""); // JSON_ARRAY_LENGTH(json_doc) JSON文档中的元素数
438+
SQL_FUNCTION_MAP.put("json_format", ""); // JSON_FORMAT(json_doc) 格式化 JSON
439+
SQL_FUNCTION_MAP.put("json_parse", ""); // JSON_PARSE(val) 转换为 JSON
433440
SQL_FUNCTION_MAP.put("json_merge", ""); // JSON_MERGE(json_doc1, json_doc2) （已弃用） 合并JSON文档，保留重复的键。JSON_MERGE_PRESERVE（）的已弃用同义词
434441
SQL_FUNCTION_MAP.put("json_merge_patch", ""); // JSON_MERGE_PATCH(json_doc1, json_doc2) 合并JSON文档，替换重复键的值
435442
SQL_FUNCTION_MAP.put("json_merge_preserve", ""); // JSON_MERGE_PRESERVE(json_doc1, json_doc2) 合并JSON文档，保留重复的键
@@ -451,6 +458,7 @@ public abstract class AbstractSQLConfig implements SQLConfig {
451458
SQL_FUNCTION_MAP.put("json_valid", ""); // JSON_VALID(json_doc) JSON值是否有效
452459
SQL_FUNCTION_MAP.put("json_arrayagg", ""); // JSON_ARRAYAGG(key) 将每个表达式转换为 JSON 值，然后返回一个包含这些 JSON 值的 JSON 数组
453460
SQL_FUNCTION_MAP.put("json_objectagg", ""); // JSON_OBJECTAGG(key, val)) 将每个表达式转换为 JSON 值，然后返回一个包含这些 JSON 值的 JSON 对象
461+
SQL_FUNCTION_MAP.put("is_json_scalar", ""); // IS_JSON_SCALAR(val)) 是否为JSON基本类型，例如 Boolean, Number, String
454462

455463
// MySQL 高级函数
456464
// SQL_FUNCTION_MAP.put("bin", ""); // BIN(x) 返回 x 的二进制编码
@@ -3553,7 +3561,10 @@ public String getRegExpString(String key, String column, String value, boolean i
35533561
if (isPostgreSQL() || isInfluxDB()) {
35543562
return getKey(column) + " ~" + (ignoreCase ? "* " : " ") + getValue(key, column, value);
35553563
}
3556-
if (isPresto() || isTrino() || isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
3564+
if (isPresto() || isTrino()) {
3565+
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + ")";
3566+
}
3567+
if (isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
35573568
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
35583569
}
35593570
if (isClickHouse()) {
@@ -3864,6 +3875,9 @@ public String getContainString(String key, String column, Object[] childs, int t
38643875
else if (isOracle() || isDameng() || isKingBase()) {
38653876
condition += ("json_textcontains(" + getKey(column) + ", " + (StringUtil.isEmpty(path, true) ? "'$'" : getValue(key, column, path)) + ", " + getValue(key, column, c == null ? null : c.toString()) + ")");
38663877
}
3878+
else if (isPresto() || isTrino()) {
3879+
condition += ("json_array_contains(cast(" + getKey(column) + " AS VARCHAR), " + getValue(key, column, c) + (StringUtil.isEmpty(path, true) ? "" : ", " + getValue(key, column, path)) + ")");
3880+
}
38673881
else {
38683882
String v = c == null ? "null" : (c instanceof Boolean || c instanceof Number ? c.toString() : "\"" + c + "\"");
38693883
if (isClickHouse()) {
@@ -4158,7 +4172,6 @@ protected String getOraclePageSql(String sql) {
41584172
}
41594173

41604174
/**获取条件SQL字符串
4161-
* @param column
41624175
* @param table
41634176
* @param config
41644177
* @return
@@ -4423,7 +4436,11 @@ else if (rt.endsWith("~")) {
44234436
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
44244437
+ " ~" + (ignoreCase ? "* " : " ") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
44254438
}
4426-
else if (isPresto() || isTrino() || isOracle() || isDameng() || isKingBase()) {
4439+
else if (isPresto() || isTrino()) {
4440+
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
4441+
+ ", " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + ")";
4442+
}
4443+
else if (isOracle() || isDameng() || isKingBase()) {
44274444
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
44284445
+ ", " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
44294446
}
@@ -4490,6 +4507,11 @@ else if (isOracle() || isDameng() || isKingBase()) {
44904507
+ " IS NOT NULL AND json_textcontains(" + arrKeyPath
44914508
+ ", '$', " + itemKeyPath + ")") + (isNot ? ") " : "");
44924509
}
4510+
else if (isPresto() || isTrino()) {
4511+
sql += (first ? ON : AND) + (isNot ? "( " : "") + getCondition(isNot, arrKeyPath
4512+
+ " IS NOT NULL AND json_array_contains(cast(" + arrKeyPath
4513+
+ " AS VARCHAR), " + itemKeyPath + ")") + (isNot ? ") " : "");
4514+
}
44934515
else if (isClickHouse()) {
44944516
sql += (first ? ON : AND) + (isNot ? "( " : "") + getCondition(isNot, arrKeyPath
44954517
+ " IS NOT NULL AND has(JSONExtractArrayRaw(assumeNotNull(" + arrKeyPath + "))"

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

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -427,6 +427,14 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknownType) throws
427427
sqlTable = rsmd.getTableName(i); // SQL 函数甚至部分字段都不返回表名，当然如果没传 @column 生成的 Table.* 则返回的所有字段都会带表名
428428
sqlResultDuration += System.currentTimeMillis() - startTime3;
429429

430+
if (StringUtil.isEmpty(sqlTable, true)) {
431+
boolean isEmpty = curItem == null || curItem.isEmpty();
432+
String label = isEmpty ? null : getKey(config, rs, rsmd, index, curItem, i, childMap);
433+
if (isEmpty || curItem.containsKey(label) == false) { // 重复字段几乎肯定不是一张表的，尤其是主副表同名主键 id
434+
sqlTable = i <= 1 ? config.getSQLTable() : lastTableName; // Presto 等引擎 JDBC 返回 rsmd.getTableName(i) 为空，主表如果一个字段都没有会导致 APISJON 主副表所有字段都不返回
435+
}
436+
}
437+
430438
if (StringUtil.isEmpty(sqlTable, true)) { // hasJoin 已包含这个判断 && joinList != null) {
431439

432440
int nextViceColumnStart = lastViceColumnStart; // 主表没有 @column 时会偏小 lastViceColumnStart
@@ -1219,10 +1227,13 @@ public void close() {
12191227

12201228
@Override
12211229
public ResultSet executeQuery(@NotNull SQLConfig config, String sql) throws Exception {
1222-
if (config.isTDengine()) {
1230+
if (config.isPresto() || config.isTrino() || config.isTDengine()) {
12231231
Connection conn = getConnection(config);
1224-
Statement stt = conn.createStatement();
1225-
return executeQuery(stt, StringUtil.isEmpty(sql) ? config.getSQL(false) : sql);
1232+
Statement stt = config.isTDengine()
1233+
? conn.createStatement() // fix Presto: ResultSet: Exception: set type is TYPE_FORWARD_ONLY, Result set concurrency must be CONCUR_READ_ONLY
1234+
: conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
1235+
1236+
return executeQuery(stt, StringUtil.isEmpty(sql) ? config.getSQL(false) : sql);
12261237
}
12271238

12281239
PreparedStatement stt = getStatement(config, sql);

0 commit comments

Comments
 (0)