浮生若梦，就当它是梦，尽兴地梦它一场；世事如云，就当它是云，从容地观它千变万化。
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整合常用组件
到此，本章内容就介绍完啦
