Spring Boot integration sharding-jdbc quickstart demo

1. What is sharding-jdbc?

At present, the new version has been renamed ShardingSphere-JDBC, which is positioned as a lightweight Java framework and provides additional services in the JDBC layer of Java. It uses the client to directly connect to the database and provides services in the form of jar packages, without additional deployment and dependencies, which can be understood as an enhanced version of the JDBC driver, which is fully compatible with JDBC and various ORM frameworks.

• Works with any JDBC-based ORM framework, such as: JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC;
• Support any third-party database connection pool, such as: DBCP, C3P0, BoneCP, HikariCP, etc.
• Supports any database that implements the JDBC specification, currently MySQL, PostgreSQL, Oracle, SQLServer, and any database that can be accessed using JDBC.

2. Principle

Apache ShardingSphere-JDBC can be passed Java and YAML These two ways can be configured, and developers can choose the appropriate configuration method according to the scenario.

3. Code engineering

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

Inherit the most basic crud operation class

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

In this example, you can configure database and table sharding rules in 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

Configure the field name, and JPA will follow the rules

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

Automatically generate data tables

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);
    }
}

Startup class

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);
    }
}

The above are just some of the key codes, all of which can be found in the repositories below

Code repositories

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

4. Testing

Write a test class

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);
        }
    }
}

If the unit test is executed and the unit test passes, the database and table sharding rules meet expectations

5. References

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