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

前言

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

配置

• 略

主要依赖

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