• Java(SpringBoot)实现定时创建分表、查询分表数据(日志表、浏览记录表等实现)

      😊 @ 作者： 一恍过去
      🎊 @ 社区： Java技术栈交流
      🎉 @ 主题： Java(SpringBoot)实现定时创建分表、查询分表数据(日志表、浏览记录表等实现)
      ⏱️ @ 创作时间： 2022年09月14日

      目录

      在这里插入图片描述

      1、前言

      • 说明：该方式不依赖于第三方插件，适合按单个分表查询的情况(比如查询按月分表，查询时间范围就是月),不涉及跨表查询，如果存在跨表查询可以通过Sharing-JDBC实现，《Sharing-JDBC实现》
      • 逻辑：在项目启动时会检测表是否已经被创建了，如果没有则通过读取sql语句文件实现创建，并且设置一个定时任务定期创建表，按月分表就每月底创建第二月的表，按日分表就每天晚创建后几天的表；
      • 特殊处理：为了保证项目存在集群情况下重复创建表，使用redis的实现了简单锁，保证只有一个服务进行表的创建。

      2、建表SQL语句

      在resources下创建sys_operate_log.sql文件，内容如下：
      通过%s通配符用于替换操作；

      -- 创建表：sys_operate_log_yearMonth
CREATE TABLE `%s`
(
    `id`              bigint NOT NULL COMMENT '主键',
    `model`           varchar(20)  DEFAULT NULL COMMENT '模块名称',
    `name`            varchar(20)  DEFAULT NULL COMMENT '操作名称',
    `ip`              varchar(20)  DEFAULT NULL COMMENT '请求ip',
    `service_name`    varchar(50)  DEFAULT NULL COMMENT '当前服务名称',
    `browser`         varchar(50)  DEFAULT NULL COMMENT '操作浏览器',
    `os`              varchar(50)  DEFAULT NULL COMMENT '操作系统',
    `url`             varchar(500) DEFAULT NULL COMMENT '请求url',
    `user_id`         bigint       DEFAULT NULL COMMENT '操作人ID',
    `user_name`       varchar(100) DEFAULT NULL COMMENT '操作人名称',
    `http_method`     varchar(20)  DEFAULT NULL COMMENT '请求类型',
    `request_params`  text COMMENT '请求参数',
    `response_params` text COMMENT '响应参数',
    `operate_type`    varchar(200) DEFAULT '' COMMENT '方法名称',
    `operate_event`   varchar(200) DEFAULT NULL COMMENT '类名称',
    `is_success`      int          DEFAULT NULL COMMENT '是否成功：1-成功 2-失败',
    `error_code`      varchar(10)  DEFAULT NULL COMMENT '错误代码',
    `error_msg`       varchar(200) DEFAULT NULL COMMENT '错误信息',
    `time`            bigint       DEFAULT NULL COMMENT '响应时长(ms)',
    `error_trace`     text COMMENT '错误堆栈信息',
    `create_time`     timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '操作时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26

      3、处理逻辑(ShardTableHandler)

      ShardTableHandler类中会检测指定创建的表是否存在，只有不存在时才会执行创建指令。
      ShardTableHandler

      
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.io.FileUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import javax.annotation.Resource;
import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.*;

/**
 * @Author: LiHuaZhi
 * @Description:
 **/
@Component
public class ShardTableHandler {

    /**
     * 引入数据源，也可以是 DruidDataSource，此处根据项目配置来定
     */
    @Resource
    private HikariDataSource dataSource;

    public static final String TABLE_NAME_PREFIX = "sys_operate_log_";

    /**
     * 验证表是否存在，按月维度进行分表
     */
    public void checkExistTable(String tableName) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();

            String[] types = new String[]{"TABLE", "VIEW"};
            // 查询当前数据源下的表数据
            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)) {
                    exist = true;
                    break;
                }
            }

            // 不存在则创建
            if (!exist) {
                System.out.println("创建表");

                // 获取文件的URL
                File file = ResourceUtils.getFile("classpath:sys_operate_log.sql");
                // 转成string输入文本
                String content = FileUtils.readFileToString(file, StandardCharsets.UTF_8);

                // 获取sql
                String sql = String.format(content, tableName);
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53
      • 54
      • 55
      • 56
      • 57
      • 58
      • 59
      • 60
      • 61
      • 62
      • 63
      • 64
      • 65
      • 66
      • 67
      • 68
      • 69
      • 70
      • 71
      • 72
      • 73
      • 74
      • 75

      4、项目启动处理(ShardTableRunner)

      在项目启动时，就是调用ShardTableHandler的checkExistTable()方法检测是否进行表的创建。
      ShardTableRunner 

      import com.lhz.demo.handler.ShardTableHandler;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.LocalDate;

/**
 * @Author: LiHuaZhi
 * @Description: 启动完成后加载
 **/
@Component
@Slf4j
public class ShardTableRunner implements ApplicationRunner {

    @Resource
    private ShardTableHandler shardTableHandler;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        log.info("==== 系统运行开始 ====");

        // 获取当前年月
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        String monthStr = month > 10 ? month + "" : "0" + month;

        // 比如名称为：sys_operate_log_202209，及表示存储2022年9月数据的表
        String tableName = ShardTableHandler.TABLE_NAME_PREFIX + year + monthStr;
        shardTableHandler.checkExistTable(tableName);
    }
}

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34

      5、定时任务处理(ShardTableTask)

      虽然项目在启动时会进行一次数据表是否存在的检测，但是为了包装实时性，可以通过定时任务进行数据表的创建，具体的创建频次可以根据业务实际情况来的。
      ShardTableTask

      import com.lhz.demo.handler.ShardTableHandler;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.LocalDate;

/**
 * @Author: LiHuaZhi
 * @Date: 2022/9/12 10:56
 * @Description:
 **/
@Component
public class ShardTableTask {

    @Resource
    private ShardTableHandler shardTableHandler;

    /**
     * 每月最后一天23:00执行任务 生成下月的表
     *
     * @throws Exception
     */
    @Scheduled(cron = "0 0 23 L 1/1 ?")
    public void shardTableTask() throws Exception {

        // 获取当前年月
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        // 月份 `+1`，创建下月表数据
		month = month >= 12 ? 1 : month + 1;
        String monthStr = month >= 10 ? month + "" : "0" + month;

        // 比如名称为：sys_operate_log_202209，及表示存储2022年9月数据的表
        String tableName = ShardTableHandler.TABLE_NAME_PREFIX + year + monthStr;
        shardTableHandler.checkExistTable(tableName);
    }
}

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38

      在这里插入图片描述

      6、查询、新增语句

      LogMapper.xml

      
DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lhz.demo.mapper.LogMapper">

    <select id="listOperateLog"
            resultType="com.lhz.demo.entity.OperateLog">
        select
        a.*
        from sys_operate_log_${suffix} a
        <where>
            <if test="param.model != null and param.model != ''">
                and instr(a.model,#{param.model})
            if>
            <if test="param.name != null and param.name != ''">
                and instr(a.name,#{param.name})
            if>
            <if test="param.userName != null and param.userName != ''">
                and instr(a.user_name,#{param.userName})
            if>
            <if test="param.httpMethod != null">
                and http_method =#{param.httpMethod}
            if>
            <if test="param.isSuccess != null">
                and is_success =#{param.isSuccess}
            if>
            <if test="param.startTime != null and param.endTime != null">
                and create_time BETWEEN #{param.startTime} and #{param.endTime}
            if>
        where>
    select>


    <insert id="insertOperateLog">
        insert into sys_operate_log_${suffix}(id, model, name, ip, browser, os, url,
                                              user_id, user_name, http_method,
                                              request_params, response_params, is_success,
                                              error_code, error_msg, time, error_trace, create_time)
        values (#{log.id}, #{log.model}, #{log.name}, #{log.ip}, #{log.browser}, #{log.os},
                #{log.url}, #{log.userId}, #{log.userName}, #{log.httpMethod},
                #{log.requestParams}, #{log.responseParams}, #{log.isSuccess}, #{log.errorCode},
                #{log.errorMsg}, #{log.time}, #{log.errorTrace}, #{log.createTime})
    insert>
mapper>

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43

      7、代码演示

      public class ShardTableService {

	@Resource
    private LogMapper logMapper;

    /**
     * 测试查询
     *
     * @param param
     * @return
     */
    public List<OperateLog> list(OperateLogParam param) {

        // 获取当前年月
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        String monthStr = month > 10 ? month + "" : "0" + month;
        String suffix = year + "" + monthStr;

        return logMapper.listOperateLog(suffix, param);

    }

    /**
     * 测试新增
     *
     * @return
     */
    public String add() {

        // 获取当前年月
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        String monthStr = month > 10 ? month + "" : "0" + month;
        String suffix = year + "" + monthStr;

        // 模拟实体类
        OperateLog operateLog = new OperateLog();
        long id = new Random().nextInt(999999999);
        operateLog.setId(id);
        operateLog.setIp("127.0.0.1");
        // 省略其他字段数据

        logMapper.insertOperateLog(suffix, operateLog);
        return "success";
    }
}

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47

      在这里插入图片描述

    • 相关阅读:
      【练习赛】2022年高教杯数学建模C题（第一题的第一小问）
      redis高级案列case
      浅尝KBQA中使用语义角色标注进行约束挂载
      乘法除法运算符规范
      JupyterNotebook的快捷键
      Vue如何监听键盘事件
      企业级自定义表单引擎解决方案（十六）--Excel导入导出
      Findbugs修改总结
      Python顺序表
      系统设计综述——个人思考总结
    • 原文地址：https://blog.csdn.net/zhuocailing3390/article/details/126817892