Sharding-JDBC 5.4.1+SpringBoot3.4.1+MySQL8.4.1 使用案例

最近在升级 SpringBoot 项目，原版本是 2.7.16，要升级到 3.4.0 ，JDK 版本要从 JDK8 升级 JDK21，原项目中使用了 Sharding-JDBC，版本 4.0.0-RC1，在升级 SpringBoot 版本到 3.4.0 之后，服务启动失败，因此选择升级 Sharding-JDBC，记录代码如下：

https://www.zuocode.com

环境

SpringBoot 3.4.1

Sharding-JDBC 5.4.1

MySQL 8.4.1

代码实现

下面通过代码实现，根据年份分表，2020 年之前数据一张表，之后每 2 年一张表。

准备测试数据 SQL

create table t_order
(
    id          int auto_increment
        primary key,
    order_id    varchar(36)                        null comment '订单ID',
    amount      decimal(18, 2)                     null comment '金额',
    order_year  int                                null comment '订单年份，用来作为分表字段',
    create_time datetime default CURRENT_TIMESTAMP null,
    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    is_del      bit                                null
)
    comment '逻辑表，该表没有数据，但是没有这张表，sharding-jdbc执行时会报错';

create table t_order_0
(
    id          int auto_increment
        primary key,
    order_id    varchar(36)                        null comment '订单ID',
    amount      decimal(18, 2)                     null comment '金额',
    order_year  int                                null comment '订单年份，用来作为分表字段',
    create_time datetime default CURRENT_TIMESTAMP null,
    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    is_del      bit                                null
)
    comment '2020年以前的订单数据';

create table t_order_2020
(
    id          int auto_increment
        primary key,
    order_id    varchar(36)                        null comment '订单ID',
    amount      decimal(18, 2)                     null comment '金额',
    order_year  int                                null comment '订单年份，用来作为分表字段',
    create_time datetime default CURRENT_TIMESTAMP null,
    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    is_del      bit                                null
)
    comment '2020、2021年的订单数据';

create table t_order_2022
(
    id          int auto_increment
        primary key,
    order_id    varchar(36)                        null comment '订单ID',
    amount      decimal(18, 2)                     null comment '金额',
    order_year  int                                null comment '订单年份，用来作为分表字段',
    create_time datetime default CURRENT_TIMESTAMP null,
    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    is_del      bit                                null
)
    comment '2021、2022年的订单数据';

create table t_order_2024
(
    id          int auto_increment
        primary key,
    order_id    varchar(36)                        null comment '订单ID',
    amount      decimal(18, 2)                     null comment '金额',
    order_year  int                                null comment '订单年份，用来作为分表字段',
    create_time datetime default CURRENT_TIMESTAMP null,
    update_time datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    is_del      bit                                null
)
    comment '2023、2024年的订单数据';

INSERT INTO db2025.t_order_2022 (order_id, amount, order_year) VALUES ('76cfe091-d87f-11ef-b84b-0242ac110002', 7777.13, 2022);
INSERT INTO db2025.t_order_2024 (order_id, amount, order_year) VALUES ('76d66bb8-d87f-11ef-b84b-0242ac110002', 3106.80, 2023);

搭建 SpringBoot 项目 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.4.1</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.wheelmouse</groupId>
	<artifactId>sharding-sphere-case</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>sharding-sphere-case</name>
	<description>sharding-sphere-case</description>
	<url/>
	<licenses>
		<license/>
	</licenses>
	<developers>
		<developer/>
	</developers>
	<scm>
		<connection/>
		<developerConnection/>
		<tag/>
		<url/>
	</scm>
	<properties>
		<java.version>21</java.version>
		<shardingsphere.version>5.4.1</shardingsphere.version>
		<mybatis-plus.version>3.5.9</mybatis-plus.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>shardingsphere-jdbc-core</artifactId>
			<version>${shardingsphere.version}</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>
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.3.1</version> <!-- 根据你的Java版本选择合适的版本 -->
		</dependency>
		<dependency>
			<groupId>org.glassfish.jaxb</groupId>
			<artifactId>jaxb-runtime</artifactId>
			<version>2.3.1</version> <!-- 根据你的Java版本选择合适的版本 -->
		</dependency>


		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- MyBatis-Plus -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.7</version>
		</dependency>
		<!-- Mybatis的分页插件 -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<!-- MyBatis-Plus -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-spring-boot3-starter</artifactId>
			<version>${mybatis-plus.version}</version>
		</dependency>
		<!-- 于 v3.5.9 起，PaginationInnerInterceptor 已分离出来。如需使用，则需单独引入 mybatis-plus-jsqlparser 依赖，jdk 11+ 引入可选模块 -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-jsqlparser</artifactId>
			<version>${mybatis-plus.version}</version>
		</dependency>
		<!-- Mybatis的分页插件， -->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<annotationProcessorPaths>
						<path>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</path>
					</annotationProcessorPaths>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.graalvm.buildtools</groupId>
				<artifactId>native-maven-plugin</artifactId>
			</plugin>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

Mapper

/**
* @author 重楼
* @description 针对表【t_order】的数据库操作Mapper
* @createDate 2025-01-23 12:55:01
* @Entity generator.domain.Order0
*/
public interface OrderMapper extends BaseMapper<Order> {

}

Mapper.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.wheelmouse.shardingsphere.mapper.OrderMapper">

    <resultMap id="BaseResultMap" type="com.wheelmouse.shardingsphere.domain.Order">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="orderId" column="order_id" jdbcType="VARCHAR"/>
            <result property="amount" column="amount" jdbcType="DECIMAL"/>
            <result property="orderYear" column="order_year" jdbcType="INTEGER"/>
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
            <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
            <result property="isDel" column="is_del" jdbcType="BIT"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,order_id,amount,
        order_year,create_time,update_time,
        is_del
    </sql>
</mapper>

Service

/**
* @author 重楼
* @description 针对表【t_order】的数据库操作Service
* @createDate 2025-01-23 12:55:01
*/
public interface OrderService extends IService<Order> {

}

ServiceImpl

/**
* @author 重楼
* @description 针对表【t_order】的数据库操作Service实现
* @createDate 2025-01-23 12:55:01
*/
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order>
    implements OrderService {

}

以下是Sharding-jdbc的配置类，Sharding-jdbc支持yaml和java 2中方式配置，这里采用java方式配置 ShardingConfig

/**
 * @author 重楼
 * @date 2025/1/14
 * @apiNote
 */
@Configuration
public class ShardingConfig {

    @Bean
    public DataSource dataSource() throws SQLException {
        // 配置数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", createDataSource("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://localhost:3306/db2025?serverTimezone=UTC&useSSL=false", // MySQL URL
                "root", // 用户名
                "123456" // 密码
        ));

        // 这里的案例是单库，所以没有做读个数据源配置
        //dataSourceMap.put("ds1", createDataSource("com.mysql.cj.jdbc.Driver",
        //        "jdbc:mysql://127.0.0.1:3306/db2025?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true", // MySQL URL
        //        "root", // 用户名
        //        "123456" // 密码
        //));

        // 配置 Sharding-JDBC 的分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTables().add(getOrderTableRuleConfiguration());

        // 注册自定义分片算法
        // algorithmName 由用户指定，需要和分片策略中的分片算法一致
        // type 和 props，请参考分片内置算法：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
        Properties shardingAlgorithmProps = new Properties();
        shardingAlgorithmProps.setProperty("strategy", "COMPLEX"); // 指定算法类型
        shardingAlgorithmProps.setProperty("algorithmClassName", MyComplexKeysShardingAlgorithm.class.getName());
        shardingRuleConfig.getShardingAlgorithms().put("my-complex-keys-sharding-algorithm",
                new AlgorithmConfiguration("CLASS_BASED", shardingAlgorithmProps));

        // 创建 ShardingSphere 数据源
        Properties properties = new Properties();
        properties.put("sql-show", true);
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,
                Collections.singleton(shardingRuleConfig), properties);
    }


    private ShardingTableRuleConfiguration getOrderTableRuleConfiguration() {
        // 配置表规则
        ShardingTableRuleConfiguration tableRuleConfig = new ShardingTableRuleConfiguration(
                "t_order", // 逻辑表名
                "ds0.t_order_${[" + orderActualDataNodes() + "]}" // 实际数据节点
        );

        // 配置复合分片策略
        tableRuleConfig.setTableShardingStrategy(new ComplexShardingStrategyConfiguration(
                "order_year", // 分片键
                "my-complex-keys-sharding-algorithm" // 自定义分片算法名称
        ));

        return tableRuleConfig;
    }

    /**
     * 自己实现，这里演示所以写死表名
     * @return
     */
    private String orderActualDataNodes(){
        return "0,2020,2022,2024";
    }

    private DataSource createDataSource(final String driverClassName,final String url, final String username, final String password) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setJdbcUrl(url); // MySQL 连接 URL
        dataSource.setUsername(username); // 数据库用户名
        dataSource.setPassword(password); // 数据库密码
        dataSource.setMaximumPoolSize(10); // 连接池最大连接数
        dataSource.setMinimumIdle(2); // 连接池最小空闲连接数
        dataSource.setIdleTimeout(30000); // 空闲连接超时时间
        dataSource.setMaxLifetime(1800000); // 连接最大存活时间
        dataSource.setConnectionTimeout(30000); // 连接超时时间
        return dataSource;
    }

}

自定义分片算法实现类

/**
 * @author 重楼
 * @date 2025/1/10
 * @apiNote
 */
@Component
public class MyComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {

    private static final Logger LOGGER = LoggerFactory
            .getLogger(MyComplexKeysShardingAlgorithm.class);

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<String> shardingValue) {
        Map<String, Collection<String>> columnNameAndShardingValuesMap = shardingValue
                .getColumnNameAndShardingValuesMap();

        // 获取 列名-值-方式1，范围查询
        Map<String, Range<String>> columnNameAndRangeValuesMap = shardingValue
                .getColumnNameAndRangeValuesMap();
        boolean hasDateYear = columnNameAndRangeValuesMap.containsKey("order_year");
        if (!hasDateYear) {
            return Lists.newArrayList();
        }

        Range<String> orderYear = columnNameAndRangeValuesMap.get("order_year");
        String orderIdUpperValue = orderYear.upperEndpoint();// 上限
        String orderIdLowerValue = orderYear.lowerEndpoint();// 下限

        String tablePrefix = "t_order";

        // 根据年份拼接物理表表名
        List<String> actualTableList = Lists.newArrayList();

        for (int dateYear = Integer.parseInt(orderIdLowerValue); dateYear <= Integer.parseInt(orderIdUpperValue); dateYear++) {
            // 如果交易年份小于2020则使用t_order_0，如果交易时间大于当前年则忽略
            //按照不同时间类型分的表  两年一张，小于2020年以前的数据数据都放在_0结尾的表中
            if (dateYear < 2020) {
                actualTableList.add(tablePrefix  + "_0");
                continue;
            }
            if (dateYear > Year.now().getValue()) {
                continue;
            }
            // 计算年份落在哪个时间分片键上
            int yearSharding = dateYear - (dateYear % 2);
            actualTableList.add(tablePrefix + "_" + yearSharding);
        }

        actualTableList = actualTableList.stream().distinct().collect(Collectors.toList());
        LOGGER.info("actual table name is :{}", actualTableList);
        return actualTableList;

}

通过SPI的方式注册该算法，在 resources 文件夹下创建文件夹 META-INF/services。在该文件夹下创建文件 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm，文件内写上我们自定义的分片算法。

com.wheelmouse.shardingsphere.config.MyComplexKeysShardingAlgorithm

测试类

@SpringBootTest
class ShardingSphereCaseApplicationTests {

	private final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

	@Autowired
	private OrderMapper orderMapper;

	@Test
	void list() {
		QueryWrapper<Order> queryWrapper = new QueryWrapper<>();
		queryWrapper.between("order_year", "2022", "2024");
		List<Order> list = orderMapper.selectList(queryWrapper);
		for (Order order : list) {
			System.out.println(order);
		}
	}

}

结果：

作者：重楼七叶一枝花

链接：https://juejin.cn

+友情链接