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;
|
import org.springblade.core.tool.utils.StringUtil;
|
import java.util.Locale;
|
import java.util.Map;
|
|
/**
|
* UBCSSqlKeyword,高级查询不传的时候默认是_equal
|
* map转wrapper时用到的比对转换
|
* @author ludc
|
* @date 2023/06/06 10:01
|
*/
|
public class UBCSSqlKeyword {
|
|
private static final String SQL_REGEX = "'|%|--|insert|delete|select|count|group|union|drop|truncate|alter|grant|execute|exec|xp_cmdshell|call|declare|sql";
|
private static final String EQUAL = "_equal";
|
private static final String NOT_EQUAL = "_notequal";
|
private static final String LIKE = "_like";
|
private static final String LIKE_LEFT = "_likeleft";
|
private static final String LIKE_RIGHT = "_likeright";
|
private static final String NOT_LIKE = "_notlike";
|
private static final String GE = "_ge";
|
private static final String LE = "_le";
|
private static final String GT = "_gt";
|
private static final String LT = "_lt";
|
private static final String DATE_GE = "_datege";
|
private static final String DATE_GT = "_dategt";
|
private static final String DATE_EQUAL = "_dateequal";
|
private static final String DATE_LT = "_datelt";
|
private static final String DATE_LE = "_datele";
|
private static final String IS_NULL = "_null";
|
private static final String NOT_NULL = "_notnull";
|
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) {
|
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);
|
}
|
|
}
|
});
|
}
|
}
|
|
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("_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")) {
|
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)) {
|
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);
|
}
|
|
}
|
});
|
}
|
}
|
|
public static void buildConditionByMapString(Map<String, String> query, QueryWrapper<?> qw) {
|
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);
|
}
|
|
}
|
});
|
}
|
}
|
|
/**
|
* 获取列名，没有转下划线映射
|
* @param column
|
* @param keyword
|
* @return
|
*/
|
public static String getColumn(String column, String keyword) {
|
//没有转下划线映射
|
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", "");
|
}
|
|
|
}
|
