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);
}
View Code
@Data
@Accessors(chain = true)
public class User implements Serializable {
@TableId
private String id;
private String userName;
}
View Code
<?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>
View Code
添加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;
}
}
View Code
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;
}
}
}
View Code
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;
}
}
View Code
测试一下下
然后惊奇的发现会动态到你配置的表了（不过这玩意只适合简单的动态分表）
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();
}
}
View Code
标签：年份,MybatisPlus,String,return,org,import,动态,com,public From： https://www.cnblogs.com/yiMro/p/17011683.html