526互联

3、ShardingSphere实战（三）

发布时间 2023-04-17 23:43:35作者: 爱文(Iven)

一、前言：

本项目按照时间字段进行分表，需要提前将主表写入数据库
优势:
1、实现自动建表，且不需要配置 SQL
2、范围分表查询时自动排除不存在的表

 

二、项目实战：

1、创建主表：

CREATE TABLE `t_user` (
  `id` bigint(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `create_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

 

2、相关依赖：

注：ShardingSphere版本与Springboot版本要相互配置

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.iven</groupId>
    <artifactId>sharding-jdbc-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc-demo</name>
    <description>sharding-jdbc-demo</description>

    <properties>
        <java.version>1.8</java.version>
        <mybatis-spring-boot>2.0.1</mybatis-spring-boot>
        <druid>1.1.16</druid>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot}</version>
        </dependency>
        <!--mybatis驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
        </dependency>
        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid}</version>
        </dependency>
        <!--lombok实体工具-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--JSON解析工具-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.79</version>
        </dependency>
        <!-- huTool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.7</version>
        </dependency>
        <!--shardingsphere-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
    </dependencies>

 

3、相关配置：

server.port=8080

#声明数据源
spring.shardingsphere.datasource.names=master
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
#MySQL5驱动：com.mysql.jdbc.Driver，MySQL6驱动：com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/student?characterEncoding=utf-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

#声明数据分表规则
#方式一：采用行表达式分片策略，根据id取模值分片，需要提前建好所有对应表
#指定所需分的表
#spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=master.t_user_$->{0..2}
#指定规则主键
#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
#指定分表规则
#spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{id % 3}

#方式二：采用标准分片策略，只支持对单个分片键为依据的分库分表（设定精准分片算法与范围分片算法），实现自动建表（需要手动建好主表），对时间进行分片
#指定所需分的表(如若需要可采用动态分配方式获取查询列表，否则当前只查询student.t_user)
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=master.t_user
#指定规则主键
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.sharding-column=create_at
#设定精准分片算法
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.precise-algorithm-class-name=com.iven.shardingjdbcdemo.config.DateShardingAlgorithm
#设定范围分片算法
spring.shardingsphere.sharding.tables.t_user.table-strategy.standard.range-algorithm-class-name=com.iven.shardingjdbcdemo.config.DateShardingAlgorithm

#打印SQL
spring.shardingsphere.props.sql.show=true


#指定mybatis配置路径
mybatis.mapperLocations=classpath:mapper/*.xml

#数据库名注入
db.schema-name=student

 

4、相关标准分片策略声明：

（1）、声明分表工具:

读取当前数据库缓存，用于自动建表

import com.iven.shardingjdbcdemo.dao.CommonMapper;
import com.iven.shardingjdbcdemo.model.CreateTableSql;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

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

    @Autowired
    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(JdbcTemplate jdbcTemplate, String schemaName) {
        // 读取指定数据库中所有表名（SELECT TABLES.TABLE_NAME FROM information_schema.TABLES WHERE TABLES.TABLE_SCHEMA=#{dbName} AND TABLE_TYPE = 'BASE TABLE'）
        List<String> tableNameList = new ArrayList<>();
        DatabaseMetaData databaseMetaData = null;
        try {
            databaseMetaData = jdbcTemplate.getDataSource().getConnection().getMetaData();
            ResultSet tables = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (tables.next()) {
                tableNameList.add(tables.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.tableNameCache.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
    }

}

（2）、自动建表操作：

1）实体类：

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

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

    private String table;

    private String createTable;
}

2）、DAO类：

import com.iven.shardingjdbcdemo.model.CreateTableSql;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;


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

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

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

}

3）、mapper：

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

    <resultMap id="selectTableCreateSqlResultMap" type="com.iven.shardingjdbcdemo.model.CreateTableSql">
        <result column="Table" property="table"/>
        <result column="Create Table" property="createTable"/>
    </resultMap>

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

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

</mapper>

（3）、标准分片策略声明：

指定分表规则，时间格式

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 lombok.extern.slf4j.Slf4j;
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;

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

    /**
     * 获取 指定分表
     * 精准分片算法
     * @param availableTargetNames 数据库中所有的事实表
     * @param preciseShardingValue 分片相关信息
     * @return 返回匹配的数据源
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        log.info("精准分片：数据库中所有的事实表:{},分片相关信息{}",availableTargetNames,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) {
        log.info("范围分片：数据库中所有的事实表:{},分片相关信息{}",availableTargetNames,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;
    }
}

（4）、启动时将表载入缓存：

import com.iven.shardingjdbcdemo.dao.CommonMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.JdbcTemplate;
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;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        // 给分表工具类注入属性
        ShardingAlgorithmTool.setCommonMapper(commonMapper);
        // 调用缓存重载方法
        ShardingAlgorithmTool.tableNameCacheReload(jdbcTemplate, schemaName);

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

 

5、测试：

插入的数据根据createAt时间字段进行自动建表与分表

（1）、相关实体类：

@Data
public class DemoVO implements Serializable {

    private Integer id;

    private String name;

    private Date createAt;

}

（2）、相关SQL:

    <resultMap id="BaseResultMap" type="com.iven.shardingjdbcdemo.model.DemoVO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="creat_at" property="createAt" jdbcType="TIMESTAMP"/>
    </resultMap>

    <insert id="demoData" parameterType="com.iven.shardingjdbcdemo.model.DemoVO">
    INSERT INTO student.`t_user` (`id`, `name`, `create_at`)
    VALUES (#{id}, #{name}, #{createAt})
  </insert>

 

三、报错解决：

相关报错方案一;

相关报错方案二;

 

四、相关参考：

学习一;

学习二;