基于MyBatisPlus QueryWrapper封装的基础CRUD Controller

记于：2025-03-29 晚上
地点：浙江省·温州市·家里
天气：雨天

• 背景
• 功能
• 示例
• 代码实现
• 后续

背景#

准备搭建个java脚手架，先做基础的crud封装

功能#

实现基于范型的基础CrudController，提供基础的crud接口；
其中查询接口支持按实体类中字段进行equal查询，另外支持按客户端指定字段查询；
对于非实体类的自定义查询类，支持标注@QueryField注解指定字段查询方式；

示例#

自定义查询类定义：

@Data
@EqualsAndHashCode(callSuper = true)
@Query(custom = true)
public class SysUserQueryDto extends BaseQueryDto {

    /**
     * 用户名
     */
    @QueryField(QueryField.Type.LIKE_RIGHT)
    private String username;

    /**
     * 密码
     */
    @QueryField(QueryField.Type.EQ)
    private String password;
}

@Data
public class BaseQueryDto implements Serializable {

    /**
     * 客户端自定义查询条件，需要在@Query中显式开启
     * 命名添加_后缀，避免和表字段冲突（前缀方式jackson默认解析不了）
     */
    @JsonProperty(value = Common.CONDITIONS_FIELD_NAME)
    private String conditions_;
}

username字段以likeRight方式模糊匹配；
password字段以equal方式精确匹配；
conditions_字段用于客户端指定字段查询；

查询接口和参数：

GET - /sys-user/query
params:
    username = yeshi
    password = 123456
    conditions_ = username:likeRight:ye;username:eq:yeshimin 
    # 此处conditions_表示指定两个查询查询，用英文分号分隔，每个查询条件格式为`字段名:操作符:值`

相应的sql示例：
select * from sys_user
where username like 'yeshi%'
    and password = '123456'
    and username like 'ye%'
    and username = 'yeshimin' ;

entity类由于要做成通用范型模式，类中字段默认是equal操作符，需要使用其他操作符可以指定conditions_字段

代码实现#

以下为关键类/方法：

查询基类：

/**
 * 带查询条件字段的基类
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Query(custom = true)
public class ConditionBaseEntity<T extends BaseEntity<T>> extends BaseEntity<T> {

    /**
     * 客户端自定义查询条件，需要在@Query中显式开启
     * 添加@JsonProperty注解，使在接口请求时暴露该字段，响应时不返回该字段
     * 添加@TableField注解，标识该字段非表字段
     * 命名添加_后缀，避免和表字段冲突（前缀方式jackson默认解析不了）
     */
    @JsonProperty(value = Common.CONDITIONS_FIELD_NAME, access = JsonProperty.Access.WRITE_ONLY)
    @TableField(exist = false)
    private String conditions_;
}

@Data
@EqualsAndHashCode(callSuper = true)
@Query(custom = true)
public class SysUserQueryDto extends BaseQueryDto {

    /**
     * 用户名
     */
    @QueryField(QueryField.Type.LIKE_RIGHT)
    private String username;

    /**
     * 密码
     */
    @QueryField(QueryField.Type.LIKE)
    private String password;
}

以上两个查询基类，分别用于实体查询类和自定义查询类

---

查询注解：

/**
 * 查询类上可以不添加该注解，行为保持默认
 */
@Inherited
@Documented
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface Query {

    // 是否启用，默认是
    boolean enabled() default true;

    // 是否支持客户端自定义条件查询，默认否
    boolean custom() default false;
}

@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface QueryField {

    // default
    Type value() default Type.DEFAULT;

    Type type() default Type.DEFAULT;

    // 条件生效策略
    ConditionStrategy conditionStrategy() default ConditionStrategy.NOT_BLANK;

    @Getter
    enum Type {
        // 保留DEFAULT是为了关联value()和type()
        DEFAULT("等同于EQ", "default"),
        EQ("等于", "eq"),
        NE("不等于", "ne"),
        GT("大于", "gt"),
        GE("大于等于", "ge"),
        LT("小于", "lt"),
        LE("小于等于", "le"),
        IN("包含", "in"),
        NOT_IN("非包含", "notIn"),
        IS_NULL("为null", "isNull"),
        IS_NOT_NULL("非null", "isNotNull"),
        BETWEEN("区间", "between"),
        NOT_BETWEEN("非区间", "notBetween"),
        LIKE("模糊", "like"),
        LIKE_LEFT("左模糊", "likeLeft"),
        LIKE_RIGHT("右模糊", "likeRight"),
        NOT_LIKE("非模糊", "notLike"),
        NOT_LIKE_LEFT("非左模糊", "notLikeLeft"),
        NOT_LIKE_RIGHT("非右模糊", "notLikeRight");

        private final String desc;
        private final String exp;

        Type(String desc, String exp) {
            this.desc = desc;
            this.exp = exp;
        }

        // of
        public static Type of(String value) {
            // default为非法操作符，不可从接口参数指定，只能内部注解中指定
            if (DEFAULT.name().equalsIgnoreCase(value)) {
                return null;
            }
            for (Type type : values()) {
                if (type.getExp().equalsIgnoreCase(value)) {
                    return type;
                }
            }
            return null;
        }
    }

    /**
     * 条件生效策略
     */
    @Getter
    enum ConditionStrategy {
        NOT_NULL("非null"),
        NOT_BLANK("非空串");

        private final String desc;

        ConditionStrategy(String desc) {
            this.desc = desc;
        }
    }
}

以上两个注解分别标注在查询类上和查询字段上，分别控制查询类的行为（比如是否启动、条件生效策略等）和查询字段的行为

---

核心工具类：

package com.yeshimin.unknown.common.config.mybatis;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.yeshimin.unknown.common.consts.Common;
import com.yeshimin.unknown.domain.base.BaseQueryDto;
import com.yeshimin.unknown.domain.base.ConditionBaseEntity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

@Slf4j
public class QueryHelper<T> {

    public static <T> QueryWrapper<T> getQueryWrapper(Object query) {
        return getQueryWrapper(query, Wrappers.query());
    }

    /**
     * 生成QueryWrapper
     *
     * @param clazz 实体类Class，用于设置按实体类查询
     */
    public static <T> QueryWrapper<T> getQueryWrapper(Object query, Class<T> clazz) {
        QueryWrapper<T> wrapper = new QueryWrapper<>();
        if (clazz.isInstance(query)) {
            wrapper.setEntity(clazz.cast(query));
        }
        return getQueryWrapper(query, wrapper);
    }

    public static <T> QueryWrapper<T> getQueryWrapper(Object query, QueryWrapper<T> wrapper) {
        // 获取@Query注解
        Query queryAnno = query.getClass().getAnnotation(Query.class);
        // 是否启用查询
        boolean queryEnabled = queryAnno != null && queryAnno.enabled();
        // 是否启用自定义查询
        boolean queryCustom = queryAnno != null && queryAnno.custom();
        if (!queryEnabled) {
            log.debug("class: [{}], @Query is not enabled, skip", query.getClass().getName());
            return wrapper;
        }

        // 解析自定义查询条件
        List<Condition> listCondition = parseConditions(queryCustom, query);

        // 解析类字段定义的查询条件
        Field[] fields = query.getClass().getDeclaredFields();
        for (Field field : fields) {
            // 跳过conditions字段
            if (field.getName().equals(Common.CONDITIONS_FIELD_NAME)) {
                log.debug("skip 'conditions' field");
                continue;
            }

            // 判断是否有 @QueryField 注解
            QueryField queryField = field.getAnnotation(QueryField.class);
            if (queryField == null) {
                log.debug("{} is not annotated with @QueryField, skip", field.getName());
                continue;
            }

            // 获取原访问标识，用于复原
            boolean isAccessible = field.isAccessible();
            // 允许访问私有字段
            field.setAccessible(true);
            try {
                Object value = field.get(query);

                // 获取条件生效策略
                QueryField.ConditionStrategy conditionStrategy = queryField.conditionStrategy();
                if (conditionStrategy == QueryField.ConditionStrategy.NOT_BLANK) {
                    if (value == null) {
                        log.debug("{} is null, skip", field.getName());
                        continue;
                    }
                    if (value instanceof String && StrUtil.isBlank((String) value)) {
                        log.debug("{} is blank, skip", field.getName());
                        continue;
                    }
                } else if (conditionStrategy == QueryField.ConditionStrategy.NOT_NULL) {
                    if (value == null) {
                        log.debug("{} is null, skip", field.getName());
                        continue;
                    }
                } else {
                    log.warn("conditionStrategy is not supported, skip");
                    continue;
                }

                // 获取查询类型
                QueryField.Type type = queryField.value() == QueryField.Type.DEFAULT ?
                        (queryField.type() == QueryField.Type.DEFAULT ? (QueryField.Type.EQ) : (queryField.type())) :
                        queryField.value();

                listCondition.add(new Condition(field.getName(), type, value));
            } catch (IllegalAccessException e) {
                log.error("{}", e.getMessage());
                e.printStackTrace();
            } finally {
                field.setAccessible(isAccessible);
            }
        }

        for (Condition condition : listCondition) {
            String fieldName = condition.getProperty();
            // 实体类字段命名转为表字段命名（小驼峰转下划线）
            String columnName = StrUtil.toUnderlineCase(fieldName);
            QueryField.Type operator = condition.getOperator();
            Object value = condition.getValue();

            switch (operator) {
                // equal
                case EQ:
                    wrapper.eq(columnName, value);
                    break;
                // not equal
                case NE:
                    wrapper.ne(columnName, value);
                    break;
                // greater than
                case GT:
                    wrapper.gt(columnName, value);
                    break;
                // greater and equal
                case GE:
                    wrapper.ge(columnName, value);
                    break;
                // less than
                case LT:
                    wrapper.lt(columnName, value);
                    break;
                // less and equal
                case LE:
                    wrapper.le(columnName, value);
                    break;
                // in
                case IN:
                    if (value instanceof Object[]) {
                        if (((Object[]) value).length > 0) {
                            wrapper.in(columnName, (Object[]) value);
                        } else {
                            log.warn("{} is empty, skip", fieldName);
                        }
                    } else if (value instanceof Collection) {
                        if (!((Collection<?>) value).isEmpty()) {
                            wrapper.in(columnName, (Collection<?>) value);
                        } else {
                            log.warn("{} is empty, skip", fieldName);
                        }
                    }
                    break;
                // not in
                case NOT_IN:
                    if (value instanceof Object[]) {
                        if (((Object[]) value).length > 0) {
                            wrapper.notIn(columnName, (Object[]) value);
                        } else {
                            log.warn("{} is empty, skip", fieldName);
                        }
                    } else if (value instanceof Collection) {
                        if (!((Collection<?>) value).isEmpty()) {
                            wrapper.notIn(columnName, (Collection<?>) value);
                        } else {
                            log.warn("{} is empty, skip", fieldName);
                        }
                    }
                    break;
                // is null
                case IS_NULL:
                    wrapper.isNull(columnName);
                    break;
                // is not null
                case IS_NOT_NULL:
                    wrapper.isNotNull(columnName);
                    break;
                // between
                case BETWEEN:
                    if (value instanceof Object[]) {
                        Object[] arr = (Object[]) value;
                        if (arr.length == 2) {
                            wrapper.between(columnName, arr[0], arr[1]);
                        } else {
                            log.warn("{} is invalid, skip", fieldName);
                        }
                    } else if (value instanceof Collection) {
                        Collection<?> col = (Collection<?>) value;
                        if (col.size() == 2) {
                            Iterator<?> iterator = col.iterator();
                            wrapper.between(columnName, iterator.next(), iterator.next());
                        } else {
                            log.warn("{} is invalid, skip", fieldName);
                        }
                    } else {
                        log.warn("{} is invalid, skip", fieldName);
                    }
                    break;
                // not between
                case NOT_BETWEEN:
                    if (value instanceof Object[]) {
                        Object[] arr = (Object[]) value;
                        if (arr.length == 2) {
                            wrapper.notBetween(columnName, arr[0], arr[1]);
                        } else {
                            log.warn("{} is invalid, skip", fieldName);
                        }
                    } else if (value instanceof Collection) {
                        Collection<?> col = (Collection<?>) value;
                        if (col.size() == 2) {
                            Iterator<?> iterator = col.iterator();
                            wrapper.notBetween(columnName, iterator.next(), iterator.next());
                        } else {
                            log.warn("{} is invalid, skip", fieldName);
                        }
                    } else {
                        log.warn("{} is invalid, skip", fieldName);
                    }
                    break;
                // like
                case LIKE:
                    wrapper.like(columnName, value);
                    break;
                // like left
                case LIKE_LEFT:
                    wrapper.likeLeft(columnName, value);
                    break;
                // like right
                case LIKE_RIGHT:
                    wrapper.likeRight(columnName, value);
                    break;
                // not like
                case NOT_LIKE:
                    wrapper.notLike(columnName, value);
                    break;
                // not like left
                case NOT_LIKE_LEFT:
                    wrapper.notLikeLeft(columnName, value);
                    break;
                // not like right
                case NOT_LIKE_RIGHT:
                    wrapper.notLikeRight(columnName, value);
                    break;
                default:
                    break;
            }
        }

        return wrapper;
    }

    private static List<Condition> parseConditions(boolean queryCustom, Object query) {
        String conditions = null;
        if (queryCustom) {
            if (query instanceof BaseQueryDto) {
                conditions = ((BaseQueryDto) query).getConditions_();
            } else if (query instanceof ConditionBaseEntity) {
                conditions = ((ConditionBaseEntity<?>) query).getConditions_();
            }
        }
        return conditions == null ? new LinkedList<>() : parseConditions(conditions);
    }

    /**
     * 解析自定义查询条件
     *
     * @param conditions 自定义查询条件（可能多个） 示例：username:likeLeft:yeshi;password:eq:123456
     * @return List<Condition>
     */
    private static List<Condition> parseConditions(String conditions) {
        List<Condition> list = new LinkedList<>();
        if (StrUtil.isBlank(conditions)) {
            log.debug("conditions is blank, ignore");
            return list;
        }
        String[] arr = conditions.split(";");
        for (String s : arr) {
            String[] arr2 = s.split(":");
            if (arr2.length != 3) {
                log.warn("condition [{}] is invalid, ignore", s);
                continue;
            }
            if (StrUtil.isBlank(arr2[0])) {
                log.warn("condition [{}] -> fieldName is blank, ignore", s);
                continue;
            }
            QueryField.Type operator = QueryField.Type.of(arr2[1]);
            if (operator == null) {
                log.warn("condition [{}] -> operator is invalid, ignore", s);
                continue;
            }

            list.add(new Condition(arr2[0], operator, arr2[2]));
        }
        return list;
    }

    @Data
    @AllArgsConstructor
    private static class Condition {
        private String property;
        QueryField.Type operator;
        Object value;
    }
}

以上工具方法根据查询类中字段@QueryField注解添加相应的查询条件到QueryWrapper；
以及支持解析自定义查询条件字段conditions_，添加查询条件到QueryWrapper；
以及支持实体类默认equal匹配方式，添加查询条件到QueryWrapper；

---

代码使用示例：

/**
 * 提供基础的CRUD接口
 *
 * @param <E> 实体类
 * @param <M> BaseMapper
 * @param <S> ServiceImpl
 */
@Slf4j
@RequiredArgsConstructor
public class CrudController<E extends BaseEntity<E>, M extends BaseMapper<E>, S extends ServiceImpl<M, E>>
        extends BaseController {

    private final S service;

    /**
     * CRUD-查询
     */
    @GetMapping("/crud/query")
    public R<Page<E>> crudQuery(Page<E> page, E query) {
        @SuppressWarnings("unchecked")
        Class<E> clazz = (Class<E>) query.getClass();
        return R.ok(service.page(page, QueryHelper.getQueryWrapper(query, clazz)));
    }
}

以上为实体查询类场景，需要指定Class

public Page<SysUserEntity> query(Page<SysUserEntity> page, SysUserQueryDto dto) {
    return super.page(page, QueryHelper.getQueryWrapper(dto));
}

以上为自定义查询类场景

后续#

• conditions_方式支持数组/集合类字段查询