sharding-jdbc-demo/
├── pom.xml
├── src/main/java/com/shardingdatabasetable/shardingsphere/
│   ├── ShardingJdbcApplication.java
│   ├── entity/
│   │   └── Order.java
│   ├── mapper/
│   │   ├── OrderMapper.java
│   │   └── xml/
│   │       └── OrderMapper.xml
│   ├── service/
│   │   ├── OrderService.java
│   │   └── impl/
│   │       └── OrderServiceImpl.java
│   └── controller/
│       └── OrderController.java
├── src/main/resources/
│   ├── application.yml
│   └── mapper/
│       └── OrderMapper.xml  (如果放在這里，需要配置路徑)
└── src/test/java/com/shardingdatabasetable/shardingsphere/
    └── ShardingJdbcTest.java

<?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">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.10</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>sharding-jdbc-demo</artifactId>
    <version>1.0.0</version>
    <name>sharding-jdbc-demo</name>
    <description>ShardingSphere-JDBC 4.1.1 分庫(kù)分表示例</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <sharding-sphere.version>4.1.1</sharding-sphere.version>
        <druid.version>1.2.8</druid.version>
        <mybatis-plus.version>3.4.2</mybatis-plus.version>
        <mysql.version>8.0.33</mysql.version>
        <druid.version>1.1.18</druid.version>
    </properties>
    <dependencies>
        <!-- Spring Boot Web Starter -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- Spring Boot Test Starter -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- ShardingSphere-JDBC Spring Boot Starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <!-- MyBatis-Plus Spring Boot Starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <!-- Druid 連接池 Spring Boot Starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!-- MySQL 驅(qū)動(dòng) (適配 MySQL 8.0.33) -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
            <scope>runtime</scope>
        </dependency>
        <!-- Lombok 簡(jiǎn)化代碼 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- Groovy 用于分片算法表達(dá)式 -->
        <dependency>
            <groupId>org.codehaus.groovy</groupId>
            <artifactId>groovy</artifactId>
            <version>2.4.5</version>
        </dependency>
        <!-- JUnit 4 (Spring Boot 2.5.x 默認(rèn)兼容) -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
        <resources>
            <!-- 確保 resources 目錄下的文件都被打包 -->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
            <!-- 確保 java 目錄下的 xml 文件也被打包（如果放在這里） -->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

-- 創(chuàng)建兩個(gè)分庫(kù)，使用 MySQL 8.0 的字符集和排序規(guī)則
CREATE DATABASE IF NOT EXISTS `ds0` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_0900_ai_ci;
CREATE DATABASE IF NOT EXISTS `ds1` 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_0900_ai_ci;
-- 創(chuàng)建用戶并授權(quán)（如果需要）
-- CREATE USER 'fund_center'@'%' IDENTIFIED BY '123456';
-- GRANT ALL PRIVILEGES ON ds0.* TO 'fund_center'@'%';
-- GRANT ALL PRIVILEGES ON ds1.* TO 'fund_center'@'%';
-- FLUSH PRIVILEGES;

-- 在 ds0 庫(kù)中執(zhí)行
USE ds0;
-- 創(chuàng)建 t_order_0
CREATE TABLE IF NOT EXISTS `t_order_0` (
    `order_id` BIGINT NOT NULL COMMENT '訂單ID，主鍵（雪花算法生成）',
    `user_id` BIGINT NOT NULL COMMENT '用戶ID（分片鍵）',
    `order_no` VARCHAR(32) NOT NULL COMMENT '訂單編號(hào)',
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài)：0-待支付，1-已支付，2-已取消',
    `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創(chuàng)建時(shí)間（毫秒精度）',
    PRIMARY KEY (`order_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='訂單表_0';
-- 創(chuàng)建 t_order_1
CREATE TABLE IF NOT EXISTS `t_order_1` (
    `order_id` BIGINT NOT NULL COMMENT '訂單ID，主鍵（雪花算法生成）',
    `user_id` BIGINT NOT NULL COMMENT '用戶ID（分片鍵）',
    `order_no` VARCHAR(32) NOT NULL COMMENT '訂單編號(hào)',
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài)：0-待支付，1-已支付，2-已取消',
    `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創(chuàng)建時(shí)間（毫秒精度）',
    PRIMARY KEY (`order_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='訂單表_1';

-- 在 ds1 庫(kù)中執(zhí)行
USE ds1;
-- 創(chuàng)建 t_order_0
CREATE TABLE IF NOT EXISTS `t_order_0` (
    `order_id` BIGINT NOT NULL COMMENT '訂單ID，主鍵（雪花算法生成）',
    `user_id` BIGINT NOT NULL COMMENT '用戶ID（分片鍵）',
    `order_no` VARCHAR(32) NOT NULL COMMENT '訂單編號(hào)',
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài)：0-待支付，1-已支付，2-已取消',
    `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創(chuàng)建時(shí)間（毫秒精度）',
    PRIMARY KEY (`order_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='訂單表_0';
-- 創(chuàng)建 t_order_1
CREATE TABLE IF NOT EXISTS `t_order_1` (
    `order_id` BIGINT NOT NULL COMMENT '訂單ID，主鍵（雪花算法生成）',
    `user_id` BIGINT NOT NULL COMMENT '用戶ID（分片鍵）',
    `order_no` VARCHAR(32) NOT NULL COMMENT '訂單編號(hào)',
    `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額',
    `status` TINYINT NOT NULL DEFAULT 0 COMMENT '訂單狀態(tài)：0-待支付，1-已支付，2-已取消',
    `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創(chuàng)建時(shí)間（毫秒精度）',
    PRIMARY KEY (`order_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='訂單表_1';

server:
  port: 8006
  servlet:
    context-path: /
spring:
  application:
    name: sharding-jdbc-demo
  # 關(guān)閉所有數(shù)據(jù)源相關(guān)的自動(dòng)配置
  autoconfigure:
    exclude:
      - org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
      - org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration
      - org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration
      - com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  main:
    allow-bean-definition-overriding: true
  # ShardingSphere 配置
  shardingsphere:
    datasource:
      # 數(shù)據(jù)源名稱列表 - 新增主庫(kù)ds2，原來(lái)的ds0和ds1變?yōu)閺膸?kù)
      names: ds2, ds0, ds1
      # 數(shù)據(jù)源 ds2 (主庫(kù))
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.16.66.88:3306/ds2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&nullCatalogMeansCurrent=true
        username: center
        password: 123456
        # Druid連接池配置
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat,wall
      # 數(shù)據(jù)源 ds0 (從庫(kù)1)
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.16.66.88:3306/ds0?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&nullCatalogMeansCurrent=true
        username: center
        password: 123456
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        validation-query: SELECT 1
      # 數(shù)據(jù)源 ds1 (從庫(kù)2)
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.16.66.88:3306/ds1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&nullCatalogMeansCurrent=true
        username: center
        password: 123456
        initial-size: 5
        min-idle: 5
        max-active: 20
        max-wait: 60000
        validation-query: SELECT 1
    # 分片規(guī)則配置
    sharding:
      # 主從規(guī)則配置 - 新增部分 [citation:1][citation:3]
      master-slave-rules:
        # 主從邏輯名稱，可以自定義
        ms_ds:
          # 指定主庫(kù)數(shù)據(jù)源名稱
          master-data-source-name: ds2
          # 指定從庫(kù)數(shù)據(jù)源名稱列表，多個(gè)用逗號(hào)分隔 [citation:3]
          slave-data-source-names: ds0, ds1
          # 從庫(kù)負(fù)載均衡算法：round_robin(輪詢) / random(隨機(jī)) [citation:3]
          load-balance-algorithm-type: round_robin
      # 默認(rèn)的分庫(kù)策略：按user_id取模
      default-database-strategy:
        inline:
          sharding-column: user_id
          # 注意：這里用的是主從邏輯名 ms_ds，而不是具體的數(shù)據(jù)庫(kù)名
          algorithm-expression: ms_ds
      # 綁定表
      binding-tables:
        - t_order
      # 具體的表分片規(guī)則
      tables:
        # 邏輯表名
        t_order:
          # 實(shí)際數(shù)據(jù)節(jié)點(diǎn)：使用主從邏輯名，后面跟實(shí)際的物理表 [citation:6]
          actual-data-nodes: ms_ds.t_order_$->{0..1}
          # 分表策略：按order_id取模
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_$->{order_id % 2}
          # 分布式主鍵生成策略（雪花算法）
          key-generator:
            column: order_id
            type: SNOWFLAKE
            props:
              worker.id: 1
      # 默認(rèn)數(shù)據(jù)源（可選）
      default-data-source-name: ms_ds
    # 屬性配置
    props:
      sql:
        show: true  # 打印SQL日志，便于調(diào)試
      executor.size: 10
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
    cache-enabled: false
  global-config:
    db-config:
      id-type: none
# 日志級(jí)別配置
logging:
  level:
    com.shardingdatabasetable.shardingsphere.mapper: debug
    org.apache.shardingsphere: info
    com.alibaba.druid: info
  pattern:
    console: '%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n'

package com.shardingdatabasetable.shardingsphere;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class,
        HibernateJpaAutoConfiguration.class,
        com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure.class
})
@ComponentScan("com.shardingdatabasetable.shardingsphere")
@EnableTransactionManagement
public class ShardingJdbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }
}

package com.shardingdatabasetable.shardingsphere.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;
/**
 * 訂單實(shí)體類(lèi)
 * 邏輯表名：t_order，ShardingSphere會(huì)自動(dòng)路由到物理表
 */
@Data
@TableName("t_order")
public class Order {
    /**
     * 訂單ID，使用ShardingSphere的雪花算法自動(dòng)生成
     * 注意：不需要@TableId注解，讓ShardingSphere處理主鍵
     */
    private Long orderId;
    /**
     * 用戶ID（分庫(kù)分表鍵）
     */
    private Long userId;
    /**
     * 訂單編號(hào)
     */
    private String orderNo;
    /**
     * 訂單金額
     */
    private BigDecimal amount;
    /**
     * 訂單狀態(tài)：0-待支付，1-已支付，2-已取消
     */
    private Integer status;
    /**
     * 創(chuàng)建時(shí)間
     */
    //private LocalDateTime createTime;
    private Date createTime;
}

package com.shardingdatabasetable.shardingsphere.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shardingdatabasetable.shardingsphere.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
 * 訂單Mapper接口
 * 繼承BaseMapper獲得基礎(chǔ)的CRUD方法
 */
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    /**
     * 自定義查詢：根據(jù)用戶ID和狀態(tài)查詢訂單
     * 使用XML文件實(shí)現(xiàn)
     */
    List<Order> selectByUserIdAndStatus(@Param("userId") Long userId, @Param("status") Integer status);
    /**
     * 批量插入訂單
     * 使用XML文件實(shí)現(xiàn)
     */
    int batchInsert(@Param("list") List<Order> orders);
    /**
     * 統(tǒng)計(jì)某個(gè)用戶的訂單總數(shù)
     * 使用注解方式實(shí)現(xiàn)
     */
    @Select("SELECT COUNT(*) FROM t_order WHERE user_id = #{userId}")
    int countByUserId(@Param("userId") Long userId);
    /**
     * 根據(jù)用戶ID列表查詢訂單（演示多鍵查詢）
     * 使用XML文件實(shí)現(xiàn)
     */
    List<Order> selectByUserIds(@Param("userIds") List<Long> userIds);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shardingdatabasetable.shardingsphere.mapper.OrderMapper">
    <!-- 通用結(jié)果映射 -->
    <resultMap id="BaseResultMap" type="com.shardingdatabasetable.shardingsphere.entity.Order">
        <id column="order_id" property="orderId" jdbcType="BIGINT"/>
        <result column="user_id" property="userId" jdbcType="BIGINT"/>
        <result column="order_no" property="orderNo" jdbcType="VARCHAR"/>
        <result column="amount" property="amount" jdbcType="DECIMAL"/>
        <result column="status" property="status" jdbcType="TINYINT"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>
    <!-- 通用查詢列 -->
    <sql id="Base_Column_List">
        order_id, user_id, order_no, amount, status, create_time
    </sql>
    <!-- 批量插入 -->
    <insert id="batchInsert" parameterType="list">
        INSERT INTO t_order (
        order_id, user_id, order_no, amount, status, create_time
        ) VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.orderId},
            #{item.userId},
            #{item.orderNo},
            #{item.amount},
            #{item.status},
            #{item.createTime}
            )
        </foreach>
    </insert>
    <!-- 根據(jù)用戶ID和狀態(tài)查詢 -->
    <select id="selectByUserIdAndStatus" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_order
        WHERE user_id = #{userId}
        <if test="status != null">
            AND status = #{status}
        </if>
        ORDER BY create_time DESC
    </select>
    <!-- 根據(jù)用戶ID列表查詢 -->
    <select id="selectByUserIds" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM t_order
        WHERE user_id IN
        <foreach collection="userIds" item="userId" open="(" separator="," close=")">
            #{userId}
        </foreach>
        ORDER BY user_id, create_time DESC
    </select>
</mapper>

package com.shardingdatabasetable.shardingsphere.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.shardingdatabasetable.shardingsphere.entity.Order;
import java.util.List;
/**
 * 訂單服務(wù)接口
 */
public interface OrderService extends IService<Order> {
    /**
     * 批量插入訂單
     * @param orders 訂單列表
     * @return 是否成功
     */
    boolean insertBatch(List<Order> orders);
    /**
     * 根據(jù)用戶ID查詢訂單列表
     * @param userId 用戶ID
     * @return 訂單列表
     */
    List<Order> getOrdersByUserId(Long userId);
    /**
     * 復(fù)雜查詢：同時(shí)使用分片鍵和狀態(tài)
     * @param userId 用戶ID
     * @param status 訂單狀態(tài)
     * @return 訂單列表
     */
    List<Order> complexQuery(Long userId, Integer status);
    /**
     * 根據(jù)用戶ID列表查詢
     * @param userIds 用戶ID列表
     * @return 訂單列表
     */
    List<Order> getOrdersByUserIds(List<Long> userIds);
    /**
     * 生成測(cè)試訂單數(shù)據(jù)
     * @param userId 用戶ID
     * @return 測(cè)試訂單
     */
    Order generateTestOrder(Long userId);
    /**
     * 初始化測(cè)試數(shù)據(jù)
     * @param count 每個(gè)用戶生成的訂單數(shù)
     */
    void initTestData(int count);
    /**
     * 獲取訂單的路由信息（用于展示）
     */
    String getRoutingInfo(Order order);
    /**
     * 插入數(shù)據(jù)
     */
    boolean save(Order entity);
}

package com.shardingdatabasetable.shardingsphere.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shardingdatabasetable.shardingsphere.entity.Order;
import com.shardingdatabasetable.shardingsphere.mapper.OrderMapper;
import com.shardingdatabasetable.shardingsphere.service.OrderService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
/**
 * 訂單服務(wù)實(shí)現(xiàn)類(lèi)
 */
@Slf4j
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean insertBatch(List<Order> orders) {
        if (orders == null || orders.isEmpty()) {
            return false;
        }
        // 補(bǔ)全訂單信息（但不設(shè)置 orderId，讓 ShardingSphere 的雪花算法生成）
        orders.forEach(order -> {
            if (order.getOrderNo() == null) {
                order.setOrderNo("ORD" + System.currentTimeMillis() +
                        UUID.randomUUID().toString().replace("-", "").substring(0, 4).toUpperCase());
            }
            if (order.getCreateTime() == null) {
                order.setCreateTime(new Date());
            }
            // 確保 orderId 為 null，讓 ShardingSphere 生成
            order.setOrderId(null);
        });
        // 使用 MyBatis-Plus 的 saveBatch 方法
        boolean success = this.saveBatch(orders);
        if (success) {
            log.info("批量插入成功，數(shù)量：{}", orders.size());
            // 方法1：根據(jù)訂單號(hào)和用戶ID組合查詢（最精確）
            for (Order order : orders) {
                LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
                wrapper.eq(Order::getUserId, order.getUserId())
                        .eq(Order::getOrderNo, order.getOrderNo())
                        .orderByDesc(Order::getCreateTime)
                        .last("LIMIT 1");
                Order savedOrder = this.getOne(wrapper);
                if (savedOrder != null) {
                    // 將查詢到的 orderId 設(shè)置回原對(duì)象
                    order.setOrderId(savedOrder.getOrderId());
                    // 也可以更新創(chuàng)建時(shí)間，如果需要
                    order.setCreateTime(savedOrder.getCreateTime());
                }
            }
            // 打印路由信息
            orders.forEach(order -> {
                if (order.getOrderId() != null) {
                    log.info("插入訂單 - {}", getRoutingInfo(order));
                } else {
                    log.warn("訂單 {} 的 orderId 未能獲取", order.getOrderNo());
                }
            });
        } else {
            log.error("批量插入失敗，數(shù)量：{}", orders.size());
        }
        return success;
    }
    @Override
    public List<Order> getOrdersByUserId(Long userId) {
        LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(Order::getUserId, userId)
                .orderByDesc(Order::getCreateTime);
        return list(wrapper);
    }
    @Override
    public List<Order> complexQuery(Long userId, Integer status) {
        return baseMapper.selectByUserIdAndStatus(userId, status);
    }
    @Override
    public List<Order> getOrdersByUserIds(List<Long> userIds) {
        if (userIds == null || userIds.isEmpty()) {
            return new ArrayList<>();
        }
        return baseMapper.selectByUserIds(userIds);
    }
    @Override
    public Order generateTestOrder(Long userId) {
        Order order = new Order();
        order.setUserId(userId);
        order.setOrderNo("ORD" + System.currentTimeMillis());
        order.setAmount(new BigDecimal(Math.random() * 1000).setScale(2, BigDecimal.ROUND_HALF_UP));
        order.setStatus((int)(Math.random() * 3)); // 0,1,2隨機(jī)
        order.setCreateTime(new Date()); // 使用java.util.Date
        return order;
    }
    @Override
    public boolean save(Order entity) {
        // 先保存
        boolean result = super.save(entity);
        if (result) {
            // 保存成功后，根據(jù)訂單號(hào)查詢
            LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(Order::getUserId, entity.getUserId())
                    .eq(Order::getOrderNo, entity.getOrderNo())
                    .orderByDesc(Order::getCreateTime)
                    .last("LIMIT 1");
            Order savedOrder = this.getOne(wrapper);
            if (savedOrder != null) {
                entity.setOrderId(savedOrder.getOrderId());
                entity.setCreateTime(savedOrder.getCreateTime());
            }
        }
        return result;
    }
    @Override
    public void initTestData(int count) {
        // 測(cè)試10個(gè)用戶
        Long[] userIds = {1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L};
        List<Order> allOrders = new ArrayList<>();
        for (Long userId : userIds) {
            for (int i = 0; i < count; i++) {
                Order order = generateTestOrder(userId);
                completeOrder(order);
                allOrders.add(order);
            }
        }
        if (!allOrders.isEmpty()) {
            // 分批插入，避免一次性插入太多
            int batchSize = 100;
            for (int i = 0; i < allOrders.size(); i += batchSize) {
                int end = Math.min(i + batchSize, allOrders.size());
                List<Order> batch = allOrders.subList(i, end);
                baseMapper.batchInsert(batch);
            }
            log.info("初始化測(cè)試數(shù)據(jù)完成，共插入 {} 條訂單", allOrders.size());
        }
    }
    @Override
    public String getRoutingInfo(Order order) {
        String db = (order.getUserId() % 2 == 0) ? "ds0" : "ds1";
        String table = (order.getOrderId() % 2 == 0) ? "t_order_0" : "t_order_1";
        return String.format("%s.%s (userId: %d, orderId: %d)", db, table, order.getUserId(), order.getOrderId());
    }
    /**
     * 補(bǔ)全訂單信息
     */
    private void completeOrder(Order order) {
        if (order.getOrderNo() == null) {
            order.setOrderNo(generateOrderNo());
        }
        if (order.getCreateTime() == null) {
            //order.setCreateTime(LocalDateTime.now());
            order.setCreateTime(new Date());
        }
    }
    /**
     * 生成訂單號(hào)
     * 格式：ORD + 時(shí)間戳 + 隨機(jī)數(shù)
     */
    private String generateOrderNo() {
        return "ORD" + System.currentTimeMillis() +
                UUID.randomUUID().toString().replace("-", "").substring(0, 8).toUpperCase();
    }
}

package com.shardingdatabasetable.shardingsphere.controller;
import com.shardingdatabasetable.shardingsphere.entity.Order;
import com.shardingdatabasetable.shardingsphere.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * 訂單控制器 - 提供REST API
 */
@RestController
@RequestMapping("/api/order")
public class OrderController {
    @Autowired
    private OrderService orderService;
    /**
     * 創(chuàng)建單個(gè)訂單
     */
    @PostMapping("/create")
    public Map<String, Object> createOrder(@RequestParam Long userId) {
        Order order = orderService.generateTestOrder(userId);
        orderService.save(order);
        Map<String, Object> result = new HashMap<>();
        result.put("success", true);
        result.put("message", "訂單創(chuàng)建成功");
        result.put("orderId", order.getOrderId());
        result.put("userId", userId);
        result.put("routing", orderService.getRoutingInfo(order));
        return result;
    }
    /**
     * 批量創(chuàng)建訂單
     */
    @PostMapping("/batch")
    public Map<String, Object> batchCreateOrders(
            @RequestParam Long userId,
            @RequestParam(defaultValue = "10") Integer count) {
        List<Order> orders = new java.util.ArrayList<>();
        for (int i = 0; i < count; i++) {
            orders.add(orderService.generateTestOrder(userId));
        }
        boolean success = orderService.insertBatch(orders);
        Map<String, Object> result = new HashMap<>();
        result.put("success", success);
        result.put("message", success ? "批量創(chuàng)建成功" : "批量創(chuàng)建失敗");
        result.put("userId", userId);
        result.put("count", count);
        return result;
    }
    /**
     * 根據(jù)用戶ID查詢訂單
     */
    @GetMapping("/user/{userId}")
    public List<Order> getOrdersByUser(@PathVariable Long userId) {
        return orderService.getOrdersByUserId(userId);
    }
    /**
     * 復(fù)雜查詢
     */
    @GetMapping("/query")
    public List<Order> complexQuery(
            @RequestParam Long userId,
            @RequestParam(required = false) Integer status) {
        return orderService.complexQuery(userId, status);
    }
    /**
     * 根據(jù)多個(gè)用戶ID查詢
     */
    @PostMapping("/query/users")
    public List<Order> getOrdersByUserIds(@RequestBody List<Long> userIds) {
        return orderService.getOrdersByUserIds(userIds);
    }
    /**
     * 初始化測(cè)試數(shù)據(jù)
     */
    @PostMapping("/init")
    public Map<String, Object> initTestData(@RequestParam(defaultValue = "5") Integer count) {
        orderService.initTestData(count);
        Map<String, Object> result = new HashMap<>();
        result.put("success", true);
        result.put("message", "測(cè)試數(shù)據(jù)初始化完成，每個(gè)用戶生成 " + count + " 條訂單");
        return result;
    }
    /**
     * 根據(jù)訂單ID查詢（演示全表掃描）
     */
    @GetMapping("/{orderId}")
    public Order getOrderById(@PathVariable Long orderId) {
        // 注意：只根據(jù)order_id查詢會(huì)進(jìn)行全庫(kù)全表掃描
        return orderService.getById(orderId);
    }
    /**
     * 獲取數(shù)據(jù)分布統(tǒng)計(jì)
     */
    /*@GetMapping("/distribution")
    public Map<String, Object> getDistribution() {
        Long[] userIds = {1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L};
        Map<String, Integer> stats = new HashMap<>();
        for (Long userId : userIds) {
            int count = orderService.countByUserId(userId);
            String db = (userId % 2 == 0) ? "ds0" : "ds1";
            stats.put("用戶" + userId + "(" + db + ")", count);
        }
        Map<String, Object> result = new HashMap<>();
        result.put("success", true);
        result.put("stats", stats);
        return result;
    }*/
}

package com.shardingdatabasetable.shardingsphere;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.shardingdatabasetable.shardingsphere.entity.Order;
import com.shardingdatabasetable.shardingsphere.mapper.OrderMapper;
import com.shardingdatabasetable.shardingsphere.service.OrderService;
import lombok.extern.slf4j.Slf4j;
import net.minidev.json.JSONObject;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import java.util.Arrays;
import java.util.List;
import static org.junit.Assert.*;
/**
 * ShardingSphere分庫(kù)分表測(cè)試類(lèi)
 */
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingJdbcTest {
    @Autowired
    private OrderService orderService;
    @Autowired
    private OrderMapper orderMapper;
    @Before
    public void setUp() {
        log.info("========== 開(kāi)始測(cè)試 ==========");
    }
    @After
    public void tearDown() {
        log.info("========== 測(cè)試結(jié)束 ==========");
    }
    /**
     * 測(cè)試1: 測(cè)試Spring上下文和Bean注入
     */
    @Test
    public void testContextLoads() {
        log.info("測(cè)試1: 驗(yàn)證Bean注入");
        assertNotNull("OrderService注入失敗", orderService);
        assertNotNull("OrderMapper注入失敗", orderMapper);
        log.info("? OrderService 注入成功: {}", orderService.getClass().getName());
        log.info("? OrderMapper 注入成功: {}", orderMapper.getClass().getName());
    }
    /**
     * 測(cè)試2: 測(cè)試插入單個(gè)訂單
     */
    @Test
    public void testInsertSingleOrder() {
        /**
         String db = (order.getUserId() % 2 == 0) ? "ds0" : "ds1";
         String table = (order.getOrderId() % 2 == 0) ? "t_order_0" : "t_order_1";
         用戶ID字段userId是偶數(shù)在ds0庫(kù)，是奇數(shù)在ds1庫(kù)。
         訂單ID字段(使用雪花算法自動(dòng)生成)orderId是偶數(shù)在t_order_0庫(kù)，是奇數(shù)在t_order_1庫(kù)。
         */
        log.info("測(cè)試2: 插入單個(gè)訂單");
        // 測(cè)試不同用戶ID的路由
        Long[] userIds = {1L, 2L, 3L, 4L};
        for (Long userId : userIds) {
            Order order = orderService.generateTestOrder(userId);
            boolean saved = orderService.save(order);
            assertTrue("訂單保存失敗", saved);
            assertNotNull("訂單ID不應(yīng)為null", order.getOrderId());
            log.info("插入訂單 - {}", orderService.getRoutingInfo(order));
            // 驗(yàn)證查詢
           /* System.out.println("驗(yàn)證查詢前數(shù)據(jù):"+order.getOrderId());
            Order retrieved = orderService.getById(order.getOrderId());
            assertNotNull("查詢到的訂單不應(yīng)為null", retrieved);
            assertEquals("訂單ID應(yīng)匹配", order.getOrderId(), retrieved.getOrderId());*/
        }
    }
    /**
     * 測(cè)試3: 測(cè)試批量插入
     */
    @Test
    public void testBatchInsert() {
        /**
         String db = (order.getUserId() % 2 == 0) ? "ds0" : "ds1";
         String table = (order.getOrderId() % 2 == 0) ? "t_order_0" : "t_order_1";
         用戶ID字段userId是偶數(shù)在ds0庫(kù)，是奇數(shù)在ds1庫(kù)。
         訂單ID字段(使用雪花算法自動(dòng)生成)orderId是偶數(shù)在t_order_0庫(kù)，是奇數(shù)在t_order_1庫(kù)。
         */
        log.info("測(cè)試3: 批量插入訂單");
        List<Order> orders = Arrays.asList(
                orderService.generateTestOrder(1L),
                orderService.generateTestOrder(2L),
                orderService.generateTestOrder(3L),
                orderService.generateTestOrder(4L),
                orderService.generateTestOrder(5L)
        );
        boolean inserted = orderService.insertBatch(orders);
        assertTrue("批量插入失敗", inserted);
        log.info("批量插入 {} 條訂單成功", orders.size());
        orders.forEach(order ->
                log.info("插入訂單 - {}", orderService.getRoutingInfo(order))
        );
    }
    /**
     * 測(cè)試4: 測(cè)試根據(jù)用戶ID查詢
     */
    @Test
    public void testQueryByUserId() {
        log.info("測(cè)試4: 根據(jù)用戶ID查詢訂單");
        Long userId = 2L; // 偶數(shù) -> ds0
        String expectedDb = "ds0";
        // 先插入幾條測(cè)試數(shù)據(jù)
        for (int i = 0; i < 3; i++) {
            orderService.save(orderService.generateTestOrder(userId));
        }
        // 查詢
        List<Order> orders = orderService.getOrdersByUserId(userId);
        assertNotNull("查詢結(jié)果不應(yīng)為null", orders);
        assertTrue("訂單數(shù)量應(yīng)大于0", orders.size() > 0);
        log.info("用戶ID {} ({}) 的訂單數(shù): {}", userId, expectedDb, orders.size());
        orders.forEach(order -> {
            assertEquals("用戶ID應(yīng)匹配", userId, order.getUserId());
            log.info("  訂單 {} -> {}", order.getOrderId(), orderService.getRoutingInfo(order));
        });
    }
    /**
     * 測(cè)試5: 測(cè)試復(fù)雜查詢（帶狀態(tài)過(guò)濾）
     */
    @Test
    public void testComplexQuery() {
        log.info("測(cè)試5: 復(fù)雜查詢（用戶ID + 狀態(tài)）");
        Long userId = 3L; // 奇數(shù) -> ds1
        Integer targetStatus = 1; // 已支付
        // 插入不同狀態(tài)的訂單
        for (int i = 0; i < 5; i++) {
            Order order = orderService.generateTestOrder(userId);
            order.setStatus(i % 3); // 0,1,2 循環(huán)
            orderService.save(order);
        }
        // 查詢狀態(tài)為1的訂單
        List<Order> orders = orderService.complexQuery(userId, targetStatus);
        log.info("用戶ID {} 狀態(tài) {} 的訂單數(shù): {}", userId, targetStatus, orders.size());
        orders.forEach(order -> {
            assertEquals("用戶ID應(yīng)匹配", userId, order.getUserId());
            assertEquals("狀態(tài)應(yīng)匹配", targetStatus, order.getStatus());
            log.info("  訂單 {} - 狀態(tài){}", order.getOrderId(), order.getStatus());
        });
    }
    /**
     * 測(cè)試6: 測(cè)試統(tǒng)計(jì)功能
     */
    @Test
    public void testCountByUserId() {
        log.info("測(cè)試6: 統(tǒng)計(jì)用戶訂單總數(shù)");
        Long userId = 4L; // 偶數(shù) -> ds0
        int expectedCount = 3;
        // 插入指定數(shù)量的訂單
        for (int i = 0; i < expectedCount; i++) {
            orderService.save(orderService.generateTestOrder(userId));
        }
        int count = orderMapper.countByUserId(userId);
        assertTrue("統(tǒng)計(jì)數(shù)量應(yīng)至少為" + expectedCount, count >= expectedCount);
        log.info("用戶ID {} 的訂單總數(shù): {}", userId, count);
    }
    /**
     * 測(cè)試7: 測(cè)試數(shù)據(jù)分布(運(yùn)行報(bào)錯(cuò))
     */
    @Test
    public void testDataDistribution() {
        log.info("測(cè)試7: 測(cè)試數(shù)據(jù)分布情況");
        // 初始化測(cè)試數(shù)據(jù)
        orderService.initTestData(3);
        Long[] userIds = {1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L};
        log.info("\n數(shù)據(jù)分布統(tǒng)計(jì):");
        log.info("用戶ID\t數(shù)據(jù)庫(kù)\t訂單數(shù)");
        log.info("-------------------");
        for (Long userId : userIds) {
            int count = orderMapper.countByUserId(userId);
            String db = (userId % 2 == 0) ? "ds0" : "ds1";
            log.info("{}\t{}\t{}", userId, db, count);
        }
    }
    /**
     * 測(cè)試8: 測(cè)試多用戶ID查詢
     */
    @Test
    public void testQueryByUserIds() {
        log.info("測(cè)試8: 根據(jù)多個(gè)用戶ID查詢");
        // 先插入測(cè)試數(shù)據(jù)
        //orderService.initTestData(2);
        List<Long> userIds = Arrays.asList(1L, 3L, 5L); // 都是奇數(shù) -> ds1
        List<Order> orders = orderService.getOrdersByUserIds(userIds);
        log.info("查詢用戶 {} 的訂單數(shù): {}", userIds, orders.size());
        orders.forEach(order ->
                log.info("  用戶{} - 訂單{}", order.getUserId(), order.getOrderId())
        );
        // 驗(yàn)證所有訂單的用戶ID都在查詢列表中
        orders.forEach(order ->
                assertTrue("用戶ID不在查詢列表中", userIds.contains(order.getUserId()))
        );
    }
    /**
     * 測(cè)試9: 測(cè)試不帶分片鍵的查詢（全表掃描）
     */
    @Test
    public void testFullTableScan() {
        log.info("測(cè)試9: 全表掃描查詢（不帶分片鍵）");
        // 注意：這個(gè)查詢會(huì)掃描所有庫(kù)所有表
        List<Order> allOrders = orderService.list();
        log.info("全庫(kù)訂單總數(shù): {}", allOrders.size());
        if (allOrders.size() > 0) {
            log.info("前5條訂單記錄:");
            allOrders.stream().limit(5).forEach(order ->
                    log.info("  訂單{} - 用戶{}", order.getOrderId(), order.getUserId())
            );
        }
    }
    /**
     * 測(cè)試10: 測(cè)試事務(wù)
     */
    @Test
    public void testTransaction() {
        log.info("測(cè)試10: 測(cè)試事務(wù)");
        Long userId = 6L; // 偶數(shù) -> ds0
        try {
            // 批量插入，故意讓其中一個(gè)失敗來(lái)測(cè)試事務(wù)
            List<Order> orders = Arrays.asList(
                    orderService.generateTestOrder(userId),
                    orderService.generateTestOrder(userId),
                    orderService.generateTestOrder(userId)
            );
            // 正常插入
            boolean success = orderService.insertBatch(orders);
            assertTrue("批量插入失敗", success);
            // 驗(yàn)證插入成功
            int count = orderMapper.countByUserId(userId);
            log.info("用戶ID {} 當(dāng)前訂單數(shù): {}", userId, count);
        } catch (Exception e) {
            log.error("事務(wù)測(cè)試異常", e);
            fail("事務(wù)測(cè)試失敗: " + e.getMessage());
        }
    }
    /**
     * 測(cè)試讀寫(xiě)分離
     * Order retrieved = orderService.getById(order.getOrderId());(這塊感覺(jué)有問(wèn)題)
     */
    @Test
    public void testReadWriteSplittingFirst() {
        log.info("測(cè)試讀寫(xiě)分離");
        // 1. 寫(xiě)操作 - 應(yīng)該走主庫(kù) ds2
        Long userId = 6L; // 偶數(shù)，按分片規(guī)則應(yīng)該到 ms_ds
        Order order = orderService.generateTestOrder(userId);
        orderService.save(order);
        log.info("寫(xiě)操作完成，訂單ID: {}", order.getOrderId());
        // 2. 讀操作 - 應(yīng)該走從庫(kù) (ds0 或 ds1，輪詢)
        Order retrieved = orderService.getById(order.getOrderId());
        log.info("讀操作完成，查詢到的訂單: {}", retrieved != null ? "成功" : "失敗");
        // 3. 批量讀 - 測(cè)試負(fù)載均衡
        for (int i = 0; i < 5; i++) {
            List<Order> orders = orderService.getOrdersByUserId(userId);
            log.info("第{}次查詢，獲取到{}條記錄", i+1, orders.size());
        }
        // 4. 事務(wù)內(nèi)操作 - 應(yīng)該都走主庫(kù)
        @Transactional
        class TransactionTest {
            public void test() {
                Order order1 = orderService.generateTestOrder(userId);
                orderService.save(order1);  // 寫(xiě) - 主庫(kù)
                Order query = orderService.getById(order1.getOrderId());  // 讀 - 也走主庫(kù)（事務(wù)內(nèi)）
                log.info("事務(wù)內(nèi)查詢: {}", query != null ? "成功" : "失敗");
            }
        }
    }
    /**
     * 完整的讀寫(xiě)分離測(cè)試
     */
    @Test
    public void testCompleteReadWriteSplitting() {
        log.info("========== 完整讀寫(xiě)分離測(cè)試 ==========");
        // 測(cè)試不同的用戶ID（奇數(shù)和偶數(shù)）
        Long[] userIds = {1L, 2L, 3L, 4L, 5L, 6L};
        for (Long userId : userIds) {
            log.info("\n--- 測(cè)試用戶ID: {} ---", userId);
            // 1. 插入數(shù)據(jù)（寫(xiě)操作 -> 主庫(kù) ds2）
            Order order = orderService.generateTestOrder(userId);
            orderService.save(order);
            log.info("插入訂單 - {}", orderService.getRoutingInfo(order));
            // 2. 根據(jù)ID查詢（讀操作 -> 從庫(kù)）
            Order queryById = orderService.getById(order.getOrderId());
            log.info("ID查詢 - {}", orderService.getRoutingInfo(queryById));
            // 3. 根據(jù)用戶ID查詢列表（讀操作 -> 從庫(kù)）
            List<Order> userOrders = orderService.getOrdersByUserId(userId);
            log.info("用戶查詢 - 獲取到 {} 條記錄", userOrders.size());
            if (!userOrders.isEmpty()) {
                log.info("  第一條記錄路由: {}",
                        orderService.getRoutingInfo(userOrders.get(0)));
            }
            // 4. 更新操作（寫(xiě)操作 -> 主庫(kù)）
            order.setStatus(2);
            orderService.updateById(order);
            log.info("更新訂單狀態(tài)完成");
            // 5. 再次查詢驗(yàn)證更新（讀操作 -> 從庫(kù)，可能存在主從延遲）
            Order updated = orderService.getById(order.getOrderId());
            log.info("更新后查詢 - 狀態(tài): {}", updated.getStatus());
        }
    }
    /**
     * 測(cè)試主從延遲場(chǎng)景
     */
    @Test
    public void testMasterSlaveDelay() {
        log.info("========== 測(cè)試主從延遲場(chǎng)景 ==========");
        Long userId = 8L;
        // 1. 插入數(shù)據(jù)（主庫(kù)）
        Order order = orderService.generateTestOrder(userId);
        orderService.save(order);
        log.info("數(shù)據(jù)已插入主庫(kù)，訂單ID: {}", order.getOrderId());
        // 2. 立即查詢（可能因?yàn)橹鲝难舆t查不到）
        Order immediately = orderService.getById(order.getOrderId());
        log.info("立即查詢結(jié)果: {}", immediately != null ? "成功" : "失?。赡苤鲝难舆t）");
        // 3. 等待1秒后查詢
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
        Order afterWait = orderService.getById(order.getOrderId());
        log.info("等待后查詢結(jié)果: {}", afterWait != null ? "成功" : "失敗");
        // 4. 強(qiáng)制走主庫(kù)查詢（事務(wù)內(nèi)）
        @Transactional
        class ForceMasterRead {
            public Order query() {
                return orderService.getById(order.getOrderId());
            }
        }
        Order forceMaster = new ForceMasterRead().query();
        log.info("強(qiáng)制主庫(kù)查詢結(jié)果: {}", forceMaster != null ? "成功" : "失敗");
    }
    /**
     * 測(cè)試負(fù)載均衡效果
     */
    @Test
    public void testLoadBalance() {
        log.info("========== 測(cè)試從庫(kù)負(fù)載均衡 ==========");
        Long userId = 10L;
        // 先插入一些測(cè)試數(shù)據(jù)
        for (int i = 0; i < 5; i++) {
            orderService.save(orderService.generateTestOrder(userId));
        }
        log.info("開(kāi)始執(zhí)行讀操作，觀察路由分布:");
        // 執(zhí)行20次查詢，觀察路由到哪個(gè)從庫(kù)
        for (int i = 0; i < 20; i++) {
            List<Order> orders = orderService.getOrdersByUserId(userId);
            if (!orders.isEmpty()) {
                String routing = orderService.getRoutingInfo(orders.get(0));
                log.info("第{}次查詢 - 路由到: {}", i+1, routing.split(" ")[0]);
            }
        }
    }
    /**
     * 測(cè)試讀寫(xiě)分離 - 使用 LambdaQueryWrapper 替代 getById
     */
    @Test
    public void testReadWriteSplitting() {
        log.info("========== 測(cè)試讀寫(xiě)分離 ==========");
        // 1. 寫(xiě)操作 - 應(yīng)該走主庫(kù) ds2
        /*
        //偶數(shù)錄入
        Long userId = 6L;
        Order order = orderService.generateTestOrder(userId);
        boolean saved = orderService.save(order);
        assertTrue("訂單保存失敗", saved);
        log.info("? 寫(xiě)操作完成 - 用戶ID: {}, 訂單號(hào): {}", userId, order.getOrderNo());*/
        //奇數(shù)錄入
        /*Long userId = 9L;
        Order order = orderService.generateTestOrder(userId);
        boolean saved = orderService.save(order);
        assertTrue("訂單保存失敗", saved);
        log.info("? 寫(xiě)操作完成 - 用戶ID: {}, 訂單號(hào): {}", userId, order.getOrderNo());*/
        //造數(shù)據(jù)測(cè)試讀操作
        //用戶ID字段userId是偶數(shù)在ds0庫(kù)，是奇數(shù)在ds1庫(kù)
        //下面的userId是偶數(shù)所以在ds0庫(kù)
        /*Long userId = 6L;
        Order order = new Order();
        order.setOrderNo("ORD1772503153629");*/
        //下面的userId是奇數(shù)所以在ds1庫(kù)
        Long userId = 9L;
        Order order = new Order();
        order.setOrderNo("ORD1772507872251");
        // 2. 讀操作 - 使用 LambdaQueryWrapper 查詢(單條讀) 下面的在userId是偶數(shù)在ds0庫(kù)是對(duì)的
        LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(Order::getUserId, userId)
                .eq(Order::getOrderNo, order.getOrderNo());
        Order retrieved = orderService.getOne(queryWrapper);
        log.info("讀操作查詢: {},", JSON.toJSONString(retrieved));
        assertNotNull("查詢到的訂單不應(yīng)為null", retrieved);
        // 獲取路由信息（需要確保 retrieved 有 orderId）
        String routingInfo = (retrieved.getOrderId() != null) ?
                orderService.getRoutingInfo(retrieved) : "未知路由";
        log.info("? 讀操作完成 - 訂單ID: {}, 路由: {}",
                retrieved.getOrderId(), routingInfo);
        // 3. 批量讀 - 測(cè)試負(fù)載均衡
        log.info("\n?? 測(cè)試從庫(kù)負(fù)載均衡（多次查詢）:");
        for (int i = 0; i < 5; i++) {
            LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(Order::getUserId, userId);
            List<Order> orders = orderService.list(wrapper);
            String routeInfo = "未知路由";
            if (!orders.isEmpty() && orders.get(0).getOrderId() != null) {
                routeInfo = orderService.getRoutingInfo(orders.get(0));
            }
            //這塊只打印出來(lái)了1條
            log.info("  第{}次查詢 - 獲取到{}條記錄, 路由: {}",
                    i+1, orders.size(), routeInfo);
        }//for end
        // 4. 更新操作 - 寫(xiě)操作，應(yīng)該走主庫(kù)
       /* retrieved.setStatus(1);
        boolean updated = orderService.updateById(retrieved);
        assertTrue("訂單更新失敗", updated);
        log.info("? 更新操作完成 - 訂單狀態(tài)已修改為: 已支付");
        // 5. 再次查詢驗(yàn)證更新
        LambdaQueryWrapper<Order> verifyWrapper = new LambdaQueryWrapper<>();
        verifyWrapper.eq(Order::getUserId, userId)
                .eq(Order::getOrderNo, order.getOrderNo());
        Order updatedOrder = orderService.getOne(verifyWrapper);
        assertNotNull("更新后查詢失敗", updatedOrder);
        assertEquals("狀態(tài)應(yīng)該已更新", Integer.valueOf(1), updatedOrder.getStatus());
        String verifyRoute = (updatedOrder.getOrderId() != null) ?
                orderService.getRoutingInfo(updatedOrder) : "未知路由";
        log.info("? 驗(yàn)證查詢完成 - 訂單狀態(tài): {}, 路由: {}",
                updatedOrder.getStatus(), verifyRoute);*/
    }
    /**
     * 測(cè)試不同用戶的讀寫(xiě)分離
     */
    @Test
    public void testReadWriteSplittingWithDifferentUsers() {
        log.info("========== 測(cè)試多用戶讀寫(xiě)分離 ==========");
        Long[] userIds = {1L, 2L, 3L, 4L, 5L};
        for (Long userId : userIds) {
            log.info("\n--- 測(cè)試用戶ID: {} ---", userId);
            // 插入數(shù)據(jù)
            Order order = orderService.generateTestOrder(userId);
            orderService.save(order);
            // 查詢數(shù)據(jù)
            LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(Order::getUserId, userId)
                    .eq(Order::getOrderNo, order.getOrderNo());
            Order queried = orderService.getOne(wrapper);
            assertNotNull("查詢失敗", queried);
            String expectedDb = (userId % 2 == 0) ? "ds0" : "ds1";
            log.info("用戶{}的數(shù)據(jù)應(yīng)分布在: {}, 實(shí)際查詢到訂單ID: {}",
                    userId, expectedDb, queried.getOrderId());
        }
    }
}

package com.shardingdatabasetable.shardingsphere.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
/**
 * MyBatis-Plus 配置類(lèi)
 * 解決 ShardingSphere + MyBatis-Plus 集成問(wèn)題
 */
@Configuration
@MapperScan(basePackages = "com.shardingdatabasetable.shardingsphere.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class MyBatisPlusConfig {
    @Autowired
    @Qualifier("shardingDataSource")
    private DataSource shardingDataSource;
    /**
     * 創(chuàng)建SqlSessionFactory
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        // 設(shè)置數(shù)據(jù)源為ShardingSphere的數(shù)據(jù)源
        sqlSessionFactoryBean.setDataSource(shardingDataSource);
        // 設(shè)置mapper.xml文件位置
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*.xml"));
        // 設(shè)置實(shí)體類(lèi)包路徑
        sqlSessionFactoryBean.setTypeAliasesPackage("com.shardingdatabasetable.shardingsphere.entity");
        // 使用MybatisConfiguration，而不是原生的Configuration
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        sqlSessionFactoryBean.setConfiguration(configuration);
        // 設(shè)置MyBatis-Plus攔截器
        sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor());
        // 設(shè)置全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        GlobalConfig.DbConfig dbConfig = new GlobalConfig.DbConfig();
        dbConfig.setIdType(com.baomidou.mybatisplus.annotation.IdType.NONE); // 使用ShardingSphere的主鍵生成策略
        globalConfig.setDbConfig(dbConfig);
        sqlSessionFactoryBean.setGlobalConfig(globalConfig);
        return sqlSessionFactoryBean.getObject();
    }
    /**
     * 創(chuàng)建SqlSessionTemplate
     */
    @Bean
    public SqlSessionTemplate sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory());
    }
    /**
     * MyBatis-Plus 攔截器（分頁(yè)等功能）
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分頁(yè)插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

# 1. 初始化測(cè)試數(shù)據(jù)（每個(gè)用戶生成5條訂單）
curl -X POST "http://localhost:8006/api/order/init?count=5"
# 2. 創(chuàng)建單個(gè)訂單
curl -X POST "http://localhost:8006/api/order/create?userId=1"
# 3. 批量創(chuàng)建訂單（用戶2創(chuàng)建10條）
curl -X POST "http://localhost:8006/api/order/batch?userId=2&count=10"
# 4. 查詢用戶1的訂單
curl "http://localhost:8006/api/order/user/1"
# 5. 復(fù)雜查詢（用戶3的狀態(tài)為1的訂單）
curl "http://localhost:8006/api/order/query?userId=3&status=1"
# 6. 獲取數(shù)據(jù)分布統(tǒng)計(jì)
curl "http://localhost:8006/api/order/distribution"