有這樣一個業務需求，需要將資料歸檔的表每月定時備份，并且洗掉之前表中的資料，話不多說，直接上代碼！

注意：這種方法適合資料量小，業務要求不高的場景！

專案采用SpringBoot  + MyBatis  + MySql實作，

以下是詳細的代碼步驟說明：

第一步：在springboot下新建task定時任務類,并且定義方法，

package com.nb.nbbl.utils.task;

import com.nb.nbbl.mapper.TaskMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;

@Component
@Slf4j
public class Task {
    private final static String  ip="127.0.0.1";
    private final static int  port= 3306;
    private final static String  user="root";
    private final static String  password="root";
    private  final static String databasenameame ="nbbl.2#";


    @Autowired
    private  TaskMapper taskMapper;

    @Scheduled(cron = "0 55 23 1 * ?") // 每月1號的23點59分執行
//    @Scheduled(cron = "*/10 * * * * ?")
    public void backMysql() throws IOException, InterruptedException {
        log.info("備份資料庫");
        String backDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss").format(new Date());
        //判斷是windows系統還是linux系統
        String os = System.getProperty("os.name");
        if(os.toLowerCase().startsWith("win")) {
            System.out.println(os + "執行備份");
            dataBaseDump(ip, port, user, password, backDate);
        }
    }
    public  void dataBaseDump(String ip,int port,String user,String passWord,String backDate) throws IOException, InterruptedException {
        File file = new File("D:\\MysqlBackFile");
        if(!file.exists()){
            file.mkdir();
        }
         // 注意這句命令一定要加上\\很容易出錯
        String command =" D:\\mysqldump\\mysqldump -h" +ip+ " -P" +port+ " -u" +user+ " -p" +passWord+ " " +databasenameame+ ">" +file+"\\"+backDate+".sql";
        System.out.println(command);
        StringBuilder msg = new StringBuilder();
        BufferedReader bufferedReader = null;
        Process proc = null;
        //命令之間不能有空格
        ProcessBuilder pb = new ProcessBuilder("cmd", "/c", command);
//        pb.directory(new File(dir));
        try {
            proc = pb.start();
            System.out.println( proc.getOutputStream());

            bufferedReader = new BufferedReader(new InputStreamReader(proc.getErrorStream(), "gbk"));
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                msg.append(line);
            }
            System.out.println(msg);
            proc.waitFor();
            if(  proc.waitFor() ==0){
                System.out.println("資料備份成功");
                //資料備份成功后，執行洗掉操作，洗掉7天以前的資料
                taskMapper.deleteData2();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (bufferedReader != null) {
                try {
                    bufferedReader.close();
                } catch (Exception ex) {
                }
            }
            if(proc!=null){
                proc.destroy();
            }
        }

    }
}
第二步:定義洗掉資料介面

package com.nb.nbbl.mapper;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TaskMapper {

   void deleteData2();

}
第三步：撰寫task.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.nb.nbbl.mapper.TaskMapper">

<!--    <insert id="insert" parameterType="com.nb.nbbl.entity.DeviceTable">-->
<!--        insert into device_table(device_code,age,birthday,status) values(#{name},#{age},#{birthday},#{status})-->
<!--    </insert>-->
    <delete id="deleteData2" >
        {call deleteData()}
    </delete>
</mapper>


第四步：撰寫存盤程序

CREATE DEFINER=`root`@`%` PROCEDURE `deleteData`()
BEGIN
#Routine body goes here...
delete from bar_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from btm where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from cavitytemp where DATE_SUB(CURDATE(), INTERVAL 7 DAY) >date(systime);
delete from device where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_area_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_whole_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_inn_out_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from pressure where DATE_SUB(CURDATE(), INTERVAL 7 DAY) >date(systime);
delete from product_type where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from robot_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from safety_bolt_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from warnning_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);

END

第五步：查看結果

至此，簡單的資料備份這個功能點已經實作了！

標籤：Java

上一篇：代碼寫的垃圾被嫌棄？這 3 個插件你值得擁有！

下一篇：Spring Cloud Alibaba 使用Seata解決分布式事務