Skip to content

Commit b908fd0

Browse files
cloudAndMonkeycloudAndMonkey
authored
Add files via upload
1、一个json(事务)同时支持新增、修改、删除、查询、别名 2、delete、put 支持子查询 3、mysql8 with-as表达式 4、bug修复 删除操作 主表 和 子查询 是同一张表 mysql8以下 非with-as表达式 会报错: "msg": "You can't specify target table 'User' for update in FROM clause", 5、must、refuses判断、delete、put支持 ref
1 parent 242cbf4 commit b908fd0

File tree

7 files changed

+280
-34
lines changed

7 files changed

+280
-34
lines changed

‎APIJSONORM/src/main/java/apijson/JSONObject.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -151,6 +151,7 @@ public JSONObject setUserIdIn(List<Object> list) {
151151
public static final String KEY_ORDER = "@order"; //排序方式
152152
public static final String KEY_RAW = "@raw"; // 自定义原始 SQL 片段
153153
public static final String KEY_JSON = "@json"; //SQL Server 把字段转为 JSON 输出
154+
public static final String KEY_METHOD = "@method"; //json对象配置操作方法
154155

155156
public static final List<String> TABLE_KEY_LIST;
156157
static {

‎APIJSONORM/src/main/java/apijson/StringUtil.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -354,7 +354,7 @@ public static boolean isNotEmpty(String s, boolean trim) {
354354
PATTERN_ALPHA = Pattern.compile("^[a-zA-Z]+$");
355355
PATTERN_ALPHA_BIG = Pattern.compile("^[A-Z]+$");
356356
PATTERN_ALPHA_SMALL = Pattern.compile("^[a-z]+$");
357-
PATTERN_NAME = Pattern.compile("^[0-9a-zA-Z_]+$");//已用55个中英字符测试通过
357+
PATTERN_NAME = Pattern.compile("^[0-9a-zA-Z_:]+$");//已用55个中英字符测试通过
358358
//newest phone regex expression reference https://github.com/VincentSit/ChinaMobilePhoneNumberRegex
359359
PATTERN_PHONE = Pattern.compile("^1(?:3\\d{3}|5[^4\\D]\\d{2}|8\\d{3}|7(?:[0-35-9]\\d{2}|4(?:0\\d|1[0-2]|9\\d))|9[0-35-9]\\d{2}|6[2567]\\d{2}|4(?:(?:10|4[01])\\d{3}|[68]\\d{4}|[579]\\d{2}))\\d{6}$");
360360
PATTERN_EMAIL = Pattern.compile("^([a-zA-Z0-9_\\-\\.]+)@((\\[[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)|(([a-zA-Z0-9\\-]+\\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\\]?)$");

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

Lines changed: 168 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@
4747
*/
4848
public abstract class AbstractParser<T extends Object> implements Parser<T>, ParserCreator<T>, VerifierCreator<T>, SQLCreator {
4949
protected static final String TAG = "AbstractParser";
50-
50+
protected Map<Object, RequestMethod> key_method_Map = new HashMap<>();
5151
/**
5252
* 可以通过切换该变量来控制是否打印关键的接口请求内容。保守起见，该值默认为false。
5353
* 与 {@link Log#DEBUG} 任何一个为 true 都会打印关键的接口请求内容。
@@ -572,28 +572,11 @@ public JSONObject parseCorrectRequest(RequestMethod method, String tag, int vers
572572
return request;//需要指定JSON结构的get请求可以改为post请求。一般只有对安全性要求高的才会指定，而这种情况用明文的GET方式几乎肯定不安全
573573
}
574574

575-
if (StringUtil.isEmpty(tag, true)) {
576-
throw new IllegalArgumentException("请在最外层传 tag ！一般是 Table 名，例如 \"tag\": \"User\" ");
577-
}
578-
579-
//获取指定的JSON结构 <<<<<<<<<<<<
580-
JSONObject object = null;
581-
String error = "";
582-
try {
583-
object = getStructure("Request", method.name(), tag, version);
584-
} catch (Exception e) {
585-
error = e.getMessage();
586-
}
587-
if (object == null) { //empty表示随意操作 || object.isEmpty()) {
588-
throw new UnsupportedOperationException("找不到 version: " + version + ", method: " + method.name() + ", tag: " + tag + " 对应的 structure ！"
589-
+ "非开放请求必须是后端 Request 表中校验规则允许的操作！\n " + error + "\n如果需要则在 Request 表中新增配置！");
590-
}
591-
592-
//获取指定的JSON结构 >>>>>>>>>>>>>>
593-
JSONObject target = wrapRequest(method, tag, object, true);
575+
// if (StringUtil.isEmpty(tag, true)) {
576+
// throw new IllegalArgumentException("请在最外层传 tag ！一般是 Table 名，例如 \"tag\": \"User\" ");
577+
// }
594578

595-
//JSONObject clone 浅拷贝没用，Structure.parse 会导致 structure 里面被清空，第二次从缓存里取到的就是 {}
596-
return getVerifier().verifyRequest(method, name, target, request, maxUpdateCount, getGlobalDatabase(), getGlobalSchema(), creator);
579+
return batchVerify(method, tag, version, name, request, maxUpdateCount, creator);
597580
}
598581

599582

@@ -1047,6 +1030,8 @@ public JSONObject onObjectParse(final JSONObject request
10471030
if (op == null) {
10481031
op = createObjectParser(request, parentPath, arrayConfig, isSubquery, isTable, isArrayMainTable);
10491032
}
1033+
// 对象 - 设置 method
1034+
setOpMethod(request, op, name);
10501035
op = op.parse(name, isReuse);
10511036

10521037
JSONObject response = null;
@@ -2022,7 +2007,8 @@ protected void onBegin() {
20222007
*/
20232008
protected void onCommit() {
20242009
// Log.d(TAG, "onCommit >>");
2025-
if (RequestMethod.isQueryMethod(requestMethod)) {
2010+
// this.sqlExecutor.getTransactionIsolation() 只有json第一次执行才会设置, get请求=0
2011+
if (RequestMethod.isQueryMethod(requestMethod) && this.sqlExecutor.getTransactionIsolation() == Connection.TRANSACTION_NONE ) {
20262012
return;
20272013
}
20282014

@@ -2068,4 +2054,163 @@ protected void onClose() {
20682054
queryResultMap = null;
20692055
}
20702056

2057+
private void setOpMethod(JSONObject request,ObjectParser op, String key) {
2058+
if(key != null && request.getString(apijson.JSONObject.KEY_METHOD) != null) {
2059+
String _method = request.getString(apijson.JSONObject.KEY_METHOD);
2060+
if( _method != null) {
2061+
RequestMethod method = RequestMethod.valueOf(_method.toUpperCase());
2062+
this.setMethod(method);
2063+
op.setMethod(method);
2064+
}
2065+
}
2066+
}
2067+
2068+
protected JSONObject getRequestStructure(RequestMethod method, String tag, int version) throws Exception {
2069+
// 获取指定的JSON结构 <<<<<<<<<<<<
2070+
JSONObject object = null;
2071+
String error = "";
2072+
try {
2073+
object = getStructure("Request", method.name(), tag, version);
2074+
} catch (Exception e) {
2075+
error = e.getMessage();
2076+
}
2077+
if (object == null) { // empty表示随意操作 || object.isEmpty()) {
2078+
throw new UnsupportedOperationException("找不到 version: " + version + ", method: " + method.name() + ", tag: " + tag + " 对应的 structure ！" + "非开放请求必须是后端 Request 表中校验规则允许的操作！\n " + error + "\n如果需要则在 Request 表中新增配置！");
2079+
}
2080+
return object;
2081+
}
2082+
2083+
private JSONObject batchVerify(RequestMethod method, String tag, int version, String name, @NotNull JSONObject request, int maxUpdateCount, SQLCreator creator) throws Exception {
2084+
JSONObject jsonObject = new JSONObject(true);
2085+
if (request.keySet() == null || request.keySet().size() == 0) {
2086+
throw new IllegalArgumentException("json对象格式不正确 ！,例如 \"User\": {}");
2087+
}
2088+
2089+
for (String key : request.keySet()) {
2090+
// key重复直接抛错(xxx:alias, xxx:alias[])
2091+
if (jsonObject.containsKey(key) || jsonObject.containsKey(key + apijson.JSONObject.KEY_ARRAY)) {
2092+
throw new IllegalArgumentException("对象名重复,请添加别名区分 ! ,重复对象名为: " + key);
2093+
}
2094+
2095+
// @post、@get等RequestMethod
2096+
try {
2097+
if (key.startsWith("@")) {
2098+
try {
2099+
// 如果不匹配,不处理即可
2100+
RequestMethod l_method = RequestMethod.valueOf(key.substring(1).toUpperCase());
2101+
if (l_method != null) {
2102+
if (request.get(key) instanceof JSONArray) {
2103+
for (Object objKey : request.getJSONArray(key)) {
2104+
key_method_Map.put(objKey, l_method);
2105+
}
2106+
continue;
2107+
} else {
2108+
throw new IllegalArgumentException("参数 " + key + " 必须是数组格式 ! ,例如: [\"Moment\", \"Comment[]\"]");
2109+
}
2110+
}
2111+
} catch (Exception e) {
2112+
}
2113+
}
2114+
2115+
// 如果对象设置了@method, 优先使用 对象内部的@method
2116+
// 对于没有显式声明操作方法的，直接用 URL(/get, /post 等) 对应的默认操作方法
2117+
// 将method 设置到每个object, op执行会解析
2118+
if (request.get(key) instanceof JSONObject) {
2119+
if(request.getJSONObject(key).getString(apijson.JSONObject.KEY_METHOD) == null) {
2120+
if (key_method_Map.get(key) == null) {
2121+
// 数组会解析为对象进行校验,做一下兼容
2122+
if(key_method_Map.get(key + apijson.JSONObject.KEY_ARRAY) == null) {
2123+
request.getJSONObject(key).put(apijson.JSONObject.KEY_METHOD, method);
2124+
}else {
2125+
request.getJSONObject(key).put(apijson.JSONObject.KEY_METHOD, key_method_Map.get(key + apijson.JSONObject.KEY_ARRAY));
2126+
}
2127+
} else {
2128+
request.getJSONObject(key).put(apijson.JSONObject.KEY_METHOD, key_method_Map.get(key));
2129+
}
2130+
}
2131+
2132+
// get请求不校验
2133+
RequestMethod _method = RequestMethod.valueOf(request.getJSONObject(key).getString(apijson.JSONObject.KEY_METHOD).toUpperCase());
2134+
if (RequestMethod.isPublicMethod(_method)) {
2135+
jsonObject.put(key, request.getJSONObject(key));
2136+
continue;
2137+
}
2138+
}
2139+
2140+
if (key.startsWith("@") || key.endsWith("@")) {
2141+
jsonObject.put(key, request.get(key));
2142+
continue;
2143+
}
2144+
2145+
2146+
if (request.get(key) instanceof JSONObject || request.get(key) instanceof JSONArray) {
2147+
RequestMethod _method = null;
2148+
if (request.get(key) instanceof JSONObject) {
2149+
_method = RequestMethod.valueOf(request.getJSONObject(key).getString(apijson.JSONObject.KEY_METHOD).toUpperCase());
2150+
} else {
2151+
if (key_method_Map.get(key) == null) {
2152+
_method = method;
2153+
} else {
2154+
_method = key_method_Map.get(key);
2155+
}
2156+
}
2157+
2158+
String _tag = buildTag(request, key);
2159+
JSONObject requestItem = new JSONObject();
2160+
requestItem.put(_tag, request.get(key));
2161+
JSONObject object = getRequestStructure(_method, _tag, version);
2162+
JSONObject ret = objectVerify(_method, _tag, version, name, requestItem, maxUpdateCount, creator, object);
2163+
jsonObject.put(key, ret.get(_tag));
2164+
} else {
2165+
jsonObject.put(key, request.get(key));
2166+
}
2167+
} catch (Exception e) {
2168+
e.printStackTrace();
2169+
throw new Exception(e);
2170+
}
2171+
}
2172+
2173+
return jsonObject;
2174+
}
2175+
2176+
/**
2177+
* { "xxx:aa":{ "@tag": "" }, "tag": "User" }
2178+
* 1、@tag存在,tag=@tag
2179+
* 2、@tag不存在
2180+
* 生成规则:
2181+
* 1、存在别名
2182+
* key=对象: tag=key去除别名
2183+
* key=数组: tag=key去除别名 + []
2184+
* 2、不存在别名
2185+
* tag=key
2186+
* tag=key + []
2187+
* @param request
2188+
* @param key
2189+
* @return
2190+
*/
2191+
private String buildTag(JSONObject request, String key) {
2192+
String _tag = null;
2193+
if (request.get(key) instanceof JSONObject && request.getJSONObject(key).getString("@tag") != null) {
2194+
_tag = request.getJSONObject(key).getString("@tag");
2195+
} else {
2196+
int keyIndex = key.indexOf(":");
2197+
if (keyIndex != -1) {
2198+
_tag = key.substring(0, keyIndex);
2199+
if (apijson.JSONObject.isTableArray(key)) {
2200+
_tag += apijson.JSONObject.KEY_ARRAY;
2201+
}
2202+
} else {
2203+
// 不存在别名
2204+
_tag = key;
2205+
}
2206+
}
2207+
return _tag;
2208+
}
2209+
2210+
protected JSONObject objectVerify(RequestMethod method, String tag, int version, String name, @NotNull JSONObject request, int maxUpdateCount, SQLCreator creator, JSONObject object) throws Exception {
2211+
// 获取指定的JSON结构 >>>>>>>>>>>>>>
2212+
JSONObject target = wrapRequest(method, tag, object, true);
2213+
// JSONObject clone 浅拷贝没用，Structure.parse 会导致 structure 里面被清空，第二次从缓存里取到的就是 {}
2214+
return getVerifier().verifyRequest(method, name, target, request, maxUpdateCount, getGlobalDatabase(), getGlobalSchema(), creator);
2215+
}
20712216
}

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

Lines changed: 86 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -761,6 +761,9 @@ public abstract class AbstractSQLConfig implements SQLConfig {
761761

762762
}
763763

764+
// mysql8版本以上,子查询支持with as表达式
765+
private List<String> withAsExpreSqlList = null;
766+
protected List<Object> withAsExprePreparedValueList = new ArrayList<>();
764767
private int[] dbVersionNums = null;
765768
@Override
766769
public int[] getDBVersionNums() {
@@ -3909,6 +3912,39 @@ else if (isPresto() || isTrino()) {
39093912

39103913
//key@:{} Subquery <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
39113914

3915+
/**
3916+
* 只要 method != RequestMethod.POST 就都支持 with-as表达式
3917+
* @param cfg
3918+
* @param subquery
3919+
* @return
3920+
* @throws Exception
3921+
*/
3922+
private String withAsExpreSubqueryString(SQLConfig cfg, Subquery subquery) throws Exception {
3923+
if(cfg.getMethod() != RequestMethod.POST && this.withAsExpreSqlList == null) {
3924+
this.setWithAsExpreList();
3925+
}
3926+
String withAsExpreSql;
3927+
if(this.withAsExpreSqlList != null) {
3928+
String withQuoteName = getQuote() + subquery.getKey() + getQuote();
3929+
this.withAsExpreSqlList.add(" " + withQuoteName + " AS " + "(" + cfg.getSQL(isPrepared()) + ") ");
3930+
withAsExpreSql = " SELECT * FROM " + withQuoteName;
3931+
3932+
// 预编译参数
3933+
List<Object> subPvl = cfg.getPreparedValueList();
3934+
if (subPvl != null && subPvl.isEmpty() == false) {
3935+
this.withAsExprePreparedValueList.addAll(subPvl);
3936+
cfg.setPreparedValueList(new ArrayList<>());
3937+
}
3938+
}else {
3939+
withAsExpreSql = cfg.getSQL(isPrepared());
3940+
// mysql 才存在这个问题, 主表和子表是一张表
3941+
if(this.isMySQL() && StringUtil.equals(this.getTable(), subquery.getFrom())) {
3942+
withAsExpreSql = " SELECT * FROM (" + withAsExpreSql+") AS " + getQuote() + subquery.getKey() + getQuote();
3943+
}
3944+
}
3945+
return withAsExpreSql;
3946+
}
3947+
39123948
@Override
39133949
public String getSubqueryString(Subquery subquery) throws Exception {
39143950
if (subquery == null) {
@@ -3919,7 +3955,8 @@ public String getSubqueryString(Subquery subquery) throws Exception {
39193955
SQLConfig cfg = subquery.getConfig();
39203956

39213957
cfg.setPreparedValueList(new ArrayList<>());
3922-
String sql = (range == null || range.isEmpty() ? "" : range) + "(" + cfg.getSQL(isPrepared()) + ") ";
3958+
String withAsExpreSql = withAsExpreSubqueryString(cfg, subquery);
3959+
String sql = (range == null || range.isEmpty() ? "" : range) + "(" + withAsExpreSql + ") ";
39233960

39243961
//// SELECT .. FROM(SELECT ..) .. WHERE .. 格式需要把子查询中的预编译值提前
39253962
//// 如果外查询 SELECT concat(`name`,?) 这种 SELECT 里也有预编译值，那就不能这样简单反向
@@ -4123,19 +4160,24 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
41234160
return null;
41244161
}
41254162

4163+
String cSql = null;
41264164
switch (config.getMethod()) {
41274165
case POST:
41284166
return "INSERT INTO " + tablePath + config.getColumnString() + " VALUES" + config.getValuesString();
41294167
case PUT:
41304168
if(config.isClickHouse()){
41314169
return "ALTER TABLE " + tablePath + " UPDATE" + config.getSetString() + config.getWhereString(true);
41324170
}
4133-
return "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true) + (config.isMySQL() ? config.getLimitString() : "");
4171+
cSql = "UPDATE " + tablePath + config.getSetString() + config.getWhereString(true) + (config.isMySQL() ? config.getLimitString() : "");
4172+
cSql = buildWithAsExpreSql(config, cSql);
4173+
return cSql;
41344174
case DELETE:
41354175
if(config.isClickHouse()){
41364176
return "ALTER TABLE " + tablePath + " DELETE" + config.getWhereString(true);
41374177
}
4138-
return "DELETE FROM " + tablePath + config.getWhereString(true) + (config.isMySQL() ? config.getLimitString() : ""); // PostgreSQL 不允许 LIMIT
4178+
cSql = "DELETE FROM " + tablePath + config.getWhereString(true) + (config.isMySQL() ? config.getLimitString() : ""); // PostgreSQL 不允许 LIMIT
4179+
cSql = buildWithAsExpreSql(config, cSql);
4180+
return cSql;
41394181
default:
41404182
String explain = config.isExplain() ? (config.isSQLServer() ? "SET STATISTICS PROFILE ON " : (config.isOracle() || config.isDameng() || config.isKingBase() ? "EXPLAIN PLAN FOR " : "EXPLAIN ")) : "";
41414183
if (config.isTest() && RequestMethod.isGetMethod(config.getMethod(), true)) { // FIXME 为啥是 code 而不是 count ？
@@ -4156,8 +4198,32 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
41564198
return explain + config.getOraclePageSql(sql);
41574199
}
41584200

4159-
return explain + "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(tablePath, config) + config.getLimitString();
4201+
cSql = "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(tablePath, config) + config.getLimitString();
4202+
cSql = buildWithAsExpreSql(config, cSql);
4203+
return explain + cSql;
4204+
}
4205+
}
4206+
4207+
private static String buildWithAsExpreSql(@NotNull AbstractSQLConfig config, String cSql) throws Exception {
4208+
if(config.withAsExpreSqlList != null && config.withAsExpreSqlList.size() > 0) {
4209+
String withAsExpreSql = "WITH ";
4210+
// 只有一条
4211+
if(config.withAsExpreSqlList.size() == 1) {
4212+
withAsExpreSql += config.withAsExpreSqlList.get(0) + "\n" + cSql;
4213+
}else {
4214+
int lastIndex = config.withAsExpreSqlList.size() - 1;
4215+
for (int i = 0; i < config.withAsExpreSqlList.size(); i++) {
4216+
if(i == lastIndex) {
4217+
withAsExpreSql += config.withAsExpreSqlList.get(i) + "\n" + cSql;
4218+
}else {
4219+
withAsExpreSql += config.withAsExpreSqlList.get(i) + ",\n";
4220+
}
4221+
}
4222+
}
4223+
cSql = withAsExpreSql;
4224+
config.setWithAsExpreList();
41604225
}
4226+
return cSql;
41614227
}
41624228

41634229
/**Oracle的分页获取
@@ -5508,4 +5574,20 @@ public void onMissingKey4Combine(String name, JSONObject request, String combine
55085574

55095575
}
55105576

5577+
private void setWithAsExpreList() {
5578+
// mysql8版本以上,子查询支持with as表达式
5579+
if(this.isMySQL() && this.getDBVersionNums()[0] >= 8) {
5580+
this.withAsExpreSqlList = new ArrayList<>();
5581+
}
5582+
}
5583+
5584+
@Override
5585+
public List<Object> getWithAsExprePreparedValueList() {
5586+
return this.withAsExprePreparedValueList;
5587+
}
5588+
5589+
@Override
5590+
public void setWithAsExprePreparedValueList(List<Object> list) {
5591+
this.withAsExprePreparedValueList = list;
5592+
}
55115593
}

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1101,6 +1101,15 @@ else if (RequestMethod.isGetMethod(config.getMethod(), true)) {
11011101
}
11021102

11031103
List<Object> valueList = config.isPrepared() ? config.getPreparedValueList() : null;
1104+
List<Object> withAsExprePreparedValueList = config.isPrepared() ? config.getWithAsExprePreparedValueList() : null;
1105+
1106+
// 不同数据库, 预编译mysql使用with-as
1107+
if (valueList != null && withAsExprePreparedValueList != null && withAsExprePreparedValueList.size() > 0) {
1108+
withAsExprePreparedValueList.addAll(valueList);
1109+
valueList = withAsExprePreparedValueList;
1110+
// 多条POST/PUT/DELETE语句的情况,需要重新初始化
1111+
config.setWithAsExprePreparedValueList(new ArrayList<>());
1112+
}
11041113

11051114
if (valueList != null && valueList.isEmpty() == false) {
11061115
for (int i = 0; i < valueList.size(); i++) {

0 commit comments

Comments
 (0)