MyBatis-Plus使用通配符组装QueryWrapper条件构造器

1、前端输入指定的约束条件字符进行查询，使如输入6010*或6010%，则查出以6010开头的信息，等等，后端接收Json body进行组装QueryWrapper条件构造器

   QueryWrapperConverter.class

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 组装MyBatis-Plus条件构造器,前提条件数据库字段名与json对象属性名必须定义规范
 * @author KingLee
 * @since 1.0
 * @param <T>
 */
public class QueryWrapperConverter<T> {

    private static final String LIKE_SQL_KEY = "%";

    private static final String PLACEHOLDER_SQL_KEY = "*";

    private static final String EQ_SQL_KEY = "=";

    private static final String NE_SQL_KEY = "!=";

    private static final String NULL_SQL_KEY = "~null~";

    private static final String NOT_NULL_SQL_KEY = "!=~null~";

    private static final String SPLIT_SQL_KEY = ",";

    private static final String GT_SQL_KEY = ">";

    private static final String GE_SQL_KEY = ">=";

    private static final String LT_SQL_KEY = "<";

    private static final String LE_SQL_KEY = "<=";

    private static final String BETWEEN_SQL_KEY = "between";

    private static final String NOT_BETWEEN_SQL_KEY = "notBetween";

    private static final String AND_SQL_KEY = "and";

    private static final String SWITCH_MATCHES_REGULAR = "[a-z]+[A-Z][a-z]+([A-Z][a-z]+)*";

    private static Pattern pattern = Pattern.compile("[A-Z]");

    /**
     * 判断通配符组装MyBatis-Plus条件构造器
     * @param queryWrapper 对象条件构造器
     * @param queryObject json对象
     * @param <T>
     */
    public static<T> void converter(QueryWrapper<T> queryWrapper, Object queryObject) {
        // reflect获取实体类的所有属性信息，返回Fields数组
        Field[] fields = queryObject.getClass().getDeclaredFields();
        int fieldLength = fields.length;
        while (fieldLength-- > 0) {
            //属性名
            String fieldName = fields[fieldLength].getName();
            //属性值
            String fieldValue = getFieldValueByFieldName(fieldName, queryObject);
            //表字段名
            String dbFieldName = switchFieldNameToColumnName(fieldName);

            if (StringUtils.isEmpty(fieldValue)) {
                continue;
            }
            //以*或%结尾
            if (fieldValue.endsWith(LIKE_SQL_KEY) || fieldValue.endsWith(PLACEHOLDER_SQL_KEY)) {
                queryWrapper.likeLeft(dbFieldName, fieldValue.replace(LIKE_SQL_KEY, "").replace(PLACEHOLDER_SQL_KEY, ""));
            }
            //以*或%开头
            else if (fieldValue.startsWith(LIKE_SQL_KEY) || fieldValue.startsWith(PLACEHOLDER_SQL_KEY)) {
                queryWrapper.likeRight(dbFieldName, fieldValue.replace(LIKE_SQL_KEY, "").replace(PLACEHOLDER_SQL_KEY, ""));
            }
            //以=开头，完成匹配
            else if (fieldValue.startsWith(EQ_SQL_KEY)) {
                queryWrapper.eq(dbFieldName, fieldValue.replace(EQ_SQL_KEY, ""));
            }
            //以!=开头，完成不匹配
            else if (fieldValue.startsWith(NE_SQL_KEY)) {
                queryWrapper.ne(dbFieldName, fieldValue.replace(NE_SQL_KEY, ""));
            }
            //~null~表示空值
            else if (fieldValue.startsWith(NULL_SQL_KEY)) {
                queryWrapper.isNull(dbFieldName);
            }
            //!=~null~表示搜索非空值
            else if (fieldValue.startsWith(NOT_NULL_SQL_KEY)) {
                queryWrapper.isNotNull(dbFieldName);
            }
            //如果有逗号，则以逗号折开，拼or连接
            else if (fieldValue.contains(SPLIT_SQL_KEY)) {
                String[] splitValue = fieldValue.split(SPLIT_SQL_KEY);
                StringBuffer applySql = new StringBuffer();
                for (String value : splitValue) {
                    applySql.append(dbFieldName + " like '" + value + "%' or ");
                }
                String finalApplySql = applySql.substring(0,applySql.length()-3);
                queryWrapper.and(wrapper->wrapper.apply(finalApplySql));
            }
            //如果有>，则以>去做比较
            else if (fieldValue.contains(GT_SQL_KEY) && !fieldValue.contains(GE_SQL_KEY)) {
                queryWrapper.gt(dbFieldName, fieldValue.replace(GT_SQL_KEY, ""));
            }
            //如果有>=，则以>=去做比较
            else if (fieldValue.contains(GE_SQL_KEY)) {
                queryWrapper.ge(dbFieldName, fieldValue.replace(GE_SQL_KEY, ""));
            }
            //如果有<，则以<去做比较
            else if (fieldValue.contains(LT_SQL_KEY) && !fieldValue.contains(LE_SQL_KEY)) {
                queryWrapper.lt(dbFieldName, fieldValue.replace(LT_SQL_KEY, ""));
            }
            //如果有<=，则以<=去做比较
            else if (fieldValue.contains(LE_SQL_KEY)) {
                queryWrapper.le(dbFieldName, fieldValue.replace(LE_SQL_KEY, ""));
            }
            //如果有between...and...
            else if (fieldValue.toLowerCase().contains(BETWEEN_SQL_KEY)
                    && fieldValue.toLowerCase().contains(AND_SQL_KEY)) {
                String startValue = fieldValue.substring(7, fieldValue.indexOf(AND_SQL_KEY));
                String endValue = fieldValue.substring(fieldValue.indexOf(AND_SQL_KEY) + 3, fieldValue.length());
                queryWrapper.between(dbFieldName, startValue.trim(), endValue.trim());
            }
            //如果有notBetween...and...
            else if (fieldValue.toLowerCase().contains(NOT_BETWEEN_SQL_KEY.toLowerCase())
                    && fieldValue.toLowerCase().contains(AND_SQL_KEY)) {
                String startValue = fieldValue.substring(10, fieldValue.indexOf(AND_SQL_KEY));
                int lastLetter = fieldValue.indexOf(AND_SQL_KEY) + 3;
                String endValue = fieldValue.substring(lastLetter, fieldValue.length());
                queryWrapper.notBetween(dbFieldName, startValue.trim(), endValue.trim());
            }
            //没有通配符,则模糊查询
            else {
                queryWrapper.like(dbFieldName, fieldValue);
            }
        }
    }

    /**
     * reflect根据类对象属性名获取属性值
     * @param fieldName
     * @param object
     * @return
     */
    public static String getFieldValueByFieldName(String fieldName, Object object) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = object.getClass().getMethod(getter, new Class[] {});
            Object value = method.invoke(object, new Object[] {});
            return value.toString();
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            return null;
        }
    }

    /**
     * 根据骆峰属性名转成数据库表对应字段名
     * @e.g. companyName->company_name
     * @param fieldName
     * @return
     */
    public static String switchFieldNameToColumnName(String fieldName) {
        if (fieldName.matches(SWITCH_MATCHES_REGULAR)){
            Matcher matcher = pattern.matcher(fieldName);
            while(matcher.find()){
                String oldLetter = matcher.group();
                String newLetter = matcher.group().toLowerCase();
                fieldName = fieldName.replaceAll(oldLetter, "_" + newLetter);
            }
        }
        return fieldName;
    }
}

2、 Vo类SapUnitQuery.class，负责RequestBody传输

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.mywind.qingyun.common.PageDo;
import com.mywind.qingyun.mdm.pojo.MdmSapUnit;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * @author KineLee
 * @since 1.0
 */
@Data
@EqualsAndHashCode(callSuper = true)
public class SapUnitQuery extends PageDo {

   @ApiModelProperty(value = "主键id")
   private String id;

   @ApiModelProperty(value = "度量单位代码")
   private String unitCode;

   @ApiModelProperty(value = "度量单位文本")
   private String unitDescription;

   @ApiModelProperty(value = "创建时间")
   private String createTime;

}

3、 Service层分页访问

/**
     * 分页查询单位列表
     * @param unitQuery
     * @return
     */
    @Override
    public ResultDo listUnit(SapUnitQuery unitQuery) {
        Page<MdmSapUnit> page = new Page<>(unitQuery.getPageNumber(), unitQuery.getPageSize());
        QueryWrapper<MdmSapUnit> queryWrapper = new QueryWrapper<>();
        QueryWrapperConverter.converter(queryWrapper,unitQuery);
        return ResultDo.ok(this.page(page, queryWrapper));
    }

4、分页辅助类

import lombok.Data;

import java.io.Serializable;
/**
 * @author KingLee
 */
@Data
public class PageDo implements Serializable {

    private static final long serialVersionUID = 1L;

    private int pageNumber;

    private int pageSize;

    private String sort;

    private String order;

}