若依框架下模糊查询实现的几种方式

最新推荐文章于 2024-08-22 00:28:48 发布

CodeRain程序雨

最新推荐文章于 2024-08-22 00:28:48 发布

阅读量2.2k

点赞数

文章标签： mybatis java 数据库 sql mysql

本文链接：https://blog.csdn.net/qq_44138925/article/details/128843528

版权

本文探讨了项目中常见的模糊查询方法，包括使用MyBatis的XML配置进行SQL like查询以及通过AutoQueryWrapper结合@TableSearch注解在若依框架下实现模糊查询。文中详细展示了AutoQueryWrapper的内部实现，包括对不同查询类型的处理，如eq、like、in和ne等，并提供了示例代码以展示其工作原理。

摘要由CSDN通过智能技术生成

1、背景

模糊查询可以说项目中非常常见，如下简单探索一下几种实现

2、解决方案

方式一：sql 模糊查询(一般常用容易想到的)

mybatis中xml 对查询条件使用 like 查询

<if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>

方式二：AutoQueryWrapper + @TableSearch

若依框架下的查询实体类对应字段加上 @TableSearch(type = "like") 注解使用

User实体类

public class User{
    @TableSearch(type = "like")
    private String name;
}

Controller 查询列表

查询列表使用AutoQueryWrapper

@Api(" xxx Controller")
@RestController
@RequestMapping("/xxx/user")
public class UserController extends BaseController {
    /**
     * 查询xxx列表
     */
    @ApiOperation("查询xxx列表")
    @PreAuthorize("@ss.hasPermi('xxx:xxx:list')")
    @GetMapping("/list")
    public TableDataInfo list(User user) {
        startPage();
        ==List<User> list = userService.list(new AutoQueryWrapper<User>(user).getWrapper());==
        return getDataTable(list);
    }
}

源码

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

    /**
     * <p>
     * 属性类型 默认为Condition.eq()
     * </p>
     * <p>
     * 支持：@TableSearch(type = "like") 表示Condition.like()<br/>
     * 支持：@TableSearch(type = "in",fieldForIn = "field") 仅应用在List元素 表示Condition.in()<br/>
     * 支持：@TableSearch(type = "none") 表示对其不发起搜索<br/>
     * 支持：@TableSearch(type = "ne") 表示不等于某值<br/>
     * </p>
     */
    String type() default "eq" ;

    /**
     * <p>
     * 用于between方法的识别开始属性，值为必填项.为SQL中搜索项
     * </p>
     * <code>
     * 如 WHERE field BETWEEN firstField AND lastField
     *
     * @TableSearch(betweenStart = "field")
     * Private String firstField;
     * </code>
     */
    String betweenStart() default "" ;

    /**
     * <p>
     * 用于between方法的识别结束属性，值为必填项.为SQL中搜索项
     * </p>
     * <pre>
     * 如 WHERE field BETWEEN firstField AND lastField
     *
     * @TableSearch(betweenEnd = "field")
     * Private String lastField;
     * </pre>
     */
    String betweenEnd() default "" ;

    /**
     * <p>
     * 用于where in方法的识别属性.为SQL中搜索项
     * 当不设置时，字段为对应属性
     * </p>
     * <pre>
     * 如 WHERE field IN ("${oneField}")
     *
     * @TableSearch(type = "in",fieldForIn = "field")
     * </pre>
     */
    String fieldForIn() default "" ;
}

注解实现源码

public class AutoQueryWrapper<Record extends BaseEntity> {
    private static final Logger log = LoggerFactory.getLogger(AutoQueryWrapper.class);

    private Record record;
    private QueryWrapper<Record> wrapper;
    private Map<String, Object> betweenStartMap;
    private Map<String, Object> betweenEndMap;

    public AutoQueryWrapper(Record record) {
        this.record = record;
        this.wrapper = Wrappers.query();
        this.betweenStartMap = Maps.newHashMap();
        this.betweenEndMap = Maps.newHashMap();
        initialize();
    }

    private void initialize() {
        // 获取需遍历field
        Set<Field> forSearchFieldList = findModelField();
        // 根据field生成实际数据wrapper
        for (Field field : forSearchFieldList) {
            try {
                // 判断是否为NULL(仅限装箱类型)
                field.setAccessible(true);
                Object fieldValue = field.get(this.record);
                if (Objects.isNull(fieldValue)) {
                    continue;
                }
                // 获取注解生成wrapper
                autoWrapper(field, fieldValue);

            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        wrapperStartEnd();
    }

    private void wrapperStartEnd() {
        if (betweenStartMap.size() != betweenEndMap.size()) {
            log.warn("警告：betweenStart与betweenEnd属性数量不一致，已自动放弃部分值");
        }
        for (String key : betweenStartMap.keySet()) {
            if (!betweenEndMap.containsKey(key)) {
                continue;
            }
            this.wrapper.between(key, betweenStartMap.get(key), betweenEndMap.get(key));
        }
    }

    /**
     * 绑定实体内 属性值到wrapper
     *
     * @param field      实体属性
     * @param fieldValue 属性对应的值
     */
    private void autoWrapper(Field field, Object fieldValue) {
        String searchValue = String.valueOf(fieldValue);
        TableSearch tableSearchAnnotation = field.getAnnotation(TableSearch.class);
        TableField tableFieldAnnotation = field.getAnnotation(TableField.class);

        String tableFieldName;
        // 获取mybatisplus 对应的 表列名注解值
        if (tableFieldAnnotation != null && StringUtils.isNotBlank(tableFieldAnnotation.value())) {
            tableFieldName = tableFieldAnnotation.value();
        } else {
            tableFieldName = StringUtils.camelToUnderline(field.getName());
        }
        // 没有搜索字段，直接相等条件
        if (tableSearchAnnotation == null) {
            addIntoEq(tableFieldName, searchValue);
            return;
        }
        // 判断是否开始时间条件
        String betweenStart = tableSearchAnnotation.betweenStart();
        if (StringUtils.isNotBlank(betweenStart)) {
            addIntoBetweenStart(betweenStart, fieldValue);
            return;
        }
        // 判断是否结束时间条件
        String betweenEnd = tableSearchAnnotation.betweenEnd();
        if (StringUtils.isNotBlank(betweenEnd)) {
            addIntoBetweenEnd(betweenEnd, fieldValue);
            return;
        }
        String type = tableSearchAnnotation.type();
        switch (type) {
            case "eq":
                addIntoEq(tableFieldName, searchValue);
                break;
            case "like":
                addIntoLike(tableFieldName, searchValue);
                break;
            case "in":
                addIntoIn(tableFieldName, fieldValue);
                break;
            case "ne":
                addIntoNotEq(tableFieldName, searchValue);
                break;
            default:
                log.warn("警告：未知TableSearch注解type类型：" + type);
                break;
        }
    }

    private void addIntoBetweenStart(String betweenStart, Object value) {
        if (betweenStartMap.containsKey(betweenStart)) {
            log.warn("警告：betweenStart重复写入，详细信息----------" + betweenStart + "覆盖值为：" + value);
        }
        betweenStartMap.put(betweenStart, value);
    }

    private void addIntoBetweenEnd(String betweenEnd, Object value) {
        if (betweenEndMap.containsKey(betweenEnd)) {
            log.warn("警告：betweenEnd重复写入，详细信息----------" + betweenEnd + "覆盖值为：" + value);
        }
        // 结束日期如果没有定义时分秒， 转换为当前最后一毫秒
        Date endDate = (Date) value;
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(endDate);
        if (calendar.get(Calendar.HOUR_OF_DAY) == 0 && calendar.get(Calendar.MINUTE) == 0
                && calendar.get(Calendar.SECOND) == 0 && calendar.get(Calendar.MILLISECOND) == 0) {
            calendar.set(Calendar.HOUR_OF_DAY, 23);
            calendar.set(Calendar.MINUTE, 59);
            calendar.set(Calendar.SECOND, 59);
            calendar.set(Calendar.MILLISECOND, 999);
        }
        betweenEndMap.put(betweenEnd, calendar.getTime());
    }

    private void addIntoIn(String fieldName, Object value) {
        if (value instanceof Collection && !((Collection) value).isEmpty()) {
            this.wrapper.in(fieldName, (Collection<?>) value);
        }
    }

    private void addIntoEq(String fieldName, String value) {
        if (StringUtils.isBlank(value)) {
            return;
        }
        this.wrapper.eq(fieldName, value);
    }

    private void addIntoNotEq(String fieldName, String value) {
        if (StringUtils.isBlank(value)) {
            return;
        }
        this.wrapper.ne(fieldName, value);
    }

    private void addIntoLike(String fieldName, String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        this.wrapper.like(fieldName, value);
    }

    /**
     * 获取注解下的字段
     */
    private Set<Field> findModelField() {
        Class clazz = this.record.getClass();
        Set<Field> tempList = Sets.newHashSet();
        Field[] fields = ReflectUtil.getFields(clazz);
        for (Field field : fields) {
            // 判断是否静态类型
            int modifiers = field.getModifiers();
            if (Modifier.isStatic(modifiers)) {
                continue;
            }
            // 判断是否注解强制忽略 或提前验证In属性
            TableSearch annotation = field.getAnnotation(TableSearch.class);
            if (annotation != null) {
                if (Objects.equals(annotation.type(), "none")) {
                    continue;
                } else if (Objects.equals(annotation.type(), "in")) {
                    tempList.add(field);
                    continue;
                }
            }
            // 判断是否为简单类型(除字符串和Date类)或原始类型
            Class type = field.getType();
            if (type.isPrimitive() || !ClassUtil.isSimpleValueType(type)) {
                continue;
            }
            tempList.add(field);

        }
        return tempList;
    }

    public QueryWrapper getWrapper() {
        return wrapper;
    }
}