Spring Boot+MyBatis实现数据库历史数据定时迁移

目录

项目场景：

逻辑描述：

主要代码：

---

项目场景：

Spring Boot+MyBatis实现数据库历史数据定时迁移，Mysql和Oracl都可以，需要调整sql语句，这里是Mysql的示例。

---

逻辑描述：

每天定时备份3个月前的数据，删除旧数据，会以年份自动生成表名，表不存在自动创建。

---

主要代码：

启动类

package com.test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;

/**
 * Springboot的启动类
 */
@EnableScheduling    //启用定时器
@SpringBootApplication
public class ApplicationMain {

	public static void main(String[] args) {
		SpringApplication.run(ApplicationMain.class, args);
	}
}

定时器

package com.test.scheduled;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;


import com.test.service.LogService;

import lombok.extern.slf4j.Slf4j;


@Component
@Slf4j
public class QuartzService {
	
	@Autowired
    private LogService logService;
	
	//  每分钟启动
//	@Scheduled(cron = "0 0/1 * * * ?")
	// 每天凌晨1点启动
	@Scheduled(cron = "0 0 1 * * ?")
	public void deleteScheduled() {
		log.info("==================== 删除三个月前的日志开始 ====================");
		this.delClickLog();
		log.info("==================== 删除三个月前的日志结束 ====================");
		
	}
	
	
	/**
	 * 删除三个月前的日志
	 */
	private void delClickLog() {
		try {
			Calendar c = Calendar.getInstance();
	        c.add(Calendar.MONTH, - 3);  //设置为三个月前
	        Date dateBefore = c.getTime();   //得到三个月前的时间
	        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	        String time = simpleDateFormat.format(dateBefore) + " 00:00:00";
	        
			/*
			 * 保存三个月前的记录，表名应该从前一天开始算的年份
			 * 比如三个月前是2024年1月1日，这时候查出的数据还是2023年的数据，三个月前的前一天是2023年12月31日所属的年份刚好对上那个月的数据
			 */
			Calendar c1 = Calendar.getInstance();
	        c1.setTime(dateBefore);
	        c1.add(Calendar.DAY_OF_MONTH, - 1);  //设置为三个月前的前一天
			SimpleDateFormat sf = new SimpleDateFormat("yyyy");
	        String timeSuffix = sf.format(c1.getTime());
			//操作日志备份
	        logService.backupLog(time, timeSuffix);
			
			//删除操作日志
			logService.deleteClickLog(time);
		} catch (Exception e) {
			log.error("日志删除异常:{}",e);
		}
	}
	
}

LogService.java

package com.test.service;

import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.test.desktop.mapper.LogMapper;

@Service
public class LogService {

	@Resource
    private LogMapper logMapper;
	
	/** 日志表前缀 */
    private final static String TABLE_PREFIX = "clickapp_log_";
    
	
	/**
     * 备份日志
     * @param createTime	时间
     * @param timeSuffix	后缀
     */
	@Override
	public void backupLog(String createTime, String timeSuffix) {
		String tableName = TABLE_PREFIX + timeSuffix;
		boolean flag = existTable(tableName);	//判断表是否存在
		if(!flag){
			//表不存在，先创建表
			createTable(tableName);
		}
		
		logMapper.backupLog(createTime, tableName);
	}
    
	/**
     * 表是否存在
     * @param tableName	表名
     * @return	true存在，false不存在
     */
	@Override
	public boolean existTable(String tableName){
		int count = logMapper.existTable(tableName);
		if(count > 0){
			return true;
		}
		return false;
	}
	
	/**
     * 创建表
     * @param tableName	表名
     */
	@Override
	public void createTable(String tableName){
		logMapper.createTable(tableName);
	}

	/**
     * 删除日志
     * @param userId
     * @return
     */
	@Override
	public void deleteClickLog(String createTime) {
		logMapper.deleteClickLog(createTime);
	}
	
}

LogMapper.java

package com.test.mapper;

import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.StatementType;


public interface LogMapper {
	
	
	/**
     * 备份日志
     * @param createTime	时间
     * @param tableName	表名
     */
	void backupLog(@Param("createTime")String createTime, @Param("tableName")String tableName);
	
	/**
     * 表是否存在
     * @param tableName	表名
     * @return	记录数
     */
    int existTable(@Param("tableName")String tableName);
    
    /**
     * 创建表
     * @param tableName	表名
     */
    void createTable(@Param("tableName")String tableName);
    
    /**
     * 删除日志
     * @param userId
     * @return
     */
    void deleteClickLog(@Param("createTime")String createTime);
    
}

LogMapper.xml

<?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.xinghuo.desktop.mapper.DesktopLogMapper">
	<!-- 备份日志 -->
	<insert id="backupLog">
		INSERT INTO ${tableName} 
		SELECT * FROM clickapp_log 
		WHERE INSERT_TIME <![CDATA[ < ]]> #{createTime} 
	</insert>
	
	<!-- 查看表是否存在 -->
	<select id="existTable" parameterType="String" resultType="Integer">
		select count(1) from information_schema.`TABLES` where table_name = #{tableName} 
	</select>
	
	<!-- 创建表 -->
	<update id="createTable" parameterType="String">
		CREATE TABLE ${tableName} (
		  `NUM_ID` varchar(32) NOT NULL,
		  `REG_ID` varchar(12) NOT NULL,
		  `USER_ID` varchar(18) NOT NULL,
		  `ORGANIZATION` varchar(100) NOT NULL,
		  `ORGANIZATION_ID` varchar(30) NOT NULL,
		  `USER_NAME` varchar(100) NOT NULL,
		  `OPERATE_TIME` char(14) NOT NULL,
		  `TERMINAL_ID` varchar(40) NOT NULL,
		  `OPERATE_TYPE` int(1) NOT NULL,
		  `OPERATE_RESULT` char(1) NOT NULL,
		  `ERROR_CODE` char(4) DEFAULT NULL,
		  `OPERATE_NAME` varchar(100) NOT NULL,
		  `OPERATE_CONDITION` varchar(200) NOT NULL,
		  `INSERT_TIME` datetime NOT NULL,
		  `FOREIGN_REG_ID` varchar(36) NOT NULL,
		  `OPERATE_DISPLAY` varchar(2000) DEFAULT NULL,
		  PRIMARY KEY (`NUM_ID`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

	</update>
	
	<delete id="deleteClickLog">
        delete from clickapp_log where INSERT_TIME <![CDATA[ < ]]> #{createTime} 
    </delete>
    
</mapper>