原创

Spring Boot集成sharding-jdbc快速入门Demo

1.什么是sharding-jdbc？

目前新版已经更名为ShardingSphere-JDBC ，ShardingSphere-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

2.原理

Apache ShardingSphere-JDBC 可以通过 Java 和 YAML 这 2 种方式进行配置，开发者可根据场景选择适合的配置方式。

3.代码工程

实验目的：实现分库分表规则

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.5</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharding-jdbc</artifactId>
    <version>0.1-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <shardingsphere.version>5.3.2</shardingsphere.version>
        <mysql.version>8.2.0</mysql.version>

    </properties>
    <dependencies>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>mysql</artifactId>
            <scope>test</scope>
        </dependency>


    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.testcontainers</groupId>
                <artifactId>testcontainers-bom</artifactId>
                <version>1.18.3</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
</project>

service

package com.et.sharding.jdbc;

import org.springframework.stereotype.Service;

@Service
public class OrderService {

    private final OrderRepository orderRepository;

    public OrderService(OrderRepository orderRepository) {
        this.orderRepository = orderRepository;
    }

    public Order createOrder(Order order) {
        return orderRepository.save(order);
    }

    public Order getOrder(Long id) {
        return orderRepository.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("Order not found"));
    }
}

reponsitory

继承最基本的crud操作类

package com.et.sharding.jdbc;

import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<Order, Long> { }

application.yml

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding.yml
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
    hibernate:
      ddl-auto: create-drop

sharding.yml

这里采用yml方式配置分库分表规则

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:13306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: test
    password: test
  ds1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:13307/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: test
    password: test
rules:
  - !SHARDING
    tables:
      order:
        actualDataNodes: ds${0..1}.order
    defaultDatabaseStrategy:
      standard:
        shardingColumn: order_id
        shardingAlgorithmName: database_inline
    defaultTableStrategy:
      none:
    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds${order_id % 2}
props:
  sql-show: false

entity

配置字段名，jpa会根据规则

 registry.add("spring.jpa.hibernate.ddl-auto", () -> "create-drop");

自动生成数据表

package com.et.sharding.jdbc;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.Objects;


@Entity
@Table(name = "`order`")
public class Order {

    @Id
    @Column(name = "order_id")
    private Long orderId;

    @Column(name = "customer_id")
    private Long customerId;

    @Column(name = "total_price")
    private BigDecimal totalPrice;

    @Enumerated(EnumType.STRING)
    @Column(name = "order_status")
    private Status orderStatus;

    @Column(name = "order_date")
    private LocalDate orderDate;

    @Column(name = "delivery_address")
    private String deliveryAddress;

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public Long getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    public BigDecimal getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(BigDecimal totalPrice) {
        this.totalPrice = totalPrice;
    }

    public Status getOrderStatus() {
        return orderStatus;
    }

    public void setOrderStatus(Status orderStatus) {
        this.orderStatus = orderStatus;
    }

    public LocalDate getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(LocalDate orderDate) {
        this.orderDate = orderDate;
    }

    public String getDeliveryAddress() {
        return deliveryAddress;
    }

    public void setDeliveryAddress(String deliveryAddress) {
        this.deliveryAddress = deliveryAddress;
    }

    protected Order() {}

    public Order(Long orderId, Long customerId, BigDecimal totalPrice, Status orderStatus, LocalDate orderDate, String deliveryAddress) {
        this.orderId = orderId;
        this.customerId = customerId;
        this.totalPrice = totalPrice;
        this.orderStatus = orderStatus;
        this.orderDate = orderDate;
        this.deliveryAddress = deliveryAddress;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Order order = (Order) o;
        return Objects.equals(orderId, order.orderId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(orderId);
    }
}

启动类

package com.et.sharding.jdbc;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Main {

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

https://github.com/Harries/springboot-demo

4.测试

编写测试类

package com.et;

import com.et.sharding.jdbc.*;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.testcontainers.containers.MySQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.List;


/**
 * This Manual test requires: Docker service running.
 */
@Testcontainers
@SpringBootTest(classes = Main.class)
class OrderServiceManualTest {

    @Container
    static MySQLContainer<?> mySQLContainer1 = new MySQLContainer<>("mysql:8.0.23")
            .withDatabaseName("ds0")
            .withUsername("test")
            .withPassword("test");

    @Container
    static MySQLContainer<?> mySQLContainer2 = new MySQLContainer<>("mysql:8.0.23")
            .withDatabaseName("ds1")
            .withUsername("test")
            .withPassword("test");

    static {
        mySQLContainer2.setPortBindings(List.of("13307:3306"));
        mySQLContainer1.setPortBindings(List.of("13306:3306"));
    }
    @Autowired
    private OrderService orderService;

    @Autowired
    private OrderRepository orderRepository;

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.jpa.hibernate.ddl-auto", () -> "create-drop");
    }

    @Test
    void shouldFindOrderInCorrectShard() {
        // given
        Order order1 = new Order(1L, 1L,  BigDecimal.TEN, Status.PROCESSING, LocalDate.now(), "123 Main St");
        Order order2 = new Order(2L, 2L, BigDecimal.valueOf(12.5), Status.SHIPPED, LocalDate.now(), "456 Main St");

        // when
        Order savedOrder1 = orderService.createOrder(order1);
        Order savedOrder2 = orderService.createOrder(order2);

        // then
        // Assuming the sharding strategy is based on the order id, data for order1 should be present only in ds0
        // and data for order2 should be present only in ds1
        Assertions.assertThat(orderService.getOrder(savedOrder1.getOrderId())).isEqualTo(savedOrder1);
        Assertions.assertThat(orderService.getOrder(savedOrder2.getOrderId())).isEqualTo(savedOrder2);

        // Verify that the orders are not present in the wrong shards.
        // You would need to implement these methods in your OrderService.
        // They should use a JdbcTemplate or EntityManager to execute SQL directly against each shard.
        Assertions.assertThat(assertOrderInShard(savedOrder1, mySQLContainer2)).isTrue();
        Assertions.assertThat(assertOrderInShard(savedOrder2, mySQLContainer1)).isTrue();
    }

    private boolean assertOrderInShard(Order order, MySQLContainer<?> container) {
        try (Connection conn = DriverManager.getConnection(container.getJdbcUrl(), container.getUsername(), container.getPassword())) {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM `order` WHERE order_id = ?");
            stmt.setLong(1, order.getOrderId());
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        } catch (SQLException ex) {
            throw new RuntimeException("Error querying order in shard", ex);
        }
    }
}

执行单元测试，单元测试通过，说明分库分表规则符合预期