在项目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> <!--mybatis plus和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}表结构
CREATE TABLE `product_order_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `out_trade_no` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单唯一标识', `state` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单', `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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
数据库表结构图
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测试添加订单数据
@SpringBootTestclass 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); } }}插入的订单数据
表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