mybatis-plus 高级特性-动态表名

背景

在分表的背景下，有时候查询数据的时候需要跨表查询，那此时就需要MP在解析的时候，能够很好的自适应表格名称

 实现

MP中是通过PaginationInterceptor(分页插件)完成动态表名解析的，配置如下：

数据库中表 

 依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.1.tmp</version>
</dependency>

 配置类

package com.huanchuang.common.config;

import com.baomidou.mybatisplus.extension.parsers.DynamicTableNameParser;
import com.baomidou.mybatisplus.extension.parsers.ITableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Collections;
import java.util.HashMap;

/**
 * @Package: com.huanchuang.common.config
 * @Description: <mybatis-plush配置类>
 * @Author: MILLA
 * @CreateDate: 2020/09/04 14:42
 * @UpdateUser: MILLA
 * @UpdateDate: 2020/09/04 14:42
 * @UpdateRemark: <>
 * @Version: 1.0
 */
@Configuration
@MapperScan("com.huanchuang.ext.mapper**")
@ConditionalOnProperty(prefix = "spring.config", name = "enableMybatisPlusDynamicTable", havingValue = "true")
public class MybatisPlusDynamicTableConfig {

    private static final String DYNAMIC_TABLE_PRE = "common_user";

    /**
     * mybatis-plus分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        paginationInterceptor.setLimit(500);
//        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser();
        dynamicTableNameParser.setTableNameHandlerMap(new HashMap<String, ITableNameHandler>(2) {{
            //动态表规则-生成自己需要的动态表名
            put(DYNAMIC_TABLE_PRE, (metaObject, sql, tableName) -> DynamicTableTreadLocal.INSTANCE.getTableName());
        }});
        paginationInterceptor.setSqlParserList(Collections.singletonList(dynamicTableNameParser));
        return paginationInterceptor;
    }
}

动态表名存储类

package com.huanchuang.common.config;

/**
 * @Package: com.huanchuang.common.config
 * @Description: <动态表格存储类>
 * @Author: MILLA
 * @CreateDate: 2020/09/04 14:42
 * @UpdateUser: MILLA
 * @UpdateDate: 2020/09/04 14:42
 * @UpdateRemark: <>
 * @Version: 1.0
 */
public enum DynamicTableTreadLocal {
    INSTANCE;
    private ThreadLocal<String> tableName = new ThreadLocal<>();

    public String getTableName() {
        return tableName.get();
    }

    public void setTableName(String tableName) {
        this.tableName.set(tableName);
    }

    public void remove() {
        tableName.remove();
    }

}

使用

    private void select(int year) {

         DynamicTableTreadLocal.INSTANCE.setTableName("user_" + year);
         LambdaQueryWrapper<SparkDownSample> wrapper = Wrappers.lambdaQuery(User.class);
         List<User> userList = userMapper.list(wrapper )

    }

@Data
public class User {

    private Long id;

    private String userName;
    
    private String address;

    private char sex;
    
    private Byte age;

}

 

原理

 

 

1.  以mybatis的query方法作为入口
2. 通过动态代理执行到配置的分页插件
3. 通过分页插件进行sql解析
4. 根据分页插件中配置的tableNameHandler进行目标表格的替换
5. 最后形成一个可执行sql，执行查询