Skip to content

Commit 746f291

Browse files
author
LY
committed
fix: 处理自关联查询时，对Table:alias的写法sql表名拼写异常的问题
1 parent f787f8e commit 746f291

1 file changed

Lines changed: 56 additions & 50 deletions

File tree

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

Lines changed: 56 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -188,7 +188,7 @@ public abstract class AbstractSQLConfig<T extends Object> implements SQLConfig<T
188188
TABLE_KEY_MAP.put(AllTableComment.class.getSimpleName(), AllTableComment.TABLE_NAME);
189189
TABLE_KEY_MAP.put(AllColumnComment.class.getSimpleName(), AllColumnComment.TABLE_NAME);
190190

191-
ALLOW_PARTIAL_UPDATE_FAIL_TABLE_MAP = new HashMap<>();
191+
ALLOW_PARTIAL_UPDATE_FAIL_TABLE_MAP = new HashMap<>();
192192

193193
CONFIG_TABLE_LIST = new ArrayList<>(); // Table, Column 等是系统表 AbstractVerifier.SYSTEM_ACCESS_MAP.keySet());
194194
CONFIG_TABLE_LIST.add(Function.class.getSimpleName());
@@ -492,8 +492,8 @@ public abstract class AbstractSQLConfig<T extends Object> implements SQLConfig<T
492492
SQL_FUNCTION_MAP.put("json_array_insert", ""); // JSON_ARRAY_INSERT(json_doc, val) 插入JSON数组
493493
SQL_FUNCTION_MAP.put("json_array_get", ""); // JSON_ARRAY_GET(json_doc, position) 从JSON数组提取指定位置的元素
494494
SQL_FUNCTION_MAP.put("json_contains", ""); // JSON_CONTAINS(json_doc, val) JSON文档是否在路径中包含特定对象
495-
SQL_FUNCTION_MAP.put("json_array_contains", ""); // JSON_ARRAY_CONTAINS(json_doc, path) JSON文档是否在路径中包含特定对象
496-
SQL_FUNCTION_MAP.put("json_contains_path", ""); // JSON_CONTAINS_PATH(json_doc, path) JSON文档是否在路径中包含任何数据
495+
SQL_FUNCTION_MAP.put("json_array_contains", ""); // JSON_ARRAY_CONTAINS(json_doc, path) JSON文档是否在路径中包含特定对象
496+
SQL_FUNCTION_MAP.put("json_contains_path", ""); // JSON_CONTAINS_PATH(json_doc, path) JSON文档是否在路径中包含任何数据
497497
SQL_FUNCTION_MAP.put("json_depth", ""); // JSON_DEPTH(json_doc) JSON文档的最大深度
498498
SQL_FUNCTION_MAP.put("json_extract", ""); // JSON_EXTRACT(json_doc, path) 从JSON文档返回数据
499499
SQL_FUNCTION_MAP.put("json_extract_scalar", ""); // JSON_EXTRACT_SCALAR(json_doc, path) 从JSON文档返回基础类型数据，例如 Boolean, Number, String
@@ -1384,6 +1384,12 @@ public String getSQLTable() {
13841384
String nt = TABLE_KEY_MAP.get(ot);
13851385
return StringUtil.isEmpty(nt) ? ot : nt;
13861386
}
1387+
1388+
@JSONField(serialize = false)
1389+
public String getSQLTableWithAlias(String table,String alias) {
1390+
return StringUtil.isEmpty(alias) ? table : alias ;
1391+
}
1392+
13871393
@JSONField(serialize = false)
13881394
@Override
13891395
public String getTablePath() {
@@ -1547,7 +1553,7 @@ public String getHavingString(boolean hasPrefix) throws Exception {
15471553
}
15481554

15491555
List<String> raw = getRaw();
1550-
// 提前把 @having& 转为 @having，或者干脆不允许 @raw:"@having&" boolean containRaw = raw != null && (raw.contains(KEY_HAVING) || raw.contains(KEY_HAVING_AND));
1556+
// 提前把 @having& 转为 @having，或者干脆不允许 @raw:"@having&" boolean containRaw = raw != null && (raw.contains(KEY_HAVING) || raw.contains(KEY_HAVING_AND));
15511557
boolean containRaw = raw != null && raw.contains(KEY_HAVING);
15521558

15531559
// 直接把 having 类型从 Map<String, String> 定改为 Map<String, Object>，避免额外拷贝
@@ -2657,12 +2663,12 @@ public String getLimitString() {
26572663
}
26582664

26592665
return getLimitString(
2660-
getPage()
2661-
, getCount()
2662-
, isOracle() || isSQLServer() || isDb2()
2663-
, isOracle() || isDameng() || isKingBase()
2664-
, isPresto() || isTrino()
2665-
);
2666+
getPage()
2667+
, getCount()
2668+
, isOracle() || isSQLServer() || isDb2()
2669+
, isOracle() || isDameng() || isKingBase()
2670+
, isPresto() || isTrino()
2671+
);
26662672
}
26672673
/**获取限制数量及偏移量
26682674
* @param page
@@ -2672,7 +2678,7 @@ public String getLimitString() {
26722678
* @return
26732679
*/
26742680
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle) {
2675-
return getLimitString(page, count, isTSQL, isOracle, false);
2681+
return getLimitString(page, count, isTSQL, isOracle, false);
26762682
}
26772683
/**获取限制数量及偏移量
26782684
* @param page
@@ -2685,17 +2691,17 @@ public static String getLimitString(int page, int count, boolean isTSQL, boolean
26852691
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle, boolean isPresto) {
26862692
int offset = getOffset(page, count);
26872693

2688-
if (isOracle) { // TODO 判断版本，高版本可以用 OFFSET FETCH
2689-
return " WHERE ROWNUM BETWEEN " + offset + " AND " + (offset + count);
2690-
}
2694+
if (isOracle) { // TODO 判断版本，高版本可以用 OFFSET FETCH
2695+
return " WHERE ROWNUM BETWEEN " + offset + " AND " + (offset + count);
2696+
}
26912697

26922698
if (isTSQL) { // OFFSET FECTH 中所有关键词都不可省略, 另外 Oracle 数据库使用子查询加 where 分页
26932699
return " OFFSET " + offset + " ROWS FETCH FIRST " + count + " ROWS ONLY";
26942700
}
26952701

2696-
if (isPresto) { // https://prestodb.io/docs/current/sql/select.html
2697-
return (offset <= 0 ? "" : " OFFSET " + offset) + " LIMIT " + count;
2698-
}
2702+
if (isPresto) { // https://prestodb.io/docs/current/sql/select.html
2703+
return (offset <= 0 ? "" : " OFFSET " + offset) + " LIMIT " + count;
2704+
}
26992705

27002706
return " LIMIT " + count + (offset <= 0 ? "" : " OFFSET " + offset); // DELETE, UPDATE 不支持 OFFSET
27012707
}
@@ -3868,17 +3874,17 @@ public String getRegExpString(String key, String column, String value, boolean i
38683874
if (isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
38693875
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
38703876
}
3871-
if (isPresto() || isTrino()) {
3872-
return "regexp_like(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
3873-
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
3874-
}
3877+
if (isPresto() || isTrino()) {
3878+
return "regexp_like(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
3879+
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
3880+
}
38753881
if (isClickHouse()) {
38763882
return "match(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
38773883
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
38783884
}
3879-
if (isElasticsearch()) {
3880-
return getKey(column) + " RLIKE " + getValue(key, column, value);
3881-
}
3885+
if (isElasticsearch()) {
3886+
return getKey(column) + " RLIKE " + getValue(key, column, value);
3887+
}
38823888
if (isHive()) {
38833889
return (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
38843890
+ " REGEXP " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "");
@@ -4135,7 +4141,7 @@ public String getExistsString(String key, String column, Object value, String ra
41354141
/**WHERE key contains value
41364142
* @param key
41374143
* @param value
4138-
* @return {@link #getContainString(String, String, Object[], int)}
4144+
* @return {@link #getContainString(String, String, Object[], int)}
41394145
* @throws NotExistException
41404146
*/
41414147
@JSONField(serialize = false)
@@ -4196,11 +4202,11 @@ else if (isOracle() || isDameng() || isKingBase()) {
41964202
condition += ("json_textcontains(" + getKey(column) + ", " + (StringUtil.isEmpty(path, true)
41974203
? "'$'" : getValue(key, column, path)) + ", " + getValue(key, column, c == null ? null : c.toString()) + ")");
41984204
}
4199-
else if (isPresto() || isTrino()) {
4200-
condition += ("json_array_contains(cast(" + getKey(column) + " AS VARCHAR), "
4205+
else if (isPresto() || isTrino()) {
4206+
condition += ("json_array_contains(cast(" + getKey(column) + " AS VARCHAR), "
42014207
+ getValue(key, column, c) + (StringUtil.isEmpty(path, true)
42024208
? "" : ", " + getValue(key, column, path)) + ")");
4203-
}
4209+
}
42044210
else {
42054211
String v = c == null ? "null" : (c instanceof Boolean || c instanceof Number ? c.toString() : "\"" + c + "\"");
42064212
if (isClickHouse()) {
@@ -4613,7 +4619,7 @@ protected String getOraclePageSql(String sql) {
46134619
}
46144620
int offset = getOffset(getPage(), count);
46154621
String alias = getAliasWithQuote();
4616-
String quote = getQuote();
4622+
String quote = getQuote();
46174623
return "SELECT * FROM (SELECT " + alias + ".*, ROWNUM "+ quote + "RN" + quote +" FROM (" + sql + ") " + alias
46184624
+ " WHERE ROWNUM <= " + (offset + count) + ") WHERE "+ quote + "RN" + quote +" > " + offset;
46194625
}
@@ -4816,7 +4822,7 @@ protected String concatJoinOn(@NotNull String sql, @NotNull String quote, @NotNu
48164822
String rt = on.getRelateType();
48174823
if (StringUtil.isEmpty(rt, false)) {
48184824
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? " != " : " = ")
4819-
+ quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4825+
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48204826
}
48214827
else {
48224828
onJoinComplexRelation(sql, quote, j, jt, onList, on);
@@ -4828,7 +4834,7 @@ protected String concatJoinOn(@NotNull String sql, @NotNull String quote, @NotNu
48284834
}
48294835

48304836
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + " " + rt + " "
4831-
+ quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4837+
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48324838
}
48334839
else if (rt.endsWith("$")) {
48344840
String t = rt.substring(0, rt.length() - 1);
@@ -4874,11 +4880,11 @@ else if (l > 0 && StringUtil.isName(String.valueOf(l))) {
48744880

48754881
if (l <= 0 && r <= 0) {
48764882
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4877-
+ " LIKE " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4883+
+ " LIKE " + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48784884
}
48794885
else {
48804886
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4881-
+ (l <= 0 ? " LIKE concat(" : " LIKE concat('" + l + "', ") + quote + on.getTargetTable() + quote
4887+
+ (l <= 0 ? " LIKE concat(" : " LIKE concat('" + l + "', ") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote
48824888
+ "." + quote + on.getTargetKey() + quote + (r <= 0 ? ")" : ", '" + r + "')");
48834889
}
48844890
}
@@ -4887,38 +4893,38 @@ else if (rt.endsWith("~")) {
48874893
if (isPostgreSQL() || isInfluxDB()) {
48884894
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote
48894895
+ (isNot ? NOT : "") + " ~" + (ignoreCase ? "* " : " ")
4890-
+ quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4896+
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48914897
}
48924898
else if (isOracle() || isDameng() || isKingBase()) {
48934899
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
4894-
+ ", " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote
4900+
+ ", " + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote
48954901
+ (ignoreCase ? ", 'i'" : ", 'c'") + ")";
48964902
}
4897-
else if (isPresto() || isTrino()) {
4898-
sql += (first ? ON : AND) + "regexp_like(" + (ignoreCase ? "lower(" : "") + quote
4903+
else if (isPresto() || isTrino()) {
4904+
sql += (first ? ON : AND) + "regexp_like(" + (ignoreCase ? "lower(" : "") + quote
48994905
+ jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4900-
+ ", " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable()
4906+
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias())
49014907
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
4902-
}
4908+
}
49034909
else if (isClickHouse()) {
49044910
sql += (first ? ON : AND) + "match(" + (ignoreCase ? "lower(" : "") + quote + jt
49054911
+ quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4906-
+ ", " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable()
4912+
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias())
49074913
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
49084914
}
49094915
else if (isElasticsearch()) {
4910-
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4911-
+ " RLIKE " + quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4912-
}
4916+
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4917+
+ " RLIKE " + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4918+
}
49134919
else if (isHive()) {
4914-
sql += (first ? ON : AND) + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4915-
+ " REGEXP " + (ignoreCase ? "lower(" : "") + quote + on.getTargetTable()
4920+
sql += (first ? ON : AND) + (ignoreCase ? "lower(" : "") + quote + jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4921+
+ " REGEXP " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias())
49164922
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "");
4917-
}
4923+
}
49184924
else {
4919-
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4925+
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
49204926
+ " REGEXP " + (ignoreCase ? "" : "BINARY ")
4921-
+ quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4927+
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
49224928
}
49234929
}
49244930
else if ("{}".equals(rt) || "<>".equals(rt)) {
@@ -4950,12 +4956,12 @@ else if ("{}".equals(rt) || "<>".equals(rt)) {
49504956
String arrKeyPath;
49514957
String itemKeyPath;
49524958
if ("{}".equals(rt)) {
4953-
arrKeyPath = quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4959+
arrKeyPath = quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
49544960
itemKeyPath = quote + jt + quote + "." + quote + on.getKey() + quote;
49554961
}
49564962
else {
49574963
arrKeyPath = quote + jt + quote + "." + quote + on.getKey() + quote;
4958-
itemKeyPath = quote + on.getTargetTable() + quote + "." + quote + on.getTargetKey() + quote;
4964+
itemKeyPath = quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
49594965
}
49604966

49614967
if (isPostgreSQL() || isInfluxDB()) { //operator does not exist: jsonb @> character varying "[" + c + "]");

0 commit comments

Comments
 (0)