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;
@Servicepublic 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: testrules:  - !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;
@SpringBootApplicationpublic 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);        }    }}

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

5.引用

https://shardingsphere.apache.org/document/current/cn/test-manual/
http://www.liuhaihua.cn/archives/710577.html

ITPub博客

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