Mybatis PageHelper分页语句执行前做sql拦截并变更

fistup

已于 2024-05-17 14:50:29 修改

阅读量2.3k

点赞数

文章标签： mybatis sql 数据库

于 2022-08-19 20:05:37 首次发布

本文链接：https://blog.csdn.net/QQ317968200/article/details/126320180

版权

本文介绍了如何在使用若依前后端分离框架和PageHelper分页插件的情况下，解决在SQL操作前根据账套编号切换数据库的问题。通过创建自定义拦截器`DbSwitchInterceptor`，并在PageHelper分页插件之后执行，实现了在执行SQL前先`USE`指定数据库。详细阐述了拦截器的配置和执行顺序调整，以及在Mybatis配置文件和启动类中如何使自定义拦截器生效。

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

使用的若依前后端分离框架,

master数据库用的mysql,slave数据库使用的sqlserver,

sqlserver数据库实例下创建了很多数据库,业务场景下每个数据库称为一个账套,每个账套里面的表结构都是一样的

业务需求,要求能在前端传入账套编号(即:数据库名称),后台能在对应的数据库下进行增删该查操作,简而言之就是在进行sql操作之前先USE一下数据库,在进行sql操作.如下:

use demo;select * from xxx

照道理我们直接在mapper.xml里面直接写上Use就行了

然而在使用了PageHelper分页插件之后就行不通了,PageHelper分页在计算count数量是,将整个sql语句作为一个整体包含在了select count(0) from xxx里面,这个xxx里面包含了use,就导致整个select count(0)语句执行失败,如下:

解决办法:

mapper.xml 作为分页的select语句里面不能像上图一样直接写use了,

需要在PageHelperInterceptor分页拦截器将分页语句组装完毕后,再用自定义拦截器拦截在最外层拼接好use,这样最终执行的sql才是正确的sql,如下:

那么怎么添加mybatis自定义拦截器,并且自定义拦截器执行顺序是怎样的,我们11分析下

如下是一个自定义拦截器

package com.hsf.framework.interceptor;

import com.hsf.common.utils.StringUtils;
import com.hsf.common.utils.reflect.ReflectUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.util.Map;
import java.util.Properties;

/**
 * ClassName: DbSwitchInterceptor
 * Description:
 * date: 2022/8/10 11:57
 *
 * @author yanglp
 * @since JDK 1.8
 */
@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
@Component
public class DbSwitchInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        Executor executor = (Executor) invocation.getTarget();
        CacheKey cacheKey;
        BoundSql boundSql;
        //由于逻辑关系，只会进入一次
        if (args.length == 4) {
            //4 个参数时
            boundSql = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            //6 个参数时
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }

        if (!executor.getTransaction().getConnection().getMetaData().getURL().contains("mysql")) {//mysql不做拦截处理
            String sqlTmp = boundSql.getSql();
            String tenant = "";
            Object _parameter = boundSql.getAdditionalParameter("_parameter");
            if (_parameter != null) {
                if (_parameter instanceof Map) {
                    if (((Map) _parameter).containsKey("tenant")) {
                        Object obj = ((Map) _parameter).get("tenant");
                        tenant = obj != null ? obj.toString() : null;
                    }
                } else {
                    tenant = ReflectUtils.getFieldValue(_parameter, "tenant");
                }
                if (!StringUtils.isEmpty(tenant)) {
                    sqlTmp = "USE " + tenant + "; " + sqlTmp;
                    ReflectUtils.setFieldValue(boundSql, "sql", sqlTmp);
                }
            }
        }
        //TODO 自己要进行的各种处理
        //注：下面的方法可以根据自己的逻辑调用多次，在分页插件中，count 和 page 各调用了一次
        return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);

        /*
            // 拦截sql
            Object[] args = invocation.getArgs();
            MappedStatement statement = (MappedStatement) args[0];
            Object parameterObject = args[1];
            BoundSql boundSql = statement.getBoundSql(parameterObject);
            String sql = boundSql.getSql();
            LOGGER.info("获取到的SQL：{}"+sql);
            if (StringUtils.isBlank(sql)) {
                return invocation.proceed();
            }
            // 返回
            return invocation.proceed();

        */
    }

    @Override
    public Object plugin(Object obj) {
        return Plugin.wrap(obj, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }
}

怎么让自定义拦截器生效呢

1.在mybatis的配置文件(mytatis-config.xml)中配置

<plugins>
   <plugin interceptor="com.hsf.framework.interceptor.DbSwitchInterceptor"/>
</plugins>

重启发现生效了,但是执行顺序不对了,我们想要DbSwitchInterceptor自定义拦截器在分页拦截器PageInterceptor之后进行sql修改,继续看文档https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/Interceptor.md配置多个拦截器的顺序1,2,3执行3,2,1,2,3因此我们将配置改成这样,预期是PageInterceptor执行完之后DbSwitchInterceptor再拦截处理

<plugins>
    <plugin interceptor="com.hsf.framework.interceptor.DbSwitchInterceptor"/>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

重启验证,直接重启报错

Caused by: java.lang.RuntimeException: 在系统中发现了多个分页插件，请检查系统配置!

mybatis默认就已经加载了PageInterceptor拦截器,那么怎么禁止mybatis自动加载改为手动加载呢,在启动类上加上配置PageHelperAutoConfiguration.class

@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class,PageHelperAutoConfiguration.class
})

再重启,依然还是顺序不对.

在尝试另外一种办法,将前面在mybatis-config.xml中的<plugins>注释掉,启动类上的PageHelperAutoConfiguration.class也注释掉,在mybatis的配置类(MybatisConfig.java)中注入DbSwitchInterceptor,并且设置到sessionFactory中

sessionFactory.setPlugins(new Interceptor[]{dbSwitchInterceptor});

package com.hsf.framework.config;

import com.hsf.common.utils.StringUtils;
import com.hsf.framework.interceptor.DbSwitchInterceptor;
import org.apache.ibatis.io.VFS;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.core.type.classreading.CachingMetadataReaderFactory;
import org.springframework.core.type.classreading.MetadataReader;
import org.springframework.core.type.classreading.MetadataReaderFactory;
import org.springframework.util.ClassUtils;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;

/**
 * Mybatis支持*匹配扫描包
 * 
 * @author hsf
 */
@Configuration
public class MyBatisConfig
{
    @Autowired
    private Environment env;

    @Autowired
    DbSwitchInterceptor dbSwitchInterceptor;

    static final String DEFAULT_RESOURCE_PATTERN = "**/*.class";

    public static String setTypeAliasesPackage(String typeAliasesPackage)
    {
        ResourcePatternResolver resolver = (ResourcePatternResolver) new PathMatchingResourcePatternResolver();
        MetadataReaderFactory metadataReaderFactory = new CachingMetadataReaderFactory(resolver);
        List<String> allResult = new ArrayList<String>();
        try
        {
            for (String aliasesPackage : typeAliasesPackage.split(","))
            {
                List<String> result = new ArrayList<String>();
                aliasesPackage = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX
                        + ClassUtils.convertClassNameToResourcePath(aliasesPackage.trim()) + "/" + DEFAULT_RESOURCE_PATTERN;
                Resource[] resources = resolver.getResources(aliasesPackage);
                if (resources != null && resources.length > 0)
                {
                    MetadataReader metadataReader = null;
                    for (Resource resource : resources)
                    {
                        if (resource.isReadable())
                        {
                            metadataReader = metadataReaderFactory.getMetadataReader(resource);
                            try
                            {
                                result.add(Class.forName(metadataReader.getClassMetadata().getClassName()).getPackage().getName());
                            }
                            catch (ClassNotFoundException e)
                            {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                if (result.size() > 0)
                {
                    HashSet<String> hashResult = new HashSet<String>(result);
                    allResult.addAll(hashResult);
                }
            }
            if (allResult.size() > 0)
            {
                typeAliasesPackage = String.join(",", (String[]) allResult.toArray(new String[0]));
            }
            else
            {
                throw new RuntimeException("mybatis typeAliasesPackage 路径扫描错误,参数typeAliasesPackage:" + typeAliasesPackage + "未找到任何包");
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        return typeAliasesPackage;
    }

    public Resource[] resolveMapperLocations(String[] mapperLocations)
    {
        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
        List<Resource> resources = new ArrayList<Resource>();
        if (mapperLocations != null)
        {
            for (String mapperLocation : mapperLocations)
            {
                try
                {
                    Resource[] mappers = resourceResolver.getResources(mapperLocation);
                    resources.addAll(Arrays.asList(mappers));
                }
                catch (IOException e)
                {
                    // ignore
                }
            }
        }
        return resources.toArray(new Resource[resources.size()]);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
    {
        String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
        String mapperLocations = env.getProperty("mybatis.mapperLocations");
        String configLocation = env.getProperty("mybatis.configLocation");
        typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
        VFS.addImplClass(SpringBootVFS.class);

        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
        sessionFactory.setMapperLocations(resolveMapperLocations(StringUtils.split(mapperLocations, ",")));
        sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
        sessionFactory.setPlugins(new Interceptor[]{dbSwitchInterceptor});
        return sessionFactory.getObject();
    }
}

重启之后拦截顺序达到目的了,先执行PageInterceptor再执行自定义拦截器