使用mybatisplus插件实现按时间分表及默认查询

pom 需3.4.版本及以上

<mybatis-plus-spring-boot.version>3.4.1</mybatis-plus-spring-boot.version>

定义及注册mybatisplus插件

package com.xxx.wxxms.mybatis;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.HashMap;
import java.util.Map;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {

        Map<String, TableNameHandler> tableNameHandlerMap = new HashMap<>();
        MonthTableNameHandler wms_dev_ins_history = new MonthTableNameHandler();
        tableNameHandlerMap.put(wms_dev_ins_history.getTableNames(), wms_dev_ins_history);

        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(tableNameHandlerMap);

        //以拦截器的方式处理表名称
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }

}

package com.xxx.wxxms.mybatis;

import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;

public class MonthTableNameHandler implements TableNameHandler {

    //用于记录哪些表可以使用该月份动态表名处理器（即哪些表按月分表）
    private String tableNames = "wms_devins_history";

    public String getTableNames() {
        return this.tableNames;
    }

    //每个请求线程维护一个month数据，避免多线程数据冲突。所以使用ThreadLocal
    private static final ThreadLocal<String> MONTH_DATA = new ThreadLocal<>();

    //设置请求线程的month数据
    public static void setData(String month) {
        MONTH_DATA.set(month);
    }

    //删除当前请求线程的month数据
    public static void removeData() {
        MONTH_DATA.remove();
    }

    @Override
    public String dynamicTableName(String sql, String tableName) {
        if (this.tableNames.contains(tableName)){
            return tableName + "_" + MONTH_DATA.get();  //表名增加月份后缀
        }else{
            return tableName;   //表名原样返回
        }
    }

}

package com.xxx.xxx.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xxx.common.core.utils.DateUtils;
import com.xxx.common.core.utils.bean.BeanUtils;
import com.xxx.common.core.web.domain.AjaxResult;
import com.xxx.wxxms.api.domain.DongHuanDevIns;
import com.xxx.wxxms.domain.entity.WmsDevinsHistoryEntity;
import com.xxx.wxxms.mapper.WmsDevinsHistoryMapper;
import com.xxx.wxxms.mybatis.MonthTableNameHandler;
import com.xxx.wxxms.service.WmsDevinsHistoryService;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
@AllArgsConstructor
@Slf4j
public class WmsDevinsHistoryServiceImpl extends ServiceImpl<WmsDevinsHistoryMapper, WmsDevinsHistoryEntity> implements WmsDevinsHistoryService {

    @Override
    public AjaxResult syncDevInsHistory(List<DongHuanDevIns> devInsList) {

        String month = DateUtils.getYearMonth();
        Integer tableExist = baseMapper.isTableExist(month);

        MonthTableNameHandler.setData(month);
        if(tableExist == null || tableExist ==0){
            baseMapper.createNewTable();
        }

        List<WmsDevinsHistoryEntity> list = new ArrayList<>();
        for (DongHuanDevIns devIns :devInsList){
            WmsDevinsHistoryEntity devinsHistory = new WmsDevinsHistoryEntity();
            BeanUtils.copyBeanProp(devinsHistory, devIns);
            list.add(devinsHistory);
        }
        baseMapper.insertDevinsHistory(list);

        MonthTableNameHandler.removeData();
        return AjaxResult.success();
    }
}

/**
 * 日期路径 即年/月/日 如201808
 */
public static final String getYearMonth()
{
    return DateFormatUtils.format(new Date(), "yyyyMM");
}

判断表是否存在及创建新表（create table wxxms_history like wxxms_history中，默认会将日期后缀添加到第一个wxxms_history后面）

<select id="isTableExist" parameterType="string" resultType="int">
    select count(*) from information_schema.TABLES where table_name = concat('wxxms_history_', #{table_extra})
</select>

<update id="createNewTable">
    create table wxxms_history like wxxms_history;
</update>