<!-- hutool工具類(lèi)-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.3</version>
</dependency>

## url 數(shù)據(jù)庫(kù)連接地址
## user 連接數(shù)據(jù)庫(kù)賬號(hào)
## pass 連接數(shù)據(jù)庫(kù)密碼
url = jdbc:mysql://localhost:3306/test
user = root
pass = 123456

# 導(dǎo)出sql文件的位置
sql:
  dbname: notepad # 數(shù)據(jù)庫(kù)名
  filePath: /data/sql/ # 導(dǎo)出sql文件的位置 win下會(huì)直接在項(xiàng)目所在磁盤(pán)下建立 data/sql文件

package com.an.notepad.task;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.io.file.FileWriter;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.experimental.Accessors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.util.List;

@Configuration
public class ExportSQL {
    // 用于打印執(zhí)行日志
    private static final Logger logger = LoggerFactory.getLogger(ExportSQL.class);

    // 存儲(chǔ)的路徑
    @Value("${sql.filePath}")
    private String filePath;

    // 數(shù)據(jù)庫(kù)名
    @Value("${sql.dbname}")
    private String database_name;

    // 需要導(dǎo)出的表名
    private final List<TempBean> tempBeanList = CollectionUtil.newArrayList(
            // 這里寫(xiě)需要存儲(chǔ)的表名 有幾張表就new幾個(gè)
            new TempBean().setTable("n_note"),
            new TempBean().setTable("n_user")
    );

    @SneakyThrows
    public void export() {
        //獲取默認(rèn)數(shù)據(jù)源
        DataSource ds = DSFactory.get();
        // 獲取數(shù)據(jù)庫(kù)連接對(duì)象
        Connection connection = ds.getConnection();
        // 獲取連接信息
        DatabaseMetaData metaData = connection.getMetaData();
        // 創(chuàng)建sql文件對(duì)象
        FileWriter sqlFileWriter = FileWriter.create(new File(filePath + database_name + ".sql"));
        sqlFileWriter.write("");
        sqlFileWriter.append("USE " + database_name + ";\n");
        sqlFileWriter.append("/*\n");
        sqlFileWriter.append(" --------------------------------------------------\n");
        sqlFileWriter.append(" Target Server Type    : " + metaData.getDatabaseProductName() + ";\n");
        sqlFileWriter.append(" Target Server Version : " + metaData.getDatabaseProductVersion() + ";\n");
        sqlFileWriter.append(" \n");
        sqlFileWriter.append(" Target Server Date : " + DateTime.now() + ";\n");
        sqlFileWriter.append(" \n");
        sqlFileWriter.append(" --------------------------------------------------\n");
        sqlFileWriter.append("*/\n");
        sqlFileWriter.append("SET NAMES utf8mb4;\n");
        sqlFileWriter.append("SET FOREIGN_KEY_CHECKS = 0;\n");
        for (TempBean tempBean : tempBeanList) {
            String table = tempBean.table;
            sqlFileWriter.append("\n\n\n");
            // DROP TABLE
            sqlFileWriter.append("DROP TABLE IF EXISTS `" + table + "`;\n");
            // CREATE TABLE
            Entity createTableEntity = Db.use().queryOne("SHOW CREATE TABLE " + table);
            sqlFileWriter.append((String) createTableEntity.get("Create Table"));
            sqlFileWriter.append(";\n");
            // 看配置，是否需要insert語(yǔ)句
            if (!tempBean.insert) {
                continue;
            }
            // INSERT INTO
            List<Entity> dataEntityList = Db.use().query("SELECT * FROM " + table);
            for (Entity dataEntity : dataEntityList) {
                StrBuilder field = StrBuilder.create();
                StrBuilder data = StrBuilder.create();

                dataEntity.forEach((key, value) -> {
                    field.append(key).append(", ");
                    if (ObjectUtil.isNotNull(value)) {
                        if (StrUtil.equals("true", String.valueOf(value))) {
                            data.append("b'1'");
                        } else if (StrUtil.equals("false", String.valueOf(value))) {
                            data.append("b'0'");
                        } else {
                            data.append("'").append(value).append("'");
                        }
                    } else {
                        data.append("NULL");
                    }
                    data.append(", ");
                });

                sqlFileWriter.append("INSERT INTO `" + table + "`(");
                String fieldStr = field.subString(0, field.length() - 2);
                sqlFileWriter.append(fieldStr);
                sqlFileWriter.append(") VALUES (");
                String dataStr = data.subString(0, data.length() - 2);
                sqlFileWriter.append(dataStr);
                sqlFileWriter.append(");\n");
            }
        }
        sqlFileWriter.append("\n\n\n");
        sqlFileWriter.append("SET FOREIGN_KEY_CHECKS = 1;\n");
        logger.info(">>>>>>>>>> 存儲(chǔ)sql成功" + DateTime.now());
    }

    @Data
    @Accessors(chain = true)
    static class TempBean {
        // 表名
        public String table;
        // 是否需要insert語(yǔ)句，默認(rèn)需要 (表中數(shù)據(jù))
        public Boolean insert = true;
    }
}

package com.an.notepad.task;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

/**
 * 定時(shí)導(dǎo)出數(shù)據(jù)庫(kù)sql功能
 */
@Configuration
@EnableScheduling // 開(kāi)啟定時(shí)任務(wù)
public class ExportTask {
    @Autowired
    private ExportSQL exportSQL;

    //@Scheduled(cron = "0 0 1 * * ?") // 每天凌晨1點(diǎn)觸發(fā)一次
    @Scheduled(cron = "*/5 * * * * ?") // 5秒觸發(fā)一次
    public void task() {
        exportSQL.export();
    }
}