【教程】 MyBatis-Plus 多表联查 MyBatis-Plus-Join
MyBatis-Plus-Join ()
MyBatis-Plus-Join (opens new window)（简称 MPJ）是一个 MyBatis-Plus (opens new window)的增强工具，在 MyBatis-Plus 的基础上只做增强不做改变，为简化开发、提高效率而生。
#特性
- 无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
- 无感引入, 支持MP风格的查询, 您会MP就会MPJ, 无需额外的学习成本
- 兼容MP的别名、逻辑删除、枚举列、TypeHandle列等特性
- 支持注解形式一对一、一对多和连表查询形式的一对一和一对多
使用方法示例 1. 修改mapper
public interface UserMapper extends BaseMapper<User> {
}修改为
public interface UserMapper extends MPJBaseMapper<User> {
}2. 添加联查表
//组装条件
//MPJQueryWrapper<User> queryWrapper = (user, ());
MPJQueryWrapper<User> queryWrapper = new MPJQueryWrapper();
Page<User> page = new Page<User>(pageNo, pageSize);
//添加联查表
("sys_user_ext q on q.user_id = ");
IPage<User> pageList = (page, queryWrapper);3. 服务层查询
public IPage<User> pageJoin(Page<User> page, MPJQueryWrapper<User> queryWrapper) {
//配置需要查询的字段
();
// ("");
return (page, , queryWrapper);
}4. 请求体
@Data
@ApiModel(description = "请求体")
public class UserListReq extends User {
//联查表的字段 需要加别名 不加别名的话 默认是主表 也就是 t.
@TableField("")
@ApiModelProperty(value = "用户地址")
private String addr;
}可以不用这个
JoinQueryGenerator
package ;
import ;
import ;
import .slf4j.Slf4j;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .*;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .*;
import ;
import ;
import ;
/**
* @Description: 查询生成器
* @author: jeecg-boot
*/
@Slf4j
public class JoinQueryGenerator {
public static final String SQL_RULES_COLUMN = "SQL_RULES_COLUMN";
private static final String BEGIN = "_begin";
private static final String END = "_end";
/**
* 数字类型字段，拼接此后缀 接受多值参数
*/
private static final String MULTI = "_MultiString";
private static final String STAR = "*";
private static final String COMMA = ",";
/**
* 查询 逗号转义符 相当于一个逗号【作废】
*/
public static final String QUERY_COMMA_ESCAPE = "++";
private static final String NOT_EQUAL = "!";
/**页面带有规则值查询，空格作为分隔符*/
private static final String QUERY_SEPARATE_KEYWORD = " ";
/**高级查询前端传来的参数名*/
private static final String SUPER_QUERY_PARAMS = "superQueryParams";
/** 高级查询前端传来的拼接方式参数名 */
private static final String SUPER_QUERY_MATCH_TYPE = "superQueryMatchType";
/** 单引号 */
public static final String SQL_SQ = "'";
/**排序列*/
private static final String ORDER_COLUMN = "column";
/**排序方式*/
private static final String ORDER_TYPE = "order";
private static final String ORDER_TYPE_ASC = "ASC";
/**mysql 模糊查询之特殊字符下划线 （_、\）*/
public static final String LIKE_MYSQL_SPECIAL_STRS = "_,%";
/**日期格式化yyyy-MM-dd*/
public static final String YYYY_MM_DD = "yyyy-MM-dd";
/**to_date*/
public static final String TO_DATE = "to_date";
/**时间格式化 */
private static final ThreadLocal<SimpleDateFormat> LOCAL = new ThreadLocal<SimpleDateFormat>();
private static SimpleDateFormat getTime(){
SimpleDateFormat time = ();
if(time == null){
time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
(time);
}
return time;
}
/**
* 获取查询条件构造器QueryWrapper实例 通用查询条件已被封装完成
* @param searchObj 查询实体
* @param parameterMap ()
* @return QueryWrapper实例
*/
public static <T> MPJQueryWrapper<T> initQueryWrapper(T searchObj, Map<String, String[]> parameterMap){
long start = ();
MPJQueryWrapper<T> queryWrapper = new MPJQueryWrapper<T>();
installMplus(queryWrapper, searchObj, parameterMap, null);
("---查询条件构造器初始化完成,耗时:"+(()-start)+"毫秒----");
return queryWrapper;
}
//update-begin---author:chenrui ---date:20240527 for：[TV360X-378]增加自定义字段查询规则功能------------
/**
* 获取查询条件构造器QueryWrapper实例 通用查询条件已被封装完成
* @param searchObj 查询实体
* @param parameterMap ()
* @param customRuleMap 自定义字段查询规则 {field:QueryRuleEnum}
* @return QueryWrapper实例
*/
public static <T> MPJQueryWrapper<T> initQueryWrapper(T searchObj,Map<String, String[]> parameterMap, Map<String, QueryRuleEnum> customRuleMap){
long start = ();
MPJQueryWrapper<T> queryWrapper = new MPJQueryWrapper<T>();
installMplus(queryWrapper, searchObj, parameterMap, customRuleMap);
("---查询条件构造器初始化完成,耗时:"+(()-start)+"毫秒----");
return queryWrapper;
}
//update-end---author:chenrui ---date:20240527 for：[TV360X-378]增加自定义字段查询规则功能------------
/**
* 组装Mybatis Plus 查询条件
* <p>使用此方法 需要有如下几点注意:
* <br>1.使用QueryWrapper 而非LambdaQueryWrapper;
* <br>2.实例化QueryWrapper时不可将实体传入参数
* <br>错误示例:如QueryWrapper<JeecgDemo> queryWrapper = new QueryWrapper<JeecgDemo>(jeecgDemo);
* <br>正确示例:QueryWrapper<JeecgDemo> queryWrapper = new QueryWrapper<JeecgDemo>();
* <br>3.也可以不使用这个方法直接调用 {@link #initQueryWrapper}直接获取实例
*/
private static void installMplus(MPJQueryWrapper<?> queryWrapper, Object searchObj, Map<String, String[]> parameterMap, Map<String, QueryRuleEnum> customRuleMap) {
/*
* 注意:权限查询由前端配置数据规则 当一个人有多个所属部门时候 可以在规则配置包含条件 orgCode 包含 #{sys_org_code}
但是不支持在自定义SQL中写orgCode in #{sys_org_code}
当一个人只有一个部门 就直接配置等于条件: orgCode 等于 #{sys_org_code} 或者配置自定义SQL: orgCode = '#{sys_org_code}'
*/
//区间条件组装 模糊查询 高级查询组装 简单排序 权限查询
PropertyDescriptor[] origDescriptors = (searchObj);
Map<String,SysPermissionDataRuleModel> ruleMap = getRuleMap();
Map<String, Field> fieldMap = new HashMap<>();
Field[] declaredFields = ().getDeclaredFields();
for (Field declaredField : declaredFields) {
((), declaredField);
}
//权限规则自定义SQL表达式
for (String c : ()) {
if((c) && (SQL_RULES_COLUMN)){
(i ->(getSqlRuleValue((c).getRuleValue())));
}
}
String name, type, column;
// update-begin--Author:taoyan Date:20200923 for：issues/1671 如果字段加注解了@TableField(exist = false),不走DB查询-------
//定义实体字段和数据库字段名称的映射 高级查询中 只能获取实体字段 如果设置TableField注解 那么查询条件会出问题
Map<String,String> fieldColumnMap = new HashMap<>(5);
for (int i = 0; i < ; i++) {
//aliasName = origDescriptors[i].getName(); mybatis 不存在实体属性 不用处理别名的情况
name = origDescriptors[i].getName();
// Field field1 = (name);
// TableField annotationTableField = ();
// if(annotationTableField != null){
// String value = ();
// }
type = origDescriptors[i].getPropertyType().toString();
try {
if (judgedIsUselessField(name)|| !(searchObj, name)) {
continue;
}
Object value = (searchObj, name);
column = ((), name);
if(column==null){
//column为null只有一种情况 那就是 添加了注解@TableField(exist = false) 后续都不用处理了
continue;
}
(name,column);
//数据权限查询
if((name)) {
addRuleToQueryWrapper((name), column, origDescriptors[i].getPropertyType(), queryWrapper);
}
//区间查询
doIntervalQuery(queryWrapper, parameterMap, type, name, column);
//判断单值 参数带不同标识字符串 走不同的查询
//TODO 这种前后带逗号的支持分割后模糊查询(多选字段查询生效) 示例：,1,3,
if (null != value && ().startsWith(COMMA) && ().endsWith(COMMA)) {
String multiLikeval = ().replace(",,", COMMA);
String[] vals = (1, ()).split(COMMA);
final String field = (column);
if(>1) {
(j -> {
("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", field, "like", vals[0]);
j = (field,vals[0]);
for (int k=1;k<;k++) {
j = ().like(field,vals[k]);
("---查询过滤器，Query规则 .or()---field:{}, rule:{}, value:{}", field, "like", vals[k]);
}
//return j;
});
}else {
("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", field, "like", vals[0]);
(j -> (field,vals[0]));
}
}else {
//update-begin---author:chenrui ---date:20240527 for：[TV360X-378]增加自定义字段查询规则功能------------
QueryRuleEnum rule;
if(null != customRuleMap && (name)) {
// 有自定义规则,使用自定义规则.
rule = (name);
}else {
//根据参数值带什么关键字符串判断走什么类型的查询
rule = convert2Rule(value);
}
//update-end---author:chenrui ---date:20240527 for：[TV360X-378]增加自定义字段查询规则功能------------
value = replaceValue(rule,value);
// add -begin 添加判断为字符串时设为全模糊查询
//if( (rule==null || (rule)) && "class ".equals(type)) {
// 可以设置左右模糊或全模糊，因人而异
//rule = ;
//}
// add -end 添加判断为字符串时设为全模糊查询
addEasyQuery(queryWrapper, column, rule, value);
}
} catch (Exception e) {
((), e);
}
}
// 排序逻辑 处理
doMultiFieldsOrder(queryWrapper, parameterMap, fieldColumnMap);
//高级查询
doSuperQuery(queryWrapper, parameterMap, fieldColumnMap);
// update-end--Author:taoyan Date:20200923 for：issues/1671 如果字段加注解了@TableField(exist = false),不走DB查询-------
}
/**
* 区间查询
* @param queryWrapper query对象
* @param parameterMap 参数map
* @param type 字段类型
* @param filedName 字段名称
* @param columnName 列名称
*/
private static void doIntervalQuery(MPJQueryWrapper<?> queryWrapper, Map<String, String[]> parameterMap, String type, String filedName, String columnName) throws ParseException {
// 添加 判断是否有区间值
String endValue = null,beginValue = null;
if (parameterMap != null && (filedName + BEGIN)) {
beginValue = (filedName + BEGIN)[0].trim();
addQueryByRule(queryWrapper, columnName, type, beginValue, );
}
if (parameterMap != null && (filedName + END)) {
endValue = (filedName + END)[0].trim();
addQueryByRule(queryWrapper, columnName, type, endValue, );
}
//多值查询
if (parameterMap != null && (filedName + MULTI)) {
endValue = (filedName + MULTI)[0].trim();
addQueryByRule(queryWrapper, (MULTI,""), type, endValue, );
}
}
private static void doMultiFieldsOrder(MPJQueryWrapper<?> queryWrapper,Map<String, String[]> parameterMap, Map<String,String> fieldColumnMap) {
Set<String> allFields = ();
String column=null,order=null;
if(parameterMap!=null&& (ORDER_COLUMN)) {
column = (ORDER_COLUMN)[0];
}
if(parameterMap!=null&& (ORDER_TYPE)) {
order = (ORDER_TYPE)[0];
}
("排序规则>>列:" + column + ",排序方式:" + order);
//update-begin-author:scott date:2022-11-07 for:避免用户自定义表无默认字段{创建时间}，导致排序报错
//TODO 避免用户自定义表无默认字段创建时间，导致排序报错
if(DataBaseConstant.CREATE_TIME.equals(column) && !(DataBaseConstant.CREATE_TIME)){
column = "id";
("检测到实体里没有字段createTime，改成采用ID排序！");
}
//update-end-author:scott date:2022-11-07 for:避免用户自定义表无默认字段{创建时间}，导致排序报错
if ((column) && (order)) {
//字典字段，去掉字典翻译文本后缀
if((CommonConstant.DICT_TEXT_SUFFIX)) {
column = (0, (CommonConstant.DICT_TEXT_SUFFIX));
}
//update-begin-author:taoyan date:2022-5-16 for: issues/3676 获取系统用户列表时，使用SQL注入生效
//判断column是不是当前实体的
("当前字段有："+ allFields);
if (!allColumnExist(column, allFields)) {
throw new JeecgBootException("请注意，将要排序的列字段不存在：" + column);
}
//update-end-author:taoyan date:2022-5-16 for: issues/3676 获取系统用户列表时，使用SQL注入生效
//update-begin-author:scott date:2022-10-10 for:【jeecg-boot/issues/I5FJU6】doMultiFieldsOrder() 多字段排序方法存在问题
//多字段排序方法没有读取 MybatisPlus 注解 @TableField 里 value 的值
if ((",")) {
List<String> columnList = ((","));
String columnStrNew = ().map(c -> (c)).collect((","));
if ((columnStrNew)) {
column = columnStrNew;
}
}else{
column = (column);
}
//update-end-author:scott date:2022-10-10 for:【jeecg-boot/issues/I5FJU6】doMultiFieldsOrder() 多字段排序方法存在问题
//SQL注入check
(column);
//update-begin--Author:scott Date:20210531 for：36 多条件排序无效问题修正-------
// 排序规则修改
// 将现有排序 _ 前端传递排序条件{....,column: 'column1,column2',order: 'desc'} 翻译成sql "column1,column2 desc"
// 修改为 _ 前端传递排序条件{....,column: 'column1,column2',order: 'desc'} 翻译成sql "column1 desc,column2 desc"
List<String> sqlInjectSortFields = ((","));
List fields = new ArrayList();
for (int i = 0; i < (); i++) {
String s = (i);
if((".")){
(s);
}else{
("t." + s);
}
}
if (().indexOf(ORDER_TYPE_ASC)>=0) {
(fields);
} else {
(fields);
}
//update-end--Author:scott Date:20210531 for：36 多条件排序无效问题修正-------
}
}
//update-begin-author:taoyan date:2022-5-23 for: issues/3676 获取系统用户列表时，使用SQL注入生效
/**
* 多字段排序 判断所传字段是否存在
* @return
*/
private static boolean allColumnExist(String columnStr, Set<String> allFields){
boolean exist = true;
if((COMMA)>=0){
String[] arr = (COMMA);
for(String column: arr){
if(!(column)){
exist = false;
break;
}
}
}else{
exist = (columnStr);
}
return exist;
}
//update-end-author:taoyan date:2022-5-23 for: issues/3676 获取系统用户列表时，使用SQL注入生效
/**
* 高级查询
* @param queryWrapper 查询对象
* @param parameterMap 参数对象
* @param fieldColumnMap 实体字段和数据库列对应的map
*/
private static void doSuperQuery(MPJQueryWrapper<?> queryWrapper,Map<String, String[]> parameterMap, Map<String,String> fieldColumnMap) {
if(parameterMap!=null&& (SUPER_QUERY_PARAMS)){
String superQueryParams = (SUPER_QUERY_PARAMS)[0];
String superQueryMatchType = (SUPER_QUERY_MATCH_TYPE) != null ? (SUPER_QUERY_MATCH_TYPE)[0] : ();
MatchTypeEnum matchType = (superQueryMatchType);
// update-begin--Author:sunjianlei Date:20200325 for：高级查询的条件要用括号括起来，防止和用户的其他条件冲突 -------
try {
superQueryParams = (superQueryParams, "UTF-8");
List<QueryCondition> conditions = (superQueryParams, );
if (conditions == null || () == 0) {
return;
}
// update-begin-author:sunjianlei date:20220119 for: 【JTC-573】 过滤空条件查询，防止 sql 拼接多余的 and
List<QueryCondition> filterConditions = ().filter(
rule -> (())
&& (())
&& (())
).collect(());
if (() == 0) {
return;
}
// update-end-author:sunjianlei date:20220119 for: 【JTC-573】 过滤空条件查询，防止 sql 拼接多余的 and
("---高级查询参数-->" + filterConditions);
(andWrapper -> {
for (int i = 0; i < (); i++) {
QueryCondition rule = (i);
if ((())
&& (())
&& (())) {
("SuperQuery ==> " + ());
//update-begin-author:taoyan date:20201228 for: 【高级查询】 oracle 日期等于查询报错
Object queryValue = ();
if("date".equals(())){
queryValue = DateUtils.str2Date((),DateUtils.date_sdf.get());
}else if("datetime".equals(())){
queryValue = DateUtils.str2Date((), ());
}
// update-begin--author:sunjianlei date:20210702 for：【/issues/I3VR8E】高级查询没有类型转换，查询参数都是字符串类型 ----
String dbType = ();
if ((dbType)) {
try {
String valueStr = (queryValue);
switch (().trim()) {
case "int":
queryValue = (valueStr);
break;
case "bigdecimal":
queryValue = new BigDecimal(valueStr);
break;
case "short":
queryValue = (valueStr);
break;
case "long":
queryValue = (valueStr);
break;
case "float":
queryValue = (valueStr);
break;
case "double":
queryValue = (valueStr);
break;
case "boolean":
queryValue = (valueStr);
break;
default:
}
} catch (Exception e) {
("高级查询值转换失败：", e);
}
}
// update-begin--author:sunjianlei date:20210702 for：【/issues/I3VR8E】高级查询没有类型转换，查询参数都是字符串类型 ----
addEasyQuery(andWrapper, (()), (()), queryValue);
//update-end-author:taoyan date:20201228 for: 【高级查询】 oracle 日期等于查询报错
// 如果拼接方式是OR，就拼接OR
if ( == matchType && i < (() - 1)) {
();
}
}
}
//return andWrapper;
});
} catch (UnsupportedEncodingException e) {
("--高级查询参数转码失败：" + superQueryParams, e);
} catch (Exception e) {
("--高级查询拼接失败：" + ());
();
}
// update-end--Author:sunjianlei Date:20200325 for：高级查询的条件要用括号括起来，防止和用户的其他条件冲突 -------
}
//(" superQuery getCustomSqlSegment: "+ ());
}
/**
* 根据所传的值 转化成对应的比较方式
* 支持><= like in !
* @param value
* @return
*/
public static QueryRuleEnum convert2Rule(Object value) {
// 避免空数据
// update-begin-author:taoyan date:20210629 for: 查询条件输入空格导致return null后续判断导致抛出null异常
if (value == null) {
return ;
}
String val = (value + "").toString().trim();
if (() == 0) {
return ;
}
// update-end-author:taoyan date:20210629 for: 查询条件输入空格导致return null后续判断导致抛出null异常
QueryRuleEnum rule =null;
//update-begin--Author:scott Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
//TODO 此处规则，只适用于 le lt ge gt
// step 2 .>= =<
int length2 = 2;
int length3 = 3;
if (rule == null && () >= length3) {
if(QUERY_SEPARATE_KEYWORD.equals((length2, length3))){
rule = ((0, 2));
}
}
// step 1 .> <
if (rule == null && () >= length2) {
if(QUERY_SEPARATE_KEYWORD.equals((1, length2))){
rule = ((0, 1));
}
}
//update-end--Author:scott Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284---------------------
// step 3 like
//update-begin-author:taoyan for: /issues/3382 默认带*就走模糊，但是如果只有一个*，那么走等于查询
if(rule == null && (STAR)){
rule = ;
}
//update-end-author:taoyan for: /issues/3382 默认带*就走模糊，但是如果只有一个*，那么走等于查询
if (rule == null && (STAR)) {
if ((STAR) && (STAR)) {
rule = ;
} else if ((STAR)) {
rule = QueryRuleEnum.LEFT_LIKE;
} else if((STAR)){
rule = QueryRuleEnum.RIGHT_LIKE;
}
}
// step 4 in
if (rule == null && (COMMA)) {
//TODO in 查询这里应该有个bug 如果一字段本身就是多选 此时用in查询 未必能查询出来
rule = ;
}
// step 5 !=
if(rule == null && (NOT_EQUAL)){
rule = ;
}
// step 6 xx+xx+xx 这种情况适用于如果想要用逗号作精确查询 但是系统默认逗号走in 所以可以用++替换【此逻辑作废】
if(rule == null && (QUERY_COMMA_ESCAPE)>0){
rule = QueryRuleEnum.EQ_WITH_ADD;
}
//update-begin--Author:taoyan Date:20201229 for：initQueryWrapper组装sql查询条件错误 #284---------------------
//特殊处理：Oracle的表达式to_date('xxx','yyyy-MM-dd')含有逗号，会被识别为in查询，转为等于查询
if(rule == && (YYYY_MM_DD)>=0 && (TO_DATE)>=0){
rule = ;
}
//update-end--Author:taoyan Date:20201229 for：initQueryWrapper组装sql查询条件错误 #284---------------------
return rule != null ? rule : ;
}
/**
* 替换掉关键字字符
*
* @param rule
* @param value
* @return
*/
private static Object replaceValue(QueryRuleEnum rule, Object value) {
if (rule == null) {
return null;
}
if (! (value instanceof String)){
return value;
}
String val = (value + "").toString().trim();
//update-begin-author:taoyan date:20220302 for: 查询条件的值为等号（=）bug #3443
if(().equals(val)){
return val;
}
//update-end-author:taoyan date:20220302 for: 查询条件的值为等号（=）bug #3443
if (rule == ) {
value = (1, () - 1);
//mysql 模糊查询之特殊字符下划线 （_、\）
value = specialStrConvert(());
} else if (rule == QueryRuleEnum.LEFT_LIKE || rule == ) {
value = (1);
//mysql 模糊查询之特殊字符下划线 （_、\）
value = specialStrConvert(());
} else if (rule == QueryRuleEnum.RIGHT_LIKE) {
value = (0, () - 1);
//mysql 模糊查询之特殊字符下划线 （_、\）
value = specialStrConvert(());
} else if (rule == ) {
value = (",");
} else if (rule == QueryRuleEnum.EQ_WITH_ADD) {
value = ("\\+\\+", COMMA);
}else {
//update-begin--Author:scott Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
if((())){
//TODO 此处逻辑应该注释掉-> 如果查询内容中带有查询匹配规则符号，就会被截取的（比如：>=您好）
value = ((),"");
}else if((()+QUERY_SEPARATE_KEYWORD)){
value = (()+QUERY_SEPARATE_KEYWORD,"").trim();
}
//update-end--Author:scott Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
}
return value;
}
private static void addQueryByRule(MPJQueryWrapper<?> queryWrapper,String name,String type,String value,QueryRuleEnum rule) throws ParseException {
if((value)) {
//update-begin--Author:sunjianlei Date:20220104 for：【JTC-409】修复逗号分割情况下没有转换类型，导致类型严格的数据库查询报错 -------------------
// 针对数字类型字段，多值查询
if((COMMA)){
Object[] temp = ((COMMA)).map(v -> {
try {
return (v, type, rule);
} catch (ParseException e) {
();
return v;
}
}).toArray();
addEasyQuery(queryWrapper, name, rule, temp);
return;
}
Object temp = (value, type, rule);
addEasyQuery(queryWrapper, name, rule, temp);
//update-end--Author:sunjianlei Date:20220104 for：【JTC-409】修复逗号分割情况下没有转换类型，导致类型严格的数据库查询报错 -------------------
}
}
/**
* 根据类型转换给定的值
* @param value
* @param type
* @param rule
* @return
* @throws ParseException
*/
private static Object parseByType(String value, String type, QueryRuleEnum rule) throws ParseException {
Object temp;
switch (type) {
case "class ":
temp = (value);
break;
case "class ":
temp = new BigDecimal(value);
break;
case "class ":
temp = (value);
break;
case "class ":
temp = (value);
break;
case "class ":
temp = (value);
break;
case "class ":
temp = (value);
break;
case "class ":
temp = getDateQueryByRule(value, rule);
break;
default:
temp = value;
break;
}
return temp;
}
/**
* 获取日期类型的值
* @param value
* @param rule
* @return
* @throws ParseException
*/
private static Date getDateQueryByRule(String value,QueryRuleEnum rule) throws ParseException {
Date date = null;
int length = 10;
if(()==length) {
if(rule==) {
//比较大于
date = getTime().parse(value + " 00:00:00");
}else if(rule==) {
//比较小于
date = getTime().parse(value + " 23:59:59");
}
//TODO 日期类型比较特殊 可能oracle下不一定好使
}
if(date==null) {
date = getTime().parse(value);
}
return date;
}
/**
* 根据规则走不同的查询
* @param queryWrapper QueryWrapper
* @param name 字段名字
* @param rule 查询规则
* @param value 查询条件值
*/
public static void addEasyQuery(MPJQueryWrapper<?> queryWrapper, String name, QueryRuleEnum rule, Object value) {
if (name==null || value == null || rule == null || (value)) {
return;
}
name = (name);
if(!(".")){
name = "t." + name;
}
("---高级查询 Query规则---field:{} , rule:{} , value:{}",name,(),value);
switch (rule) {
case GT:
(name, value);
break;
case GE:
(name, value);
break;
case LT:
(name, value);
break;
case LE:
(name, value);
break;
case EQ:
case EQ_WITH_ADD:
(name, value);
break;
case NE:
(name, value);
break;
case IN:
if(value instanceof String) {
(name, (Object[])().split(COMMA));
}else if(value instanceof String[]) {
(name, (Object[]) value);
}
//update-begin-author:taoyan date:20200909 for:【bug】in 类型多值查询 不适配postgresql #1671
else if(().isArray()) {
(name, (Object[])value);
}else {
(name, value);
}
//update-end-author:taoyan date:20200909 for:【bug】in 类型多值查询 不适配postgresql #1671
break;
case LIKE:
(name, value);
break;
case LEFT_LIKE:
(name, value);
break;
case NOT_LEFT_LIKE:
(name, value);
break;
case RIGHT_LIKE:
(name, value);
break;
case NOT_RIGHT_LIKE:
(name, value);
break;
//update-begin---author:chenrui ---date:20240527 for：[TV360X-378]下拉多框根据条件查询不出来:增加自定义字段查询规则功能------------
case LIKE_WITH_OR:
final String nameFinal = name;
Object[] vals;
if (value instanceof String) {
vals = ().split(COMMA);
} else if (value instanceof String[]) {
vals = (Object[]) value;
}
//update-begin-author:taoyan date:20200909 for:【bug】in 类型多值查询 不适配postgresql #1671
else if (().isArray()) {
vals = (Object[]) value;
} else {
vals = new Object[]{value};
}
(j -> {
("---查询过滤器，Query规则---field:{}, rule:{}, value:{}", nameFinal, "like", vals[0]);
j = (nameFinal, vals[0]);
for (int k = 1; k < ; k++) {
j = ().like(nameFinal, vals[k]);
("---查询过滤器，Query规则 .or()---field:{}, rule:{}, value:{}", nameFinal, "like", vals[k]);
}
});
break;
//update-end---author:chenrui ---date:20240527 for：[TV360X-378]下拉多框根据条件查询不出来:增加自定义字段查询规则功能------------
default:
("--查询规则未匹配到---");
break;
}
}
/**
*
* @param name
* @return
*/
private static boolean judgedIsUselessField(String name) {
return "class".equals(name) || "ids".equals(name)
|| "page".equals(name) || "rows".equals(name)
|| "sort".equals(name) || "order".equals(name);
}
/**
* 获取请求对应的数据权限规则 TODO 相同列权限多个 有问题
* @return
*/
public static Map<String, SysPermissionDataRuleModel> getRuleMap() {
Map<String, SysPermissionDataRuleModel> ruleMap = new HashMap<>(5);
List<SysPermissionDataRuleModel> list = null;
//update-begin-author:taoyan date:2023-6-1 for:QQYUN-5441 【简流】获取多个用户/部门/角色 设置部门查询 报错
try {
list = ();
}catch (Exception e){
("根据request对象获取权限数据失败，可能是定时任务中执行的。", e);
}
//update-end-author:taoyan date:2023-6-1 for:QQYUN-5441 【简流】获取多个用户/部门/角色 设置部门查询 报错
if(list != null&&()>0){
if((0)==null){
return ruleMap;
}
for (SysPermissionDataRuleModel rule : list) {
String column = ();
if(QueryRuleEnum.SQL_RULES.getValue().equals(())) {
column = SQL_RULES_COLUMN+();
}
(column, rule);
}
}
return ruleMap;
}
private static void addRuleToQueryWrapper(SysPermissionDataRuleModel dataRule, String name, Class propertyType, MPJQueryWrapper<?> queryWrapper) {
QueryRuleEnum rule = (());
if(() && ! ()) {
String[] values = ().split(",");
Object[] objs = new Object[];
for (int i = 0; i < ; i++) {
objs[i] = (values[i], propertyType);
}
addEasyQuery(queryWrapper, name, rule, objs);
}else {
if (()) {
addEasyQuery(queryWrapper, name, rule, converRuleValue(()));
}else if (()) {
String dateStr =converRuleValue(());
int length = 10;
if(()==length){
addEasyQuery(queryWrapper, name, rule, DateUtils.str2Date(dateStr,DateUtils.date_sdf.get()));
}else{
addEasyQuery(queryWrapper, name, rule, DateUtils.str2Date(dateStr,()));
}
}else {
addEasyQuery(queryWrapper, name, rule, ((), propertyType));
}
}
}
public static String converRuleValue(String ruleValue) {
String value = (ruleValue,null);
return value!= null ? value : ruleValue;
}
/**
* @author: scott
* @Description: 去掉值前后单引号
* @date: 2020/3/19 21:26
* @param ruleValue:
* @Return:
*/
public static String trimSingleQuote(String ruleValue) {
if ((ruleValue)) {
return "";
}
if ((JoinQueryGenerator.SQL_SQ)) {
ruleValue = (1);
}
if ((JoinQueryGenerator.SQL_SQ)) {
ruleValue = (0, () - 1);
}
return ruleValue;
}
public static String getSqlRuleValue(String sqlRule){
try {
Set<String> varParams = getSqlRuleParams(sqlRule);
for(String var:varParams){
String tempValue = converRuleValue(var);
sqlRule = ("#{"+var+"}",tempValue);
}
} catch (Exception e) {
((), e);
}
return sqlRule;
}
/**
* 获取sql中的#{key} 这个key组成的set
*/
public static Set<String> getSqlRuleParams(String sql) {
if((sql)){
return null;
}
Set<String> varParams = new HashSet<String>();
String regex = "\\#\\{\\w+\\}";
Pattern p = (regex);
Matcher m = (sql);
while(()){
String var = ();
((("{")+1,("}")));
}
return varParams;
}
/**
* 获取查询条件
* @param field
* @param alias
* @param value
* @param isString
* @return
*/
public static String getSingleQueryConditionSql(String field,String alias,Object value,boolean isString) {
return (field, alias, value, isString,null);
}
/**
* 根据权限相关配置生成相关的SQL 语句
* @param clazz
* @return
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static String installAuthJdbc(Class<?> clazz) {
StringBuffer sb = new StringBuffer();
//权限查询
Map<String,SysPermissionDataRuleModel> ruleMap = getRuleMap();
PropertyDescriptor[] origDescriptors = (clazz);
String sqlAnd = " and ";
for (String c : ()) {
if((c) && (SQL_RULES_COLUMN)){
(sqlAnd+getSqlRuleValue((c).getRuleValue()));
}
}
String name, column;
for (int i = 0; i < ; i++) {
name = origDescriptors[i].getName();
if (judgedIsUselessField(name)) {
continue;
}
if((name)) {
column = (clazz, name);
if(column==null){
continue;
}
SysPermissionDataRuleModel dataRule = (name);
QueryRuleEnum rule = (());
Class propType = origDescriptors[i].getPropertyType();
boolean isString = ();
Object value;
//update-begin---author:chenrui ---date:20240527 for：[TV360X-539]数据权限，配置日期等于条件时后端报转换错误------------
if(isString || (propType)) {
//update-end---author:chenrui ---date:20240527 for：[TV360X-539]数据权限，配置日期等于条件时后端报转换错误------------
value = converRuleValue(());
}else {
value = ((),propType);
}
String filedSql = (rule, (column), value,isString);
(sqlAnd+filedSql);
}
}
("query auth sql is:"+());
return ();
}
/**
* 根据权限相关配置 组装mp需要的权限
* @param queryWrapper
* @param clazz
* @return
*/
public static void installAuthMplus(MPJQueryWrapper<?> queryWrapper,Class<?> clazz) {
//权限查询
Map<String,SysPermissionDataRuleModel> ruleMap = getRuleMap();
PropertyDescriptor[] origDescriptors = (clazz);
for (String c : ()) {
if((c) && (SQL_RULES_COLUMN)){
(i ->(getSqlRuleValue((c).getRuleValue())));
}
}
String name, column;
for (int i = 0; i < ; i++) {
name = origDescriptors[i].getName();
if (judgedIsUselessField(name)) {
continue;
}
column = (clazz, name);
if(column==null){
continue;
}
if((name)) {
addRuleToQueryWrapper((name), column, origDescriptors[i].getPropertyType(), queryWrapper);
}
}
}
/**
* 转换sql中的系统变量
* @param sql
* @return
*/
public static String convertSystemVariables(String sql){
return getSqlRuleValue(sql);
}
/**
* 获取系统数据库类型
*/
private static String getDbType(){
return ();
}
/**
* mysql 模糊查询之特殊字符下划线 （_、\）
*
* @param value:
* @Return:
*/
private static String specialStrConvert(String value) {
if (DataBaseConstant.DB_TYPE_MYSQL.equals(getDbType()) || DataBaseConstant.DB_TYPE_MARIADB.equals(getDbType())) {
String[] specialStr = JoinQueryGenerator.LIKE_MYSQL_SPECIAL_STRS.split(",");
for (String str : specialStr) {
if ((str) !=-1) {
value = (str, "\\" + str);
}
}
}
return value;
}
}