1. 前言
项目开发接到需求,要求将业务数据按月归档,也就是每个月的数据单独保留在一张表中,每个月都要生成新表。以前从没有遇到过这样的场景,快速思考实现方案,主要的难点如下:
- 项目使用
MyBatis
框架,ORM
的思想是一个 bean 映射一张表,如何实现一个 bean 对象映射多张结构相同而名称不同的表?- 每月生成新表,如何知道数据库是否已经存在当月表,不存在时如何创建新表?
幸亏 MyBatis
这个轻量级的 ORM
框架为手写 SQL 语句留下了余地,否则只能把活交给 DBA
去琢磨了。我们知道 MyBatis
通过 Mapper
去操作数据库,并且可以自行手写灵活的 SQL
语句,这就给了我们极大的便利
2. 动态创建表
2.1 查询数据库是否存在目标表
基于 Mapper
定义接口方法,在方法上添加注解@Select
自行写好 SQL 语句。这条语句将数据库名称
和表名称
作为入参,从数据库本身保存的表信息中统计目标数据库中目标表的数量,通过其返回值可判断目标表是否存在
@Repository
public interface ActionEventMapper extends BaseMapper<ActionEvent> {
@Select("SELECT count(*) FROM information_schema.`tables` WHERE TABLE_SCHEMA = #{dbName} " +
"AND TABLE_NAME = #{tableName}")
int countTable(@Param("tableName") String tableName, @Param("dbName") String dbName);
}
2.2 动态创建表
同样的,将手写的 SQL 语句映射到接口方法上,将表名作为参数传入,完成目标表的动态创建。需注意 MySql 中对表名的格式有要求,连接符必须使用下划线_
,否则会有语法错误。另外SQL 语句中表名使用 ${} 直接拼接,而不是使用 #{} 占位符
@Repository
public interface ActionEventMapper extends BaseMapper<ActionEvent> {
@Update("CREATE TABLE IF NOT EXISTS ${tableName}(" +
" `FuiId` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'," +
" `FuiEventType` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '事件类型'," +
" `FuiMicroSeconds` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '微秒'," +
" `FuiCreateTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间'," +
" `FuiUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
" `FuiCasVersion` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'cas'," +
" PRIMARY KEY (`FuiId`)" +
")ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8 COMMENT='按月归档表';")
@CacheEvict(value = "action_event_cache", key = "1")
void createNewTable(@Param("tableName") String tableName);
2.3 定时创建表
使用 Spring 框架自带的定时任务注解@Scheduled
创建 cron
任务,定时创建表
@Slf4j
@Component
public class CreateTableJob {
@Autowired
private ActionEventRepository actionEventRepository;
/**
* 每月 28 号 00:00:00 创建下月的表
*/
@Scheduled(cron = "0 0 0 28 1-12 ?")
public void createTable() {
String tableName = actionEventTableUtil.getNextMonthTableName();
log.warn("It is time to create next month table:{}", tableName);
try {
actionEventRepository.createTable(tableName);
} catch (Exception e) {
log.warn("Cron job create next month table:" + tableName + "fail!", e);
}
}
}
3. 数据插入
3.1 单条数据插入
单条数据的插入非常简单,只需要注意将表名入参,并使用 ${tableName} 拼接表名
@Repository
public interface ActionEventMapper extends BaseMapper<ActionEvent> {
@Insert("INSERT INTO ${tableName}(FuiEventType, FuiMicroSeconds, FuiCreateTime, FuiCasVersion) VALUES "
+ "(#{actionEvent.eventType}, #{actionEvent.microSeconds}, #{actionEvent.createTime}, #{actionEvent.casVersion})")
// @Options 注解将插入表时主键字段 FuiId 生成的值回填到 bean 对象 actionEvent 的 id 属性
@Options(useGeneratedKeys = true, keyProperty = "actionEvent.id", keyColumn = "FuiId")
int save(@Param(value = "actionEvent") ActionEvent actionEvent, @Param("tableName") String tableName);
3.2 批量插入
多条数据的批量插入相对复杂,SQL 语句为类似脚本的形式,注解@Insert
中不再是一个很长的字符串,而是一个字符串数组
@Repository
public interface ActionEventMapper extends BaseMapper<ActionEvent> {
@Insert({"<script>",
"INSERT INTO ${tableName}(FuiEventType, FuiMicroSeconds, FuiCreateTime, FuiCasVersion) VALUES ",
"<foreach collection='actionEvents' item='actionEvent' index='index' separator=','>",
"(#{actionEvent.eventType}, #{actionEvent.microSeconds}, #{actionEvent.createTime}, #{actionEvent.casVersion})",
"</foreach>",
"</script>"})
@Options(useGeneratedKeys = true, keyProperty = "param1.id", keyColumn = "FuiId")
int saveBatch(@Param(value = "actionEvents") List<ActionEvent> actionEvents,
@Param("tableName") String tableName);
注意
使用@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "FuiId")
将表生成的主键回填到 bean 对象对应属性的时候需注意,keyProperty
需要指定为对应入参的对应属性,形式为参数名.id
。一般报错会有如下信息,表明了可用参数,对于批量插入一般是使用param1.id
Specified key properties are [id] and available parameters are [actionEvent, param1, tableName, param2]
3.3 数据查询
查询数据时需要注意,实体 bean 对象映射的表名与实际查询的表会不一致,需要手动使用注解 @Results
完成表字段与 bean 属性的映射
/**
* 实际查询的表名与 {@link ActionEvent} bean 映射的表名不一致,需手动映射
* */
@Select("select * from ${tableName} ${ew.customSqlSegment}")
@Results(value ={
@Result(column = "FuiEventType", property = "eventType"),
@Result(column = "FuiSeconds", property = "seconds"),
})
List<ActionEvent> selectFrom(@Param("tableName") String tableName, @Param(Constants.WRAPPER) LambdaQueryWrapper<ActionEvent> wrapper);