使用mybatisplus处理mysql中模糊查询参数为%的问题

在做后台管理系统时，列表查询不可避免的会查询需要支持模糊查询的字段，经过测试发现，当输入框中输入"%"的时候，会查询出所有的数据，这种结果不是我们想要的效果。创建一个实现类，继承这个抽象类，并实现这里的抽象方法。这里再在输入框中输入"%"就不会查出所有数据了。

阿飞คิดถึง

526人浏览 · 2024-11-17 20:28:42

阿飞คิดถึง · 2024-11-17 20:28:42 发布

场景描述

在做后台管理系统时，列表查询不可避免的会查询需要支持模糊查询的字段，经过测试发现，当输入框中输入"%"的时候，会查询出所有的数据，这种结果不是我们想要的效果。

解决办法

引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
    <version>3.5.5</version>
</dependency>

创建抽象类

package com.bxj.zf.handle.interceptor;

import com.bxj.zf.handle.BizException;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;

@Slf4j
public abstract class AbstractCustomSqlDeal {

    public String deal(String sql){
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            dealSql(statement);
            if(statement instanceof Select){
                Select select = (Select) statement;
                SelectBody selectBody = select.getSelectBody();
                if(selectBody instanceof PlainSelect){
                    PlainSelect plainSelect = (PlainSelect) selectBody;
                    return plainSelect.toString();
                }
            }
            return sql;
        } catch (JSQLParserException e) {
            log.error("sql解析异常：{}",e);
            throw new BizException("sql解析异常");
        }
    }

    protected abstract void dealSql(Statement statement);
}

创建一个实现类，继承这个抽象类，并实现这里的抽象方法

package com.bxj.zf.handle.interceptor;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.springframework.stereotype.Component;

@Slf4j
@Component
public class SelectLikeDeal extends AbstractCustomSqlDeal {

    @SneakyThrows
    @Override
    public void dealSql(Statement statement) {
        if(statement instanceof Select){
            Select select = (Select) statement;
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            if(!plainSelect.toString().toLowerCase().contains(" like ")){
                return;
            }
            Expression where = plainSelect.getWhere();
            if(where instanceof AndExpression){
                AndExpression andExpression = (AndExpression) where;
                expressionDeal(andExpression);
            }
            log.info("当前处理掉了模糊查询问题");
        }
    }

    private void expressionDeal(AndExpression expression){
        Expression leftExpression = expression.getLeftExpression();
        Expression rightExpression = expression.getRightExpression();
        
        if(leftExpression instanceof LikeExpression){
            LikeExpression likeExpression = (LikeExpression) leftExpression;
            likeExpression.setEscape(new StringValue("%"));
        }

        if(rightExpression instanceof LikeExpression){
            LikeExpression likeExpression = (LikeExpression) rightExpression;
            likeExpression.setEscape(new StringValue("%"));
        }
        
        if(leftExpression instanceof Parenthesis){
            Parenthesis parenthesis = (Parenthesis) leftExpression;
            Expression expression1 = parenthesis.getExpression();
            if(expression1 instanceof LikeExpression){
                LikeExpression likeExpression = (LikeExpression) expression1;
                likeExpression.setEscape(new StringValue("%"));
            }

        }
        if(rightExpression instanceof Parenthesis){
            Parenthesis parenthesis = (Parenthesis) rightExpression;
            Expression expression1 = parenthesis.getExpression();
            if(expression1 instanceof LikeExpression){
                LikeExpression likeExpression = (LikeExpression) expression1;
                likeExpression.setEscape(new StringValue("%"));
            }

        }
        if(leftExpression instanceof AndExpression){
            AndExpression andExpression = (AndExpression) leftExpression;
            expressionDeal(andExpression);
        }
        if(rightExpression instanceof AndExpression){
            AndExpression andExpression = (AndExpression) rightExpression;
            expressionDeal(andExpression);
        }
    }
}

创建一个实现`InnerInterceptor`接口的实现类

package com.bxj.zf.handle.interceptor;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.SQLException;
import java.util.List;

@AllArgsConstructor
@Slf4j
public class CustomInnerInterceptor implements InnerInterceptor {

    private List<AbstractCustomSqlDeal> customSqlDealList;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
        String sql = mpBoundSql.sql();
        for (AbstractCustomSqlDeal abstractCustomSqlDeal : customSqlDealList) {
            sql = abstractCustomSqlDeal.deal(sql);
        }
        mpBoundSql.sql(sql);
    }

    @Override
    public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
       
    }

}

将CustomInnerInterceptor添加到mybatisplus的配置中去

package com.bxj.zf.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.bxj.zf.common.entity.CommonConfig;
import com.bxj.zf.handle.custom.CustomTenantHandler;
import com.bxj.zf.handle.custom.SuffixTableNameHandler;
import com.bxj.zf.handle.interceptor.AbstractCustomSqlDeal;
import com.bxj.zf.handle.interceptor.CustomInnerInterceptor;
import lombok.RequiredArgsConstructor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.List;

@Configuration
@RequiredArgsConstructor
@MapperScan("com.bxj.zf.application.mapper")
public class MybatisPlusConfig {

    private final CustomTenantHandler customTenantHandler;

    private final CommonConfig commonConfig;

    private final List<AbstractCustomSqlDeal> customSqlDealList;

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(
            @Qualifier("tenantLineInnerInterceptor") TenantLineInnerInterceptor tenantLineInnerInterceptor,
            @Qualifier("tableNameInnerInterceptor")DynamicTableNameInnerInterceptor tableNameInnerInterceptor,
            @Qualifier("optimisticLocker")OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor,
            @Qualifier("paginationInnerInterceptor") PaginationInnerInterceptor paginationInnerInterceptor,
            @Qualifier("customInnerInterceptor") CustomInnerInterceptor customInnerInterceptor){
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //配置多租户
        mybatisPlusInterceptor.addInnerInterceptor(tenantLineInnerInterceptor);
        //配置个性化表名
        mybatisPlusInterceptor.addInnerInterceptor(tableNameInnerInterceptor);
        //配置乐观锁
        mybatisPlusInterceptor.addInnerInterceptor(optimisticLockerInnerInterceptor);
        //自定义处理器
        mybatisPlusInterceptor.addInnerInterceptor(customInnerInterceptor);
        //配置分页
        mybatisPlusInterceptor.addInnerInterceptor(paginationInnerInterceptor);

        return mybatisPlusInterceptor;
    }

    @Bean
    public TenantLineInnerInterceptor tenantLineInnerInterceptor(){
        TenantLineInnerInterceptor tenantLineInnerInterceptor = new TenantLineInnerInterceptor();
        tenantLineInnerInterceptor.setTenantLineHandler(customTenantHandler);
        return tenantLineInnerInterceptor;
    }

    @Bean
    public PaginationInnerInterceptor paginationInnerInterceptor(){
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        return paginationInnerInterceptor;
    }

    //就是这里做了模糊查询的处理
    @Bean
    public CustomInnerInterceptor customInnerInterceptor(){
        return new CustomInnerInterceptor(customSqlDealList);
    }

    @Bean("tableNameInnerInterceptor")
    public DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor(
            @Qualifier("tableNameHandler")TableNameHandler tableNameHandler){
        DynamicTableNameInnerInterceptor interceptor = new DynamicTableNameInnerInterceptor();
        interceptor.setTableNameHandler(tableNameHandler);
        return interceptor;
    }

    @Bean
    public TableNameHandler tableNameHandler(){
        return new SuffixTableNameHandler(commonConfig);
    }

    @Bean("optimisticLocker")
    public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor(){
        return new OptimisticLockerInnerInterceptor();
    }
}

主要看CustomInnerInterceptor这个类被应用的地方

构造查询的wrapper示例如下

@Override
public List<UserEntity> getList() {
    LambdaQueryWrapper<UserPO> wrapper =
            Wrappers.lambdaQuery(UserPO.class).like(UserPO::getNickName, "aaa");
    return userConvert.toEntityList(list(wrapper));
}

生成的sql如下：

SELECT id, account_id, account_pwd, real_name, sex, real_id, nick_name, real_email, account_status, mobile, create_time, update_time, create_user, update_user, delete_status, version, tenant_id FROM bxj_user WHERE delete_status = '0' AND (nick_name LIKE ? ESCAPE '%') AND tenant_id = 'bxj'