shareJDBC+springboot 实现垂直分库水平分表

目标：t_order0/1表数据存放在数据库db0;t_order_item0/1表数据存放在数据库db1中；

建表语句：

数据库db0:
CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `create_at` date DEFAULT NULL,
  `update_at` date DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=888827342141919233 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order0` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `create_at` date DEFAULT NULL,
  `update_at` date DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=888827342141919233 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

数据库db1:
CREATE TABLE `t_order_item0` (
  `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `create_at` date DEFAULT NULL,
  `update_at` date DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=888827342146113538 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_order_item1` (
  `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `create_at` date DEFAULT NULL,
  `update_at` date DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=888827342146113538 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

pom关键依赖：

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>

 

yml：这里我对yml进行了分层，由于有其他要测试的

#垂直分库水平分表
sharding:
  jdbc:
    config:
      sharding:
        props:
          sql.show: true
        tables:
          t_order: #t_user表
            key-generator-column-name: order_id  #主键
            actual-data-nodes: ds0.t_order${0..1}    #垂直分库，t_order只在ds0库中
            table-strategy: #分表策略
              inline: #行表达式
                sharding-column: user_id
                algorithm-expression: t_order${user_id % 2}  #按模运算分配
          t_order_item: #t_user表
            key-generator-column-name: order_item_id  #主键
            actual-data-nodes: ds1.t_order_item${0..1}    #垂直分库，t_order_item只在ds1库中
            table-strategy: #分表策略
              inline: #行表达式
                sharding-column: user_id
                algorithm-expression: t_order_item${user_id % 2}  #按模运算分配

 

 

mybatis:
  mapper-locations: classpath:mapper/*.xml

# 官网 https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/usage/sharding/yaml/
sharding:
  jdbc:
    datasource:
      # 数据源ds0,ds1
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db0?useSSL=false&serverTimezone=UTC
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db1?useSSL=false&serverTimezone=UTC
        username: root
        password: root

#引入yml文件
spring:
  profiles:
    active: vertical

 

 xml中sql编写：

        <insert id="saveOrder" parameterType="com.example.sharejdbctable.dto.OrderDto">
        INSERT INTO t_order(order_id,user_id,status)
        VALUES
        (
            #{order_id},#{user_id},#{status}
        )
    </insert>

        <insert id="saveOrderDetail" parameterType="java.util.ArrayList">
        INSERT INTO t_order_item(order_item_id,order_id,user_id,status)
        VALUES
        <foreach collection="list" item="item" separator=",">
        (#{item.order_item_id},#{item.order_id},#{item.user_id},#{item.status})
        </foreach>
    </insert>

dao层：

    void saveOrder(OrderDto orderDto);

    void saveOrderDetail(@Param("list")List<OrderItem>orderItemList);

service层：

    public void saveOrder(OrderDto orderDto) {
        userMapper.saveOrder(orderDto);
    }

    public  void saveOrderDetail(List orderItemList) {
        userMapper.saveOrderDetail(orderItemList);
    }

controller层：

    @Transactional
    @RequestMapping("/saveOrder")
    public String save1() {
        SnowflakeIdWorker order_id = new SnowflakeIdWorker(1, 3);
        OrderDto orderDto = null;
        List<OrderItem> list = null;
        for (int i = 0; i < 10; i++) {
            list=new ArrayList<>();
            orderDto = new OrderDto();
            SnowflakeIdWorker item_id = new SnowflakeIdWorker(1, 3);
            Long orderId = order_id.nextId();
            orderDto.setOrder_id(orderId);
            orderDto.setUser_id(i);
            orderDto.setStatus("test" + i);
            this.userService.saveOrder(orderDto); //保存订单
            //明细
            OrderItem orderItem = null;
            for (int j = 0; j < 2; j++) {
                orderItem = new OrderItem();
                orderItem.setOrder_id(orderId);
                orderItem.setOrder_item_id(item_id.nextId());
                orderItem.setUser_id(i);
                orderItem.setStatus("itest" + j);
                list.add(orderItem);
            }
            this.userService.saveOrderDetail(list); //保存订单明细
        }
        return "success";
    }

数据库插入的数据：

db0库中只有订单表数据，而且用户id为奇数的在订单表1中，偶数在订单表0中

 

 db1库中只有订单明细表数据，而且用户id为奇数的在订单明细表1中，偶数在订单明细表0中

 

 总结：和预期的结果一致，撒花