springboot 整合 shardingsphere-jdbc MybatisPlus 行表达式水平分表

创建springboot项目(我这里版本是 2.6.6)

在项目pom.xml添加配置

 	<properties>
        <java.version>8java.version>
        <maven.compiler.source>8maven.compiler.source>
        <maven.compiler.target>8maven.compiler.target>
        <mybatisplus.boot.starter.version>3.4.2mybatisplus.boot.starter.version>
        <sharding-jdbc.version>4.1.1sharding-jdbc.version>
        <mysql.version>8.0.27mysql.version>
    properties>

	<dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-configuration-processorartifactId>
            <optional>trueoptional>
        dependency>
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <optional>trueoptional>
        dependency>

        
        <dependency>
            <groupId>com.baomidougroupId>
            <artifactId>mybatis-plus-boot-starterartifactId>
            <version>${mybatisplus.boot.starter.version}version>
        dependency>

        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>${mysql.version}version>
        dependency>

        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>sharding-jdbc-spring-boot-starterartifactId>
            <version>${sharding-jdbc.version}version>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
    dependencies>

application.properties文件配置

spring.application.name=demo-sharding-jdbc
server.port=8080


# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true

#配置数据源 db0 多个数据源逗号隔开 例如: ds0,ds1
spring.shardingsphere.datasource.names=ds0

# 配置数据库参数
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456



# 配置逻辑表 product_order workid 用于解决分表id重复问题
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

#id生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

#配置数据节点
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}

#配置分片键  分片算法(通过订单id取余)
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}

ProductOrderMapper *** 作类

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.shardingspherejdbcdemo.model.ProductOrderDO;

/**
 * 订单 *** 作dao类
 */
public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {
}

ProductOrderDO 实体类

import java.util.Date;

@Data
@TableName("product_order")
public class ProductOrderDO {
    /**
     * 订单id
     */
    private Long id;
    /**
     * 交易流水号
     */
    private String outTradeNo;
    /**
     * 状态
     */
    private String state;
    /**
     * 创建时间
     */
    private Date createTime;
    /**
     * 支付金额
     */
    private Double payAmount;
    /**
     * 用户昵称
     */
    private String nickname;
    /***
     * 用户id
     */
    private Long userId;

}

springboot 启动类添加注解

@MapperScan("mapper所在路径")
@EnableTransactionManagement

订单表结构sql

CREATE TABLE `product_order_0` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `out_trade_no` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单id',
  `state` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '',
  `create_time` datetime DEFAULT NULL COMMENT '订单创建时间',
  `pay_amount` decimal(16,2) DEFAULT NULL COMMENT '订单实际支付价格',
  `nickname` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1516412560359845891 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

对应表 库结构

测试添加订单数据

@SpringBootTest
class ShardingsphereJdbcDemoApplicationTests {
    @Autowired
    private ProductOrderMapper productOrderMapper;
    @Test
    void onlyProductSaveTest() {
        //单个库 水平分表
        Random random  = new Random();
        for(int i = 0 ; i < 10; i ++){
            ProductOrderDO productOrderDO = new ProductOrderDO();
//            productOrderDO.setId(0L);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().replace("-",""));
            productOrderDO.setState("NEW");
            productOrderDO.setCreateTime(new Date());
            productOrderDO.setPayAmount(Double.valueOf(100+i));
            productOrderDO.setNickname(String.format("用户[%s]",i+""));
            productOrderDO.setUserId(Long.valueOf(random.nextInt(100)));

            productOrderMapper.insert(productOrderDO);
        }
    }

}

插入的订单数据