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

灰太狼 2023-10-04 19:18 41阅读 0赞

##  ##

**目录**

项目场景：

逻辑描述：

主要代码：

--------------------

## 项目场景： ##

`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.setTime(new Date());
    	        c.add(Calendar.MONTH, - 3);  //设置为三个月前
    	        Date dateBefore = c.getTime();   //得到三个月前的时间
    	        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	        String time = simpleDateFormat.format(dateBefore);
    	        
    			SimpleDateFormat sf = new SimpleDateFormat("yyyy");
    	        String timeSuffix = sf.format(dateBefore);
    			//操作日志备份
    	        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>