本文共 6774 字，大约阅读时间需要 22 分钟。

其他网址

简介

        本文实战一下Sharding-JDBC分库分表。依赖：sharding-jdbc-spring-boot-starter4.4.1，mybatis-plus3.4.1。

注意：不要参考官网的文档，官网文档可能没更新，配置根本无效。

实战

建库建表

建库

创建两个库：sharding-jdbc0、sharding-jdbc1

建表

在上边两个库里按下边这个结构创建表，以t_order_0和t_order_1命名。

CREATE TABLE `t_order`  (  `id` bigint(11) NOT NULL AUTO_INCREMENT,  `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(0),  PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

结果

依赖与配置

依赖

    
        
     
      4.0.0
         
             
      
       org.springframework.boot
              
      
       spring-boot-starter-parent
              
      
       2.4.2
              
       
          
         
     
      com.example
         
     
      demo
         
     
      0.0.1-SNAPSHOT
         
     
      demo
         
     
      Demo project for Spring Boot
         
             
      
       1.8
          
         
             
                  
       
        org.springframework.boot
                   
       
        spring-boot-starter-web
               
              
                  
       
        org.springframework.boot
                   
       
        spring-boot-starter-test
                   
       
        test
               
              
                  
       
        org.projectlombok
                   
       
        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.1.1
               
          
         
             
                  
                       
        
         org.springframework.boot
                        
        
         spring-boot-maven-plugin
                    
               
          
     
    

配置

application.yml

server:  port: 9011spring:  application:    name: order  #  datasource:  #    driver-class-name: com.mysql.cj.jdbc.Driver  #    url: jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8  #    username: root  #    password: 222333  profiles:    include: sharding_jdbcmybatis-plus:  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

applicaiton-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%2B8        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%2B8        username: root        password: 222333        type: com.zaxxer.hikari.HikariDataSource    sharding:      tables:        t_order:        #key-generator:        #  column: id        #  type: SNOWFLAKE          actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}          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   # 日志显示SQL

代码

Controller

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.RequestMapping;import org.springframework.web.bind.annotation.RestController;/*http://localhost:9011/order/onlyCreateOrder?userId=1&productId=1&count=10&money=100*/@RestController@RequestMapping("/order")public class OrderController {    @Autowired    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);        int i = 1 / 0;        return "success";    }}

entity

注意：id必须是IdType.INPUT类型。如果是IdType.AUTO，则不会按照预期执行，结果会是：在所有的库表里（本处是：ds0.t_order_0、ds0.t_order_1，ds1.t_order_0、ds1.t_order_1）都会操作（本处是插入数据）。

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)@TableName("t_order")public class Order{    @TableId(value = "id", type = IdType.INPUT)    private Long id;    // 用户id    private Long userId;    // 产品id    private Long productId;    // 数量    private Integer count;    // 金额    private BigDecimal money;    // 订单状态：0：创建中；1：已完结    private Integer status;    @TableField(fill = FieldFill.INSERT)    private LocalDateTime createTime;    // 插入与更新都写此字段。若使用FieldFill.UPDATE，则只更新时写此字段。    @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;    }}

mapper

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
    
      {}
    

测试

正常执行

访问：

结果：

再次访问：

结果：

抛出异常  （支持事务）

访问：

结果：

抛出异常，没有数据插入。

转载地址：http://jstjz.baihongyu.com/