Skip to content

Commit 6bfb218

Browse files
TommyLemonTommyLemon
authored
getSQLTableWithAlias 带上原表名，避免 alias 与其它表名/字段 冲突；优化 GitHub 对 tab 和 IDE 处理不一致导致缩进显示问题
1 parent 746f291 commit 6bfb218

1 file changed

Lines changed: 52 additions & 52 deletions

File tree

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

Lines changed: 52 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -143,9 +143,9 @@ public abstract class AbstractSQLConfig<T extends Object> implements SQLConfig<T
143143
* 字段名映射，隐藏真实字段名，对安全要求很高的表可以这么做，另外可以配置 name_tag:(name,tag) 来实现多字段 IN，length_tag:length(tag) 来实现 SQL 函数复杂条件
144144
*/
145145
public static Map<String, String> COLUMN_KEY_MAP;
146-
/**
147-
* 允许批量增删改部分记录失败的表
148-
*/
146+
/**
147+
* 允许批量增删改部分记录失败的表
148+
*/
149149
public static Map<String, String> ALLOW_PARTIAL_UPDATE_FAIL_TABLE_MAP;
150150
public static List<String> CONFIG_TABLE_LIST;
151151
public static List<String> DATABASE_LIST;
@@ -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
@@ -1117,7 +1117,7 @@ public static boolean isMySQL(String db) {
11171117
return DATABASE_MYSQL.equals(db);
11181118
}
11191119

1120-
@Override
1120+
@Override
11211121
public boolean isPostgreSQL() {
11221122
return isPostgreSQL(getSQLDatabase());
11231123
}
@@ -1133,7 +1133,7 @@ public static boolean isSQLServer(String db) {
11331133
return DATABASE_SQLSERVER.equals(db);
11341134
}
11351135

1136-
@Override
1136+
@Override
11371137
public boolean isOracle() {
11381138
return isOracle(getSQLDatabase());
11391139
}
@@ -1149,15 +1149,15 @@ public static boolean isDb2(String db) {
11491149
return DATABASE_DB2.equals(db);
11501150
}
11511151

1152-
@Override
1152+
@Override
11531153
public boolean isMariaDB() {
11541154
return isMariaDB(getSQLDatabase());
11551155
}
11561156
public static boolean isMariaDB(String db) {
11571157
return DATABASE_MARIADB.equals(db);
11581158
}
11591159

1160-
@Override
1160+
@Override
11611161
public boolean isTiDB() {
11621162
return isTiDB(getSQLDatabase());
11631163
}
@@ -1387,7 +1387,7 @@ public String getSQLTable() {
13871387

13881388
@JSONField(serialize = false)
13891389
public String getSQLTableWithAlias(String table,String alias) {
1390-
return StringUtil.isEmpty(alias) ? table : alias ;
1390+
return StringUtil.isEmpty(alias) ? table : table + ":" + alias; // 带上原表名，避免 alias 和其它表名/字段名冲突
13911391
}
13921392

13931393
@JSONField(serialize = false)
@@ -2678,8 +2678,8 @@ public String getLimitString() {
26782678
* @return
26792679
*/
26802680
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle) {
2681-
return getLimitString(page, count, isTSQL, isOracle, false);
2682-
}
2681+
return getLimitString(page, count, isTSQL, isOracle, false);
2682+
}
26832683
/**获取限制数量及偏移量
26842684
* @param page
26852685
* @param count
@@ -2691,17 +2691,17 @@ public static String getLimitString(int page, int count, boolean isTSQL, boolean
26912691
public static String getLimitString(int page, int count, boolean isTSQL, boolean isOracle, boolean isPresto) {
26922692
int offset = getOffset(page, count);
26932693

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

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

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

27062706
return " LIMIT " + count + (offset <= 0 ? "" : " OFFSET " + offset); // DELETE, UPDATE 不支持 OFFSET
27072707
}
@@ -3874,17 +3874,17 @@ public String getRegExpString(String key, String column, String value, boolean i
38743874
if (isOracle() || isDameng() || isKingBase() || (isMySQL() && getDBVersionNums()[0] >= 8)) {
38753875
return "regexp_like(" + getKey(column) + ", " + getValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
38763876
}
3877-
if (isPresto() || isTrino()) {
3878-
return "regexp_like(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
3877+
if (isPresto() || isTrino()) {
3878+
return "regexp_like(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
38793879
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
3880-
}
3880+
}
38813881
if (isClickHouse()) {
38823882
return "match(" + (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
38833883
+ ", " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "") + ")";
38843884
}
3885-
if (isElasticsearch()) {
3886-
return getKey(column) + " RLIKE " + getValue(key, column, value);
3887-
}
3885+
if (isElasticsearch()) {
3886+
return getKey(column) + " RLIKE " + getValue(key, column, value);
3887+
}
38883888
if (isHive()) {
38893889
return (ignoreCase ? "lower(" : "") + getKey(column) + (ignoreCase ? ")" : "")
38903890
+ " REGEXP " + (ignoreCase ? "lower(" : "") + getValue(key, column, value) + (ignoreCase ? ")" : "");
@@ -4202,11 +4202,11 @@ else if (isOracle() || isDameng() || isKingBase()) {
42024202
condition += ("json_textcontains(" + getKey(column) + ", " + (StringUtil.isEmpty(path, true)
42034203
? "'$'" : getValue(key, column, path)) + ", " + getValue(key, column, c == null ? null : c.toString()) + ")");
42044204
}
4205-
else if (isPresto() || isTrino()) {
4206-
condition += ("json_array_contains(cast(" + getKey(column) + " AS VARCHAR), "
4205+
else if (isPresto() || isTrino()) {
4206+
condition += ("json_array_contains(cast(" + getKey(column) + " AS VARCHAR), "
42074207
+ getValue(key, column, c) + (StringUtil.isEmpty(path, true)
42084208
? "" : ", " + getValue(key, column, path)) + ")");
4209-
}
4209+
}
42104210
else {
42114211
String v = c == null ? "null" : (c instanceof Boolean || c instanceof Number ? c.toString() : "\"" + c + "\"");
42124212
if (isClickHouse()) {
@@ -4619,7 +4619,7 @@ protected String getOraclePageSql(String sql) {
46194619
}
46204620
int offset = getOffset(getPage(), count);
46214621
String alias = getAliasWithQuote();
4622-
String quote = getQuote();
4622+
String quote = getQuote();
46234623
return "SELECT * FROM (SELECT " + alias + ".*, ROWNUM "+ quote + "RN" + quote +" FROM (" + sql + ") " + alias
46244624
+ " WHERE ROWNUM <= " + (offset + count) + ") WHERE "+ quote + "RN" + quote +" > " + offset;
46254625
}
@@ -4796,10 +4796,10 @@ public String getJoinString() throws Exception {
47964796

47974797

47984798
//if (changed) {
4799-
// List<Object> opvl = getPreparedValueList();
4800-
// if (opvl != null && opvl.isEmpty() == false) {
4801-
// pvl.addAll(opvl);
4802-
// }
4799+
// List<Object> opvl = getPreparedValueList();
4800+
// if (opvl != null && opvl.isEmpty() == false) {
4801+
// pvl.addAll(opvl);
4802+
// }
48034803
setPreparedValueList(pvl);
48044804
//}
48054805

@@ -4822,7 +4822,7 @@ protected String concatJoinOn(@NotNull String sql, @NotNull String quote, @NotNu
48224822
String rt = on.getRelateType();
48234823
if (StringUtil.isEmpty(rt, false)) {
48244824
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? " != " : " = ")
4825-
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4825+
+ quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48264826
}
48274827
else {
48284828
onJoinComplexRelation(sql, quote, j, jt, onList, on);
@@ -4834,7 +4834,7 @@ protected String concatJoinOn(@NotNull String sql, @NotNull String quote, @NotNu
48344834
}
48354835

48364836
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + " " + rt + " "
4837-
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4837+
+ quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48384838
}
48394839
else if (rt.endsWith("$")) {
48404840
String t = rt.substring(0, rt.length() - 1);
@@ -4880,11 +4880,11 @@ else if (l > 0 && StringUtil.isName(String.valueOf(l))) {
48804880

48814881
if (l <= 0 && r <= 0) {
48824882
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4883-
+ " LIKE " + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4883+
+ " LIKE " + quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48844884
}
48854885
else {
48864886
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote + (isNot ? NOT : "")
4887-
+ (l <= 0 ? " LIKE concat(" : " LIKE concat('" + l + "', ") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote
4887+
+ (l <= 0 ? " LIKE concat(" : " LIKE concat('" + l + "', ") + quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote
48884888
+ "." + quote + on.getTargetKey() + quote + (r <= 0 ? ")" : ", '" + r + "')");
48894889
}
48904890
}
@@ -4893,38 +4893,38 @@ else if (rt.endsWith("~")) {
48934893
if (isPostgreSQL() || isInfluxDB()) {
48944894
sql += (first ? ON : AND) + quote + jt + quote + "." + quote + on.getKey() + quote
48954895
+ (isNot ? NOT : "") + " ~" + (ignoreCase ? "* " : " ")
4896-
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4896+
+ quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
48974897
}
48984898
else if (isOracle() || isDameng() || isKingBase()) {
48994899
sql += (first ? ON : AND) + "regexp_like(" + quote + jt + quote + "." + quote + on.getKey() + quote
4900-
+ ", " + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote
4900+
+ ", " + quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote
49014901
+ (ignoreCase ? ", 'i'" : ", 'c'") + ")";
49024902
}
4903-
else if (isPresto() || isTrino()) {
4904-
sql += (first ? ON : AND) + "regexp_like(" + (ignoreCase ? "lower(" : "") + quote
4903+
else if (isPresto() || isTrino()) {
4904+
sql += (first ? ON : AND) + "regexp_like(" + (ignoreCase ? "lower(" : "") + quote
49054905
+ jt + quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4906-
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias())
4906+
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias())
49074907
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
4908-
}
4908+
}
49094909
else if (isClickHouse()) {
49104910
sql += (first ? ON : AND) + "match(" + (ignoreCase ? "lower(" : "") + quote + jt
49114911
+ quote + "." + quote + on.getKey() + quote + (ignoreCase ? ")" : "")
4912-
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias())
4912+
+ ", " + (ignoreCase ? "lower(" : "") + quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias())
49134913
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "") + ")";
49144914
}
49154915
else if (isElasticsearch()) {
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-
}
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+
}
49194919
else if (isHive()) {
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())
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())
49224922
+ quote + "." + quote + on.getTargetKey() + quote + (ignoreCase ? ")" : "");
4923-
}
4923+
}
49244924
else {
4925-
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 : "")
49264926
+ " REGEXP " + (ignoreCase ? "" : "BINARY ")
4927-
+ quote + getSQLTableWithAlias(on.getTargetTable(),on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
4927+
+ quote + getSQLTableWithAlias(on.getTargetTable(), on.getTargetAlias()) + quote + "." + quote + on.getTargetKey() + quote;
49284928
}
49294929
}
49304930
else if ("{}".equals(rt) || "<>".equals(rt)) {

0 commit comments

Comments
 (0)