Database and Table Initialization

Create two separate databases for horizontal partitioning:

sql CREATE SCHEMA partition_ds_0 DEFAULT CHARACTER SET utf8 ; CREATE SCHEMA partition_ds_1 DEFAULT CHARACTER SET utf8 ;

Within each database, generate three identical order tables:

sql CREATE TABLE purchase_record_0 ( id bigint(20) NOT NULL, customer_id bigint(20) not null, merchant_id bigint(20) not null, product_name varchar(64) not NULL, amount decimal(10,2) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE purchase_record_1 ( id bigint(20) NOT NULL, customer_id bigint(20) not null, merchant_id bigint(20) not null, product_name varchar(64) not NULL, amount decimal(10,2) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE purchase_record_2 ( id bigint(20) NOT NULL, customer_id bigint(20) not null, merchant_id bigint(20) not null, product_name varchar(64) not NULL, amount decimal(10,2) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Maven Dependencies

xml

<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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> 3.1.5 <relativePath/> <groupId>com.tech.demo</groupId> <artifactId>sharding-sphere-demo</artifactId> 0.0.1-SNAPSHOT <java.version>17</java.version> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> true <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> test <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> 8.0.33 <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-spring-boot3-starter</artifactId> 3.5.7 <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> 5.2.1 <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> 5.8.25 <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId>

Entity Definitions

java import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Builder; import lombok.Data; import java.math.BigDecimal;

@Data @Builder @TableName("customer") public class Customer { @TableId(type = IdType.ASSIGN_ID) private Long id; private String fullName; private Boolean gender; private Integer age; }

@Data @Builder @TableName("merchant") public class Merchant { @TableId(type = IdType.ASSIGN_ID) private Long id; private String storeName; private Boolean gender; private Integer age; }

@Data @Builder @TableName("purchase_record") public class PurchaseRecord { @TableId(type = IdType.ASSIGN_ID) private Long id; private Long customerId; private Long merchantId; private String productName; private BigDecimal amount; }

Mapper Interfaces

java import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.apache.ibatis.annotations.Mapper;

@Mapper public interface CustomerMapper extends BaseMapper<Customer> {}

@Mapper public interface MerchantMapper extends BaseMapper<Merchant> {}

@Mapper public interface PurchaseRecordMapper extends BaseMapper<PurchaseRecord> {}

REST Controller

java import com.tech.demo.mapper.CustomerMapper; import com.tech.demo.mapper.MerchantMapper; import com.tech.demo.mapper.PurchaseRecordMapper; import com.tech.demo.entity.PurchaseRecord; import lombok.RequiredArgsConstructor; import org.springframework.web.bind.annotation.*;

import java.util.HashMap; import java.util.Map;

@RestController @RequiredArgsConstructor public class PurchaseController {

private final CustomerMapper customerMapper;
private final MerchantMapper merchantMapper;
private final PurchaseRecordMapper purchaseMapper;

@GetMapping("/purchases/{id}")
public Map<String, Object> fetchDetails(@PathVariable Long id) {
    Map<String, Object> resultMap = new HashMap<>();
    PurchaseRecord record = purchaseMapper.selectById(id);
    if (record != null) {
        resultMap.put("record", record);
        resultMap.put("customer", customerMapper.selectById(record.getCustomerId()));
        resultMap.put("merchant", merchantMapper.selectById(record.getMerchantId()));
    }
    return resultMap;
}

}

Distributed ID Generator

java import cn.hutool.core.lang.Snowflake; import cn.hutool.core.util.IdUtil; import cn.hutool.core.util.RandomUtil; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.SystemUtils; import org.springframework.boot.ApplicationArguments; import org.springframework.boot.ApplicationRunner; import org.springframework.stereotype.Component;

import java.net.Inet4Address; import java.net.InetAddress; import java.net.UnknownHostException;

@Slf4j @Component public class DistributedIdGenerator implements ApplicationRunner {

private long nodeId;
private long clusterId;
private Snowflake snowflake;

@Override
public void run(ApplicationArguments args) {
    initializeNodeId();
    initializeClusterId();
    createSnowflake(nodeId, clusterId);
}

private void initializeNodeId() {
    try {
        String hostAddress = Inet4Address.getLocalHost().getHostAddress();
        int[] codePoints = StringUtils.toCodePoints(hostAddress);
        int sum = 0;
        for (int point : codePoints) {
            sum += point;
        }
        nodeId = sum % 32;
    } catch (UnknownHostException e) {
        log.error("Failed to resolve IP for Node ID", e);
        nodeId = RandomUtil.randomLong(0, 31);
    }
}

private void initializeClusterId() {
    String hostName = resolveHostName();
    int[] codePoints = StringUtils.toCodePoints(hostName);
    int sum = 0;
    for (int point : codePoints) {
        sum += point;
    }
    clusterId = sum % 32;
}

private String resolveHostName() {
    String osName = System.getProperty("os.name");
    if (!StringUtils.startsWithIgnoreCase(osName, "mac")) {
        return SystemUtils.getHostName();
    }
    try {
        return InetAddress.getLocalHost().getHostName().toUpperCase();
    } catch (UnknownHostException e) {
        log.error("Failed to resolve Host Name", e);
        return "FALLBACK_HOST";
    }
}

private Snowflake createSnowflake(long workerId, long datacenterId) {
    try {
        this.snowflake = IdUtil.createSnowflake(workerId, datacenterId);
        if (this.snowflake == null) {
            throw new IllegalStateException("Snowflake instance initialization failed.");
        }
        return this.snowflake;
    } catch (Exception e) {
        log.error("Snowflake creation error: {}", e.getMessage());
        throw new RuntimeException("ID Generator initialization failure.", e);
    }
}

public synchronized long nextId() {
    return this.snowflake.nextId();
}

public synchronized long nextId(long workerId, long datacenterId) {
    return createSnowflake(workerId, datacenterId).nextId();
}

}

Unit Tests

java import cn.hutool.core.util.RandomUtil; import cn.hutool..JSONUtil; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.tech.demo.entity.PurchaseRecord; import com.tech.demo.mapper.PurchaseRecordMapper; import com.tech.demo.provider.DistributedIdGenerator; import lombok.extern.slf4j.Slf4j; import org.junit.Assert; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal; import java.math.RoundingMode; import java.util.ArrayList; import java.util.List;

@Slf4j public class ShardingIntegrationTest extends BaseTestContainer {

@Autowired
private DistributedIdGenerator idGenerator;

@Autowired
private PurchaseRecordMapper purchaseMapper;

@Test
public void insertBatchRecords() {
    List<PurchaseRecord> records = new ArrayList<>();
    for (int i = 1; i <= 10; i++) {
        PurchaseRecord record = PurchaseRecord.builder()
                .customerId(idGenerator.nextId())
                .merchantId(idGenerator.nextId())
                .productName("Product_" + RandomUtil.randomInt(6))
                .amount(RandomUtil.randomBigDecimal().setScale(2, RoundingMode.HALF_UP))
                .build();
        records.add(record);
    }
    purchaseMapper.insert(records);
}

@Test
public void fetchSingleRecord() {
    PurchaseRecord record = purchaseMapper.selectById(5L);
    Assert.assertNotNull(record);
    log.info("Query Result: {}", JSONUtil.toJsonStr(record));
}

@Test
public void modifyRecord() {
    PurchaseRecord record = purchaseMapper.selectById(3L);
    Assert.assertNotNull(record);
    record.setCustomerId(1L);
    record.setMerchantId(3L);
    int affectedRows = purchaseMapper.updateById(record);
    log.info("Rows affected: {}", affectedRows);
}

@Test
public void fetchPaginatedRecords() {
    Page<PurchaseRecord> pageConfig = new Page<>(1, 2);
    LambdaQueryWrapper<PurchaseRecord> wrapper = new LambdaQueryWrapper<>();
    Page<PurchaseRecord> resultPage = purchaseMapper.selectPage(pageConfig, wrapper);
    log.info("Pagination Result: {}", JSONUtil.toJsonStr(resultPage));
}

}

Application Configuration

yaml server: port: 8095

spring: application: name: sharding-sphere-demo shardingsphere: mode: type: Standalone repository: type: JDBC database: name: ds_0 datasource: names: ds_0,ds_1 ds_0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/partition_ds_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true username: root password: root ds_1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/partition_ds_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true username: root password: root rules: sharding: sharding-algorithms: ds-inline: type: INLINE props: algorithm-expression: ds_${id % 2} tbl-inline: type: INLINE props: algorithm-expression: purchase_record_${id % 3} tables: purchase_record: actual-data-nodes: ds_${0..1}.purchase_record_${0..2} database-strategy: standard: sharding-column: id sharding-algorithm-name: ds-inline table-strategy: standard: sharding-column: id sharding-algorithm-name: tbl-inline props: sql-show: true

mybatis-plus: type-aliases-package: com.tech.demo.entity configuration: map-underscore-to-camel-case: true log-impl: org.apache.ibatis.logging.stdout.StdOutImpl mapper-locations: - classpath:/mapper/*.xml

Compatibility Fix: SnakeYAML NoSuchMethodError

When utilizing shardingsphere-jdbc-core-spring-boot-starter version 5.2.1 with Spring Boot 3.x, a compatibility issue arises due to SnakeYAML version conflicts. ShardingSphere relies on SnakeYAML 1.33, while Spring Boot 3.x bundles SnakeYAML 2.2. Version 2.2 removed the no-argument constructors for Representer and SafeRepresenter, causing a NoSuchMethodError.

To resolve this without downgrading Spring Boot (which would introduce security vulnerabilities from older SnakeYAML versions), create local class shadow overrides within your project under the org.yaml.snakeyaml.representer package. This restores the required constructors.

org.yaml.snakeyaml.representer.Representer

java package org.yaml.snakeyaml.representer;

import org.yaml.snakeyaml.DumperOptions; import org.yaml.snakeyaml.TypeDescription; import org.yaml.snakeyaml.introspector.Property; import org.yaml.snakeyaml.introspector.PropertyUtils; import org.yaml.snakeyaml.nodes.*;

import java.util.*;

public class Representer extends SafeRepresenter { protected Map<Class<?>, TypeDescription> typeDefinitions = Collections.emptyMap();

public Representer() {
    this.representers.put(null, new RepresentJavaBean());
}

public Representer(DumperOptions options) {
    super(options);
    this.representers.put(null, new RepresentJavaBean());
}

public TypeDescription addTypeDescription(TypeDescription td) {
    if (Collections.EMPTY_MAP == this.typeDefinitions) {
        this.typeDefinitions = new HashMap<>();
    }
    if (td.getTag() != null) {
        this.addClassTag(td.getType(), td.getTag());
    }
    td.setPropertyUtils(this.getPropertyUtils());
    return this.typeDefinitions.put(td.getType(), td);
}

public void setPropertyUtils(PropertyUtils propertyUtils) {
    super.setPropertyUtils(propertyUtils);
    for (TypeDescription td : this.typeDefinitions.values()) {
        td.setPropertyUtils(propertyUtils);
    }
}

protected MappingNode representJavaBean(Set<Property> properties, Object javaBean) {
    List<NodeTuple> value = new ArrayList<>(properties.size());
    Tag customTag = this.classTags.get(javaBean.getClass());
    Tag tag = customTag != null ? customTag : new Tag(javaBean.getClass());
    MappingNode node = new MappingNode(tag, value, DumperOptions.FlowStyle.AUTO);
    this.representedObjects.put(javaBean, node);
    DumperOptions.FlowStyle bestStyle = DumperOptions.FlowStyle.FLOW;
    for (Property property : properties) {
        Object memberValue = property.get(javaBean);
        Tag customPropertyTag = memberValue == null ? null : this.classTags.get(memberValue.getClass());
        NodeTuple tuple = this.representJavaBeanProperty(javaBean, property, memberValue, customPropertyTag);
        if (tuple == null) continue;
        if (!((ScalarNode) tuple.getKeyNode()).isPlain()) bestStyle = DumperOptions.FlowStyle.BLOCK;
        Node nodeValue = tuple.getValueNode();
        if (!(nodeValue instanceof ScalarNode) || !((ScalarNode) nodeValue).isPlain()) bestStyle = DumperOptions.FlowStyle.BLOCK;
        value.add(tuple);
    }
    if (this.defaultFlowStyle != DumperOptions.FlowStyle.AUTO) {
        node.setFlowStyle(this.defaultFlowStyle);
    } else {
        node.setFlowStyle(bestStyle);
    }
    return node;
}

protected NodeTuple representJavaBeanProperty(Object javaBean, Property property, Object propertyValue, Tag customTag) {
    ScalarNode nodeKey = (ScalarNode) this.representData(property.getName());
    boolean hasAlias = this.representedObjects.containsKey(propertyValue);
    Node nodeValue = this.representData(propertyValue);
    if (propertyValue != null && !hasAlias) {
        NodeId nodeId = nodeValue.getNodeId();
        if (customTag == null) {
            if (nodeId == NodeId.scalar) {
                if (property.getType() != Enum.class && propertyValue instanceof Enum) {
                    nodeValue.setTag(Tag.STR);
                }
            } else {
                if (nodeId == NodeId.mapping && property.getType() == propertyValue.getClass() && !(propertyValue instanceof Map) && !nodeValue.getTag().equals(Tag.SET)) {
                    nodeValue.setTag(Tag.MAP);
                }
                this.checkGlobalTag(property, nodeValue, propertyValue);
            }
        }
    }
    return new NodeTuple(nodeKey, nodeValue);
}

protected void checkGlobalTag(Property property, Node node, Object object) {
    if (object.getClass().isArray() && object.getClass().getComponentType().isPrimitive()) return;
    Class<?>[] arguments = property.getActualTypeArguments();
    if (arguments != null) {
        if (node.getNodeId() == NodeId.sequence) {
            Class<?> t = arguments[0];
            SequenceNode snode = (SequenceNode) node;
            Iterable<Object> memberList = object.getClass().isArray() ? Arrays.asList((Object[]) object) : (Iterable<Object>) object;
            Iterator<Object> iter = memberList.iterator();
            if (iter.hasNext()) {
                for (Node childNode : snode.getValue()) {
                    Object member = iter.next();
                    if (member != null && t.equals(member.getClass()) && childNode.getNodeId() == NodeId.mapping) {
                        childNode.setTag(Tag.MAP);
                    }
                }
            }
        } else if (object instanceof Set) {
            Class<?> t = arguments[0];
            MappingNode mnode = (MappingNode) node;
            Iterator<NodeTuple> ite = mnode.getValue().iterator();
            Set<?> set = (Set<?>) object;
            for (Object member : set) {
                NodeTuple tuple = ite.next();
                Node keyNode = tuple.getKeyNode();
                if (t.equals(member.getClass()) && keyNode.getNodeId() == NodeId.mapping) keyNode.setTag(Tag.MAP);
            }
        } else if (object instanceof Map) {
            Class<?> t = arguments[0];
            Class<?> valueType = arguments[1];
            MappingNode mnode = (MappingNode) node;
            for (NodeTuple tuple : mnode.getValue()) {
                this.resetTag(t, tuple.getKeyNode());
                this.resetTag(valueType, tuple.getValueNode());
            }
        }
    }
}

private void resetTag(Class<?> type, Node node) {
    Tag tag = node.getTag();
    if (tag.matches(type)) {
        if (Enum.class.isAssignableFrom(type)) node.setTag(Tag.STR);
        else node.setTag(Tag.MAP);
    }
}

protected Set<Property> getProperties(Class<?> type) {
    return this.typeDefinitions.containsKey(type) ? this.typeDefinitions.get(type).getProperties() : this.getPropertyUtils().getProperties(type);
}

protected class RepresentJavaBean implements Represent {
    public Node representData(Object data) {
        return Representer.this.representJavaBean(Representer.this.getProperties(data.getClass()), data);
    }
}

}

org.yaml.snakeyaml.representer.SafeRepresenter

java package org.yaml.snakeyaml.representer;

import org.yaml.snakeyaml.DumperOptions; import org.yaml.snakeyaml.nodes.Node; import org.yaml.snakeyaml.nodes.Tag;

public class SafeRepresenter extends BaseRepresenter { protected Map<Class<?>, Tag> classTags; protected DumperOptions.NonPrintableStyle nonPrintableStyle;

public SafeRepresenter() {
    this(new DumperOptions());
}

public SafeRepresenter(DumperOptions options) {
    if (options == null) throw new NullPointerException("DumperOptions must be provided.");
    this.nullRepresenter = new RepresentNull();
    this.representers.put(String.class, new RepresentString());
    this.representers.put(Boolean.class, new RepresentBoolean());
    this.representers.put(Character.class, new RepresentString());
    this.classTags = new HashMap<>();
    this.nonPrintableStyle = options.getNonPrintableStyle();
    this.setDefaultScalarStyle(options.getDefaultScalarStyle());
    this.setDefaultFlowStyle(options.getDefaultFlowStyle());
}

protected Tag getTag(Class<?> clazz, Tag defaultTag) {
    return this.classTags.containsKey(clazz) ? this.classTags.get(clazz) : defaultTag;
}

public Tag addClassTag(Class<?> clazz, Tag tag) {
    if (tag == null) throw new NullPointerException("Tag must be provided.");
    return this.classTags.put(clazz, tag);
}

protected class RepresentNull implements Represent {
    public Node representData(Object data) {
        return SafeRepresenter.this.representScalar(Tag.NULL, "null");
    }
}

protected class RepresentString implements Represent {
    public Node representData(Object data) {
        return SafeRepresenter.this.representScalar(Tag.STR, data.toString());
    }
}

protected class RepresentBoolean implements Represent {
    public Node representData(Object data) {
        return SafeRepresenter.this.representScalar(Tag.BOOL, Boolean.TRUE.equals(data) ? "true" : "false");
    }
}

}