Java 定时器按照日期创建表，并备份原表数据

Serendipity_77

已于 2024-04-12 14:24:17 修改

阅读量243

点赞数 7

文章标签：数据库 java sql 后端系统架构

于 2024-04-12 14:09:14 首次发布

本文链接：https://blog.csdn.net/Serendipity_77/article/details/137677471

版权

一、背景

因单表数据量太大，查询等操作时间过长，遂根据日期做分表处理

二、实现思路

1. 新建表（新表名：原表名_年月日）- 零点

2. 复制昨天的和今天的数据到新表中 - 零点

3. 上述操作完成后，删除原表中昨天及之前的数据

三、具体实现

1. 封装工具类

为后续方便扩展，表名和排序字段通过手动传入的方式

代码实现：上述三个步骤的实现

@Component
public class BackupDataUtil {
    //引入业务层，后续用来做查询数据，删除数据的操作
    @Autowired
    private BusinessFlightDataService configService;

    private static BusinessFlightDataService flightDataService;

    // 静态属性注入
    @PostConstruct
    void initProperties() {

        flightDataService = this.configService;
    }

    /**
     * 1.新建新表 表名+日期
     */
    public static void createTable(String url,String username,String password,String tableName){
        try {
            // 加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 创建数据库连接
            Connection conn = DriverManager.getConnection(url, username, password);

            Set<String> columnSet = new LinkedHashSet<>(); // 使用 Set 来去重

            // 创建Statement对象用于执行SQL语句
            Statement stmt = conn.createStatement();

            // 获取数据库元数据
            DatabaseMetaData meta = conn.getMetaData();
            // 获取表创建语句
            ResultSet columns = meta.getColumns(null, null, tableName,null);

            //组装表字段
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String dataType = columns.getString("TYPE_NAME");
                int columnSize = columns.getInt("COLUMN_SIZE");
                //时间特殊处理
                if (dataType.contains("TIME")){
                    columnSize = 6;
                }
                String columnDef = "";
                //json类型长度特殊处理
                if (dataType.toLowerCase().contains("json")){
                    columnDef = columnName + " " + dataType;
                }else{
                    columnDef = columnName + " " + dataType+"("+columnSize+")";
                }
                columnSet.add(columnDef);
            }

            if (!columnSet.isEmpty()) {
                //获取昨天的年月日
                // 获取当前日期
                LocalDate today = LocalDate.now();
                // 获取昨天的日期
                LocalDate yesterday = today.minus(1, ChronoUnit.DAYS);
                String date = yesterday.getYear() + "" + yesterday.getMonthValue() + "" + yesterday.getDayOfMonth();
                tableName = tableName + "_" + date;
                //组装建表语句
                String createTableSQL = "CREATE TABLE " + tableName + " (\n";
                createTableSQL += String.join(",\n", columnSet);
                createTableSQL += "\n);";
                // 执行SQL语句创建新表
                stmt.executeUpdate(createTableSQL);
            } else {
                System.out.println("找不到字段!");
            }
            // 关闭连接和Statement对象
            stmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 2.复制昨天的和今天的数据到新表中
     */
    public static void copyTableData(String url,String username,String password,String tableName, String time){
        try {
            // 加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 创建数据库连接
            Connection conn = DriverManager.getConnection(url, username, password);

            // 创建Statement对象用于执行SQL语句
            Statement stmt = conn.createStatement();

            //获取昨天的年月日
            // 获取当前日期
            LocalDate today = LocalDate.now();
            // 获取昨天的日期
            LocalDate yesterday = today.minus(1, ChronoUnit.DAYS);
            String date = yesterday.getYear() + "" + yesterday.getMonthValue() + "" + yesterday.getDayOfMonth();
            String sourceTableName = tableName;
            String newTableName = tableName + "_" + date;

            //获取昨日数据 第一条数据id
            Long id = flightDataService.getYesterdayFirstId(sourceTableName,time);
            //定义SQL语句,复制昨日与今日数据
            String sql = "INSERT INTO " + newTableName + " SELECT * FROM " + sourceTableName + " where id >= "+id;
            //执行SQL语句,复制数据
            stmt.executeUpdate(sql);

            // 关闭连接和Statement对象
            stmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 3. 删除原表中昨天及之前的数据
     */
    public static void deleteSourceData(String tableName,String time){
        try {
            //获取昨日数据 最后一条数据id
            Long id = flightDataService.getYesterdayLastId(tableName,time);
            //删除此id之前的数据
            boolean flag = flightDataService.deleteDataById(tableName,id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2. 业务层与dao层

业务层调dao层，这里就不过多赘述

ServiceImpl：

   @Override
    public Long getYesterdayFirstId(String tableName, String time) {
        return businessFlightDataMapper.getYesterdayFirstId(tableName,time);
    }

    @Override
    public boolean deleteDataById(String tableName,Long id) {
        return businessFlightDataMapper.deleteDataById(tableName,id);
    }

    @Override
    public Long getYesterdayLastId(String tableName, String time) {
        return businessFlightDataMapper.getYesterdayLastId(tableName,time);
    }

Mapper：

  Long getYesterdayFirstId(@Param("tableName") String tableName,@Param("time") String time);

    boolean deleteDataById(@Param("tableName") String tableName, @Param("id") Long id);

    Long getYesterdayLastId(@Param("tableName") String tableName,@Param("time") String time);

sql：

说明：因为表名与时间字段需要动态传入，所以用了${}

    <!-- 获取昨天的第一条数据-->
    <select id="getYesterdayFirstId" resultType="java.lang.Long">
        SELECT id
        FROM ${tableName}
        WHERE DATE(${time}) = CURDATE() - INTERVAL 1 DAY
        ORDER BY id ASC
        LIMIT 1;
    </select>

    <!-- 删除数据 -->
    <delete id="deleteDataById">
        delete from ${tableName} where id &lt;= #{id}
    </delete>

    <!-- 获取昨天的最后一条数据-->
    <select id="getYesterdayLastId" resultType="java.lang.Long">
        SELECT id
        FROM ${tableName}
        WHERE DATE(${time}) = CURDATE() - INTERVAL 1 DAY
        ORDER BY id DESC
        LIMIT 1;
    </select>

3. 设置定时器，调用工具类

  @Value("${spring.datasource.url}")
  private String url;
  @Value("${spring.datasource.username}")
  private String username;
  @Value("${spring.datasource.password}")
  private String password;

    //每天零点零一分，创建新表
    @Scheduled(cron = "0 0 0 * * ?")
    public void createTableAndCopyData() {
        //创建新表
        BackupDataUtil.createTable(url,username,password,"test");

        //复制表数据-昨日与今日的
        BackupDataUtil.copyTableData(url,username,password,"test","atime");
    }

    //每天5点，删除原表昨日数据
    @Scheduled(cron = "0 0 5 * * ?")
    public void deleteData() {
        //创建新表
        BackupDataUtil.deleteSourceData("test","atime");
    }