Sharding-JDBC 分库分表实战

模块说明

本文将实战使用 Sharding-JDBC 进行分库分表，基于以下依赖：

sharding-jdbc-spring-boot-starter 4.4.1

mybatis-plus 3.4.1

注：建议不要参考官方文档，官网文档可能未更新，配置可能无效。

实战步骤

一、数据库准备

1. 创建数据库

创建两个数据库：sharding-jdbc0 和 sharding-jdbc1。

2. 创建表

在两个数据库中创建表 t_order，表结构如下：

CREATE TABLE `t_order` (    `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',    `user_id` bigint(11) NULL DEFAULT NULL COMMENT '用户ID',    `product_id` bigint(11) NULL DEFAULT NULL COMMENT '产品ID',    `count` int(11) NULL DEFAULT NULL COMMENT '数量',    `money` decimal(11, 0) NULL DEFAULT NULL COMMENT '金额',    `status` int(1) NULL DEFAULT NULL COMMENT '订单状态：0：创建中；1：已完结',    `create_time` datetime(0) NULL DEFAULT NULL,    `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,    PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

二、依赖与配置

1. pom.xml


       
        
            
     
      org.springframework.boot
             
     
      spring-boot-starter-web
         
        
            
     
      org.springframework.boot
             
     
      spring-boot-starter-test
             
     
      test
         
        
        
            
     
      org.project.lombok
             
     
      lombok
             
     
      1.18.12
             
     
      provided
         
        
        
            
     
      mysql
             
     
      mysql-connector-java
         
        
        
            
     
      com.baomidou
             
     
      mybatis-plus-boot-starter
             
     
      3.4.1
         
        
        
            
     
      org.apache.shardingsphere
             
     
      sharding-jdbc-spring-boot-starter
             
     
      4.4.1

2. 配置文件

1. `application.yml`

server:    port: 9011spring:    application:        name: order    datasource:        # 数据库配置在 `application-sharding_jdbc.yml`中    profiles:        include: sharding_jdbcmybatis-plus:    configuration:        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2. `application-sharding_jdbc.yml`

spring:    shardingsphere:        datasource:            names: ds0, ds1            ds0:                driver-class-name: com.mysql.cj.jdbc.Driver                jdbc-url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT+8                username: root                password: 222333                type: com.zaxxer.hikari.HikariDataSource            ds1:                driver-class-name: com.mysql.cj.jdbc.Driver                jdbc-url: jdbc:mysql://127.0.0.1:3306/sharding-jdbc1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT+8                username: root                password: 222333                type: com.zaxxer.hikari.HikariDataSource        sharding:            tables:                t_order:                    database-strategy:                        inline:                            sharding-column: id                            algorithm-expression: ds${id % 2}                    table-strategy:                        inline:                            sharding-column: id                            algorithm-expression: t_order_${id % 2}            props:                sql: show: true

代码示例

1. 控制器

package com.example.demo.controller;import com.example.demo.entity.Order;import com.example.demo.service.OrderService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.transaction.annotation.Transactional;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RestController;@RestController("/order")public class OrderController {    @Autowired    private OrderService orderService;    private Long id = 1L;    @PostMapping("onlyCreateOrder")    @Transactional    public String onlyCreateOrder(Order order) {        order.setId(id++);        orderService.save(order);        return "success";    }    @PostMapping("onlyCreateOrderError")    @Transactional    public String onlyCreateOrderError(Order order) {        order.setId(id++);        orderService.save(order);        order.setId(id++);  // 这行会导致事务回滚        orderService.save(order);        throw new RuntimeException("事务异常");        return "success";    }}

2. 实体类

package com.example.demo.entity;import com.baomidou.mybatisplus.annotation.*;import lombok.Data;import lombok.EqualsAndHashCode;import java.math.BigDecimal;import java.time.LocalDateTime;@Data@EqualsAndHashCode(callSuper = false)@Table(name = "t_order")public class Order {    @TableId(type = IdType.INPUT)    private Long id;    private Long userId;    private Long productId;    private Integer count;    private BigDecimal money;    private Integer status;    @TableField(fill = FieldFill.INSERT)    private LocalDateTime createTime;    @TableField(fill = FieldFill.INSERT_UPDATE)    private LocalDateTime updateTime;    public Order(Long id, Long userId, Long productId, Integer count, BigDecimal money, Integer status) {        this.id = id;        this.userId = userId;        this.productId = productId;        this.count = count;        this.money = money;        this.status = status;    }}

3. 模型映射

package com.example.demo.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.demo.entity.Order;import org.springframework.stereotype.Repository;@Repositorypublic interface OrderMapper extends BaseMapper
   
     {}