Java(SpringBoot)实现定时创建分表、查询分表数据(日志表、浏览记录表等实现)1、前言2、建表SQL语句3、处理逻辑(ShardTableHandler)4、项目启动处理(ShardTableRunner)5、定时任务处理(ShardTableTask)6、查询、新增语句7、代码演示

90 阅读 0 评论 60 点赞

我是靠谱客的博主优秀大树，最近开发中收集的这篇文章主要介绍Java(SpringBoot)实现定时创建分表、查询分表数据(日志表、浏览记录表等实现)1、前言2、建表SQL语句3、处理逻辑(ShardTableHandler)4、项目启动处理(ShardTableRunner)5、定时任务处理(ShardTableTask)6、查询、新增语句7、代码演示，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

???? @ 作者：一恍过去

???? @ 主页： https://blog.csdn.net/zhuocailing3390

???? @ 社区： 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

3、处理逻辑(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();
            }
        }
    }
}

4、项目启动处理(ShardTableRunner)

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);
    }
}

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);
    }
}

6、查询、新增语句

LogMapper.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.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>

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";
    }
}