<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
spring:
datasource:
url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
@Data
@TableName("t_user")
public class User {
@TableId(type = IdType.AUTO)
private Integer id;
@TableField("name")
private String name;
}
这里的@TableName("t_user")映射表名不需要带上日期后缀
@Mapper
public interface UserMapper extends BaseMapper<User> {
// 方法1
@Select("Select * from ${tableName}")
List<User> getUserInfo(@Param("tableName") String tableName);
// 方法2
List<User> getUserInfoMP(String tableName);
}
<?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.xx.xx.mapper.UserMapper">
<select id="getUserInfoMP" resultType="com.xx.xx.entity.User" >
select * from ${tableName} order by phone_number ASC
</select>
</mapper>
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
void query(){
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
String rDay = LocalDateTime.now().format(formatter);
String tableName = "t_user_" + rDay;
List<User> list1 = userMapper.getUserInfo(tableName);
List<User> list2 = userMapper.getUserInfoMP(tableName);
}
}
二、方案二(DynamicTableNameInnerInterceptor插件)
使用方法（插件配置+ThreadLocal+辅助类）
@Configuration
@MapperScan(basePackages = {"com.xx.**.mapper"})
public class MybatisPlusConfig {
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
//动态表名
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
//可以传多个表名参数，指定哪些表使用DayTableNameHandler处理表名称
dynamicTableNameInnerInterceptor.setTableNameHandler(new DayTableNameHandler("t_user"));
//以拦截器的方式处理表名称
//可以传递多个拦截器，即：可以传递多个表名处理器TableNameHandler
mybatisPlusInterceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return mybatisPlusInterceptor;
}
}
/**
* 按天参数，组成动态表名
*/
public class DayTableNameHandler implements TableNameHandler {
//用于记录哪些表可以使用该动态表名处理器（即哪些表需要分表）
private List<String> tableNames;
//构造函数，构造动态表名处理器的时候，传递tableNames参数
public DayTableNameHandler(String ...tableNames) {
this.tableNames = Arrays.asList(tableNames);
}
//每个请求线程维护一个day数据，避免多线程数据冲突。所以使用ThreadLocal
private static final ThreadLocal<String> DAY_DATA = new ThreadLocal<>();
//设置请求线程的day数据
public static void setData(String day) {
DAY_DATA.set(day);
}
//删除当前请求线程的day数据
public static void removeData() {
DAY_DATA.remove();
}
//动态表名接口实现方法
@Override
public String dynamicTableName(String sql, String tableName) {
if (this.tableNames.contains(tableName)){
return tableName + "_" + DAY_DATA.get(); //表名增加后缀
}else{
return tableName; //表名原样返回
}
}
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserService userService;
void query(){
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
String rDay = LocalDateTime.now().format(formatter);
DayTableNameHandler.setData(rDay);
List<User> list = userService.list();
// 用完即销毁
DayTableNameHandler.removeData();
}
}
这里可以自行打印SQL语句验证 Select * from t_user_20230909
三、方案三(DynamicTableNameInnerInterceptor插件、省略辅助类)
这里可以直接配置DynamicTableNameInnerInterceptor插件做简单的使用,但是用法相对固定,不能根据实际情况控制实体类所映射的表名，原因是被统一拦截
使用方法（插件配置+ThreadLocal）
@Configuration
@MapperScan(basePackages = {"com.xx.**.mapper"})
public class MybatisPlusConfig {
public static ThreadLocal<String> myTableName = new ThreadLocal<>();
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
//动态表名
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
HashMap<String, TableNameHandler> map = new HashMap<String, TableNameHandler>(2) {{
put("t_user", (sql, tableName) -> {
return myTableName.get();
});
}};
dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
mybatisPlusInterceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
myTableName.remove();
return mybatisPlusInterceptor;
}
}
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserService userService;
void query(){
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
String rDay = LocalDateTime.now().format(formatter);
DayTableNameHandler.setData(rDay);
String tableName = "t_user" + rDay;
MybatisPlusConfig.myTableName.set(tableName);
List<User> list = userService.list();
}
}
总结
三种方法可以结合实际需要选择，使用Mapper自定义SQL要注意SQL注入,使用线程池的方式要记得清理。
注意
Threadlocal 中的数据在AOP中最好自己释放掉 ，spring是用的线程池，如果不清理掉会影响线程下次使用的程序。
文章知识点与官方知识档案匹配，可进一步学习相关知识