Skip to content

Commit f23204e

Browse files
TommyLemonTommyLemon
committed
完善对 Presto, Trino 的支持：正则忽略大小写；支持预编译模式等
1 parent c445c71 commit f23204e

2 files changed

Lines changed: 36 additions & 18 deletions

File tree

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

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3561,12 +3561,13 @@ public String getRegExpString(String key, String column, String value, boolean i
35613561
if (isPostgreSQL() || isInfluxDB()) {
35623562
return getKey(column) + " ~" + (ignoreCase ? "* " : " ") + getValue(key, column, value);
35633563
}
3564-
if (isPresto() || isTrino()) {
3565-
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + ")";
3566-
}
35673564
if (isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
35683565
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
35693566
}
3567+
if (isPresto() || isTrino()) {
3568+
return "regexp_like(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
3569+
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
3570+
}
35703571
if (isClickHouse()) {
35713572
return "match(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
35723573
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
@@ -4436,14 +4437,14 @@ else if (rt.endsWith("~")) {
44364437
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
44374438
+ " ~" + (ignoreCase ? "* " : " ") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
44384439
}
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-
}
44434440
else if (isOracle() || isDameng() || isKingBase()) {
44444441
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
44454442
+ ", " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
44464443
}
4444+
else if (isPresto() || isTrino()) {
4445+
sql += (first ? ON : AND) + "regexp_like(" + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4446+
+ ", " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
4447+
}
44474448
else if (isClickHouse()) {
44484449
sql += (first ? ON : AND) + "match(" + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
44494450
+ ", " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";

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

Lines changed: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -700,7 +700,7 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
700700
jc.putWhere(originKey, null, false); // remove originKey
701701
jc.putWhere(key + "{}", targetValueList, true); // add originKey{} }
702702

703-
jc.setMain(true).setPreparedValueList(new ArrayList<>());
703+
jc.setMain(true).setPreparedValueList(new ArrayList<>());
704704

705705
// 放一块逻辑更清晰，也避免解析 * 等不支持或性能开销
706706
// String q = jc.getQuote();
@@ -752,7 +752,7 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
752752
//
753753
// if (noAggrFun) { // 加 row_number 字段会导致 count 等聚合函数统计出错，结果偏大？
754754
String q = jc.getQuote();
755-
sql2 = prepared ? jc.getSQL(true) : sql;
755+
sql2 = prepared && jc.isTDengine() == false ? jc.getSQL(true) : sql;
756756

757757
String prefix = "SELECT * FROM(";
758758
String rnStr = ", row_number() OVER (PARTITION BY " + q + key + q + ((AbstractSQLConfig) jc).getOrderString(true) + ") _row_num_ FROM ";
@@ -786,7 +786,7 @@ protected void executeAppJoin(SQLConfig config, List<JSONObject> resultList, Map
786786
executedSQLCount ++;
787787
executedSQLStartTime = System.currentTimeMillis();
788788
}
789-
rs = executeQuery(jc, sql2);
789+
rs = executeQuery(jc, sql2);
790790
if (isExplain == false) {
791791
executedSQLDuration += System.currentTimeMillis() - executedSQLStartTime;
792792
}
@@ -1085,7 +1085,16 @@ public PreparedStatement getStatement(@NotNull SQLConfig config, String sql) thr
10851085
}
10861086
}
10871087
else if (RequestMethod.isGetMethod(config.getMethod(), true)) {
1088-
statement = getConnection(config).prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1088+
//if (config.isPresto() || config.isTrino()) {
1089+
// statement = getConnection(config).prepareStatement(sql); // , ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
1090+
//} else {
1091+
// statement = getConnection(config).prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1092+
//}
1093+
if (config.isMySQL() || config.isPostgreSQL() || config.isOracle() || config.isSQLServer() || config.isDb2()) {
1094+
statement = getConnection(config).prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1095+
} else {
1096+
statement = getConnection(config).prepareStatement(sql);
1097+
}
10891098
}
10901099
else {
10911100
statement = getConnection(config).prepareStatement(sql);
@@ -1227,15 +1236,19 @@ public void close() {
12271236

12281237
@Override
12291238
public ResultSet executeQuery(@NotNull SQLConfig config, String sql) throws Exception {
1230-
if (config.isPresto() || config.isTrino() || config.isTDengine()) {
1231-
Connection conn = getConnection(config);
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);
1239+
if (config.isPrepared() == false || config.isTDengine() // TDengine JDBC 不支持 PreparedStatement
1240+
|| (config.isExplain() && (config.isPresto() || config.isTrino()))) { // Presto JDBC 0.277 在 EXPLAIN 模式下预编译值不会替代 ? 占位导致报错
1241+
1242+
Connection conn = getConnection(config);
1243+
Statement stt = conn.createStatement();
1244+
//Statement stt = config.isTDengine()
1245+
// ? conn.createStatement() // fix Presto: ResultSet: Exception: set type is TYPE_FORWARD_ONLY, Result set concurrency must be CONCUR_READ_ONLY
1246+
// : conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
12351247

12361248
return executeQuery(stt, StringUtil.isEmpty(sql) ? config.getSQL(false) : sql);
12371249
}
12381250

1251+
// Presto JDBC 0.277 在 EXPLAIN 模式下预编译值不会替代 ? 占位导致报错
12391252
PreparedStatement stt = getStatement(config, sql);
12401253
ResultSet rs = stt.executeQuery(); //PreparedStatement 不用传 SQL
12411254
// if (config.isExplain() && (config.isSQLServer() || config.isOracle())) {
@@ -1252,8 +1265,12 @@ public int executeUpdate(@NotNull SQLConfig config, String sql) throws Exception
12521265
int count;
12531266
if (config.isTDengine()) {
12541267
Connection conn = getConnection(config);
1255-
stt = conn.createStatement();
1256-
count = stt.executeUpdate(StringUtil.isEmpty(sql) ? config.getSQL(false) : sql);
1268+
stt = conn.createStatement();
1269+
//stt = config.isTDengine()
1270+
// ? conn.createStatement() // fix Presto: ResultSet: Exception: set type is TYPE_FORWARD_ONLY, Result set concurrency must be CONCUR_READ_ONLY
1271+
// : conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
1272+
1273+
count = stt.executeUpdate(StringUtil.isEmpty(sql) ? config.getSQL(false) : sql);
12571274
}
12581275
else {
12591276
stt = getStatement(config);

0 commit comments

Comments
 (0)