MybatisPlus实现按年份动态操作表数据
MybatisPlus实现按年份动态操作表数据
在mp的官方网站上最近的一次更新可以看到，其提供了动态表名插件：https://baomidou.com/pages/2a45ff/#dynamictablenameinnerinterceptor
创建个项目试试吧
项目基础配置
由于MP的动态表名插件是最近才加入的功能所以选择版本的时候一定要注意下
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
先让项目跑起来
server: port: 9090 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/sharding?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8 username: root password: 123456 hikari: minimum-idle: 5 idle-timeout: 180000 maximum-pool-size: 10 auto-commit: true pool-name: MyHikariCP connection-timeout: 30000 mybatis-plus: mapperPackage: com.mpkt.mapper mapper-locations: classpath:mapper/*.xml type-aliases-package: com/mpkt/entity configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
建需要用到的entity、mapper、xml
在mapper里面已经预先把分页查询和创建表结构的接口写好了
import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.mpkt.entity.User; import org.apache.ibatis.annotations.Param; public interface UserMapper extends BaseMapper<User> { /** * 分页查询 * @param page * @return */ Page<User> queryAll(@Param("page") Page<User> page); /** * 创建表 * @param tableName */ void createTable(@Param("tableName") String tableName); }
@Data @Accessors(chain = true) public class User implements Serializable { @TableId private String id; private String userName; }
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mpkt.mapper.UserMapper"> <select id="queryAll" resultType="com.mpkt.entity.User"> select * from user </select> <update id="createTable"> CREATE TABLE ${tableName} ( `id` bigint(255) NOT NULL, `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; </update> </mapper>
添加MybatisPlusInterceptor配置
在这里也可以看到我定义了一个YearTableNameHandler的控制器和DicTableNameEnum的枚举类，然后就是你执行sql语句的时候会先拦截住执行的sql和表名，我这里就直接
就手动判断了。
import lombok.*; import java.util.ArrayList; import java.util.List; @Getter @NoArgsConstructor @AllArgsConstructor public enum DicTableNameEnum { A("user") ; private String tableName; public static List<String> findAll() { List<String> list = new ArrayList<>(); for(DicTableNameEnum v : values()) { list.add(v.getTableName()); } return list; } }
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler; import org.springframework.util.ObjectUtils; import java.time.LocalDate; import java.util.Arrays; import java.util.List; public class YearTableNameHandler implements TableNameHandler { /** * 动态表名处理器 */ private List<String> tableNames; /** * 每个请求线程维护一个数据 */ private static final ThreadLocal<String> YEAR_DATA = new ThreadLocal<>(); /** * 默认本年 */ private static String DEFAULT_YEAR = String.valueOf(LocalDate.now().getYear()); /** * 构造方法 * @param tableNames */ public YearTableNameHandler(String ...tableNames) { this.tableNames = !ObjectUtils.isEmpty(tableNames) ? Arrays.asList(tableNames) : DicTableNameEnum.findAll(); } public static void setData(String year) { YEAR_DATA.set(year); } public static String getData() { return YEAR_DATA.get(); } public static void removeData() { YEAR_DATA.remove(); } public static void setDefaultYear() { YEAR_DATA.set(DEFAULT_YEAR); } public static String getDefaultYear() { return DEFAULT_YEAR; } @Override public String dynamicTableName(String sql, String tableName) { if (this.tableNames.contains(tableName)) { return tableName + "_" + YEAR_DATA.get(); }else{ return tableName; } } }
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration @MapperScan("com.mpkt") public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor(); // 可以传多个表名参数，指定哪些表处理表名称 dynamicTableNameInnerInterceptor.setTableNameHandler(new YearTableNameHandler()); interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor); interceptor.addInnerInterceptor(paginationInnerInterceptor()); interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor()); return interceptor; } public PaginationInnerInterceptor paginationInnerInterceptor() { PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(); // 设置最大单页限制数量，默认 500 条，-1 不受限制 paginationInnerInterceptor.setMaxLimit(-1L); // 分页合理化 paginationInnerInterceptor.setOverflow(true); return paginationInnerInterceptor; } }
测试一下下
然后惊奇的发现会动态到你配置的表了（不过这玩意只适合简单的动态分表）
import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.mpkt.config.YearTableNameHandler; import com.mpkt.entity.User; import com.mpkt.mapper.UserMapper; import com.mpkt.utils.R; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.UUID; @RestController @RequestMapping("/test") public class TestController { @Autowired private UserMapper userMapper; @GetMapping("/i1") public R i1() { userMapper.createTable("user_" + YearTableNameHandler.getDefaultYear()); return R.ok(); } @GetMapping("/k1") public R k1() { YearTableNameHandler.setDefaultYear(); Page<User> page = new Page<>(1, 10); return R.ok().data(userMapper.queryAll(page)); } @GetMapping("/k2") public R k2() { YearTableNameHandler.setDefaultYear(); userMapper.insert(new User().setUserName("张三" + UUID.randomUUID())); return R.ok(); } }
