FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

feat: UPSERT compat Postgres, Oracle, DB2, SQLServer, SQLite ... · APIJSON/APIJSON@e521cf4 · GitHub

/ APIJSON Public

Commit e521cf4

Browse files
TommyLemon
committed
feat: UPSERT compat Postgres, Oracle, DB2, SQLServer, SQLite ...
1 parent 9f64b61 commit e521cf4

2 files changed

Lines changed: 191 additions & 63 deletions

File tree

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

Lines changed: 188 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -86,7 +86,7 @@ public abstract class AbstractSQLConfig<T, M extends Map<String, Object>, L exte
8686
* 表名映射，隐藏真实表名，对安全要求很高的表可以这么做
8787
*/
8888
public static Map<String, String> TABLE_KEY_MAP;
89-
public static Map<String, String> UPSERT_TABLE_MAP;
89+
public static Map<String, Object> UPSERT_TABLE_MAP;
9090
/**
9191
* 字段名映射，隐藏真实字段名，对安全要求很高的表可以这么做，另外可以配置 name_tag:(name,tag) 来实现多字段 IN，length_tag:length(tag) 来实现 SQL 函数复杂条件
9292
*/
@@ -1190,23 +1190,23 @@ public boolean isKingBase() {
11901190
public static boolean isKingBase(String db) {
11911191
return KingbaseSQLDialect.from(db).isKingbase();
11921192
}
1193-
1193+
11941194
@Override
11951195
public boolean isKingBaseMySQL() {
11961196
return isKingBaseMySQL(gainSQLDatabase());
11971197
}
11981198
public static boolean isKingBaseMySQL(String db) {
11991199
return KingbaseSQLDialect.from(db).isMySQL();
12001200
}
1201-
1201+
12021202
@Override
12031203
public boolean isKingBaseOracle() {
12041204
return isKingBaseOracle(gainSQLDatabase());
12051205
}
12061206
public static boolean isKingBaseOracle(String db) {
12071207
return KingbaseSQLDialect.from(db).isOracle();
12081208
}
1209-
1209+
12101210
@Override
12111211
public boolean isKingBaseSQLServer() {
12121212
return isKingBaseSQLServer(gainSQLDatabase());
@@ -2898,7 +2898,7 @@ else if (ck.contains("`") || ck.contains("'") || origin.startsWith("_") || origi
28982898
public List<List<Object>> getValues() {
28992899
return values;
29002900
}
2901-
public String getValuesString() {
2901+
public String gainValuesString() {
29022902
String s = "";
29032903
if (values != null && values.size() > 0) {
29042904
Object[] items = new Object[values.size()];
@@ -5036,70 +5036,198 @@ public static <T, M extends Map<String, Object>, L extends List<Object>> String
50365036
String cSql;
50375037
switch (method) {
50385038
case POST:
5039-
String s = UPSERT_TABLE_MAP.get(table);
5040-
// 暂不支持，UNIQUE KEY 应该比较固定不会频繁改动，而且改了也是同步改后端配置，和前端传参关系不大
5041-
// Object obj = UPSERT_TABLE_MAP.get(table);
5042-
// if (obj instanceof String) {
5043-
// s += obj;
5044-
// } else if (obj instanceof Map<?, ?>) { // { "a+": 1, "b-": 1 }
5045-
// Map<String, Object> setObj = (Map<String, Object>) obj;
5046-
// AbstractSQLConfig<T, M, L> cfg = gainParser().createSQLConfig();
5047-
// s = cfg.gainSetString();
5048-
// }
5049-
5050-
return "INSERT INTO " + tablePath + config.gainColumnString() + " VALUES" + config.getValuesString()
5051-
+ (StringUtil.isEmpty(s) ? "" : " ON DUPLICATE KEY UPDATE " + s);
5039+
return config.gainUpsertSQL();
50525040
case PUT:
5053-
if(config.isClickHouse()){
5054-
return "ALTER TABLE " + tablePath + " UPDATE" + config.gainSetString() + config.gainWhereString(true);
5055-
}
5056-
cSql = "UPDATE " + tablePath + config.gainSetString() + config.gainWhereString(true)
5057-
+ (config.isMySQL() || KingbaseSQLDialect.from(config.gainSQLDatabase()).supportsDmlLimit() ? config.gainLimitString() : "");
5058-
cSql = buildWithAsExprSql(config, cSql);
5059-
return cSql;
5041+
return config.gainUpdateSQL();
50605042
case DELETE:
5061-
if(config.isClickHouse()){
5062-
return "ALTER TABLE " + tablePath + " DELETE" + config.gainWhereString(true);
5063-
}
5064-
cSql = "DELETE FROM " + tablePath + config.gainWhereString(true)
5065-
+ (config.isMySQL() || KingbaseSQLDialect.from(config.gainSQLDatabase()).supportsDmlLimit() ? config.gainLimitString() : ""); // PostgreSQL 不允许 LIMIT
5066-
cSql = buildWithAsExprSql(config, cSql);
5067-
return cSql;
5043+
return config.gainDeleteSQL();
50685044
default:
5069-
KingbaseSQLDialect kingbaseDialect = KingbaseSQLDialect.from(config.gainSQLDatabase());
5070-
String kingbaseExplain = kingbaseDialect.getExplainPrefix();
5071-
String explain = config.isExplain() ? (kingbaseExplain != null ? kingbaseExplain
5072-
: (config.isSQLServer() ? "SET STATISTICS PROFILE ON "
5073-
: (config.isOracle() || config.isDameng() ? "EXPLAIN PLAN FOR " : "EXPLAIN "))) : "";
5074-
if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) { // FIXME 为啥是 code 而不是 count ？
5075-
String q = config.getQuote(); // 生成 SELECT ( (24 >=0 AND 24 <3) ) AS `code` LIMIT 1 OFFSET 0
5076-
return explain + "SELECT " + config.gainWhereString(false)
5077-
+ config.gainAs() + q + JSONResponse.KEY_COUNT + q + config.gainLimitString();
5078-
}
5079-
5080-
config.setPreparedValueList(new ArrayList<Object>());
5081-
String column = config.gainColumnString();
5082-
if (config.isOracle() || config.isDameng() || config.isKingBaseOracle()) {
5083-
//When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
5084-
//针对oracle分组后条数的统计
5085-
if (StringUtil.isNotEmpty(config.getGroup(),true) && RequestMethod.isHeadMethod(config.getMethod(), true)){
5086-
return explain + "SELECT count(*) FROM (SELECT " + (config.getCache() == JSONMap.CACHE_RAM
5087-
? "SQL_NO_CACHE " : "") + column + " FROM " + gainConditionString(tablePath, config) + ") " + config.gainLimitString();
5045+
return config.gainSelectSQL();
5046+
}
5047+
}
5048+
5049+
public static String gainInsertSQL(String tablePath, String columns, String values) {
5050+
return "INSERT INTO " + tablePath + columns + " VALUES" + values;
5051+
}
5052+
5053+
public String gainUpsertSQL() throws Exception {
5054+
String table = getTable();
5055+
Object obj = UPSERT_TABLE_MAP.get(table);
5056+
String tablePath = gainTablePath();
5057+
if (obj == null) {
5058+
return gainInsertSQL(tablePath, gainColumnString(), gainValuesString());
5059+
}
5060+
5061+
String database = gainSQLDatabase();
5062+
String q = getQuote();
5063+
5064+
String key = "";
5065+
String setStr = "";
5066+
if (obj instanceof String) { // MySQL/PostgreSQL 简单写 version = version + 1
5067+
setStr = (String) obj;
5068+
} else if (obj instanceof Map<?, ?>) {
5069+
// PostgreSQL 写 { "": "excluded.version = Method.version" }
5070+
// 或 自定义 CONFLICT key 写 { "package,method": "excluded.version = Method.version + 1" }
5071+
// 或多数据库写 { "Oracle": { "method,tag": "excluded.version = Request.version + 1" }, "DB2": {...} } 等
5072+
5073+
Map<String, Object> setObj = (Map<String, Object>) obj;
5074+
Object dbSetObj = setObj.get(database);
5075+
if (dbSetObj instanceof String) {
5076+
setStr = (String) dbSetObj;
5077+
} else {
5078+
boolean isDBSet = dbSetObj instanceof Map<?, ?>;
5079+
if (isDBSet) {
5080+
setObj = (Map<String, Object>) dbSetObj;
5081+
} else if (dbSetObj != null) {
5082+
throw new IllegalArgumentException("UPSERT_TABLE_MAP 中 " + table + ": {" + database + ": value} 中 value 不合法，必须是 String 或 Map<String, Object> 类型！");
5083+
}
5084+
5085+
Set<Entry<String, Object>> set = setObj.entrySet();
5086+
boolean first = true;
5087+
for (Entry<String, Object> entry : set) {
5088+
String k = entry == null ? null : entry.getKey();
5089+
Object v = k == null ? null : entry.getValue();
5090+
if (v == null || (isDBSet == false && DATABASE_LIST.contains(k))) {
5091+
continue;
50885092
}
50895093

5090-
String sql = "SELECT " + (config.getCache() == JSONMap.CACHE_RAM
5091-
? "SQL_NO_CACHE " : "") + column + " FROM " + gainConditionString(tablePath, config);
5092-
return explain + config.gainOraclePageSQL(sql);
5094+
if (StringUtil.isEmpty(k)) {
5095+
k = q + getIdKey() + q;
5096+
} else if (!(k.contains(",") || (k.startsWith("`") && k.endsWith("`")) || (k.startsWith("\"") && k.endsWith("\"")))) {
5097+
k = q + k + q;
5098+
}
5099+
5100+
key += (first ? "" : ", ") + k;
5101+
setStr += (first ? "" : ", ") + v; // 自定义表达式不好自动处理，后端配置正确即可 gainValue(k, k, v);
5102+
first = false;
50935103
}
5104+
}
5105+
} else {
5106+
throw new IllegalArgumentException("UPSERT_TABLE_MAP 中 " + table + ": value 中 value 不合法，必须是 String 或 Map<String, Object> 类型！");
5107+
}
5108+
5109+
if (StringUtil.isEmpty(setStr)) {
5110+
return gainInsertSQL(tablePath, gainColumnString(), gainValuesString());
5111+
}
5112+
5113+
String columns = gainColumnString();
5114+
5115+
if (isPostgreSQL(database) || isSQLite(database) || isCockroachDB(database) || isTimescaleDB(database)) {
5116+
if (StringUtil.isEmpty(key)) {
5117+
key = q + getIdKey() + q;
5118+
}
5119+
return gainInsertSQL(tablePath, columns, gainValuesString()) + " ON CONFLICT(" + key + ") DO UPDATE SET " + setStr;
5120+
}
5121+
5122+
boolean isDB2 = isDb2(database);
5123+
boolean isOracle = isDB2 == false && isOracle(database);
5124+
boolean isDameng = isDB2 == false && isOracle == false && isDameng(database);
5125+
if (isDB2 || isOracle || isDameng || isSQLServer(database)) {
5126+
String alias = gainSQLAlias();
5127+
String idKey = getIdKey();
5128+
5129+
String vs;
5130+
String cvs = "";
50945131

5095-
cSql = "SELECT " + (config.getCache() == JSONMap.CACHE_RAM ? "SQL_NO_CACHE " : "")
5096-
+ column + " FROM " + gainConditionString(tablePath, config) + config.gainLimitString();
5097-
cSql = buildWithAsExprSql(config, cSql);
5098-
if(config.isElasticsearch()) { // elasticSearch 不支持 explain
5099-
return cSql;
5132+
vs = "";
5133+
List<String> cols = getColumn();
5134+
List<List<Object>> valss = cols == null || cols.isEmpty() ? null : getValues();
5135+
if (valss == null || valss.size() != 1) {
5136+
throw new IllegalArgumentException("POST " + table + " 不合法，UPSERT 模式下 VALUES 必须是 1 条记录！");
5137+
}
5138+
5139+
List<Object> vals = valss.get(0);
5140+
boolean first = true;
5141+
for (int i = 0; i < cols.size(); i++) {
5142+
String c = cols.get(i);
5143+
if (c == null) { // FIXME 带上 id 是否有问题？ || idKey.equals(c)) {
5144+
continue;
51005145
}
5101-
return explain + cSql;
5146+
5147+
Object v = vals.get(i);
5148+
String s = gainValue(c, c, v) + (isDB2 ? "" : " " + q + c + q);
5149+
5150+
vs += (first ? "" : ", ") + s;
5151+
cvs += (first ? "" : ", ") + "excluded." + q + c + q;
5152+
first = false;
5153+
}
5154+
5155+
if (StringUtil.isEmpty(vs)) {
5156+
throw new IllegalArgumentException("POST " + table + " 不合法，UPSERT 模式下 VALUES 必须是 1 条记录！");
5157+
}
5158+
5159+
vs = isDB2 ? "VALUES(" + vs + ")" : "SELECT " + vs + (isOracle || isDameng ? " FROM dual" : "");
5160+
5161+
idKey = q + idKey + q;
5162+
return "MERGE INTO " + tablePath + " " + alias + " USING (\n " + vs + "\n) excluded"
5163+
+ (isDB2 ? columns : "") + " ON " + alias + "." + idKey + " = excluded." + idKey + " \nWHEN MATCHED THEN \n UPDATE SET "
5164+
+ setStr + " \nWHEN NOT MATCHED THEN \n INSERT" + columns + " VALUES(" + cvs + ")";
5165+
}
5166+
5167+
return gainInsertSQL(tablePath, columns, gainValuesString()) + " ON DUPLICATE KEY UPDATE " + setStr;
5168+
}
5169+
5170+
public String gainUpdateSQL() throws Exception {
5171+
String tablePath = gainTablePath();
5172+
if (isClickHouse()){
5173+
return "ALTER TABLE " + tablePath + " UPDATE" + gainSetString() + gainWhereString(true);
5174+
}
5175+
5176+
String cSql = "UPDATE " + tablePath + gainSetString() + gainWhereString(true)
5177+
+ (isMySQL() || KingbaseSQLDialect.from(gainSQLDatabase()).supportsDmlLimit() ? gainLimitString() : "");
5178+
cSql = buildWithAsExprSql(this, cSql);
5179+
return cSql;
5180+
}
5181+
5182+
public String gainDeleteSQL() throws Exception {
5183+
String tablePath = gainTablePath();
5184+
if(isClickHouse()){
5185+
return "ALTER TABLE " + tablePath + " DELETE" + gainWhereString(true);
5186+
}
5187+
5188+
String cSql = "DELETE FROM " + tablePath + gainWhereString(true)
5189+
+ (isMySQL() || KingbaseSQLDialect.from(gainSQLDatabase()).supportsDmlLimit() ? gainLimitString() : ""); // PostgreSQL 不允许 LIMIT
5190+
cSql = buildWithAsExprSql(this, cSql);
5191+
return cSql;
5192+
}
5193+
5194+
public String gainSelectSQL() throws Exception {
5195+
KingbaseSQLDialect kingbaseDialect = KingbaseSQLDialect.from(gainSQLDatabase());
5196+
String kingbaseExplain = kingbaseDialect.getExplainPrefix();
5197+
String explain = isExplain() ? (kingbaseExplain != null ? kingbaseExplain
5198+
: (isSQLServer() ? "SET STATISTICS PROFILE ON "
5199+
: (isOracle() || isDameng() ? "EXPLAIN PLAN FOR " : "EXPLAIN "))) : "";
5200+
if (isTest() && RequestMethod.isGetMethod(getMethod(), true)) { // FIXME 为啥是 code 而不是 count ？
5201+
String q = getQuote(); // 生成 SELECT ( (24 >=0 AND 24 <3) ) AS `code` LIMIT 1 OFFSET 0
5202+
return explain + "SELECT " + gainWhereString(false)
5203+
+ gainAs() + q + JSONResponse.KEY_COUNT + q + gainLimitString();
51025204
}
5205+
5206+
setPreparedValueList(new ArrayList<>());
5207+
5208+
String tablePath = gainTablePath();
5209+
String column = gainColumnString();
5210+
if (isOracle() || isDameng() || isKingBaseOracle()) {
5211+
//When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
5212+
//针对oracle分组后条数的统计
5213+
if (StringUtil.isNotEmpty(getGroup(),true) && RequestMethod.isHeadMethod(getMethod(), true)){
5214+
return explain + "SELECT count(*) FROM (SELECT " + (getCache() == JSONMap.CACHE_RAM
5215+
? "SQL_NO_CACHE " : "") + column + " FROM " + gainConditionString(tablePath, this) + ") " + gainLimitString();
5216+
}
5217+
5218+
String sql = "SELECT " + (getCache() == JSONMap.CACHE_RAM
5219+
? "SQL_NO_CACHE " : "") + column + " FROM " + gainConditionString(tablePath, this);
5220+
return explain + gainOraclePageSQL(sql);
5221+
}
5222+
5223+
String cSql = "SELECT " + (getCache() == JSONMap.CACHE_RAM ? "SQL_NO_CACHE " : "")
5224+
+ column + " FROM " + gainConditionString(tablePath, this) + gainLimitString();
5225+
cSql = buildWithAsExprSql(this, cSql);
5226+
if(isElasticsearch()) { // elasticSearch 不支持 explain
5227+
return cSql;
5228+
}
5229+
5230+
return explain + cSql;
51035231
}
51045232

51055233
private static <T, M extends Map<String, Object>, L extends List<Object>> String buildWithAsExprSql(@NotNull AbstractSQLConfig<T, M, L> config, String cSql) throws Exception {

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -27,9 +27,9 @@ public interface SQLConfig<T, M extends Map<String, Object>, L extends List<Obje
2727
String DATABASE_COCKROACHDB = "COCKROACHDB"; // https://www.cockroachlabs.com
2828
String DATABASE_DAMENG = "DAMENG"; // https://www.dameng.com
2929
String DATABASE_KINGBASE = "KINGBASE"; // https://www.kingbase.com.cn
30-
String DATABASE_KINGBASE_MYSQL = "KINGBASE-MYSQL"; // Kingbase in MySQL compatibility mode
31-
String DATABASE_KINGBASE_ORACLE = "KINGBASE-ORACLE"; // Kingbase in Oracle compatibility mode
32-
String DATABASE_KINGBASE_SQLSERVER = "KINGBASE-SQLSERVER"; // Kingbase in SQL Server compatibility mode
30+
String DATABASE_KINGBASE_MYSQL = "KINGBASE_MYSQL"; // Kingbase in MySQL compatibility mode
31+
String DATABASE_KINGBASE_ORACLE = "KINGBASE_ORACLE"; // Kingbase in Oracle compatibility mode
32+
String DATABASE_KINGBASE_SQLSERVER = "KINGBASE_SQLSERVER"; // Kingbase in SQL Server compatibility mode
3333
String DATABASE_ELASTICSEARCH = "ELASTICSEARCH"; // https://www.elastic.co/guide/en/elasticsearch/reference/7.4/xpack-sql.html
3434
String DATABASE_MANTICORE = "MANTICORE"; // https://manticoresearch.com
3535
String DATABASE_CLICKHOUSE = "CLICKHOUSE"; // https://clickhouse.com

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL