SpringBoot整合ShardingJdbc实现数据库水平分表实战

(1)添加Maven依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>gdut.ff</groupId>
    <artifactId>ShardingJdbcTest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.12.RELEASE</version>
    </parent>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>

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

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

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.5</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>
</project>

(2)SpringBoot配置文件application.properties

server.port=8083
spring.application.name=shardingJdbc

mybatis.mapper-locations=classpath:mapper/*.xml
#mybatis.type-handlers-package=gdut.handler

logging.level.root=DEBUG

spring.shardingsphere.datasource.names=ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://xx.xx.xx.xx:31090/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
spring.shardingsphere.sharding.tables.time_year_month.actual-data-nodes=ds0.time_year_month_$->{2023..2024}_$->{1..2}
spring.shardingsphere.sharding.tables.time_year_month.table-strategy.standard.sharding-column=create_date
spring.shardingsphere.sharding.tables.time_year_month.table-strategy.standard.precise-algorithm-class-name=gdut.algorithm.CreateDatePreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.time_year_month.key-generator.column=id
spring.shardingsphere.sharding.tables.time_year_month.key-generator.type=SNOWFLAKE
spring.shardingsphere.props.sql.show=true

根据配置的分表策略，逻辑表是time_year_month，根据create_date字段分表，分表名称是time_year_month_2023_1、time_year_month_2023_2、time_year_month_2024_1、time_year_month_2024_2，ShardingJdbc不会自动创建表，需要我们自己去创建。分表算法是自定义的CreateDatePreciseShardingAlgorithm。这里主键自动生成，用的是雪花算法。

(3)创建Entity、Mapper、Controller类

package gdut.entity;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.time.LocalDateTime;

@Data
public class TimeYearMonth implements Serializable {

    private long id;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createDate;

}

package gdut.mapper;

import gdut.entity.TimeYearMonth;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TimeYearMonthMapper {

    void insertTime(TimeYearMonth timeYearMonth);

    List<TimeYearMonth> selectAll();

}

<?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="gdut.mapper.TimeYearMonthMapper">
    <resultMap id="BaseResultMap" type="gdut.entity.TimeYearMonth">
        <result column="id" jdbcType="BIGINT" property="id" />
        <result column="create_date" jdbcType="TIMESTAMP" property="createDate" javaType="java.time.LocalDateTime"/>
    </resultMap>

    <insert id="insertTime" parameterType="gdut.entity.TimeYearMonth">
        insert into time_year_month(create_date) values(#{createDate})
    </insert>

    <select id="selectAll" resultMap="BaseResultMap">
        select * from time_year_month
    </select>
    
</mapper>

package gdut.controller;

import gdut.entity.TimeYearMonth;
import gdut.mapper.TimeYearMonthMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

@RestController
public class TimeController {

    @Autowired
    private TimeYearMonthMapper timeYearMonthMapper;

    @PostMapping("/insertTime")
    public String insertTime(@RequestParam("createDate")String createDate) {
        TimeYearMonth timeYearMonth = new TimeYearMonth();
        timeYearMonth.setCreateDate(LocalDateTime.parse(createDate, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        timeYearMonthMapper.insertTime(timeYearMonth);
        return "success";
    }

    @PostMapping("/selectAll")
    public List<TimeYearMonth> selectAll() {
        return timeYearMonthMapper.selectAll();
    }
}

(4)分表算法CreateDatePreciseShardingAlgorithm

package gdut.algorithm;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.time.LocalDateTime;
import java.util.Collection;

public class CreateDatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<LocalDateTime> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<LocalDateTime> preciseShardingValue) {
        LocalDateTime value = preciseShardingValue.getValue();
        int year = value.getYear();
        int month = value.getMonthValue();
        String logicTableName = preciseShardingValue.getLogicTableName();
        return logicTableName + "_" + year + "_" + month;
    }
}

(5)启动类

package gdut;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication(proxyBeanMethods = false)
@MapperScan("gdut")
@ComponentScan("gdut")
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }
}

(6)LocalDateTime类型转化异常
参考:shardingJdbc的LocalDateTime问题

(7)测试
http://localhost:8083/selectAll

http://localhost:8083/insertTime?createDate=2024-01-23 10:00:00

总结：ShardingJdbc客户端分表还是挺容易实现的，比起Mycat需要搭建一个服务端和稍微麻烦的配置，我觉得ShardingJdbc会更容易上手一些。

参考

mybatis注入Date日期值为null的解决方法
springboot整合sharding-jdbc实现按年分库按月分表
使用ShardingJDBC实现按时间维度分表轻松支撑千万级数据