java-自动建表-按月份

采用的是xxl-job任务调度

import org.apache.commons.io.FileUtils;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.IJobHandler;
import com.xxl.job.core.handler.annotation.XxlJob;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.File;

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;

/**
 * 自动建表-按月份
 */
@Component
public class CreateTableJob {
    private static final Logger logger = LoggerFactory.getLogger(CreateTableJob.class);

    public static final String TABLE_PREFIX = "_";

    @Resource
    private JdbcTemplate jdbcTemplate;

    @XxlJob(value = "createTableJob")
    public ReturnT<String> execute(String param) throws Exception {
        // 表名需要通过参数传递，多个表用逗号分隔。只需传表名即可
        String[] tableNames = param.split(",");
        // 获取当前年月
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        // 月份 `+1`，创建下月表结构
        month = month == 12 ? 1 : month + 1;
        String monthStr = month >= 10 ? String.valueOf(month) : "0" + month;
        String suffix = year + monthStr;
        Connection conn = null;
        InputStream in = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            for (String name : tableNames) {
                String tableName = name + TABLE_PREFIX + suffix;
                String[] types = new String[]{"TABLE"};
                ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), conn.getSchema(), "%", types);

                boolean exist = false;
                // 判断是否存在
                while (rs.next()) {
                    String tableStr = rs.getString("TABLE_NAME");
                    if (tableStr.equals(tableName)) {
                        logger.info("表存在: {}", tableName);
                        exist = true;
                        break;
                    }
                }

                // 不存在则创建
                if (!exist) {
                    logger.info("创建表: {}", tableName);
                    in = this.getClass().getClassLoader().getResourceAsStream("tableTemplate/" + name + ".sql");
                    // 创建临时文件(空文件)
                    File file = File.createTempFile("test", ".sql");
                    // 删除临时文件
                    file.deleteOnExit();
                    // 将获取的流转为文件，在转换过后我们的资源文件就被copy到前面创建的临时文件中了
                    FileUtils.copyInputStreamToFile(in, file);
                    logger.info("临时文件路径: {}", file.getAbsolutePath());
                    // 转成string输入文本
                    String content = FileUtils.readFileToString(file, StandardCharsets.UTF_8);
                    // 获取sql
                    String sql = String.format(content, tableName);
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.execute();
                }
            }
            return ReturnT.SUCCESS;
        } catch (Exception e) {
            e.printStackTrace();
            return new ReturnT<>(IJobHandler.FAIL.getCode(), "command exit value(" + e.getMessage() + ") is failed");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (in != null) {
                    in.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

sql文件存放位置（src/main/resources/tableTemplate/xxxx.sql）

ps：同时避免了“cannot be resolved to absolute file path because it does not reside in the file system: jar:file”异常