2021SC@SDUSC
客户端定制服务器返回值
<key,value>
value为空代表服务器返回这个
不为空就是传入参数。服务器根据需要返回的数据所必要的参数去获取数据并返回。
可一次性获取多个非单一接口能获取的数据。以前要么单独获取两次，要么在一个借口放两种数据。
方法一:
<"userId", 10000> - 传入必要的获取
<"user", ""> - 需要返回用户
<"workList",""> - 需要返回作品列表
方法二:最好是客户端传json到服务器，服务器补全json再返回！
请求JSON:
{
operate:get//增删改查方法放请求方法里？GET,POST,UPDATE,DELETE
user:{//根据id获取用户
id:10000
},
work:{//根据id获取作品
id:12
},
pictureUrl[]:{//根据userid和type获取图片地址数组
userId:10000,
type:1,
pageNumber:0,
maxSize:10,
},
protected String getWhereItem(String key, Object value, RequestMethod method, boolean verifyName) throws Exception {
Log.d(TAG, "getWhereItem key = " + key);
//避免筛选到全部 value = key == null ? null : where.get(key);
if (key == null || value == null || key.endsWith("()") || key.startsWith("@")) { //关键字||方法, +或-直接报错
Log.d(TAG, "getWhereItem key == null || value == null"
+ " || key.startsWith(@) || key.endsWith(()) >> continue;");
return null;
}
if (key.endsWith("@")) {//引用
// key = key.substring(0, key.lastIndexOf("@"));
throw new IllegalArgumentException(TAG + ".getWhereItem: 字符 " + key + " 不合法！");
}
// 原始 SQL 片段
String rawSQL = getRawSQL(key, value);
int keyType;
if (key.endsWith("$")) {
keyType = 1;
}
else if (key.endsWith("~")) {
keyType = key.charAt(key.length() - 2) == '*' ? -2 : 2; //FIXME StringIndexOutOfBoundsException
}
else if (key.endsWith("%")) {
keyType = 3;
}
else if (key.endsWith("{}")) {
keyType = 4;
}
else if (key.endsWith("}{")) {
keyType = 5;
}
else if (key.endsWith("<>")) {
keyType = 6;
}
else if (key.endsWith(">=")) {
keyType = 7;
}
else if (key.endsWith("<=")) {
keyType = 8;
}
else if (key.endsWith(">")) {
keyType = 9;
}
else if (key.endsWith("<")) {
keyType = 10;
} else { // else绝对不能省，避免再次踩坑！ keyType = 0; 写在for循环外面都没注意！
keyType = 0;
}
key = getRealKey(method, key, false, true, verifyName);
switch (keyType) {
case 1:
return getSearchString(key, value, rawSQL);
case -2:
case 2:
return getRegExpString(key, value, keyType < 0, rawSQL);
case 3:
return getBetweenString(key, value, rawSQL);
case 4:
return getRangeString(key, value, rawSQL);
case 5:
return getExistsString(key, value, rawSQL);
case 6:
return getContainString(key, value, rawSQL);
case 7:
return getCompareString(key, value, ">=", rawSQL);
case 8:
return getCompareString(key, value, "<=", rawSQL);
case 9:
return getCompareString(key, value, ">", rawSQL);
case 10:
return getCompareString(key, value, "<", rawSQL);
default: // TODO MySQL JSON类型的字段对比 key='[]' 会无结果！ key LIKE '[1, 2, 3]' //TODO MySQL , 后面有空格！
return getEqualString(key, value, rawSQL);
}
}
@JSONField(serialize = false)
public String getEqualString(String key, Object value, String rawSQL) throws Exception {
if (JSON.isBooleanOrNumberOrString(value) == false && value instanceof Subquery == false) {
throw new IllegalArgumentException(key + ":value 中value不合法！非PUT请求只支持 [Boolean, Number, String] 内的类型 ！");
}
boolean not = key.endsWith("!"); // & | 没有任何意义，写法多了不好控制
if (not) {
key = key.substring(0, key.length() - 1);
}
if (StringUtil.isName(key) == false) {
throw new IllegalArgumentException(key + ":value 中key不合法！不支持 ! 以外的逻辑符 ！");
}
return getKey(key) + (not ? " != " : " = ") + (value instanceof Subquery ? getSubqueryString((Subquery) value) : (rawSQL != null ? rawSQL : getValue(value)));
}
@JSONField(serialize = false)
public String getCompareString(String key, Object value, String type, String rawSQL) throws Exception {
if (JSON.isBooleanOrNumberOrString(value) == false && value instanceof Subquery == false) {
throw new IllegalArgumentException(key + type + ":value 中value不合法！比较运算 [>, <, >=, <=] 只支持 [Boolean, Number, String] 内的类型 ！");
}
if (StringUtil.isName(key) == false) {
throw new IllegalArgumentException(key + type + ":value 中key不合法！比较运算 [>, <, >=, <=] 不支持 [&, !, |] 中任何逻辑运算符 ！");
}
return getKey(key) + " " + type + " " + (value instanceof Subquery ? getSubqueryString((Subquery) value) : (rawSQL != null ? rawSQL : getValue(value)));
}
public String getKey(String key) {
if (isTest()) {
if (key.contains("'")) { // || key.contains("#") || key.contains("--")) {
throw new IllegalArgumentException("参数 " + key + " 不合法！key 中不允许有单引号 ' ！");
}
return getSQLValue(key).toString();
}
return getSQLKey(key);
}
public String getSQLKey(String key) {
String q = getQuote();
return (isKeyPrefix() ? getAliasWithQuote() + "." : "") + q + key + q;
}
/**
* 使用prepareStatement预编译，值为 ? ，后续动态set进去
*/
private List<Object> preparedValueList = new ArrayList<>();
private Object getValue(@NotNull Object value) {
if (isPrepared()) {
preparedValueList.add(value);
return "?";
}
return getSQLValue(value);
}
public Object getSQLValue(@NotNull Object value) {
// return (value instanceof Number || value instanceof Boolean) && DATABASE_POSTGRESQL.equals(getDatabase()) ? value : "'" + value + "'";
return (value instanceof Number || value instanceof Boolean) ? value : "'" + value + "'"; //MySQL 隐式转换用不了索引
}
@Override
public List<Object> getPreparedValueList() {
return preparedValueList;
}
@Override
public AbstractSQLConfig setPreparedValueList(List<Object> preparedValueList) {
this.preparedValueList = preparedValueList;
return this;
}
//$ search <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
/**找到符合 value 的key
* @param in
* @return {@link #getSearchString(String, Object[], int)}
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getSearchString(String key, Object value, String rawSQL) throws IllegalArgumentException {
if (rawSQL != null) {
throw new UnsupportedOperationException("@raw:value 中 " + key + " 不合法！@raw 不支持 key$ 这种功能符 ！只支持 key, key!, key<, key{} 等比较运算 和 @column, @having ！");
}
if (value == null) {
return "";
}
Logic logic = new Logic(key);
key = logic.getKey();
Log.i(TAG, "getSearchString key = " + key);
JSONArray arr = newJSONArray(value);
if (arr.isEmpty()) {
return "";
}
return getSearchString(key, arr.toArray(), logic.getType());
}
/**search key match values
* @param in
* @return LOGIC [ key LIKE 'values[i]' ]
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getSearchString(String key, Object[] values, int type) throws IllegalArgumentException {
if (values == null || values.length <= 0) {
return "";
}
String condition = "";
for (int i = 0; i < values.length; i++) {
Object v = values[i];
if (v instanceof String == false) {
throw new IllegalArgumentException(key + "$:value 中 value 的类型只能为 String 或 String[]！");
}
if (((String) v).isEmpty()) { // 允许查空格 StringUtil.isEmpty((String) v, true)
throw new IllegalArgumentException(key + "$:value 中 value 值 " + v + "是空字符串，没有意义，不允许这样传！");
}
// if (((String) v).contains("%%")) { // 需要通过 %\%% 来模糊搜索 %
// throw new IllegalArgumentException(key + "$:value 中 value 值 " + v + " 中包含 %% ！不允许有连续的 % ！");
// }
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR)) + getLikeString(key, v);
}
return getCondition(Logic.isNot(type), condition);
}
/**WHERE key LIKE 'value'
* @param key
* @param value
* @return key LIKE 'value'
*/
@JSONField(serialize = false)
public String getLikeString(String key, Object value) {
return getKey(key) + " LIKE " + getValue(value);
}
//$ search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
//~ regexp <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
/**search key match RegExp values
* @param key
* @param value
* @param ignoreCase
* @return {@link #getRegExpString(String, Object[], int, boolean)}
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getRegExpString(String key, Object value, boolean ignoreCase, String rawSQL) throws IllegalArgumentException {
if (rawSQL != null) {
throw new UnsupportedOperationException("@raw:value 中 " + key + " 不合法！@raw 不支持 key~ 这种功能符 ！只支持 key, key!, key<, key{} 等比较运算 和 @column, @having ！");
}
if (value == null) {
return "";
}
Logic logic = new Logic(key);
key = logic.getKey();
Log.i(TAG, "getRegExpString key = " + key);
JSONArray arr = newJSONArray(value);
if (arr.isEmpty()) {
return "";
}
return getRegExpString(key, arr.toArray(), logic.getType(), ignoreCase);
}
/**search key match RegExp values
* @param key
* @param values
* @param type
* @param ignoreCase
* @return LOGIC [ key REGEXP 'values[i]' ]
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getRegExpString(String key, Object[] values, int type, boolean ignoreCase) throws IllegalArgumentException {
if (values == null || values.length <= 0) {
return "";
}
String condition = "";
for (int i = 0; i < values.length; i++) {
if (values[i] instanceof String == false) {
throw new IllegalArgumentException(key + "$:value 中value的类型只能为String或String[]！");
}
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR)) + getRegExpString(key, (String) values[i], ignoreCase);
}
return getCondition(Logic.isNot(type), condition);
}
/**WHERE key REGEXP 'value'
* @param key
* @param value
* @param ignoreCase
* @return key REGEXP 'value'
*/
@JSONField(serialize = false)
public String getRegExpString(String key, String value, boolean ignoreCase) {
if (isPostgreSQL()) {
return getKey(key) + " ~" + (ignoreCase ? "* " : " ") + getValue(value);
}
if (isOracle()) {
return "regexp_like(" + getKey(key) + ", " + getValue(value) + (ignoreCase ? ", 'i'" : ", 'c'") + ")";
}
if (isClickHouse()) {
return "match(" + (ignoreCase ? "lower(" : "") + getKey(key) + (ignoreCase ? ")" : "") + ", " + (ignoreCase ? "lower(" : "") + getValue(value) + (ignoreCase ? ")" : "") + ")";
}
return getKey(key) + " REGEXP " + (ignoreCase ? "" : "BINARY ") + getValue(value);
}
//~ regexp >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
//% between <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
/**WHERE key BETWEEN 'start' AND 'end'
* @param key
* @param value 'start,end'
* @return LOGIC [ key BETWEEN 'start' AND 'end' ]
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getBetweenString(String key, Object value, String rawSQL) throws IllegalArgumentException {
if (rawSQL != null) {
throw new UnsupportedOperationException("@raw:value 中 " + key + " 不合法！@raw 不支持 key% 这种功能符 ！只支持 key, key!, key<, key{} 等比较运算 和 @column, @having ！");
}
if (value == null) {
return "";
}
Logic logic = new Logic(key);
key = logic.getKey();
Log.i(TAG, "getBetweenString key = " + key);
JSONArray arr = newJSONArray(value);
if (arr.isEmpty()) {
return "";
}
return getBetweenString(key, arr.toArray(), logic.getType());
}
/**WHERE key BETWEEN 'start' AND 'end'
* @param key
* @param value 'start,end' TODO 在 '1,2' 和 ['1,2', '3,4'] 基础上新增支持 [1, 2] 和 [[1,2], [3,4]] ？
* @return LOGIC [ key BETWEEN 'start' AND 'end' ]
* @抛出非法异常
*/
@JSONField(serialize = false)
public String getBetweenString(String key, Object[] values, int type) throws IllegalArgumentException {
if (values == null || values.length <= 0) {
return "";
}
String condition = "";
String[] vs;
for (int i = 0; i < values.length; i++) {
if (values[i] instanceof String == false) {
throw new IllegalArgumentException(key + "%:value 中 value 的类型只能为 String 或 String[] ！");
}
vs = StringUtil.split((String) values[i]);
if (vs == null || vs.length != 2) {
throw new IllegalArgumentException(key + "%:value 中 value 不合法！类型为 String 时必须包括1个逗号 , 且左右两侧都有值！类型为 String[] 里面每个元素要符合前面类型为 String 的规则 ！");
}
condition += (i <= 0 ? "" : (Logic.isAnd(type) ? AND : OR)) + "(" + getBetweenString(key, (Object) vs[0], (Object) vs[1]) + ")";
}
return getCondition(Logic.isNot(type), condition);
}
/**WHERE key BETWEEN 'start' AND 'end'
* @param key
* @param value 'start,end' TODO 在 '1,2' 和 ['1,2', '3,4'] 基础上新增支持 [1, 2] 和 [[1,2], [3,4]] ？
* @返回 key 'start' 与 'end'之中一个
* @throws IllegalArgumentException
*/
@JSONField(serialize = false)
public String getBetweenString(String key, Object start, Object end) throws IllegalArgumentException {
if (JSON.isBooleanOrNumberOrString(start) == false || JSON.isBooleanOrNumberOrString(end) == false) {
throw new IllegalArgumentException(key + "%:value 中 value 不合法！类型为 String 时必须包括1个逗号 , 且左右两侧都有值！类型为 String[] 里面每个元素要符合前面类型为 String 的规则 ！");
}
return getKey(key) + " BETWEEN " + getValue(start) + AND + getValue(end);
}
//% between >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
}