2121import static apijson .JSONObject .KEY_ROLE ;
2222import static apijson .JSONObject .KEY_SCHEMA ;
2323import static apijson .JSONObject .KEY_USER_ID ;
24- import static apijson .RequestMethod .DELETE ;
25- import static apijson .RequestMethod .GET ;
26- import static apijson .RequestMethod .GETS ;
27- import static apijson .RequestMethod .HEADS ;
28- import static apijson .RequestMethod .POST ;
29- import static apijson .RequestMethod .PUT ;
24+ import static apijson .RequestMethod .*;
3025import static apijson .SQL .AND ;
3126import static apijson .SQL .NOT ;
3227import static apijson .SQL .OR ;
@@ -124,6 +119,7 @@ public abstract class AbstractSQLConfig implements SQLConfig {
124119 DATABASE_LIST .add (DATABASE_SQLSERVER );
125120 DATABASE_LIST .add (DATABASE_ORACLE );
126121 DATABASE_LIST .add (DATABASE_DB2 );
122+ DATABASE_LIST .add (DATABASE_CLICKHOUSE );
127123
128124
129125 RAW_MAP = new LinkedHashMap <>(); // 保证顺序，避免配置冲突等意外情况
@@ -508,10 +504,17 @@ public boolean isDb2() {
508504 public static boolean isDb2 (String db ) {
509505 return DATABASE_DB2 .equals (db );
510506 }
507+ @ Override
508+ public boolean isClickHouse () {
509+ return isClickHouse (getSQLDatabase ());
510+ }
511+ public static boolean isClickHouse (String db ) {
512+ return DATABASE_CLICKHOUSE .equals (db );
513+ }
511514
512515 @ Override
513516 public String getQuote () {
514- return isMySQL () ? "`" : "\" " ;
517+ return isMySQL ()|| isClickHouse () ? "`" : "\" " ;
515518 }
516519
517520 @ Override
@@ -882,7 +885,7 @@ public String getOrderString(boolean hasPrefix) {
882885 // return (hasPrefix ? " ORDER BY " : "") + StringUtil.concat(order, joinOrder, ", ");
883886 // }
884887
885- if (getCount () > 0 && (isOracle () || isSQLServer () || isDb2 ())) { // Oracle, SQL Server, DB2 的 OFFSET 必须加 ORDER BY
888+ if (getCount () > 0 && (isSQLServer () || isDb2 ())) { // Oracle, SQL Server, DB2 的 OFFSET 必须加 ORDER BY.去掉Oracle，Oracle里面没有offset关键字
886889
887890 // String[] ss = StringUtil.split(order);
888891 if (StringUtil .isEmpty (order , true )) { //SQL Server 子查询内必须指定 OFFSET 才能用 ORDER BY
@@ -1171,9 +1174,9 @@ public String getColumnString(boolean inSQLJoin) throws Exception {
11711174 // }
11721175 }
11731176
1174- if (expression .length () > 50 ) {
1177+ if (expression .length () > 100 ) {
11751178 throw new UnsupportedOperationException ("@column:value 的 value 中字符串 " + expression + " 不合法！"
1176- + "不允许传超过 50 个字符的函数或表达式！请用 @raw 简化传参！" );
1179+ + "不允许传超过 100 个字符的函数或表达式！请用 @raw 简化传参！" );
11771180 }
11781181
11791182
@@ -2158,6 +2161,9 @@ public String getRegExpString(String key, String value, boolean ignoreCase) {
21582161 if (isOracle ()) {
21592162 return "regexp_like(" + getKey (key ) + ", " + getValue (value ) + (ignoreCase ? ", 'i'" : ", 'c'" ) + ")" ;
21602163 }
2164+ if (isClickHouse ()) {
2165+ return "match(" + (ignoreCase ? "lower(" : "" ) + getKey (key ) + (ignoreCase ? ")" : "" ) + ", " + (ignoreCase ? "lower(" : "" ) + getValue (value ) + (ignoreCase ? ")" : "" ) + ")" ;
2166+ }
21612167 return getKey (key ) + " REGEXP " + (ignoreCase ? "" : "BINARY " ) + getValue (value );
21622168 }
21632169 //~ regexp >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
@@ -2448,7 +2454,12 @@ else if (isOracle()) {
24482454 else {
24492455 boolean isNum = c instanceof Number ;
24502456 String v = (isNum ? "" : "\" " ) + childs [i ] + (isNum ? "" : "\" " );
2451- condition += ("json_contains(" + getKey (key ) + ", " + getValue (v ) + ")" );
2457+ if (isClickHouse ()) {
2458+ condition += condition + "has(JSONExtractArrayRaw(assumeNotNull(" + getKey (key ) + "))" + ", " + getValue (v ) + ")" ;
2459+ }
2460+ else {
2461+ condition += ("json_contains(" + getKey (key ) + ", " + getValue (v ) + ")" );
2462+ }
24522463 }
24532464 }
24542465 }
@@ -2571,7 +2582,7 @@ public String getSetString(RequestMethod method, Map<String, Object> content, bo
25712582 if (setString .isEmpty ()) {
25722583 throw new IllegalArgumentException ("PUT 请求必须在Table内设置要修改的 key:value ！" );
25732584 }
2574- return " SET "setString ;
2585+ return ( isClickHouse ()? " " : " SET ") + setString ;
25752586 }
25762587
25772588 /**SET key = concat(key, 'value')
@@ -2649,8 +2660,14 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
26492660 case POST :
26502661 return "INSERT INTO " + tablePath + config .getColumnString () + " VALUES" + config .getValuesString ();
26512662 case PUT :
2663+ if (config .isClickHouse ()){
2664+ return "ALTER TABLE " + tablePath + " UPDATE" + config .getSetString ()+ config .getWhereString (true );
2665+ }
26522666 return "UPDATE " + tablePath + config .getSetString () + config .getWhereString (true ) + (config .isMySQL () ? config .getLimitString () : "" );
26532667 case DELETE :
2668+ if (config .isClickHouse ()){
2669+ return "ALTER TABLE " + tablePath + " DELETE" + config .getWhereString (true );
2670+ }
26542671 return "DELETE FROM " + tablePath + config .getWhereString (true ) + (config .isMySQL () ? config .getLimitString () : "" ); // PostgreSQL 不允许 LIMIT
26552672 default :
26562673 String explain = (config .isExplain () ? (config .isSQLServer () || config .isOracle () ? "SET STATISTICS PROFILE ON " : "EXPLAIN " ) : "" );
@@ -2663,18 +2680,22 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
26632680 String column = config .getColumnString ();
26642681 if (config .isOracle ()) {
26652682 //When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
2666- return explain + "SELECT * FROM (SELECT" + (config .getCache () == JSONRequest .CACHE_RAM ? "SQL_NO_CACHE " : "" ) + column + " FROM " + getConditionString (column , tablePath , config ) + ") " + config .getLimitString ();
2683+ //针对oracle分组后条数的统计
2684+ if ((config .getMethod () == HEAD || config .getMethod () == HEADS )
2685+ && StringUtil .isNotEmpty (config .getGroup (),true )){
2686+ return explain + "SELECT count(*) FROM (SELECT " + (config .getCache () == JSONRequest .CACHE_RAM ? "SQL_NO_CACHE " : "" ) + column + " FROM " + getConditionString (column , tablePath , config ) + ") " + config .getLimitString ();
2687+ }
2688+ return explain + "SELECT * FROM (SELECT " + (config .getCache () == JSONRequest .CACHE_RAM ? "SQL_NO_CACHE " : "" ) + column + " FROM " + getConditionString (column , tablePath , config ) + ") " + config .getLimitString ();
26672689 }
26682690
26692691 return explain + "SELECT " + (config .getCache () == JSONRequest .CACHE_RAM ? "SQL_NO_CACHE " : "" ) + column + " FROM " + getConditionString (column , tablePath , config ) + config .getLimitString ();
26702692 }
26712693 }
26722694
26732695 /**获取条件SQL字符串
2674- * @param page
26752696 * @param column
26762697 * @param table
2677- * @param where
2698+ * @param config
26782699 * @return
26792700 * @throws Exception
26802701 */
@@ -2686,11 +2707,21 @@ private static String getConditionString(String column, String table, AbstractSQ
26862707 table = config .getSubqueryString (from ) + " AS " + config .getAliasWithQuote () + " " ;
26872708 }
26882709
2689- String condition = table + config .getJoinString () + where + (
2690- RequestMethod .isGetMethod (config .getMethod (), true ) == false ?
2691- "" : config .getGroupString (true ) + config .getHavingString (true ) + config .getOrderString (true )
2692- )
2693- ; //+ config.getLimitString();
2710+ //根据方法不同，聚合语句不同。GROUP BY 和 HAVING 可以加在 HEAD 上, HAVING 可以加在 PUT, DELETE 上，GET 全加，POST 全都不加
2711+ String aggregation = "" ;
2712+ if (RequestMethod .isGetMethod (config .getMethod (), true )){
2713+ aggregation = config .getGroupString (true ) + config .getHavingString (true ) +
2714+ config .getOrderString (true );
2715+ }
2716+ if (RequestMethod .isHeadMethod (config .getMethod (), true )){
2717+ aggregation = config .getGroupString (true ) + config .getHavingString (true ) ;
2718+ }
2719+ if (config .getMethod () == PUT || config .getMethod () == DELETE ){
2720+ aggregation = config .getHavingString (true ) ;
2721+ }
2722+
2723+ String condition = table + config .getJoinString () + where + aggregation ;
2724+ ; //+ config.getLimitString();
26942725
26952726 //no need to optimize
26962727 // if (config.getPage() <= 0 || ID.equals(column.trim())) {
@@ -2727,7 +2758,6 @@ private static String getConditionString(String column, String table, AbstractSQ
27272758 // return table + " AS t0 INNER JOIN (SELECT id FROM " + condition + ") AS t1 ON t0.id = t1.id";
27282759 }
27292760
2730-
27312761 private boolean keyPrefix ;
27322762 @ Override
27332763 public boolean isKeyPrefix () {
@@ -3111,7 +3141,7 @@ else if (w.startsWith("!")) {
31113141 }
31123142
31133143 //解决AccessVerifier新增userId没有作为条件，而是作为内容，导致PUT，DELETE出错
3114- if (isWhere ) {
3144+ if (isWhere || ( StringUtil . isName ( key ) == false ) ) {
31153145 tableWhere .put (key , value );
31163146 if (whereList == null || whereList .contains (key ) == false ) {
31173147 andList .add (key );
0 commit comments