-
pom.xml
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/p6spy/p6spy --> <dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>3.8.0</version> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.49</version> <scope>test</scope> </dependency> <!-- for testing --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
-
application.yml
spring: datasource: driver-class-name: com.p6spy.engine.spy.P6SpyDriver url: jdbc:p6spy:h2:tcp://192.168.180.115:19200/~/mem/test username: root password: test # Logger Config logging: level: com.mp.dynamic-tablename: debug
-
实现 ITableNameHandler 接口注入到 DynamicTableNameParser 处理器链中,将动态表名解析器注入到 MP 解析链。
@Configuration @MapperScan("com.mq.dynamictablename.mapper") public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); DynamicTableNameParser dynamicTableNameParser = new DynamicTableNameParser(); dynamicTableNameParser.setTableNameHandlerMap(new HashMap<String, ITableNameHandler>(2) {{ put("user", (metaObject, sql, tableName) -> { // metaObject 可以获取传入参数,这里实现你自己的动态规则 String year = "_2018"; int random = new Random().nextInt(10); if (random % 2 == 1) { year = "_2019"; } return tableName + year; }); }}); paginationInterceptor.setSqlParserList(Collections.singletonList(dynamicTableNameParser)); return paginationInterceptor; } }
-
实体类
@Data @Accessors(chain = true) public class User { private Long id; private String name; private Integer age; private String email; } public interface UserMapper extends BaseMapper<User> { }
-
数据库脚本
DELETE FROM user_2018; INSERT INTO user_2018 (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'); DELETE FROM user_2019; INSERT INTO user_2019 (id, name, age, email) VALUES (1, 'Jack', 20, 'test2@baomidou.com'); DROP TABLE IF EXISTS user_2018; CREATE TABLE user_2018 ( id BIGINT(20) NOT NULL COMMENT '主键ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', age INT(11) NULL DEFAULT NULL COMMENT '年龄', email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id) ); DROP TABLE IF EXISTS user_2019; CREATE TABLE user_2019 ( id BIGINT(20) NOT NULL COMMENT '主键ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', age INT(11) NULL DEFAULT NULL COMMENT '年龄', email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id) );
-
测试类
@SpringBootTest class DynamicTablenameApplicationTests { @Autowired private UserMapper userMapper; @Test public void test() { // 自己去观察打印 SQL 目前随机访问 user_2018 user_2019 表 for (int i = 0; i < 6; i++) { User user = userMapper.selectById(1); System.err.println(user.getName()); } } }
-
测试结果:
Consume Time:19 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2018 WHERE id=1 Jone Consume Time:2 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2019 WHERE id=1 Jack Consume Time:0 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2019 WHERE id=1 Jack Consume Time:0 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2019 WHERE id=1 Jack Consume Time:0 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2018 WHERE id=1 Jone Consume Time:0 ms 2019-10-30 16:46:39 Execute SQL:SELECT id,name,email,age FROM user_2018 WHERE id=1 Jone