SpringBoot与MybatisPlus3.X整合之动态表名 SQL 解析器(七)

时间：2019-10-30 18:15:14 阅读：166 评论：0 收藏：0 [点我收藏+]

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

SpringBoot与MybatisPlus3.X整合之动态表名 SQL 解析器(七)

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

SpringBoot与MybatisPlus3.X整合之动态表名 SQL 解析器(七)

原文：https://www.cnblogs.com/dalianpai/p/11766155.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)