使用shardingsphere实现分库分表和读写分离教程

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 分库分表示例</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 驱动 (适配 mysql 8.0.33) -->
        <dependency>
            <groupid>mysql</groupid>
            <artifactid>mysql-connector-java</artifactid>
            <version>${mysql.version}</version>
            <scope>runtime</scope>
        </dependency>
        <!-- lombok 简化代码 -->
        <dependency>
            <groupid>org.projectlombok</groupid>
            <artifactid>lombok</artifactid>
            <optional>true</optional>
        </dependency>
        <!-- groovy 用于分片算法表达式 -->
        <dependency>
            <groupid>org.codehaus.groovy</groupid>
            <artifactid>groovy</artifactid>
            <version>2.4.5</version>
        </dependency>
        <!-- junit 4 (spring boot 2.5.x 默认兼容) -->
        <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>

-- 创建两个分库，使用 mysql 8.0 的字符集和排序规则
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;
-- 创建用户并授权（如果需要）
-- 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 库中执行
use ds0;
-- 创建 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 '订单编号',
    `amount` decimal(10,2) not null default 0.00 comment '订单金额',
    `status` tinyint not null default 0 comment '订单状态：0-待支付，1-已支付，2-已取消',
    `create_time` datetime(3) not null default current_timestamp(3) comment '创建时间（毫秒精度）',
    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';
-- 创建 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 '订单编号',
    `amount` decimal(10,2) not null default 0.00 comment '订单金额',
    `status` tinyint not null default 0 comment '订单状态：0-待支付，1-已支付，2-已取消',
    `create_time` datetime(3) not null default current_timestamp(3) comment '创建时间（毫秒精度）',
    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 库中执行
use ds1;
-- 创建 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 '订单编号',
    `amount` decimal(10,2) not null default 0.00 comment '订单金额',
    `status` tinyint not null default 0 comment '订单状态：0-待支付，1-已支付，2-已取消',
    `create_time` datetime(3) not null default current_timestamp(3) comment '创建时间（毫秒精度）',
    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';
-- 创建 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 '订单编号',
    `amount` decimal(10,2) not null default 0.00 comment '订单金额',
    `status` tinyint not null default 0 comment '订单状态：0-待支付，1-已支付，2-已取消',
    `create_time` datetime(3) not null default current_timestamp(3) comment '创建时间（毫秒精度）',
    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
  # 关闭所有数据源相关的自动配置
  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:
      # 数据源名称列表 - 新增主库ds2，原来的ds0和ds1变为从库
      names: ds2, ds0, ds1
      # 数据源 ds2 (主库)
      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
      # 数据源 ds0 (从库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
      # 数据源 ds1 (从库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
    # 分片规则配置
    sharding:
      # 主从规则配置 - 新增部分 [citation:1][citation:3]
      master-slave-rules:
        # 主从逻辑名称，可以自定义
        ms_ds:
          # 指定主库数据源名称
          master-data-source-name: ds2
          # 指定从库数据源名称列表，多个用逗号分隔 [citation:3]
          slave-data-source-names: ds0, ds1
          # 从库负载均衡算法：round_robin(轮询) / random(随机) [citation:3]
          load-balance-algorithm-type: round_robin
      # 默认的分库策略：按user_id取模
      default-database-strategy:
        inline:
          sharding-column: user_id
          # 注意：这里用的是主从逻辑名 ms_ds，而不是具体的数据库名
          algorithm-expression: ms_ds
      # 绑定表
      binding-tables:
        - t_order
      # 具体的表分片规则
      tables:
        # 逻辑表名
        t_order:
          # 实际数据节点：使用主从逻辑名，后面跟实际的物理表 [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
      # 默认数据源（可选）
      default-data-source-name: ms_ds
    # 属性配置
    props:
      sql:
        show: true  # 打印sql日志，便于调试
      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
# 日志级别配置
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;
/**
 * 订单实体类
 * 逻辑表名：t_order，shardingsphere会自动路由到物理表
 */
@data
@tablename("t_order")
public class order {
    /**
     * 订单id，使用shardingsphere的雪花算法自动生成
     * 注意：不需要@tableid注解，让shardingsphere处理主键
     */
    private long orderid;
    /**
     * 用户id（分库分表键）
     */
    private long userid;
    /**
     * 订单编号
     */
    private string orderno;
    /**
     * 订单金额
     */
    private bigdecimal amount;
    /**
     * 订单状态：0-待支付，1-已支付，2-已取消
     */
    private integer status;
    /**
     * 创建时间
     */
    //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获得基础的crud方法
 */
@mapper
public interface ordermapper extends basemapper<order> {
    /**
     * 自定义查询：根据用户id和状态查询订单
     * 使用xml文件实现
     */
    list<order> selectbyuseridandstatus(@param("userid") long userid, @param("status") integer status);
    /**
     * 批量插入订单
     * 使用xml文件实现
     */
    int batchinsert(@param("list") list<order> orders);
    /**
     * 统计某个用户的订单总数
     * 使用注解方式实现
     */
    @select("select count(*) from t_order where user_id = #{userid}")
    int countbyuserid(@param("userid") long userid);
    /**
     * 根据用户id列表查询订单（演示多键查询）
     * 使用xml文件实现
     */
    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">
    <!-- 通用结果映射 -->
    <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>
    <!-- 根据用户id和状态查询 -->
    <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>
    <!-- 根据用户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;
/**
 * 订单服务接口
 */
public interface orderservice extends iservice<order> {
    /**
     * 批量插入订单
     * @param orders 订单列表
     * @return 是否成功
     */
    boolean insertbatch(list<order> orders);
    /**
     * 根据用户id查询订单列表
     * @param userid 用户id
     * @return 订单列表
     */
    list<order> getordersbyuserid(long userid);
    /**
     * 复杂查询：同时使用分片键和状态
     * @param userid 用户id
     * @param status 订单状态
     * @return 订单列表
     */
    list<order> complexquery(long userid, integer status);
    /**
     * 根据用户id列表查询
     * @param userids 用户id列表
     * @return 订单列表
     */
    list<order> getordersbyuserids(list<long> userids);
    /**
     * 生成测试订单数据
     * @param userid 用户id
     * @return 测试订单
     */
    order generatetestorder(long userid);
    /**
     * 初始化测试数据
     * @param count 每个用户生成的订单数
     */
    void inittestdata(int count);
    /**
     * 获取订单的路由信息（用于展示）
     */
    string getroutinginfo(order order);
    /**
     * 插入数据
     */
    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;
/**
 * 订单服务实现类
 */
@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;
        }
        // 补全订单信息（但不设置 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("批量插入成功，数量：{}", orders.size());
            // 方法1：根据订单号和用户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 设置回原对象
                    order.setorderid(savedorder.getorderid());
                    // 也可以更新创建时间，如果需要
                    order.setcreatetime(savedorder.getcreatetime());
                }
            }
            // 打印路由信息
            orders.foreach(order -> {
                if (order.getorderid() != null) {
                    log.info("插入订单 - {}", getroutinginfo(order));
                } else {
                    log.warn("订单 {} 的 orderid 未能获取", order.getorderno());
                }
            });
        } else {
            log.error("批量插入失败，数量：{}", 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随机
        order.setcreatetime(new date()); // 使用java.util.date
        return order;
    }
    @override
    public boolean save(order entity) {
        // 先保存
        boolean result = super.save(entity);
        if (result) {
            // 保存成功后，根据订单号查询
            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) {
        // 测试10个用户
        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("初始化测试数据完成，共插入 {} 条订单", 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());
    }
    /**
     * 补全订单信息
     */
    private void completeorder(order order) {
        if (order.getorderno() == null) {
            order.setorderno(generateorderno());
        }
        if (order.getcreatetime() == null) {
            //order.setcreatetime(localdatetime.now());
            order.setcreatetime(new date());
        }
    }
    /**
     * 生成订单号
     * 格式：ord + 时间戳 + 随机数
     */
    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;
    /**
     * 创建单个订单
     */
    @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", "订单创建成功");
        result.put("orderid", order.getorderid());
        result.put("userid", userid);
        result.put("routing", orderservice.getroutinginfo(order));
        return result;
    }
    /**
     * 批量创建订单
     */
    @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 ? "批量创建成功" : "批量创建失败");
        result.put("userid", userid);
        result.put("count", count);
        return result;
    }
    /**
     * 根据用户id查询订单
     */
    @getmapping("/user/{userid}")
    public list<order> getordersbyuser(@pathvariable long userid) {
        return orderservice.getordersbyuserid(userid);
    }
    /**
     * 复杂查询
     */
    @getmapping("/query")
    public list<order> complexquery(
            @requestparam long userid,
            @requestparam(required = false) integer status) {
        return orderservice.complexquery(userid, status);
    }
    /**
     * 根据多个用户id查询
     */
    @postmapping("/query/users")
    public list<order> getordersbyuserids(@requestbody list<long> userids) {
        return orderservice.getordersbyuserids(userids);
    }
    /**
     * 初始化测试数据
     */
    @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", "测试数据初始化完成，每个用户生成 " + count + " 条订单");
        return result;
    }
    /**
     * 根据订单id查询（演示全表扫描）
     */
    @getmapping("/{orderid}")
    public order getorderbyid(@pathvariable long orderid) {
        // 注意：只根据order_id查询会进行全库全表扫描
        return orderservice.getbyid(orderid);
    }
    /**
     * 获取数据分布统计
     */
    /*@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分库分表测试类
 */
@slf4j
@runwith(springrunner.class)
@springboottest
public class shardingjdbctest {
    @autowired
    private orderservice orderservice;
    @autowired
    private ordermapper ordermapper;
    @before
    public void setup() {
        log.info("========== 开始测试 ==========");
    }
    @after
    public void teardown() {
        log.info("========== 测试结束 ==========");
    }
    /**
     * 测试1: 测试spring上下文和bean注入
     */
    @test
    public void testcontextloads() {
        log.info("测试1: 验证bean注入");
        assertnotnull("orderservice注入失败", orderservice);
        assertnotnull("ordermapper注入失败", ordermapper);
        log.info("✅ orderservice 注入成功: {}", orderservice.getclass().getname());
        log.info("✅ ordermapper 注入成功: {}", ordermapper.getclass().getname());
    }
    /**
     * 测试2: 测试插入单个订单
     */
    @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是偶数在ds0库，是奇数在ds1库。
         订单id字段(使用雪花算法自动生成)orderid是偶数在t_order_0库，是奇数在t_order_1库。
         */
        log.info("测试2: 插入单个订单");
        // 测试不同用户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不应为null", order.getorderid());
            log.info("插入订单 - {}", orderservice.getroutinginfo(order));
            // 验证查询
           /* system.out.println("验证查询前数据:"+order.getorderid());
            order retrieved = orderservice.getbyid(order.getorderid());
            assertnotnull("查询到的订单不应为null", retrieved);
            assertequals("订单id应匹配", order.getorderid(), retrieved.getorderid());*/
        }
    }
    /**
     * 测试3: 测试批量插入
     */
    @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是偶数在ds0库，是奇数在ds1库。
         订单id字段(使用雪花算法自动生成)orderid是偶数在t_order_0库，是奇数在t_order_1库。
         */
        log.info("测试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))
        );
    }
    /**
     * 测试4: 测试根据用户id查询
     */
    @test
    public void testquerybyuserid() {
        log.info("测试4: 根据用户id查询订单");
        long userid = 2l; // 偶数 -> ds0
        string expecteddb = "ds0";
        // 先插入几条测试数据
        for (int i = 0; i < 3; i++) {
            orderservice.save(orderservice.generatetestorder(userid));
        }
        // 查询
        list<order> orders = orderservice.getordersbyuserid(userid);
        assertnotnull("查询结果不应为null", orders);
        asserttrue("订单数量应大于0", orders.size() > 0);
        log.info("用户id {} ({}) 的订单数: {}", userid, expecteddb, orders.size());
        orders.foreach(order -> {
            assertequals("用户id应匹配", userid, order.getuserid());
            log.info("  订单 {} -> {}", order.getorderid(), orderservice.getroutinginfo(order));
        });
    }
    /**
     * 测试5: 测试复杂查询（带状态过滤）
     */
    @test
    public void testcomplexquery() {
        log.info("测试5: 复杂查询（用户id + 状态）");
        long userid = 3l; // 奇数 -> ds1
        integer targetstatus = 1; // 已支付
        // 插入不同状态的订单
        for (int i = 0; i < 5; i++) {
            order order = orderservice.generatetestorder(userid);
            order.setstatus(i % 3); // 0,1,2 循环
            orderservice.save(order);
        }
        // 查询状态为1的订单
        list<order> orders = orderservice.complexquery(userid, targetstatus);
        log.info("用户id {} 状态 {} 的订单数: {}", userid, targetstatus, orders.size());
        orders.foreach(order -> {
            assertequals("用户id应匹配", userid, order.getuserid());
            assertequals("状态应匹配", targetstatus, order.getstatus());
            log.info("  订单 {} - 状态{}", order.getorderid(), order.getstatus());
        });
    }
    /**
     * 测试6: 测试统计功能
     */
    @test
    public void testcountbyuserid() {
        log.info("测试6: 统计用户订单总数");
        long userid = 4l; // 偶数 -> ds0
        int expectedcount = 3;
        // 插入指定数量的订单
        for (int i = 0; i < expectedcount; i++) {
            orderservice.save(orderservice.generatetestorder(userid));
        }
        int count = ordermapper.countbyuserid(userid);
        asserttrue("统计数量应至少为" + expectedcount, count >= expectedcount);
        log.info("用户id {} 的订单总数: {}", userid, count);
    }
    /**
     * 测试7: 测试数据分布(运行报错)
     */
    @test
    public void testdatadistribution() {
        log.info("测试7: 测试数据分布情况");
        // 初始化测试数据
        orderservice.inittestdata(3);
        long[] userids = {1l, 2l, 3l, 4l, 5l, 6l, 7l, 8l, 9l, 10l};
        log.info("\n数据分布统计:");
        log.info("用户id\t数据库\t订单数");
        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);
        }
    }
    /**
     * 测试8: 测试多用户id查询
     */
    @test
    public void testquerybyuserids() {
        log.info("测试8: 根据多个用户id查询");
        // 先插入测试数据
        //orderservice.inittestdata(2);
        list<long> userids = arrays.aslist(1l, 3l, 5l); // 都是奇数 -> ds1
        list<order> orders = orderservice.getordersbyuserids(userids);
        log.info("查询用户 {} 的订单数: {}", userids, orders.size());
        orders.foreach(order ->
                log.info("  用户{} - 订单{}", order.getuserid(), order.getorderid())
        );
        // 验证所有订单的用户id都在查询列表中
        orders.foreach(order ->
                asserttrue("用户id不在查询列表中", userids.contains(order.getuserid()))
        );
    }
    /**
     * 测试9: 测试不带分片键的查询（全表扫描）
     */
    @test
    public void testfulltablescan() {
        log.info("测试9: 全表扫描查询（不带分片键）");
        // 注意：这个查询会扫描所有库所有表
        list<order> allorders = orderservice.list();
        log.info("全库订单总数: {}", allorders.size());
        if (allorders.size() > 0) {
            log.info("前5条订单记录:");
            allorders.stream().limit(5).foreach(order ->
                    log.info("  订单{} - 用户{}", order.getorderid(), order.getuserid())
            );
        }
    }
    /**
     * 测试10: 测试事务
     */
    @test
    public void testtransaction() {
        log.info("测试10: 测试事务");
        long userid = 6l; // 偶数 -> ds0
        try {
            // 批量插入，故意让其中一个失败来测试事务
            list<order> orders = arrays.aslist(
                    orderservice.generatetestorder(userid),
                    orderservice.generatetestorder(userid),
                    orderservice.generatetestorder(userid)
            );
            // 正常插入
            boolean success = orderservice.insertbatch(orders);
            asserttrue("批量插入失败", success);
            // 验证插入成功
            int count = ordermapper.countbyuserid(userid);
            log.info("用户id {} 当前订单数: {}", userid, count);
        } catch (exception e) {
            log.error("事务测试异常", e);
            fail("事务测试失败: " + e.getmessage());
        }
    }
    /**
     * 测试读写分离
     * order retrieved = orderservice.getbyid(order.getorderid());(这块感觉有问题)
     */
    @test
    public void testreadwritesplittingfirst() {
        log.info("测试读写分离");
        // 1. 写操作 - 应该走主库 ds2
        long userid = 6l; // 偶数，按分片规则应该到 ms_ds
        order order = orderservice.generatetestorder(userid);
        orderservice.save(order);
        log.info("写操作完成，订单id: {}", order.getorderid());
        // 2. 读操作 - 应该走从库 (ds0 或 ds1，轮询)
        order retrieved = orderservice.getbyid(order.getorderid());
        log.info("读操作完成，查询到的订单: {}", retrieved != null ? "成功" : "失败");
        // 3. 批量读 - 测试负载均衡
        for (int i = 0; i < 5; i++) {
            list<order> orders = orderservice.getordersbyuserid(userid);
            log.info("第{}次查询，获取到{}条记录", i+1, orders.size());
        }
        // 4. 事务内操作 - 应该都走主库
        @transactional
        class transactiontest {
            public void test() {
                order order1 = orderservice.generatetestorder(userid);
                orderservice.save(order1);  // 写 - 主库
                order query = orderservice.getbyid(order1.getorderid());  // 读 - 也走主库（事务内）
                log.info("事务内查询: {}", query != null ? "成功" : "失败");
            }
        }
    }
    /**
     * 完整的读写分离测试
     */
    @test
    public void testcompletereadwritesplitting() {
        log.info("========== 完整读写分离测试 ==========");
        // 测试不同的用户id（奇数和偶数）
        long[] userids = {1l, 2l, 3l, 4l, 5l, 6l};
        for (long userid : userids) {
            log.info("\n--- 测试用户id: {} ---", userid);
            // 1. 插入数据（写操作 -> 主库 ds2）
            order order = orderservice.generatetestorder(userid);
            orderservice.save(order);
            log.info("插入订单 - {}", orderservice.getroutinginfo(order));
            // 2. 根据id查询（读操作 -> 从库）
            order querybyid = orderservice.getbyid(order.getorderid());
            log.info("id查询 - {}", orderservice.getroutinginfo(querybyid));
            // 3. 根据用户id查询列表（读操作 -> 从库）
            list<order> userorders = orderservice.getordersbyuserid(userid);
            log.info("用户查询 - 获取到 {} 条记录", userorders.size());
            if (!userorders.isempty()) {
                log.info("  第一条记录路由: {}",
                        orderservice.getroutinginfo(userorders.get(0)));
            }
            // 4. 更新操作（写操作 -> 主库）
            order.setstatus(2);
            orderservice.updatebyid(order);
            log.info("更新订单状态完成");
            // 5. 再次查询验证更新（读操作 -> 从库，可能存在主从延迟）
            order updated = orderservice.getbyid(order.getorderid());
            log.info("更新后查询 - 状态: {}", updated.getstatus());
        }
    }
    /**
     * 测试主从延迟场景
     */
    @test
    public void testmasterslavedelay() {
        log.info("========== 测试主从延迟场景 ==========");
        long userid = 8l;
        // 1. 插入数据（主库）
        order order = orderservice.generatetestorder(userid);
        orderservice.save(order);
        log.info("数据已插入主库，订单id: {}", order.getorderid());
        // 2. 立即查询（可能因为主从延迟查不到）
        order immediately = orderservice.getbyid(order.getorderid());
        log.info("立即查询结果: {}", immediately != null ? "成功" : "失败（可能主从延迟）");
        // 3. 等待1秒后查询
        try {
            thread.sleep(1000);
        } catch (interruptedexception e) {
            thread.currentthread().interrupt();
        }
        order afterwait = orderservice.getbyid(order.getorderid());
        log.info("等待后查询结果: {}", afterwait != null ? "成功" : "失败");
        // 4. 强制走主库查询（事务内）
        @transactional
        class forcemasterread {
            public order query() {
                return orderservice.getbyid(order.getorderid());
            }
        }
        order forcemaster = new forcemasterread().query();
        log.info("强制主库查询结果: {}", forcemaster != null ? "成功" : "失败");
    }
    /**
     * 测试负载均衡效果
     */
    @test
    public void testloadbalance() {
        log.info("========== 测试从库负载均衡 ==========");
        long userid = 10l;
        // 先插入一些测试数据
        for (int i = 0; i < 5; i++) {
            orderservice.save(orderservice.generatetestorder(userid));
        }
        log.info("开始执行读操作，观察路由分布:");
        // 执行20次查询，观察路由到哪个从库
        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]);
            }
        }
    }
    /**
     * 测试读写分离 - 使用 lambdaquerywrapper 替代 getbyid
     */
    @test
    public void testreadwritesplitting() {
        log.info("========== 测试读写分离 ==========");
        // 1. 写操作 - 应该走主库 ds2
        /*
        //偶数录入
        long userid = 6l;
        order order = orderservice.generatetestorder(userid);
        boolean saved = orderservice.save(order);
        asserttrue("订单保存失败", saved);
        log.info("✅ 写操作完成 - 用户id: {}, 订单号: {}", userid, order.getorderno());*/
        //奇数录入
        /*long userid = 9l;
        order order = orderservice.generatetestorder(userid);
        boolean saved = orderservice.save(order);
        asserttrue("订单保存失败", saved);
        log.info("✅ 写操作完成 - 用户id: {}, 订单号: {}", userid, order.getorderno());*/
        //造数据测试读操作
        //用户id字段userid是偶数在ds0库，是奇数在ds1库
        //下面的userid是偶数所以在ds0库
        /*long userid = 6l;
        order order = new order();
        order.setorderno("ord1772503153629");*/
        //下面的userid是奇数所以在ds1库
        long userid = 9l;
        order order = new order();
        order.setorderno("ord1772507872251");
        // 2. 读操作 - 使用 lambdaquerywrapper 查询(单条读) 下面的在userid是偶数在ds0库是对的
        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("查询到的订单不应为null", retrieved);
        // 获取路由信息（需要确保 retrieved 有 orderid）
        string routinginfo = (retrieved.getorderid() != null) ?
                orderservice.getroutinginfo(retrieved) : "未知路由";
        log.info("✅ 读操作完成 - 订单id: {}, 路由: {}",
                retrieved.getorderid(), routinginfo);
        // 3. 批量读 - 测试负载均衡
        log.info("\n📊 测试从库负载均衡（多次查询）:");
        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));
            }
            //这块只打印出来了1条
            log.info("  第{}次查询 - 获取到{}条记录, 路由: {}",
                    i+1, orders.size(), routeinfo);
        }//for end
        // 4. 更新操作 - 写操作，应该走主库
       /* retrieved.setstatus(1);
        boolean updated = orderservice.updatebyid(retrieved);
        asserttrue("订单更新失败", updated);
        log.info("✅ 更新操作完成 - 订单状态已修改为: 已支付");
        // 5. 再次查询验证更新
        lambdaquerywrapper<order> verifywrapper = new lambdaquerywrapper<>();
        verifywrapper.eq(order::getuserid, userid)
                .eq(order::getorderno, order.getorderno());
        order updatedorder = orderservice.getone(verifywrapper);
        assertnotnull("更新后查询失败", updatedorder);
        assertequals("状态应该已更新", integer.valueof(1), updatedorder.getstatus());
        string verifyroute = (updatedorder.getorderid() != null) ?
                orderservice.getroutinginfo(updatedorder) : "未知路由";
        log.info("✅ 验证查询完成 - 订单状态: {}, 路由: {}",
                updatedorder.getstatus(), verifyroute);*/
    }
    /**
     * 测试不同用户的读写分离
     */
    @test
    public void testreadwritesplittingwithdifferentusers() {
        log.info("========== 测试多用户读写分离 ==========");
        long[] userids = {1l, 2l, 3l, 4l, 5l};
        for (long userid : userids) {
            log.info("\n--- 测试用户id: {} ---", userid);
            // 插入数据
            order order = orderservice.generatetestorder(userid);
            orderservice.save(order);
            // 查询数据
            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("用户{}的数据应分布在: {}, 实际查询到订单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 配置类
 * 解决 shardingsphere + mybatis-plus 集成问题
 */
@configuration
@mapperscan(basepackages = "com.shardingdatabasetable.shardingsphere.mapper", sqlsessionfactoryref = "sqlsessionfactory")
public class mybatisplusconfig {
    @autowired
    @qualifier("shardingdatasource")
    private datasource shardingdatasource;
    /**
     * 创建sqlsessionfactory
     */
    @bean
    public sqlsessionfactory sqlsessionfactory() throws exception {
        mybatissqlsessionfactorybean sqlsessionfactorybean = new mybatissqlsessionfactorybean();
        // 设置数据源为shardingsphere的数据源
        sqlsessionfactorybean.setdatasource(shardingdatasource);
        // 设置mapper.xml文件位置
        resourcepatternresolver resolver = new pathmatchingresourcepatternresolver();
        sqlsessionfactorybean.setmapperlocations(resolver.getresources("classpath*:mapper/**/*.xml"));
        // 设置实体类包路径
        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);
        // 设置mybatis-plus拦截器
        sqlsessionfactorybean.setplugins(mybatisplusinterceptor());
        // 设置全局配置
        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();
    }
    /**
     * 创建sqlsessiontemplate
     */
    @bean
    public sqlsessiontemplate sqlsessiontemplate() throws exception {
        return new sqlsessiontemplate(sqlsessionfactory());
    }
    /**
     * mybatis-plus 拦截器（分页等功能）
     */
    @bean
    public mybatisplusinterceptor mybatisplusinterceptor() {
        mybatisplusinterceptor interceptor = new mybatisplusinterceptor();
        // 添加分页插件
        interceptor.addinnerinterceptor(new paginationinnerinterceptor(dbtype.mysql));
        return interceptor;
    }
}

# 1. 初始化测试数据（每个用户生成5条订单）
curl -x post "http://localhost:8006/api/order/init?count=5"
# 2. 创建单个订单
curl -x post "http://localhost:8006/api/order/create?userid=1"
# 3. 批量创建订单（用户2创建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. 复杂查询（用户3的状态为1的订单）
curl "http://localhost:8006/api/order/query?userid=3&status=1"
# 6. 获取数据分布统计
curl "http://localhost:8006/api/order/distribution"