Visitar URL original
feat: 支持 KingbaseES MySQL、Oracle、SQL Server 三种兼容模式，感谢 fuziran, xiaoao… · APIJSON/APIJSON@8a8d33e · GitHub
Skip to content

Commit 8a8d33e

Browse files
TommyLemonTommyLemon
authored
feat: 支持 KingbaseES MySQL、Oracle、SQL Server 三种兼容模式，感谢 fuziran, xiaoaozz 的贡献 #868
#868
2 parents 2ea3700 + f5df294 commit 8a8d33e

7 files changed

Lines changed: 1569 additions & 139 deletions

File tree

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

Lines changed: 85 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -154,6 +154,9 @@ public abstract class AbstractSQLConfig<T, M extends Map<String, Object>, L exte
154154
DATABASE_LIST.add(DATABASE_COCKROACHDB);
155155
DATABASE_LIST.add(DATABASE_DAMENG);
156156
DATABASE_LIST.add(DATABASE_KINGBASE);
157+
DATABASE_LIST.add(DATABASE_KINGBASE_MYSQL);
158+
DATABASE_LIST.add(DATABASE_KINGBASE_ORACLE);
159+
DATABASE_LIST.add(DATABASE_KINGBASE_SQLSERVER);
157160
DATABASE_LIST.add(DATABASE_ELASTICSEARCH);
158161
DATABASE_LIST.add(DATABASE_MANTICORE);
159162
DATABASE_LIST.add(DATABASE_CLICKHOUSE);
@@ -894,6 +897,7 @@ public String getUserIdKey() {
894897
private boolean main = true;
895898

896899
private Object id; // Table 的 id
900+
private boolean idGeneratedByAPIJSON;
897901
private Object idIn; // User Table 的 id IN
898902
private Object userId; // Table 的 userId
899903
private Object userIdIn; // Table 的 userId IN
@@ -1011,6 +1015,15 @@ public AbstractSQLConfig<T, M, L> setId(Object id) {
10111015
this.id = id;
10121016
return this;
10131017
}
1018+
@Override
1019+
public boolean isIdGeneratedByAPIJSON() {
1020+
return idGeneratedByAPIJSON;
1021+
}
1022+
@Override
1023+
public AbstractSQLConfig<T, M, L> setIdGeneratedByAPIJSON(boolean generated) {
1024+
this.idGeneratedByAPIJSON = generated;
1025+
return this;
1026+
}
10141027

10151028
@Override
10161029
public Object getIdIn() {
@@ -1084,11 +1097,11 @@ public String gainSQLDatabase() {
10841097

10851098
@Override
10861099
public boolean isTSQL() { // 兼容 TSQL 语法
1087-
return isOracle() || isSQLServer() || isDb2();
1100+
return isOracle() || isSQLServer() || isDb2() || isKingBaseOracle() || isKingBaseSQLServer();
10881101
}
10891102
@Override
10901103
public boolean isMSQL() { // 兼容 MySQL 语法，但不一定可以使用它的 JDBC/ODBC
1091-
return isMySQL() || isTiDB() || isMariaDB() || isSQLite() || isTDengine();
1104+
return isMySQL() || isTiDB() || isMariaDB() || isSQLite() || isTDengine() || isKingBaseMySQL();
10921105
}
10931106
@Override
10941107
public boolean isPSQL() { // 兼容 PostgreSQL 语法，但不一定可以使用它的 JDBC/ODBC
@@ -1172,7 +1185,31 @@ public boolean isKingBase() {
11721185
return isKingBase(gainSQLDatabase());
11731186
}
11741187
public static boolean isKingBase(String db) {
1175-
return DATABASE_KINGBASE.equals(db);
1188+
return KingbaseSQLDialect.from(db).isKingbase();
1189+
}
1190+
1191+
@Override
1192+
public boolean isKingBaseMySQL() {
1193+
return isKingBaseMySQL(gainSQLDatabase());
1194+
}
1195+
public static boolean isKingBaseMySQL(String db) {
1196+
return KingbaseSQLDialect.from(db).isMySQL();
1197+
}
1198+
1199+
@Override
1200+
public boolean isKingBaseOracle() {
1201+
return isKingBaseOracle(gainSQLDatabase());
1202+
}
1203+
public static boolean isKingBaseOracle(String db) {
1204+
return KingbaseSQLDialect.from(db).isOracle();
1205+
}
1206+
1207+
@Override
1208+
public boolean isKingBaseSQLServer() {
1209+
return isKingBaseSQLServer(gainSQLDatabase());
1210+
}
1211+
public static boolean isKingBaseSQLServer(String db) {
1212+
return KingbaseSQLDialect.from(db).isSQLServer();
11761213
}
11771214

11781215
@Override
@@ -1389,6 +1426,10 @@ public String getQuote() { // MongoDB 同时支持 `tbl` 反引号 和 "col"
13891426
if(isElasticsearch() || isManticore() || isIoTDB() || isSurrealDB()) {
13901427
return "";
13911428
}
1429+
KingbaseSQLDialect kingbaseDialect = KingbaseSQLDialect.from(gainSQLDatabase());
1430+
if (kingbaseDialect.isKingbase()) {
1431+
return kingbaseDialect.getIdentifierQuote();
1432+
}
13921433
return isMySQL() || isMariaDB() || isTiDB() || isClickHouse() || isTDengine() || isMilvus() || isDoris() || isStarRocks() ? "`" : "\"";
13931434
}
13941435

@@ -1525,7 +1566,7 @@ public AbstractSQLConfig<T, M, L> setTable(String table) { //Table已经在Parse
15251566
}
15261567

15271568
public String gainAs() {
1528-
return isOracle() || isManticore() ? " " : " AS ";
1569+
return isOracle() || isKingBaseOracle() || isManticore() ? " " : " AS ";
15291570
}
15301571

15311572
@Override
@@ -2074,7 +2115,7 @@ public String gainOrderString(boolean hasPrefix) {
20742115
// return (hasPrefix ? " ORDER BY " : "") + StringUtil.concat(order, joinOrder, ", ");
20752116
// }
20762117

2077-
if (getCount() > 0 && (isSQLServer() || isDb2())) {
2118+
if (getCount() > 0 && (isSQLServer() || isKingBaseSQLServer() || isDb2())) {
20782119
// Oracle, SQL Server, DB2 的 OFFSET 必须加 ORDER BY.去掉Oracle，Oracle里面没有offset关键字
20792120

20802121
// String[] ss = StringUtil.split(order);
@@ -3073,8 +3114,14 @@ else if (isSurrealDB()) {
30733114
}
30743115
}
30753116

3117+
KingbaseSQLDialect kingbaseDialect = KingbaseSQLDialect.from(gainSQLDatabase());
3118+
String kingbaseLimit = kingbaseDialect.getSelectLimit(getOffset(page, count), count);
3119+
if (kingbaseLimit != null) {
3120+
return kingbaseLimit;
3121+
}
3122+
30763123
boolean isOracle = isOracle();
3077-
return gainLimitString(page, count, isTSQL(), isOracle || isDameng() || isKingBase(), isPresto() || isTrino());
3124+
return gainLimitString(page, count, isTSQL(), isOracle || isDameng() || isKingBaseOracle(), isPresto() || isTrino());
30783125
}
30793126
/**获取限制数量及偏移量
30803127
* @param page
@@ -4014,7 +4061,7 @@ public String gainCompareString(String key, String column, Object value, String
40144061
public String gainKey(@NotNull String key) {
40154062
String lenFun = "";
40164063
if (key.endsWith("[")) {
4017-
lenFun = isSQLServer() ? "datalength" : "length";
4064+
lenFun = isSQLServer() || isKingBaseSQLServer() ? "datalength" : "length";
40184065
key = key.substring(0, key.length() - 1);
40194066
}
40204067
else if (key.endsWith("{")) {
@@ -4302,10 +4349,11 @@ public String gainRegExpString(String key, String column, Object[] values, int t
43024349
* @return key REGEXP 'value'
43034350
*/
43044351
public String gainRegExpString(String key, String column, String value, boolean ignoreCase) {
4305-
if (isPSQL()) {
4352+
if (isPSQL() || isKingBaseSQLServer()) {
43064353
return gainKey(column) + " ~" + (ignoreCase ? "* " : " ") + gainValue(key, column, value);
43074354
}
4308-
if (isOracle() || isDameng() || isKingBase() || (isMySQL() && gainDBVersionNums()[0] >= 8)) {
4355+
if (isOracle() || isDameng() || DATABASE_KINGBASE.equals(gainSQLDatabase())
4356+
|| isKingBaseOracle() || isKingBaseMySQL() || (isMySQL() && gainDBVersionNums()[0] >= 8)) {
43094357
return "regexp_like(" + gainKey(column) + ", " + gainValue(key, column, value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
43104358
}
43114359
if (isPresto() || isTrino()) {
@@ -4624,7 +4672,10 @@ public String gainContainString(String key, String column, Object[] childs, int
46244672
condition += (gainKey(column) + " @> " + gainValue(key, column, newJSONArray(c)));
46254673
// operator does not exist: jsonb @> character varying "[" + c + "]");
46264674
}
4627-
else if (isOracle() || isDameng() || isKingBase()) {
4675+
else if (isKingBase() && isKingBaseOracle() == false) {
4676+
condition += (gainKey(column) + "::jsonb @> " + gainValue(key, column, newJSONArray(c)) + "::jsonb");
4677+
}
4678+
else if (isOracle() || isDameng() || isKingBaseOracle()) {
46284679
condition += ("json_textcontains(" + gainKey(column) + ", " + (StringUtil.isEmpty(path, true)
46294680
? "'$'" : gainValue(key, column, path)) + ", " + gainValue(key, column, c == null ? null : c.toString()) + ")");
46304681
}
@@ -4671,7 +4722,7 @@ public List<String> getWithAsExprSQLList() {
46714722
}
46724723
private void clearWithAsExprListIfNeed() {
46734724
// mysql8版本以上,子查询支持with as表达式
4674-
if(this.isMySQL() && this.gainDBVersionNums()[0] >= 8) {
4725+
if((this.isMySQL() || this.isKingBaseMySQL()) && this.gainDBVersionNums()[0] >= 8) {
46754726
this.withAsExprSQLList = new ArrayList<>();
46764727
}
46774728
}
@@ -4725,7 +4776,7 @@ private String withAsExprSubqueryString(SQLConfig<T, M, L> cfg, Subquery<T, M, L
47254776
} else {
47264777
withAsExpreSql = cfg.gainSQL(isPrepared());
47274778
// mysql 才存在这个问题, 主表和子表是一张表
4728-
if (isWithAsEnable && isMySQL() && StringUtil.equals(getTable(), subquery.gainFrom())) {
4779+
if (isWithAsEnable && (isMySQL() || isKingBaseMySQL()) && StringUtil.equals(getTable(), subquery.gainFrom())) {
47294780
withAsExpreSql = " SELECT * FROM (" + withAsExpreSql + ")" + as + quote + subquery.gainKey() + quote;
47304781
}
47314782
}
@@ -4988,20 +5039,23 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> String
49885039
return "ALTER TABLE " + tablePath + " UPDATE" + config.gainSetString() + config.gainWhereString(true);
49895040
}
49905041
cSql = "UPDATE " + tablePath + config.gainSetString() + config.gainWhereString(true)
4991-
+ (config.isMySQL() ? config.gainLimitString() : "");
5042+
+ (config.isMySQL() || KingbaseSQLDialect.from(config.gainSQLDatabase()).supportsDmlLimit() ? config.gainLimitString() : "");
49925043
cSql = buildWithAsExprSql(config, cSql);
49935044
return cSql;
49945045
case DELETE:
49955046
if(config.isClickHouse()){
49965047
return "ALTER TABLE " + tablePath + " DELETE" + config.gainWhereString(true);
49975048
}
49985049
cSql = "DELETE FROM " + tablePath + config.gainWhereString(true)
4999-
+ (config.isMySQL() ? config.gainLimitString() : ""); // PostgreSQL 不允许 LIMIT
5050+
+ (config.isMySQL() || KingbaseSQLDialect.from(config.gainSQLDatabase()).supportsDmlLimit() ? config.gainLimitString() : ""); // PostgreSQL 不允许 LIMIT
50005051
cSql = buildWithAsExprSql(config, cSql);
50015052
return cSql;
50025053
default:
5003-
String explain = config.isExplain() ? (config.isSQLServer() ? "SET STATISTICS PROFILE ON "
5004-
: (config.isOracle() || config.isDameng() || config.isKingBase() ? "EXPLAIN PLAN FOR " : "EXPLAIN ")) : "";
5054+
KingbaseSQLDialect kingbaseDialect = KingbaseSQLDialect.from(config.gainSQLDatabase());
5055+
String kingbaseExplain = kingbaseDialect.getExplainPrefix();
5056+
String explain = config.isExplain() ? (kingbaseExplain != null ? kingbaseExplain
5057+
: (config.isSQLServer() ? "SET STATISTICS PROFILE ON "
5058+
: (config.isOracle() || config.isDameng() ? "EXPLAIN PLAN FOR " : "EXPLAIN "))) : "";
50055059
if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) { // FIXME 为啥是 code 而不是 count ？
50065060
String q = config.getQuote(); // 生成 SELECT ( (24 >=0 AND 24 <3) ) AS `code` LIMIT 1 OFFSET 0
50075061
return explain + "SELECT " + config.gainWhereString(false)
@@ -5010,7 +5064,7 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> String
50105064

50115065
config.setPreparedValueList(new ArrayList<Object>());
50125066
String column = config.gainColumnString();
5013-
if (config.isOracle() || config.isDameng() || config.isKingBase()) {
5067+
if (config.isOracle() || config.isDameng() || config.isKingBaseOracle()) {
50145068
//When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
50155069
//针对oracle分组后条数的统计
50165070
if (StringUtil.isNotEmpty(config.getGroup(),true) && RequestMethod.isHeadMethod(config.getMethod(), true)){
@@ -5053,7 +5107,7 @@ private static <T, M extends Map<String, Object>, L extends List<Object>> String
50535107

50545108
@Override
50555109
public boolean isWithAsEnable() {
5056-
return ENABLE_WITH_AS && (isMySQL() == false || gainDBVersionNums()[0] >= 8);
5110+
return ENABLE_WITH_AS && ((isMySQL() || isKingBaseMySQL()) == false || gainDBVersionNums()[0] >= 8);
50575111
}
50585112

50595113
/**Oracle的分页获取
@@ -5358,7 +5412,11 @@ else if (rt.endsWith("~")) {
53585412
if (isPSQL()) {
53595413
sql += (first ? ON : AND) + lk + (isNot ? NOT : "") + " ~" + (ignoreCase ? "* " : " ") + rk;
53605414
}
5361-
else if (isOracle() || isDameng() || isKingBase()) {
5415+
else if (isKingBaseSQLServer()) {
5416+
sql += (first ? ON : AND) + lk + (isNot ? NOT : "") + " ~" + (ignoreCase ? "* " : " ") + rk;
5417+
}
5418+
else if (isOracle() || isDameng() || DATABASE_KINGBASE.equals(gainSQLDatabase())
5419+
|| isKingBaseOracle() || isKingBaseMySQL()) {
53625420
sql += (first ? ON : AND) + "regexp_like(" + lk + ", " + rk + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
53635421
}
53645422
else if (isPresto() || isTrino()) {
@@ -5414,7 +5472,11 @@ else if ("{}".equals(rt) || "<>".equals(rt)) {
54145472
sql += (first ? ON : AND) + (isNot ? "( " : "") + gainCondition(isNot, arrKeyPath
54155473
+ " IS NOT NULL AND " + arrKeyPath + " @> " + itemKeyPath) + (isNot ? ") " : "");
54165474
}
5417-
else if (isOracle() || isDameng() || isKingBase()) {
5475+
else if (isKingBase() && isKingBaseOracle() == false) {
5476+
sql += (first ? ON : AND) + (isNot ? "( " : "") + gainCondition(isNot, arrKeyPath
5477+
+ " IS NOT NULL AND " + arrKeyPath + "::jsonb @> " + itemKeyPath + "::jsonb") + (isNot ? ") " : "");
5478+
}
5479+
else if (isOracle() || isDameng() || isKingBaseOracle()) {
54185480
sql += (first ? ON : AND) + (isNot ? "( " : "") + gainCondition(isNot, arrKeyPath
54195481
+ " IS NOT NULL AND json_textcontains(" + arrKeyPath
54205482
+ ", '$', " + itemKeyPath + ")") + (isNot ? ") " : "");
@@ -5545,8 +5607,10 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> SQLConf
55455607
}
55465608

55475609
Object id = request.get(idKey);
5610+
boolean idGeneratedByAPIJSON = false;
55485611
if (id == null && method == POST) {
55495612
id = callback.newId(method, database, datasource, namespace, catalog, schema, table); // null 表示数据库自增 id
5613+
idGeneratedByAPIJSON = id != null;
55505614
}
55515615

55525616
if (id != null) { // null 无效
@@ -6177,6 +6241,7 @@ else if (keyMap != null) {
61776241

61786242
config.setRole(role);
61796243
config.setId(id);
6244+
config.setIdGeneratedByAPIJSON(idGeneratedByAPIJSON);
61806245
config.setIdIn(idIn);
61816246
config.setUserId(userId);
61826247
config.setUserIdIn(userIdIn);

0 commit comments

Comments
 (0)