sharding jdbc 按日分表自动建表实现

前言

本项目分表方案是按照时间字段按日分表其他分表方案也可参考本文档实现自动建表
需要提前准备待分表的主表写入数据库
优势:
可以实现自动建表且不需要配置 SQL
范围分表查询时自动排除不存在的表

配置

spring:
  # shardingJDBC
  shardingsphere:
    datasource:
      names: db1
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/database?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&tinyInt1isBit=false
        username: root
        password: 123456
    sharding:
      tables:
        table_a:
          actual-data-nodes: db1.table_a
          table-strategy:
            standard:
              sharding-column: created_at
              precise-algorithm-class-name: com.**.service.config.sharding.DateShardingAlgorithm
              range-algorithm-class-name: com.**.service.config.sharding.DateShardingAlgorithm
        table_b:
          actual-data-nodes: db1.table_b
          table-strategy:
            standard:
              sharding-column: created_at
              precise-algorithm-class-name: com.**.service.config.sharding.DateShardingAlgorithm
              range-algorithm-class-name: com.**.service.config.sharding.DateShardingAlgorithm
      defaultDataSourceName: db1
    props:
      sql:
        # 打印 sql
        show: true

主要依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<!-- huTool -->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.7</version>
</dependency>

<!-- sharding jdbc-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.1</version>
</dependency>

分表工具类 ShardingAlgorithmTool

import com.**.**.dao.CommonMapper;
import com.**.**.domain.db.CreateTableSql;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;

import java.util.HashSet;
import java.util.List;

/**
 * 分表工具
 */
@Slf4j
public abstract class ShardingAlgorithmTool<T extends Comparable<?>> implements PreciseShardingAlgorithm<T>, RangeShardingAlgorithm<T> {

    private static CommonMapper commonMapper;

    private static final HashSet<String> tableNameCache = new HashSet<>();

    /**
     * 手动注入
     */
    public static void setCommonMapper(CommonMapper commonMapper) {
        ShardingAlgorithmTool.commonMapper = commonMapper;
    }

    /**
     * 判断 分表获取的表名是否存在 不存在则自动建表
     *
     * @param logicTableName  逻辑表名(表头)
     * @param resultTableName 真实表名
     * @return 确认存在于数据库中的真实表名
     */
    public String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {

        synchronized (logicTableName.intern()) {
            // 缓存中有此表 返回
            if (shardingTablesExistsCheck(resultTableName)) {
                return resultTableName;
            }

            // 缓存中无此表 建表 并添加缓存
            CreateTableSql createTableSql = commonMapper.selectTableCreateSql(logicTableName);
            String sql = createTableSql.getCreateTable();
            sql = sql.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
            sql = sql.replace(logicTableName, resultTableName);
            commonMapper.executeSql(sql);
            tableNameCache.add(resultTableName);
        }

        return resultTableName;
    }

    /**
     * 判断表是否存在于缓存中
     *
     * @param resultTableName 表名
     * @return 是否存在于缓存中
     */
    public boolean shardingTablesExistsCheck(String resultTableName) {
        return tableNameCache.contains(resultTableName);
    }

    /**
     * 缓存重载方法
     *
     * @param schemaName 待加载表名所属数据库名
     */
    public static void tableNameCacheReload(String schemaName) {
        // 读取数据库中所有表名
        List<String> tableNameList = commonMapper.getAllTableNameBySchema(schemaName);
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.tableNameCache.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
    }

}

分表实现类 DateShardingAlgorithm

import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * 日期分表策略
 */
public class DateShardingAlgorithm extends ShardingAlgorithmTool<Date> {

    /**
     * 获取 指定分表
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        return shardingTablesCheckAndCreatAndReturn(preciseShardingValue.getLogicTableName(), preciseShardingValue.getLogicTableName() + DateUtil.format(preciseShardingValue.getValue(), "_yyyy_MM_dd"));
    }

    /**
     * 获取 范围分表
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
        Range<Date> valueRange = rangeShardingValue.getValueRange();
        Date lowerDate = valueRange.lowerEndpoint();
        Date upperDate = valueRange.upperEndpoint();
        List<String> tableNameList = new ArrayList<>();
        for (DateTime dateTime : DateUtil.rangeToList(DateUtil.beginOfDay(lowerDate), DateUtil.endOfDay(upperDate), DateField.DAY_OF_YEAR)) {
            String resultTableName = rangeShardingValue.getLogicTableName() + DateUtil.format(dateTime, "_yyyy_MM_dd");
            if (shardingTablesExistsCheck(resultTableName)) {
                tableNameList.add(resultTableName);
            }
        }
        return tableNameList;
    }
}

项目启动时将表载入缓存/注入工具类属性 ShardingTablesLoadRunner

import com.**.**.config.sharding.ShardingAlgorithmTool;
import com.**.**.dao.CommonMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

/**
 * 项目启动后 读取已有分表 进行缓存
 */
@Slf4j
@Order(value = 1) // 数字越小 越先执行
@Component
public class ShardingTablesLoadRunner implements CommandLineRunner {

    @Value("${db.schema-name}")
    private String schemaName;

    @Resource
    private CommonMapper commonMapper;

    @Override
    public void run(String... args) throws Exception {

        // 给 分表工具类注入属性
        ShardingAlgorithmTool.setCommonMapper(commonMapper);
        // 调用缓存重载方法
        ShardingAlgorithmTool.tableNameCacheReload(schemaName);

        log.info("ShardingTablesLoadRunner start OK");
    }
}

Mybatis SQL 映射 CommonMapper

import com.**.**.domain.db.CreateTableSql;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 常用工具 mapper
 */
public interface CommonMapper {

    /**
     * 查询数据库中的所有表名
     *
     * @param schema 数据库名
     * @return 表名列表
     */
    List<String> getAllTableNameBySchema(@Param("schema") String schema);

    /**
     * 查询建表语句
     *
     * @param tableName 表名
     * @return 建表语句
     */
    CreateTableSql selectTableCreateSql(@Param("tableName") String tableName);

    /**
     * 执行SQL
     *
     * @param sql 待执行SQL
     */
    void executeSql(@Param("sql") String sql);

}

Mybatis SQL 映射 XML CommonMapper

<?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.**.**.dao.CommonMapper">

    <resultMap id="selectTableCreateSqlResultMap" type="com.**.**.domain.db.CreateTableSql">
        <result column="Table" property="table"/>
        <result column="Create Table" property="createTable"/>
    </resultMap>

    <select id="getAllTableNameBySchema" resultType="java.lang.String">
        SELECT TABLES.TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLES.TABLE_SCHEMA = #{schema}
    </select>

    <select id="selectTableCreateSql" resultMap="selectTableCreateSqlResultMap">
        SHOW CREATE TABLE ${tableName}
    </select>

    <update id="executeSql">
        ${sql}
    </update>

</mapper>

Mybatis SQL 映射实体 CreateTableSql

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 建表语句查询结果
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CreateTableSql {

    private String table;

    private String createTable;
}

posted @ 2021-08-20 16:31 Heei 阅读(8359) 评论(29) 编辑收藏举报

刷新页面返回顶部

heei

sharding jdbc 按日分表自动建表实现

前言

配置

主要依赖

分表工具类 ShardingAlgorithmTool

分表实现类 DateShardingAlgorithm

项目启动时将表载入缓存/注入工具类属性 ShardingTablesLoadRunner

Mybatis SQL 映射 CommonMapper

Mybatis SQL 映射 XML CommonMapper

Mybatis SQL 映射实体 CreateTableSql

公告

heei

sharding jdbc 按日分表 自动建表实现

前言

配置

主要依赖

分表工具类 ShardingAlgorithmTool

分表实现类 DateShardingAlgorithm

项目启动时将表载入缓存/注入工具类属性 ShardingTablesLoadRunner

Mybatis SQL 映射 CommonMapper

Mybatis SQL 映射 XML CommonMapper

Mybatis SQL 映射实体 CreateTableSql

公告

sharding jdbc 按日分表自动建表实现