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

Answer 1

在项目pom.xml添加配置<properties><java.version>8</java.version><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target><mybatisplus.boot.starter.version>3.4.2</mybatisplus.boot.starter.version><sharding-jdbc.version>4.1.1</sharding-jdbc.version><mysql.version>8.0.27</mysql.version></properties>

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-configuration-processor</artifactId><optional>true</optional></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!--mybatisplus和springboot整合--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatisplus.boot.starter.version}</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql.version}</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>${sharding-jdbc.version}</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>

application.properties文件配置spring.application.name=demo-sharding-jdbcserver.port=8080#打印执行的数据库以及语句spring.shardingsphere.props.sql.show=true#配置数据源db0多个数据源逗号隔开例如:ds0,ds1spring.shardingsphere.datasource.names=ds0,ds1#配置数据库1参数spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://192.168.2.66:3306/shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=truespring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=123456#配置数据库2参数spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://192.168.2.66:3306/shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=truespring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=123456#配置分库规则spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2}#配置workidspring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1#id生成策略spring.shardingsphere.sharding.tables.product_order.key-generator.column=idspring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE#配置数据节点spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}#指定product_order表的分片策略，分片策略包括【分片键和分片算法】spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id%2}

表结构CREATETABLE`product_order_1`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`out_trade_no`varchar(64)COLLATEutf8mb4_binDEFAULTNULLCOMMENT'订单唯一标识',`state`varchar(11)COLLATEutf8mb4_binDEFAULTNULLCOMMENT'NEW未支付订单,PAY已经支付订单,CANCEL超时取消订单',`create_time`datetimeDEFAULTNULLCOMMENT'订单生成时间',`pay_amount`decimal(16,2)DEFAULTNULLCOMMENT'订单实际支付价格',`nickname`varchar(64)COLLATEutf8mb4_binDEFAULTNULLCOMMENT'昵称',`user_id`bigint(20)DEFAULTNULLCOMMENT'用户id',PRIMARYKEY(`id`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_binROW_FORMAT=DYNAMIC;

数据库表结构图

ProductOrderMapper 操作类importcom.baomidou.mybatisplus.core.mapper.BaseMapper;importcom.demo.shardingspherejdbcdemo.model.ProductOrderDO;/***订单操作dao类*/publicinterfaceProductOrderMapperextendsBaseMapper<ProductOrderDO>{}

ProductOrderDO 实体类importjava.util.Date;@Data@TableName("product_order")publicclassProductOrderDO{/***订单id*/privateLongid;/***交易流水号*/privateStringoutTradeNo;/***状态*/privateStringstate;/***创建时间*/privateDatecreateTime;/***支付金额*/privateDoublepayAmount;/***用户昵称*/privateStringnickname;/****用户id*/privateLonguserId;}

springboot 启动类添加注解@MapperScan("mapper所在路径")@EnableTransactionManagement

测试添加订单数据

@SpringBootTestclassShardingsphereJdbcDemoApplicationTests{@AutowiredprivateProductOrderMapperproductOrderMapper;@TestvoidonlyProductSaveTest(){//单个库水平分表Randomrandom=newRandom();for(inti=0;i<10;i++){ProductOrderDOproductOrderDO=newProductOrderDO();//productOrderDO.setId(0L);productOrderDO.setOutTradeNo(UUID.randomUUID().toString().replace("-",""));productOrderDO.setState("NEW");productOrderDO.setCreateTime(newDate());productOrderDO.setPayAmount(Double.valueOf(100+i));productOrderDO.setNickname(String.format("用户[%s]",i+""));productOrderDO.setUserId(Long.valueOf(random.nextInt(100)));productOrderMapper.insert(productOrderDO);}}}

插入的订单数据表shop_order_0.product_order_0

表shop_order_0.product_order_1

表shop_order_1.product_order_0

表shop_order_1.product_order_1

原文：https://juejin.cn/post/7095644932219600903