Skip to content

Commit 7b17747

Browse files
TommyLemonTommyLemon
committed
Server: 完善 Oracle 和 SQL Server 的正则匹配、包含选项、子查询 等功能
1 parent 3d860de commit 7b17747

3 files changed

Lines changed: 38 additions & 23 deletions

File tree

‎APIJSON-Java-Server/APIJSONORM/src/main/java/zuo/biao/apijson/SQL.java‎

Lines changed: 19 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -179,30 +179,39 @@ public static String subString(String s, int start, int end) {
179179

180180
/**
181181
* @param s
182-
* @param c -> 'c'
183-
* @return "instr(" + s + ", '" + c + "')"
182+
* @param c
183+
* @return "instr(" + s + ", " + c + ")"
184184
*/
185185
public static String indexOf(String s, String c) {
186-
return "instr(" + s + ", '" + c + "')";
186+
return "instr(" + s + ", " + c + ")";
187187
}
188188

189189
/**
190190
* @param s
191-
* @param c1 -> 'c1'
192-
* @param c2 -> 'c2'
193-
* @return "replace(" + s + ", '" + c1 + "', '" + c2 + "')"
191+
* @param c1
192+
* @param c2
193+
* @return "replace(" + s + ", " + c1 + ", " + c2 + ")"
194194
*/
195195
public static String replace(String s, String c1, String c2) {
196-
return "replace(" + s + ", '" + c1 + "', '" + c2 + "')";
196+
return "replace(" + s + ", " + c1 + ", " + c2 + ")";
197+
}
198+
199+
/**
200+
* @param s1
201+
* @param s2
202+
* @return "concat(" + s1 + ", " + s2 + ")"
203+
*/
204+
public static String concat(String s1, String s2) {
205+
return "concat(" + s1 + ", " + s2 + ")";
197206
}
198207

199208
/**
200209
* @param s1
201-
* @param s2 -> 's2'
202-
* @return "strcmp(" + s1 + ", '" + s2 + "')"
210+
* @param s2
211+
* @return "strcmp(" + s1 + ", " + s2 + ")"
203212
*/
204213
public static String equals(String s1, String s2) {
205-
return "strcmp(" + s1 + ", '" + s2 + "')";
214+
return "strcmp(" + s1 + ", " + s2 + ")";
206215
}
207216

208217
/**

‎APIJSON-Java-Server/APIJSONORM/src/main/java/zuo/biao/apijson/server/AbstractSQLConfig.java‎

Lines changed: 18 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -600,10 +600,10 @@ public String getOrderString(boolean hasPrefix) {
600600

601601
String order = StringUtil.getTrimedString(getOrder());
602602

603-
if (isOracle() || isSQLServer()) { // Oracle 和 SQL Server 的 OFFSET 必须加 ORDER BY
603+
if (getCount() > 0 && (isOracle() || isSQLServer())) { // Oracle 和 SQL Server 的 OFFSET 必须加 ORDER BY
604604

605605
// String[] ss = StringUtil.split(order);
606-
if (StringUtil.isEmpty(order, true)) {
606+
if (StringUtil.isEmpty(order, true)) { //SQL Server 子查询内必须指定 OFFSET 才能用 ORDER BY
607607
String idKey = getIdKey();
608608
if (StringUtil.isEmpty(idKey, true)) {
609609
idKey = "id"; //ORDER BY NULL 不行，SQL Server 会报错，必须要有排序，才能使用 OFFSET FETCH，如果没有 idKey，请求中指定 @order 即可
@@ -1657,6 +1657,9 @@ public String getRegExpString(String key, String value, boolean ignoreCase) {
16571657
if (isPostgreSQL()) {
16581658
return getKey(key) + " ~" + (ignoreCase ? "* " : " ") + getValue(value);
16591659
}
1660+
if (isOracle()) {
1661+
return "regexp_like(" + getKey(key) + ", " + getValue(value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
1662+
}
16601663
return getKey(key) + " REGEXP " + (ignoreCase ? "" : "BINARY ") + getValue(value);
16611664
}
16621665
//~ regexp >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
@@ -1889,12 +1892,15 @@ public String getContainString(String key, Object[] childs, int type) throws Ill
18891892
throw new IllegalArgumentException(key + "<>:value 中value类型不能为JSON！");
18901893
}
18911894

1895+
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR));
18921896
if (isPostgreSQL()) {
1893-
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR))
1894-
+ getKey(key) + " @> " + getValue(newJSONArray(childs[i])); //operator does not exist: jsonb @> character varying "[" + childs[i] + "]");
1895-
} else {
1896-
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR))
1897-
+ "json_contains(" + getKey(key) + ", " + getValue(childs[i].toString()) + ")";
1897+
condition += (getKey(key) + " @> " + getValue(newJSONArray(childs[i]))); //operator does not exist: jsonb @> character varying "[" + childs[i] + "]");
1898+
}
1899+
else if (isOracle()) {
1900+
condition += ("json_textcontains(" + getKey(key) + ", '$', " + getValue(childs[i].toString()) + ")");
1901+
}
1902+
else {
1903+
condition += ("json_contains(" + getKey(key) + ", " + getValue(childs[i].toString()) + ")");
18981904
}
18991905
}
19001906
}
@@ -2004,7 +2010,7 @@ public String getSetString(RequestMethod method, Map<String, Object> content, bo
20042010
value = content.get(key);
20052011
key = getRealKey(method, key, false, true, verifyName, quote);
20062012

2007-
setString += (isFirst ? "" : ", ") + (getKey(key) + "=" + (keyType == 1 ? getAddString(key, value) : (keyType == 2
2013+
setString += (isFirst ? "" : ", ") + (getKey(key) + " = " + (keyType == 1 ? getAddString(key, value) : (keyType == 2
20082014
? getRemoveString(key, value) : getValue(value)) ) );
20092015

20102016
isFirst = false;
@@ -2017,10 +2023,10 @@ public String getSetString(RequestMethod method, Map<String, Object> content, bo
20172023
return " SET " + setString;
20182024
}
20192025

2020-
/**SET key = CONCAT (key, 'value')
2026+
/**SET key = concat(key, 'value')
20212027
* @param key
20222028
* @param value
2023-
* @return CONCAT (key, 'value')
2029+
* @return concat(key, 'value')
20242030
* @throws IllegalArgumentException
20252031
*/
20262032
@JSONField(serialize = false)
@@ -2029,7 +2035,7 @@ public String getAddString(String key, Object value) throws IllegalArgumentExcep
20292035
return getKey(key) + " + " + value;
20302036
}
20312037
if (value instanceof String) {
2032-
return " CONCAT (" + getKey(key) + ", " + getValue(value) + ") ";
2038+
return SQL.concat(getKey(key), (String) getValue(value));
20332039
}
20342040
throw new IllegalArgumentException(key + "+ 对应的值 " + value + " 不是Number,String,Array中的任何一种！");
20352041
}
@@ -2045,7 +2051,7 @@ public String getRemoveString(String key, Object value) throws IllegalArgumentEx
20452051
return getKey(key) + " - " + value;
20462052
}
20472053
if (value instanceof String) {
2048-
return SQL.replace(getKey(key), (String) getValue(value), "");// " replace(" + key + ", '" + value + "', '') ";
2054+
return SQL.replace(getKey(key), (String) getValue(value), "''");// " replace(" + key + ", '" + value + "', '') ";
20492055
}
20502056
throw new IllegalArgumentException(key + "- 对应的值 " + value + " 不是Number,String,Array中的任何一种！");
20512057
}

‎APIJSON-Java-Server/APIJSONORM/src/main/java/zuo/biao/apijson/server/AbstractSQLExecutor.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -176,7 +176,7 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknowType) throws
176176
Log.d(TAG, "\n<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<"
177177
+ "\n已生成 " + generatedSQLCount + " 条 SQL"
178178
+ "\nselect startTime = " + startTime
179-
+ "\n database = " + StringUtil.getString(config.getDatabase())
179+
+ "\ndatabase = " + StringUtil.getString(config.getDatabase())
180180
+ "; schema = " + StringUtil.getString(config.getSchema())
181181
+ "; sql = \n" + sql
182182
+ "\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n");

0 commit comments

Comments
 (0)