Skip to content

Commit e64f2c4

Browse files
TommyLemonTommyLemon
committed
Server:@column支持SQL函数，例如 column0,column1:alias;function0(arg0,arg1,...);function1(...):alias...
1 parent 347f0ec commit e64f2c4

File tree

2 files changed

+103
-26
lines changed

2 files changed

+103
-26
lines changed

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

Lines changed: 102 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -317,7 +317,6 @@ public String getHavingString() {
317317
//fun(arg0,arg1,...)
318318
expression = keys[i];
319319

320-
//TODO 支持 maxId>=100 这种没括号的
321320
int start = expression.indexOf("(");
322321
if (start < 0) {
323322
if (isPrepared() && PATTERN_HAVING.matcher(expression).matches() == false) {
@@ -365,7 +364,7 @@ public String getHavingString() {
365364
}
366365
}
367366

368-
//keys[i] = method + "(" + StringUtil.getString(ckeys) + ")" + suffix;
367+
keys[i] = method + "(" + StringUtil.getString(ckeys) + ")" + suffix;
369368
}
370369

371370
return " HAVING " + StringUtil.getString(keys, AND); //TODO 支持 OR, NOT 参考 @combine:"&key0,|key1,!key2"
@@ -490,45 +489,123 @@ public String getColumnString() throws Exception {
490489

491490
String tableAlias = getAlias();
492491

493-
column = StringUtil.getString(column);
494-
if (column.isEmpty()) {
492+
String c = StringUtil.getString(column); //id,name;json_length(contactIdList):contactCount;...
493+
494+
String[] keys = StringUtil.split(c, ";");
495+
if (keys == null || keys.length <= 0) {
495496
return isKeyPrefix() == false ? "*" : (tableAlias + ".*" + (StringUtil.isEmpty(joinColumn, true) ? "" : ", " + joinColumn));
496497
}
497498

498-
String c = column;
499-
// if (isPrepared()) { //不能通过 ? 来代替，SELECT 'id','name' 返回的就是 id:"id", name:"name"，而不是数据库里的值！
500-
String[] keys = StringUtil.split(column);
501-
if (keys != null && keys.length > 0) {
502-
String origin;
503-
String alias;
504-
int index;
505-
for (int i = 0; i < keys.length; i++) {
506-
index = keys[i].indexOf(":"); //StringUtil.split返回数组中，子项不会有null
507-
origin = index < 0 ? keys[i] : keys[i].substring(0, index);
508-
alias = index < 0 ? null : keys[i].substring(index + 1);
509-
510-
if (isPrepared()) {
511-
if (StringUtil.isName(origin) == false || (alias != null && StringUtil.isName(alias) == false)) {
512-
throw new IllegalArgumentException("GET请求: 预编译模式下 @column:value 中 value里面用 , 分割的每一项"
513-
+ " column:alias 中 column必须是1个单词！如果有alias，则alias也必须为1个单词！并且不要有多余的空格！");
499+
500+
String expression;
501+
String method = null;
502+
503+
//...;fun0(arg0,arg1,...):fun0;fun1(arg0,arg1,...):fun1;...
504+
for (int i = 0; i < keys.length; i++) {
505+
506+
//fun(arg0,arg1,...)
507+
expression = keys[i];
508+
509+
int start = expression.indexOf("(");
510+
int end = 0;
511+
if (start >= 0) {
512+
end = expression.indexOf(")");
513+
if (start >= end) {
514+
throw new IllegalArgumentException("字符 " + expression + " 不合法！"
515+
+ "@having:value 中 value 里的 SQL函数必须为 function(arg0,arg1,...) 这种格式！");
516+
}
517+
518+
method = expression.substring(0, start);
519+
520+
if (StringUtil.isName(method) == false) {
521+
throw new IllegalArgumentException("字符 " + method + " 不合法！"
522+
+ "预编译模式下 @column:\"column0,column1:alias;function0(arg0,arg1,...);function1(...):alias...\""
523+
+ " 中SQL函数名 function 必须符合正则表达式 ^[0-9a-zA-Z_]+$ ！");
524+
}
525+
}
526+
527+
boolean isColumn = start < 0;
528+
529+
String[] ckeys = StringUtil.split(isColumn ? expression : expression.substring(start + 1, end));
530+
531+
// if (isPrepared()) { //不能通过 ? 来代替，SELECT 'id','name' 返回的就是 id:"id", name:"name"，而不是数据库里的值！
532+
if (ckeys != null && ckeys.length > 0) {
533+
534+
String origin;
535+
String alias;
536+
int index;
537+
for (int j = 0; j < ckeys.length; j++) {
538+
index = isColumn ? -1 : ckeys[j].indexOf(":"); //StringUtil.split返回数组中，子项不会有null
539+
origin = index < 0 ? ckeys[j] : ckeys[j].substring(0, index);
540+
alias = index < 0 ? null : ckeys[j].substring(index + 1);
541+
542+
if (isPrepared()) {
543+
if (isColumn) {
544+
if (StringUtil.isName(origin) == false || (alias != null && StringUtil.isName(alias) == false)) {
545+
throw new IllegalArgumentException("GET请求: 预编译模式下 @column:value 中 value里面用 , 分割的每一项"
546+
+ " column:alias 中 column 必须是1个单词！如果有alias，则alias也必须为1个单词！并且不要有多余的空格！");
547+
}
548+
}
549+
else {
550+
if ((StringUtil.isName(ckeys[j]) == false || ckeys[j].startsWith("_"))) {
551+
throw new IllegalArgumentException("字符 " + ckeys[j] + " 不合法！"
552+
+ "预编译模式下 @column:\"column0,column1:alias;function0(arg0,arg1,...);function1(...):alias...\""
553+
+ " 中所有 arg 都必须是1个不以 _ 开头的单词！并且不要有空格！");
554+
}
555+
}
556+
}
557+
558+
if (isKeyPrefix()) {
559+
ckeys[j] = tableAlias + "." + origin;
560+
if (isColumn) {
561+
ckeys[j] += " AS `" + (isMain() ? "" : tableAlias + ".") + (StringUtil.isEmpty(alias, true) ? origin : alias) + "`";
562+
}
563+
} else {
564+
ckeys[j] = origin + (StringUtil.isEmpty(alias, true) ? "" : " AS `" + alias + "`");
565+
}
566+
}
567+
// }
568+
569+
}
570+
571+
if (isColumn) {
572+
keys[i] = StringUtil.getString(ckeys);
573+
}
574+
else {
575+
String suffix = expression.substring(end + 1, expression.length()); //:contactCount
576+
String alias = suffix.startsWith(":") ? suffix.substring(1) : null; //contactCount
577+
578+
if (StringUtil.isEmpty(alias, true)) {
579+
if (suffix.isEmpty() == false) {
580+
throw new IllegalArgumentException("GET请求: 预编译模式下 @column:value 中 value里面用 ; 分割的每一项"
581+
+ " function(arg0,arg1,...):alias 中 alias 如果有就必须是1个单词！并且不要有多余的空格！");
582+
}
583+
}
584+
else {
585+
if (StringUtil.isEmpty(alias, true) == false && StringUtil.isName(alias) == false) {
586+
throw new IllegalArgumentException("GET请求: 预编译模式下 @column:value 中 value里面用 ; 分割的每一项"
587+
+ " function(arg0,arg1,...):alias 中 alias 必须是1个单词！并且不要有多余的空格！");
514588
}
515589
}
516590

591+
592+
String origin = method + "(" + StringUtil.getString(ckeys) + ")";
517593
if (isKeyPrefix()) {
518-
keys[i] = tableAlias + "." + origin + " AS `" + (isMain() ? "" : tableAlias + ".") + (StringUtil.isEmpty(alias, true) ? origin : alias) + "`";
519-
} else {
594+
keys[i] = origin + " AS `" + (isMain() ? "" : tableAlias + ".") + (StringUtil.isEmpty(alias, true) ? method : alias) + "`";
595+
}
596+
else {
520597
keys[i] = origin + (StringUtil.isEmpty(alias, true) ? "" : " AS `" + alias + "`");
521598
}
522599
}
523-
// }
524600

525-
c = StringUtil.getString(keys);
526601
}
602+
603+
c = StringUtil.getString(keys);
527604

528605
return c.contains(":") == false ? c : c.replaceAll(":", " AS ") + (StringUtil.isEmpty(joinColumn, true) ? "" : ", " + joinColumn);//不能在这里改，后续还要用到:
529606

530607
default:
531-
throw new UnsupportedOperationException("服务器内部错误：getColumnString 不支持 " + RequestMethod.getName(method) + " 等 [GET,GETS,HEAD,HEADS,POST] 外的ReuqestMethod！");
608+
throw new UnsupportedOperationException("服务器内部错误：getColumnString 不支持 " + RequestMethod.getName(getMethod()) + " 等 [GET,GETS,HEAD,HEADS,POST] 外的ReuqestMethod！");
532609
}
533610
}
534611

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -358,7 +358,7 @@ protected Map<Integer, JSONObject> onPutTable(@NotNull SQLConfig config, @NotNul
358358
public boolean isJSONType(ResultSetMetaData rsmd, int position) {
359359
try {
360360
//TODO CHAR和JSON类型的字段，getColumnType返回值都是1 ，如果不用CHAR，改用VARCHAR，则可以用上面这行来提高性能。
361-
//return rsmd.getColumnType(position) == 1 || rsmd.getColumnTypeName(position).toLowerCase().contains("json");
361+
//return rsmd.getColumnType(position) == 1;
362362
return rsmd.getColumnTypeName(position).toLowerCase().contains("json");
363363
} catch (SQLException e) {
364364
e.printStackTrace();

0 commit comments

Comments
 (0)