MybatisPlus实现动态分表增删改查

77 阅读 0 评论 51 点赞

我是靠谱客的博主淡定泥猴桃，最近开发中收集的这篇文章主要介绍MybatisPlus实现动态分表增删改查，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

/**
 * <p>
 * 工单记录表
 * </p>
 *
 * @author ௸྄ིོུ倾心ღ᭄ᝰꫛꫀꪝ
 * @since 2021-12-11
 * eph_ispc_order_his_y0000
 * 示例实体类,动态分表 实体类 _后是动态表名后缀
 */
 @Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("order_his_")
public class EphIspcOrderHisOrgCdEntity {

    private static final long serialVersionUID = 1L;

    /**
     * 物理主键
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 主表id
     */
    private Integer mainId;

    /**
     * 工单id
     */
    private String workOrderId;
}

/**
 * @author ௸྄ིོུ倾心ღ᭄ᝰꫛꫀꪝ
 * @since 2021-12-11
 * 分表传表名类
 */
public class TenantContext {
    private static final ThreadLocal<String> TENANT_OrgCd = new ThreadLocal<>();
    private static final ThreadLocal<String> TENANT_tableName = new ThreadLocal<>();
    private TenantContext() {
    }

    public static void setOrgCd(String orgCd) {
        TENANT_OrgCd.set(orgCd);
    }

    public static String getOrgCd() {
        return TENANT_OrgCd.get();
    }

    public static void removeOrgCd() {
        TENANT_OrgCd.remove();
    }

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

    public static String getTableName() {
       return TENANT_tableName.get();
    }
    public static void removeTableName() {
        TENANT_tableName.remove();
    }
}

/**
 * @author ௸྄ིོུ倾心ღ᭄ᝰꫛꫀꪝ
 * @since 2021-12-11
 * sql拦截器
 */
@Slf4j
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
public class MybatisSqlInterceptor implements Interceptor {
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
            StatementHandler handler = (StatementHandler) invocation.getTarget();
            BoundSql boundSql = handler.getBoundSql();
            String sql = boundSql.getSql();
            log.info("原sql======="+sql);
            if (TenantContext.getOrgCd()!=null){
				sql=sql.replace(TenantContext.getTableName(),TenantContext.getTableName()+TenantContext.getOrgCd());
                log.info("替换表名后sql======="+sql);
            }
            Field sqlField = boundSql.getClass().getDeclaredField("sql");
            sqlField.setAccessible(true);
            sqlField.set(boundSql, sql);
            return invocation.proceed();
        }
}

/**
 * @author ௸྄ིོུ倾心ღ᭄ᝰꫛꫀꪝ
 * @since 2021-12-11
 * sql拦截器注册
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisSqlInterceptor mybatisSqlInterceptor() {
        return new MybatisSqlInterceptor();
    }
}

/**
*mapper
*/
public interface EphIspcOrderHisMapper extends BaseMapper<EphIspcOrderHisEntity> {

}

 	@Autowired
    private EphIspcOrderHisOrgCdMapper orgCdMapper;
/**
*使用示例
*/
 @Test
   void work(){
        TenantContext.setTableName("eph_ispc_order_his_");
        TenantContext.setOrgCd("y0000");
        QueryWrapper<EphIspcOrderHisOrgCdEntity> ts=new QueryWrapper<EphIspcOrderHisOrgCdEntity>();
        ts.eq("del_flg",0);
        ts.eq("main_id","524696");
        Long aLong = orgCdMapper.selectCount(ts);
        System.out.println("aLong===="+aLong);
        // 使用完清空关闭，避免影响其他正常sql
        TenantContext.removeOrgCd();
        TenantContext.removeTableName();
}

 	<!-- MybatisPlus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>