Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java

@@ -1,6 +1,8 @@
package com.vci.ubcs.starter.util;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.enums.SqlKeyword;
import com.baomidou.mybatisplus.core.enums.SqlLike;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.Func;
@@ -37,6 +39,119 @@
   private static final String IGNORE = "_ignore";

   public UBCSSqlKeyword() {
   }

   private static String  likeValueSqlwhere(String field, Object value, SqlLike operation){
      String sqlwhere="";
      switch(operation) {
         case LEFT:
            return sqlwhere = field +" "+SqlKeyword.LIKE.getSqlSegment() +" '%" + value.toString() + "' ";
         case RIGHT:
            sqlwhere=field+" "+SqlKeyword.LIKE.getSqlSegment() +" '"+value.toString()+"%' ";
         case DEFAULT:
            return  sqlwhere=field+" "+SqlKeyword.LIKE.getSqlSegment() +" '%"+value.toString()+"%' ";
      }
      return sqlwhere;
   }

   private static String sqlKeywordValueSqlwhere(String field, Object value, SqlKeyword operation){
      String sqlwhere="";
      switch(operation) {
         case   IN://"IN"),
            return sqlwhere=field+" "+SqlKeyword.IN.getSqlSegment() +"('"+value.toString()+"')";
         case   NOT_IN://"NOT IN"),
            sqlwhere=field+" "+SqlKeyword.NOT_IN.getSqlSegment()  +"('"+value.toString()+"')";
         //case   LIKE://"LIKE"),
         case   NOT_LIKE://"NOT LIKE"),
            return sqlwhere=field+" "+SqlKeyword.NOT_LIKE.getSqlSegment() +" '%"+value+"%' ";
         case   EQ://"="),
            return sqlwhere=field+" "+SqlKeyword.EQ.getSqlSegment() +" '"+value.toString()+"' ";
         case   NE://"<>"),
            return sqlwhere=field+ " "+SqlKeyword.NE.getSqlSegment() +" '"+value.toString()+"' ";
         case   GT://">"),
            return sqlwhere=field+ " "+SqlKeyword.GT.getSqlSegment()  +" '"+value.toString()+"' ";
         case   GE://">="),
            return sqlwhere=field+ " "+SqlKeyword.GE.getSqlSegment() +" '"+value.toString()+"' ";
         case   LT://"<"),
            return sqlwhere=field+ " "+SqlKeyword.LT.getSqlSegment() +" '"+value.toString()+"' ";
         case   LE://"<="),
            return sqlwhere=field+" "+ SqlKeyword.LE.getSqlSegment() +" '"+value.toString()+"' ";
         case   IS_NULL://"IS NULL"),
            return sqlwhere=field+" IS NULL ";
         case   IS_NOT_NULL://"IS NOT NULL"),
            return sqlwhere=field+" IS NOT NULL ";
      }
      return sqlwhere;
   }

   private static String  dateValueSqlwhere(String field, Object value, SqlKeyword operation){
      String sqlwhere="";
      switch(operation) {
         case   EQ://"="),
            return sqlwhere=field+" "+SqlKeyword.EQ.getSqlSegment() +"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
         case   NE://"<>"),
            return sqlwhere=field+ " "+SqlKeyword.NE.getSqlSegment() +"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
         case   GT://">"),
            return sqlwhere=field+ " "+SqlKeyword.GT.getSqlSegment()  +"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
         case   GE://">="),
            return sqlwhere=field+ " "+SqlKeyword.GE.getSqlSegment() +"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
         case   LT://"<"),
            return sqlwhere=field+ " "+SqlKeyword.LT.getSqlSegment()+"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
         case   LE://"<="),
            return sqlwhere=field+" "+ SqlKeyword.LE.getSqlSegment() +"  to_date('"+value.toString()+"','yyyy-MM-dd HH:mm:ss') ";
      }
      return sqlwhere;
   }

   public static String buildSqlwhere(Map<String, Object> query) {
      StringBuffer sb=new StringBuffer();
      if (!Func.isEmpty(query)) {

         query.forEach((k, v) -> {

            if (!Func.hasEmpty(new Object[]{k, v})) {
               sb.append(" and ");
               if (k.endsWith(LIKE)) {
                  sb.append(likeValueSqlwhere(getColumn(k,LIKE),v,SqlLike.DEFAULT));
               } else if (k.endsWith(NOT_EQUAL)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,NOT_EQUAL), v,SqlKeyword.NE));
               } else if (k.endsWith(LIKE_LEFT)) {
                  sb.append(likeValueSqlwhere(getColumn(k,LIKE_LEFT),v,SqlLike.LEFT));
               } else if (k.endsWith(LIKE_RIGHT)) {
                  sb.append(likeValueSqlwhere(getColumn(k,LIKE_RIGHT),v,SqlLike.RIGHT));
               } else if (k.endsWith(NOT_LIKE)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,NOT_LIKE), v,SqlKeyword.NOT_LIKE));
               } else if (k.endsWith(GE)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,GE), v,SqlKeyword.GE));
               } else if (k.endsWith(LE)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,LE), v,SqlKeyword.LE));
               } else if (k.endsWith(GT)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,GT), v,SqlKeyword.GT));
               } else if (k.endsWith(LT)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,LT), v,SqlKeyword.LT));
               } else if (k.endsWith(DATE_GE)) {
                  sb.append(dateValueSqlwhere(getColumn(k,DATE_GE),v,SqlKeyword.GE));
               } else if (k.endsWith(DATE_GT)) {
                  sb.append(dateValueSqlwhere(getColumn(k,DATE_GT),v,SqlKeyword.GT));
               } else if (k.endsWith(DATE_EQUAL)) {
                  sb.append(dateValueSqlwhere(getColumn(k,DATE_EQUAL),v,SqlKeyword.EQ));
               } else if (k.endsWith(DATE_LE)) {
                  sb.append(dateValueSqlwhere(getColumn(k,DATE_LE),v,SqlKeyword.LE));
               } else if (k.endsWith(DATE_LT)) {
                  sb.append(dateValueSqlwhere(getColumn(k,DATE_LT),v,SqlKeyword.LT));
               } else if (k.endsWith(IS_NULL)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,IS_NULL), v,SqlKeyword.IS_NULL));
               } else if (k.endsWith(NOT_NULL)) {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,NOT_NULL), v,SqlKeyword.IS_NOT_NULL));
               } else {
                  sb.append(sqlKeywordValueSqlwhere(getColumn(k,EQUAL), v,SqlKeyword.EQ));
               }

            }
         });
      }

      return sb.toString();
   }

   public static void buildCondition(Map<String, Object> query, QueryWrapper<?> qw) {
@@ -137,6 +252,52 @@
      }
   }

   public static MPJLambdaWrapper buildConditionByMapString(Map<String, String> query) {
      MPJLambdaWrapper<?>qw=new MPJLambdaWrapper<>();
      if (!Func.isEmpty(query)) {
         query.forEach((k, v) -> {
            if (!Func.hasEmpty(new Object[]{k, v}) && !k.endsWith("_ignore")) {
               if (k.endsWith("_like")) {
                  qw.like(getColumn(k, "_like"), v);
               } else if (k.endsWith("_notequal")) {
                  qw.ne(getColumn(k, "_notequal"), v);
               } else if (k.endsWith("_likeleft")) {
                  qw.likeLeft(getColumn(k, "_likeleft"), v);
               } else if (k.endsWith("_likeright")) {
                  qw.likeRight(getColumn(k, "_likeright"), v);
               } else if (k.endsWith("_notlike")) {
                  qw.notLike(getColumn(k, "_notlike"), v);
               } else if (k.endsWith("_ge")) {
                  qw.ge(getColumn(k, "_ge"), v);
               } else if (k.endsWith("_le")) {
                  qw.le(getColumn(k, "_le"), v);
               } else if (k.endsWith("_gt")) {
                  qw.gt(getColumn(k, "_gt"), v);
               } else if (k.endsWith("_lt")) {
                  qw.lt(getColumn(k, "_lt"), v);
               } else if (k.endsWith("_datege")) {
                  qw.ge(getColumn(k, "_datege"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
               } else if (k.endsWith("_dategt")) {
                  qw.gt(getColumn(k, "_dategt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
               } else if (k.endsWith("_dateequal")) {
                  qw.eq(getColumn(k, "_dateequal"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
               } else if (k.endsWith("_datele")) {
                  qw.le(getColumn(k, "_datele"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
               } else if (k.endsWith("_datelt")) {
                  qw.lt(getColumn(k, "_datelt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
               } else if (k.endsWith("_null")) {
                  qw.isNull(getColumn(k, "_null"));
               } else if (k.endsWith("_notnull")) {
                  qw.isNotNull(getColumn(k, "_notnull"));
               } else {
                  qw.eq(getColumn(k, "_equal"), v);
               }

            }
         });
      };
      return qw;
   }

   public static void buildConditionByMapString(Map<String, String> query, MPJLambdaWrapper<?> qw) {
      if (!Func.isEmpty(query)) {
@@ -234,13 +395,14 @@
    * @param keyword
    * @return
    */
   private static String getColumn(String column, String keyword) {
   public static String getColumn(String column, String keyword) {
      //没有转下划线映射
      return StringUtil.removeSuffix(column, keyword).toLowerCase(Locale.ROOT);
      return StringUtil.removeSuffix(column, keyword).toLowerCase(Locale.ROOT).trim();
   }

   public static String filter(String param) {
      return param == null ? null : param.replaceAll("(?i)'|%|--|insert|delete|select|count|group|union|drop|truncate|alter|grant|execute|exec|xp_cmdshell|call|declare|sql", "");
   }


}