springboot 集成sharding-jdbc 5.2.1版本

前提：

功课记录表越来越多，决定使用分表拆分。
采用sharding-jdbc 实现。
不需要分库，只做分表操作

当前环境使用的版本：

java 1.8
spring-cloud Hoxton.SR8
springboot 2.3.4.RELEASE
mybatis-plus 3.4.0

mysql 8.0.27
sharding-jdbc 5.2.1

备注：
本来想使用最新版本，发现阿里云maven仓库没有。
遂使用5.2.1

一、添加sharding-jdbc依赖

		<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.yaml</groupId>
                    <artifactId>snakeyaml</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.33</version>
        </dependency>

二、application.yml 配置

spring:
  autoconfigure:
    exclude:
      - org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
      - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
# shardingsphere-jdbc-core-spring-boot-starter 5.2.1 配置
  shardingsphere:
    props:
      sql-show: true  # 打印实际执行的 SQL
      sql-dialect: mysql
    datasource:
      names: d1  # 数据源名称
      d1:  # 数据源具体配置
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver  # MySQL 驱动
        url: jdbc:mysql://192.168.0.178:3301/xsfx_course_db		#MySQL地址
        username: root  # 数据库用户名
        password: root  # 数据库密码
    rules:
      sharding:
        # 分片算法配置
        sharding-algorithms:
          # 按年份分表的分片算法
          table-inline:
            type: CLASS_BASED  # 使用 INLINE 表达式分片
            props:
              strategy: STANDARD
              algorithmClassName: com.xsfx.service.course.provider.config.TableShardingAlgorithmConfig
        # 表分片规则配置
        tables:
          xsfx_course_user_log:  # 逻辑表名
            actual-data-nodes: d1.xsfx_course_user_log, d1.xsfx_course_user_log_$->{2025..2026}  # 实际数据节点，支持动态范围
            table-strategy:  # 分表策略
              standard:
                sharding-column: sharding_column  # 分片字段
                sharding-algorithm-name: table-inline  # 使用分片算法
logging:
  level:
    org.springframework.cloud.sleuth: debug
    org.apache.shardingsphere: DEBUG
    org.apache.shardingsphere.sql: TRACE

注意：
1.起初是因为使用4.0.0版本的sharding-jdbc 分表不支持部分sql语句，所以想换成5.2.1的版本，但是启动一直报错

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

一开始以为是application.yml文件配置出错，但是修改多次配置文件一直没有解决。
后面想到可能是springBoot数据源的问题，springboot数据库连接与sharding-jdbc有冲突
后续查找资料排除这两个类即可正常启动

spring:
  autoconfigure:
    exclude:
      - org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
      - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

三、分片算法类

这里分片算法简单写的。
因为数据表已经加了分片字段，接口传参默认会带过来，所以直接匹配到年份相同直接返回

import com.xsfx.common.tools.json.JacksonTool;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Collection;
import java.util.Properties;

/**
 * 数据存入规则的实现类
 * @author xsfx
 * @company xsfx
 * @title: HintShardingAlgorithmConfig
 * @projectName xsfx
 * @description: TODO
 * @date 2025/01/09
 */

public class TableShardingAlgorithmConfig implements StandardShardingAlgorithm<Integer> {

    static Logger log = LoggerFactory.getLogger(TableShardingAlgorithmConfig.class);
    @Override
    public String getType() {
        return StandardShardingAlgorithm.super.getType();
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
        for (String str : collection) {
            //dateTimeStr: 分片键sharding_column的值
            Integer dateTimeStr = preciseShardingValue.getValue();
            String yearStr = dateTimeStr.toString();
            if(str.contains(yearStr)){
                return str;
            }
        }
        return "xsfx_course_user_log";
    }

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {
        log.info("[分表算法doSharding] return Collection<String>");
        log.info("collection is " + JacksonTool.toJson(collection));
        log.info("preciseShardingValue is " + JacksonTool.toJson(rangeShardingValue));
        return null;
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void init(Properties properties) {

    }
}

四、一些SQL修改

自从用了sharding-jdbc,发现有部分SQL无法正常运行。

1.最主要的是有关分片表的sql都得加上分片键的参数

		<!--原来-->
		select sum(al.submit_count) from xsfx_course_user_log al
         <where>
             al.user_id = ul.user_id
             <if test="resourceType != null">
                 and al.resource_type = #{resourceType}
             </if>
             <if test="aId != null">
                 and al.a_id = #{aId}
             </if>
         </where>

         <!--现在-->
         select sum(al.submit_count) from xsfx_course_user_log al
         <where>
             al.user_id = ul.user_id
             <if test="resourceType != null">
                 and al.resource_type = #{resourceType}
             </if>
             <if test="aId != null">
                 and al.a_id = #{aId}
             </if>
             <if test="shardingColumn != null ">
                 and al.sharding_column = #{shardingColumn}
             </if>
         </where>

2.一些mybatis自带修改语句需要重写
例如：updateByPrimaryKeySelective
需要手动将分片加入
不然报错

Can not update sharding value for table `xsfx_course_user_log`.

3.一些子查询使用了聚合函数需要在父级一一列出来，不能在父级直接使用*了

	<!--原来-->
	select c.* from (
        SELECT
        @rank := @rank + 1 AS ranking,
        a.user_id,
        a.course_time
        FROM (
        SELECT
        r.user_id,
        SUM(r.course_time) AS course_time
        FROM
        xsfx_course_user_log r
        <where>
            <if test="templeId != null">
                and r.temple_id = #{templeId}
            </if>
            <if test="courseId != null">
                and r.course_id = #{courseId}
            </if>
            <if test="resourceType != null">
                and r.resource_type = #{resourceType}
            </if>
            <if test="aId != null">
                and r.a_id = #{aId}
            </if>
            <if test="userIds != null and userIds.size() > 0">
                and r.user_id in
                <foreach collection="userIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="startDate != null">
                and r.submit_date <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and r.submit_date <![CDATA[ < ]]> #{endDate}
            </if>
        </where>
        GROUP BY user_id
        ORDER BY course_time DESC
        ) AS a, (SELECT @rank := 0) r) c
        having c.user_id = #{userId}

	<!--修改后-->
	select c.ranking, c.user_id, c.course_time from (
        SELECT
        @rank := @rank + 1 AS ranking,
        a.user_id,
        a.course_time
        FROM (
        SELECT
        r.user_id,
        SUM(r.course_time) AS course_time
        FROM
        xsfx_course_user_log r
        <where>
            <if test="templeId != null">
                and r.temple_id = #{templeId}
            </if>
            <if test="courseId != null">
                and r.course_id = #{courseId}
            </if>
            <if test="resourceType != null">
                and r.resource_type = #{resourceType}
            </if>
            <if test="aId != null">
                and r.a_id = #{aId}
            </if>
            <if test="userIds != null and userIds.size() > 0">
                and r.user_id in
                <foreach collection="userIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

            <if test="startDate != null">
                and r.submit_date <![CDATA[ >= ]]> #{startDate}
            </if>
            <if test="endDate != null">
                and r.submit_date <![CDATA[ < ]]> #{endDate}
            </if>
        </where>
        GROUP BY user_id
        ORDER BY course_time DESC
        ) AS a, (SELECT @rank := 0) r) c
        having c.user_id = #{userId}