Mybatis-Plus中提供了各种插件，乐观锁、多租户、动态表名。。。。今天来研究一下基于动态表名插件实现分表的案例
环境准备
数据库建三张表测试
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user_0 -- ---------------------------- DROP TABLE IF EXISTS `user_0`; CREATE TABLE `user_0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user_1 -- ---------------------------- DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user_2 -- ---------------------------- DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
pom依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> </dependencies>
yml
server: port: 8099 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/my_user?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai username: root password: root #取模分表 格式 表名&表总数量（多个,分隔）。这里随便怎么定义和解析规则对应即可 modTables: user&3 # Logger Config mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
请求参数传递辅助类，就是根据什么去分表，这里是根据id
public class RequestDataHelper { /** * 请求参数存取 */ private static final ThreadLocal<Long> REQUEST_DATA = new ThreadLocal<>(); /** * 设置请求参数 * * @param requestData 请求参数 Long */ public static void setRequestData(Long requestData) { REQUEST_DATA.set(requestData); } /** * 获取请求参数 * * @param param 请求参数 * @return 请求参数 MAP 对象 */ public static <T> T getRequestData(String param) { Long id = getRequestData(); if (null != id) { return (T) id; } return null; } /** * 获取请求参数 * * @return 请求参数 MAP 对象 */ public static Long getRequestData() { return REQUEST_DATA.get(); } }
注入插件，并且程序初始化时解析分表配置
@Configuration @MapperScan("com.sample.mapper") //和你包名一致，别问我你的怎么报错了 public class MybatisPlusConfig { private static String modTables; private static Map<String, Integer> tableMap = new HashMap<>(16); @Value("${spring.datasource.modTables:null}") public void setModTables(String modTables) { MybatisPlusConfig.modTables = modTables; } @PostConstruct public void init() throws Exception { String[] split = modTables.split(","); for (String s : split) { String[] split1 = s.split("&"); if (split1.length < 2) { throw new RuntimeException("分表配置错误"); } MybatisPlusConfig.tableMap.put(split1[0], Integer.valueOf(split1[1])); } } @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor(); dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> { Integer integer = tableMap.get(tableName); if (integer != null) { // 获取参数方法 Long id = RequestDataHelper.getRequestData(); if(id == null){ throw new RuntimeException("未设置分表配置"); } long l = Math.floorMod(id, Long.parseLong(integer.toString())); return tableName + "_" + l; } return tableName; }); interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor); // 3.4.3.2 作废该方式 // dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map); return interceptor; } }
实体类
@Data @Accessors(chain = true) @TableName(value = "user") public class User { private Long id; private String name; private Integer age; private String email; }
mapper
/** * @author: yh * @Description: 支持不需要 UserMapper.xml 这个模块演示内置 CRUD 咱们就不要 XML 部分了 * @date: 2022/6/6 12:24 */ public interface UserMapper extends BaseMapper<User> { }
测试
现在就可以测试啦，编写一个测试方法
@Resource private UserMapper userMapper; /** * 分表测试 * 执行后观察打印sql * @author yh * @date 2022/6/6 */ @Test public void test1() { RequestDataHelper.setRequestData(1L); userMapper.selectList(null); RequestDataHelper.setRequestData(2L); userMapper.selectList(null); RequestDataHelper.setRequestData(3L); userMapper.selectList(null); RequestDataHelper.setRequestData(4L); userMapper.selectList(null); }
结果：
不同的业务id按照user_ 加 id取模，组成表名查询。这里的分表规则随便怎么定义 时间、id、字符串hash都可以。
完整代码已上传Gitee Spring整合常用组件
