当前位置：首页 > news >正文

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

news 来源：原创 2024/9/21 8:28:09

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.原理

shardingsphere-jdbc_v3

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.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:sharding.ymljpa:properties:hibernate:dialect: org.hibernate.dialect.MySQL8Dialecthibernate:ddl-auto: create-drop

sharding.yml

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

dataSources:ds0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:13306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: testpassword: testds1:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.jdbc.DriverjdbcUrl: jdbc:mysql://localhost:13307/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8username: testpassword: test
rules:- !SHARDINGtables:order:actualDataNodes: ds${0..1}.orderdefaultDatabaseStrategy:standard:shardingColumn: order_idshardingAlgorithmName: database_inlinedefaultTableStrategy:none:shardingAlgorithms:database_inline:type: INLINEprops: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;}@Overridepublic 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);}@Overridepublic 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 {@Containerstatic MySQLContainer<?> mySQLContainer1 = new MySQLContainer<>("mysql:8.0.23").withDatabaseName("ds0").withUsername("test").withPassword("test");@Containerstatic 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"));}@Autowiredprivate OrderService orderService;@Autowiredprivate OrderRepository orderRepository;@DynamicPropertySourcestatic void setProperties(DynamicPropertyRegistry registry) {registry.add("spring.jpa.hibernate.ddl-auto", () -> "create-drop");}@Testvoid shouldFindOrderInCorrectShard() {// givenOrder 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");// whenOrder 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 ds1Assertions.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);}}
}

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