From d02571d59633367ac76b7f58ab38584698b1aa1b Mon Sep 17 00:00:00 2001
From: ludc
Date: 星期四, 17 十月 2024 09:55:37 +0800
Subject: [PATCH] 高级查询中增加范围查询功能
---
Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java | 209 ++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 193 insertions(+), 16 deletions(-)
diff --git a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java b/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java
index f4016c1..6b2e357 100644
--- a/Source/UBCS/ubcs-service-api/ubcs-util-api/src/main/java/com/vci/ubcs/starter/util/UBCSSqlKeyword.java
+++ b/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;
@@ -9,7 +11,7 @@
import java.util.Map;
/**
- * UBCSSqlKeyword
+ * UBCSSqlKeyword,楂樼骇鏌ヨ涓嶄紶鐨勬椂鍊欓粯璁ゆ槸_equal
* map杞瑆rapper鏃剁敤鍒扮殑姣斿杞崲
* @author ludc
* @date 2023/06/06 10:01
@@ -39,12 +41,125 @@
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) {
if (!Func.isEmpty(query)) {
query.forEach((k, v) -> {
if (!Func.hasEmpty(new Object[]{k, v}) && !k.endsWith("_ignore")) {
- if (k.endsWith("_equal")) {
- qw.eq(getColumn(k, "_equal"), v);
+ 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")) {
@@ -76,7 +191,7 @@
} else if (k.endsWith("_notnull")) {
qw.isNotNull(getColumn(k, "_notnull"));
} else {
- qw.like(getColumn(k, "_like"), v);
+ qw.eq(getColumn(k, "_equal"), v);
}
}
@@ -84,12 +199,66 @@
}
}
+ public static void buildConditionByAs(Map<String, Object> query, MPJLambdaWrapper<?> qw, String tableAlias){
+ buildConditionMPJ(query,qw,tableAlias);
+ }
+
public static void buildCondition(Map<String, Object> query, MPJLambdaWrapper<?> qw) {
+ buildConditionMPJ(query,qw,null);
+ }
+
+ private static void buildConditionMPJ(Map<String, Object> query, MPJLambdaWrapper<?> qw, String tableAlias){
if (!Func.isEmpty(query)) {
query.forEach((k, v) -> {
if (!Func.hasEmpty(new Object[]{k, v}) && !k.endsWith("_ignore")) {
- if (k.endsWith("_equal")) {
- qw.eq(getColumn(k, "_equal"), v);
+ if (k.endsWith("_like")) {
+ qw.like(tableAlias+"."+getColumn(k, "_like"), v);
+ } else if (k.endsWith("_notequal")) {
+ qw.ne(tableAlias+"."+getColumn(k, "_notequal"), v);
+ } else if (k.endsWith("_likeleft")) {
+ qw.likeLeft(tableAlias+"."+getColumn(k, "_likeleft"), v);
+ } else if (k.endsWith("_likeright")) {
+ qw.likeRight(tableAlias+"."+getColumn(k, "_likeright"), v);
+ } else if (k.endsWith("_notlike")) {
+ qw.notLike(tableAlias+"."+getColumn(k, "_notlike"), v);
+ } else if (k.endsWith("_ge")) {
+ qw.ge(tableAlias+"."+getColumn(k, "_ge"), v);
+ } else if (k.endsWith("_le")) {
+ qw.le(tableAlias+"."+getColumn(k, "_le"), v);
+ } else if (k.endsWith("_gt")) {
+ qw.gt(tableAlias+"."+getColumn(k, "_gt"), v);
+ } else if (k.endsWith("_lt")) {
+ qw.lt(tableAlias+"."+getColumn(k, "_lt"), v);
+ } else if (k.endsWith("_datege")) {
+ qw.ge(tableAlias+"."+getColumn(k, "_datege"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (k.endsWith("_dategt")) {
+ qw.gt(tableAlias+"."+getColumn(k, "_dategt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (k.endsWith("_dateequal")) {
+ qw.eq(tableAlias+"."+getColumn(k, "_dateequal"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (k.endsWith("_datele")) {
+ qw.le(tableAlias+"."+getColumn(k, "_datele"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (k.endsWith("_datelt")) {
+ qw.lt(tableAlias+"."+getColumn(k, "_datelt"), DateUtil.parse(String.valueOf(v), "yyyy-MM-dd HH:mm:ss"));
+ } else if (k.endsWith("_null")) {
+ qw.isNull(tableAlias+"."+getColumn(k, "_null"));
+ } else if (k.endsWith("_notnull")) {
+ qw.isNotNull(tableAlias+"."+getColumn(k, "_notnull"));
+ } else {
+ qw.eq(tableAlias+"."+getColumn(k, "_equal"), v);
+ }
+
+ }
+ });
+ }
+ }
+
+ 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")) {
@@ -121,20 +290,21 @@
} else if (k.endsWith("_notnull")) {
qw.isNotNull(getColumn(k, "_notnull"));
} else {
- qw.like(getColumn(k, "_like"), v);
+ qw.eq(getColumn(k, "_equal"), v);
}
}
});
- }
+ };
+ return qw;
}
public static void buildConditionByMapString(Map<String, String> query, MPJLambdaWrapper<?> qw) {
if (!Func.isEmpty(query)) {
query.forEach((k, v) -> {
if (!Func.hasEmpty(new Object[]{k, v}) && !k.endsWith("_ignore")) {
- if (k.endsWith("_equal")) {
- qw.eq(getColumn(k, "_equal"), v);
+ 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")) {
@@ -166,7 +336,7 @@
} else if (k.endsWith("_notnull")) {
qw.isNotNull(getColumn(k, "_notnull"));
} else {
- qw.like(getColumn(k, "_like"), v);
+ qw.eq(getColumn(k, "_equal"), v);
}
}
@@ -178,8 +348,8 @@
if (!Func.isEmpty(query)) {
query.forEach((k, v) -> {
if (!Func.hasEmpty(new Object[]{k, v}) && !k.endsWith("_ignore")) {
- if (k.endsWith("_equal")) {
- qw.eq(getColumn(k, "_equal"), v);
+ 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")) {
@@ -211,7 +381,7 @@
} else if (k.endsWith("_notnull")) {
qw.isNotNull(getColumn(k, "_notnull"));
} else {
- qw.like(getColumn(k, "_like"), v);
+ qw.eq(getColumn(k, "_equal"), v);
}
}
@@ -219,13 +389,20 @@
}
}
- private static String getColumn(String column, String keyword) {
+ /**
+ * 鑾峰彇鍒楀悕锛屾病鏈夎浆涓嬪垝绾挎槧灏�
+ * @param column
+ * @param keyword
+ * @return
+ */
+ 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", "");
}
+
}
--
Gitblit v1.9.3