基于springboot的ShardingSphere5.X的分库分表的解决方案之关联查询解决方案（三）

Love The Way You Lie 2023-01-01 01:50 314阅读 0赞

# 基于springboot的ShardingSphere5.X的分库分表的解决方案之关联查询解决方案（三） #

> 在前面两章已经讲解了分库分表的解决方案了，但是前面的两个章节的分库分表的解决方案都是基于单表的，那么我们在开发中的场景是复杂的，不可能数据的增删改查都是单表操作的，很多时候涉及的都是多表的关联操作，那么在这个章节将为大家讲解多表关联查询的解决方案。

# 1、数据库设计 #

在前面我们设计了其中的一张订单表了，那么在本章我们将涉及另外一张订单明细表，两者之间通过order\_id产生关联关系，如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center]

创建订单明细表的sql语句如下：

drop table if exists t_order_item0;
    
    /*==============================================================*/
    /* Table: t_order_item0 */
    /*==============================================================*/
    create table t_order_item0
    (
       order_item_id        bigint not null comment '订单明细流水ID',
       user_id              bigint comment '用户流水ID',
       order_id             bigint comment '订单流水ID',
       seller_id            bigint comment '商家流水ID',
       create_time          date comment '创建时间',
       primary key (order_item_id)
    );
    
    alter table t_order_item0 comment '订单明细';
    
    drop table if exists t_order_item1;
    
    /*==============================================================*/
    /* Table: t_order_item1 */
    /*==============================================================*/
    create table t_order_item1
    (
       order_item_id        bigint not null comment '订单明细流水ID',
       user_id              bigint comment '用户流水ID',
       order_id             bigint comment '订单流水ID',
       seller_id            bigint comment '商家流水ID',
       create_time          date comment '创建时间',
       primary key (order_item_id)
    );
    
    alter table t_order_item1 comment '订单明细';
    
    drop table if exists t_order_item2;
    
    /*==============================================================*/
    /* Table: t_order_item2 */
    /*==============================================================*/
    create table t_order_item2
    (
       order_item_id        bigint not null comment '订单明细流水ID',
       user_id              bigint comment '用户流水ID',
       order_id             bigint comment '订单流水ID',
       seller_id            bigint comment '商家流水ID',
       create_time          date comment '创建时间',
       primary key (order_item_id)
    );
    
    alter table t_order_item2 comment '订单明细';
    
    drop table if exists t_order_item3;
    
    /*==============================================================*/
    /* Table: t_order_item3 */
    /*==============================================================*/
    create table t_order_item3
    (
       order_item_id        bigint not null comment '订单明细流水ID',
       user_id              bigint comment '用户流水ID',
       order_id             bigint comment '订单流水ID',
       seller_id            bigint comment '商家流水ID',
       create_time          date comment '创建时间',
       primary key (order_item_id)
    );
    
    alter table t_order_item3 comment '订单明细';

# 2、执行SQL脚本 #

直接打开Navicat for MySQL数据库管理工具，然后分别在db0和db1中执行以上的数据库脚本，执行完成以后如下所示：

![在这里插入图片描述][20201230152255724.png_pic_center]

# 3、编写代码 #

在此处和我们平时基于mybatis编写传统代码的方式是一致的没有太大的区别，通过这个特性我们就可以很明显的感觉到ShardingSphere的优势了。

## 3.1、编写订单明细实体 ##

在entity包底下创建一个OrderItem的实体，代码如下所示：

package com.mysql.sharding.demo.entity;
    
    import javax.persistence.Column;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import java.util.Date;
    
    /** * @author linzf * @since 2020/12/14 * 类描述： */
    @Table(name = "t_order_item")
    public class OrderItem { 
    
        /** * 订单明细流水ID */
        @Id
        @Column(name = "order_item_id")
        private Long orderItemId;
    
        /** * 订单流水ID */
        @Column(name = "order_id")
        private Long orderId;
    
    
        /** * 用户流水ID */
        @Column(name = "user_Id")
        private Long userId;
    
        /** * 卖家流水ID */
        @Column(name = "seller_id")
        private Long sellerId;
    
        /** * 订单创建时间 */
        @Column(name = "create_time")
        private Date createTime;
    
        public Date getCreateTime() { 
            return createTime;
        }
    
        public void setCreateTime(Date createTime) { 
            this.createTime = createTime;
        }
    
        public Long getOrderItemId() { 
            return orderItemId;
        }
    
        public void setOrderItemId(Long orderItemId) { 
            this.orderItemId = orderItemId;
        }
    
        public Long getOrderId() { 
            return orderId;
        }
    
        public void setOrderId(Long orderId) { 
            this.orderId = orderId;
        }
    
        public Long getUserId() { 
            return userId;
        }
    
        public void setUserId(Long userId) { 
            this.userId = userId;
        }
    
        public Long getSellerId() { 
            return sellerId;
        }
    
        public void setSellerId(Long sellerId) { 
            this.sellerId = sellerId;
        }
    }

## 3.2、编写订单明细的dao ##

在dao包底下创建一个OrderItemDao的接口，代码如下所示：

package com.mysql.sharding.demo.dao;
    
    import com.mysql.sharding.demo.entity.OrderItem;
    import tk.mybatis.mapper.common.Mapper;
    
    /** * @author linzef * @since 2020-12-14 * 类描述： 订单明细的dao */
    public interface OrderItemDao extends Mapper<OrderItem> { 
    }

## 3.3、编写dao的xml ##

接着在resource/mybatis/mapper文件夹底下创建OrderItemDao.xml代码如下所示：

## 3.4、改造Order实体 ##

在改造我们的OrderService之前我们需要改造我们的Order实体，改造完成以后如下所示：

package com.mysql.sharding.demo.entity;
    
    import javax.persistence.Column;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import javax.persistence.Transient;
    import java.util.Date;
    import java.util.List;
    
    /** * @author linzef * @since 2020-12-12 * 类描述： 订单表 */
    @Table(name = "t_order")
    public class Order { 
    
        /** * 订单流水ID */
        @Id
        @Column(name = "order_id")
        private Long orderId;
    
        /** * 用户流水ID */
        @Column(name = "user_Id")
        private Long userId;
    
        /** * 订单编号 */
        @Column(name = "order_no")
        private String orderNo;
    
    
        /** * 订单创建时间 */
        @Column(name = "create_time")
        private Date createTime;
    
        /** * 订单流水明细 */
        @Transient
        private List<OrderItem> orderItemList;
    
        public List<OrderItem> getOrderItemList() { 
            return orderItemList;
        }
    
        public void setOrderItemList(List<OrderItem> orderItemList) { 
            this.orderItemList = orderItemList;
        }
    
    
        public Long getOrderId() { 
            return orderId;
        }
    
        public void setOrderId(Long orderId) { 
            this.orderId = orderId;
        }
    
        public Long getUserId() { 
            return userId;
        }
    
        public void setUserId(Long userId) { 
            this.userId = userId;
        }
    
        public String getOrderNo() { 
            return orderNo;
        }
    
        public void setOrderNo(String orderNo) { 
            this.orderNo = orderNo;
        }
    
        public Date getCreateTime() { 
            return createTime;
        }
    
        public void setCreateTime(Date createTime) { 
            this.createTime = createTime;
        }
    }

## 3.5、改造OrderService ##

接着改造我们的OrderService的saveOrder方法，在我们新增订单的时候，同时新增订单明细的数据，代码改造完成以后如下所示：

package com.mysql.sharding.demo.service;
    
    import com.mysql.sharding.demo.dao.OrderDao;
    import com.mysql.sharding.demo.dao.OrderItemDao;
    import com.mysql.sharding.demo.entity.Order;
    import com.mysql.sharding.demo.entity.OrderItem;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    /** * @author linzef * @since 2020-12-12 * 类描述： 订单的service */
    @Service
    @Transactional(rollbackFor = { Exception.class})
    public class OrderService { 
    
        @Autowired
        private OrderDao orderDao;
    
        @Autowired
        private OrderItemDao orderItemDao;
    
        /** * 功能描述： 实现新增订单 * @param order 订单的实体 */
        public void saveOrder(Order order){ 
            orderDao.insertSelective(order);
            for (OrderItem orderItem : order.getOrderItemList()) { 
                orderItemDao.insertSelective(orderItem);
            }
        }
    
        /** * 功能描述： 根据订单ID来获取订单数据 * @param orderId 订单流水ID * @return 返回查询结果 */
        public Order getOrderByOrderId( Long orderId){ 
            return orderDao.selectByPrimaryKey(orderId);
        }
    
    }

## 3.6、配置订单明细的分库分表策略 ##

最后我们打开我们的application.yml添加我们的订单明细的分库分表的策略，修改完成以后的代码如下：

# mybatis的配置
    mybatis:
      config-location: classpath:mybatis/mybatis-config.xml
      mapper-locations: classpath:mybatis/mapper/*.xml
    
    # 分页插件的配置
    pagehelper:
      helperDialect: mysql
      reasonable: true
    
    # 当前工程端口的配置
    server:
      port: 8798
    
    spring:
      shardingsphere:
        # 展示修改以后的sql语句
        props:
          sql-show: true
        datasource:
          common:
            driver-class-name: com.mysql.jdbc.Driver
            type: com.alibaba.druid.pool.DruidDataSource
          names: db0,db1
          db0:
            url: jdbc:mysql://127.0.0.1:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
            username: root
            password: '123456'
          db1:
            url: jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2b8
            username: root
            password: '123456'
        rules:
          sharding:
            key-generators:
              # 此处必须要配置，否则会导致报错，因为shardingsphere-jdbc-core-spring-boot-starter需要加载此项配置，官网的demo例子有错
              snowflake:
                type: SNOWFLAKE
                props:
                  worker-id: 123
            sharding-algorithms:
              order-item-inline:
                type: INLINE
                props:
                  # 此处写法必须是t_order$->{order_id % 4}不能写为t_order${order_id % 4}，yml不认这种写法
                  algorithm-expression: t_order_item$->{order_id % 4}
              # table-inline这个名称大家可以根据自己的表的名称来随便取，当前为分表的逻辑配置
              table-inline:
                # 使用了内置的分片算法-INLINE
                type: INLINE
                props:
                  # 此处写法必须是t_order$->{order_id % 4}不能写为t_order${order_id % 4}，yml不认这种写法
                  algorithm-expression: t_order$->{order_id % 4}
              # database-inline这个名称一样大家可以根据自己的数据库来随便取，当前为分库的逻辑配置
              database-inline:
                type: INLINE
                props:
                  # 此处根据order_id取余来实现指向相应的数据库，例如你的order_id为1则指向db1数据库，order_id为2则指向db0数据库
                  algorithm-expression: db$->{order_id % 2}
            tables:
              t_order_item:
                # 配置t_order_item的分库分表的规则
                actual-data-nodes: db$->{0..1}.t_order_item$->{0..3}
                table-strategy:
                  standard:
                    sharding-column: order_id
                    sharding-algorithm-name: order-item-inline
                  # 配置t_order_item的分库规则
                database-strategy:
                  standard:
                    sharding-column: order_id
                    sharding-algorithm-name: database-inline
              # t_order表的分库分表的策略
              t_order:
                # 配置t_order的分表的规则
                actual-data-nodes: db$->{0..1}.t_order$->{0..3}
                table-strategy:
                  standard:
                    sharding-column: order_id
                    sharding-algorithm-name: table-inline
                # 配置t_order的分库规则
                database-strategy:
                  standard:
                    sharding-column: order_id
                    # 此处使用的就是我们在sharding-algorithms里面定义的规则
                    sharding-algorithm-name: database-inline
        enabled: true

订单明细的分库分表的策略的编写方式和订单的分库分表的编写方式是一致的，没太大的差别，因此大写只要掌握了其中的一种写法，后续的编写方式都差不多。

# 4、测试数据落库 #

最后我们直接启动我们的工程，然后用浏览器打开http://127.0.0.1:8798/swagger-ui.html页面，然后在saveOrder方法中输入如下的数据：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 1]

通过上述的数据我们知道，我们的订单数据或落在db0.t\_order0中，我们的订单明细数据会落在db0.t\_order\_item0中，那这样的数据落点是完全符合我们的数据要求的，但是这会有一个遗留问题，看下大家是否关注到了，那就是我们的订单明细ID的落库规则似乎有点不对，在后续的章节我们将会讲解如何解决这个问题。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 2]

# 5、查询订单和订单明细数据 #

那么我们现在要查询订单和订单明细的数据，我们使用关联查询的语句，我们只需要改造我们的OrderDao.xml和OrderDao以及OrderService方法即可。

## 5.1、OrderDao.xml改造 ##

在OrderDao.xml中增加关联查询的方法，改造完成以后代码如下：

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.mysql.sharding.demo.dao.OrderDao">
        <resultMap id="BaseResultMap" type="com.mysql.sharding.demo.entity.Order">
            <id column="order_id" jdbcType="VARCHAR" property="orderId"/>
            <result column="order_no" jdbcType="VARCHAR" property="orderNo"/>
            <result column="user_Id" jdbcType="VARCHAR" property="userId"/>
            <result column="create_time" jdbcType="DATE" property="createTime"/>
        </resultMap>
    
        <resultMap id="OrderResultMap" type="com.mysql.sharding.demo.entity.Order">
            <id column="order_id" jdbcType="VARCHAR" property="orderId"/>
            <result column="order_no" jdbcType="VARCHAR" property="orderNo"/>
            <result column="user_Id" jdbcType="VARCHAR" property="userId"/>
            <result column="create_time" jdbcType="DATE" property="createTime"/>
            <collection property="orderItemList" ofType="com.mysql.sharding.demo.entity.OrderItem" column="order_id">
                <id column="order_item_id" jdbcType="VARCHAR" property="orderItemId"/>
                <result column="order_id" jdbcType="VARCHAR" property="orderId"/>
                <result column="user_Id" jdbcType="VARCHAR" property="userId"/>
                <result column="seller_id" jdbcType="VARCHAR" property="sellerId"/>
                <result column="create_time" jdbcType="DATE" property="createTime"/>
            </collection>
        </resultMap>
    
        <select id="queryMyOrder" resultMap="OrderResultMap" >
            select t.*,toi.* from t_order t inner join t_order_item toi on t.order_id = toi.order_id where t.user_id = #{userId}
        </select>
    
    </mapper>

## 5.2、OrderDao改造 ##

在OrderDao中我们增加queryMyOrder方法，改造完成以后代码如下所示：

package com.mysql.sharding.demo.dao;
    
    import com.mysql.sharding.demo.entity.Order;
    import org.apache.ibatis.annotations.Param;
    import tk.mybatis.mapper.common.Mapper;
    
    import java.util.List;
    
    
    /** * @author linzef * @since 2020-12-12 * 类描述： 订单的dao */
    public interface OrderDao extends Mapper<Order> { 
    
    
        /** * 功能描述： 根据用户ID来查订单数据 * * @param orderId 订单流水ID * @return 返回查询结果 */
        Order queryMyOrder(@Param("orderId") Integer orderId);
    
    
    }

## 5.3、OrderService改造 ##

OrderService中直接改造getOrderByOrderId方法即可，改造完成以后代码如下所示：

## 5.4、验证查询接口 ##

这时候我们打开我们的swagger的测试页面调用getOrderByOrderId方法，然后输入我们前面的测试数据order\_id的值为20的值，如下所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 3]

然后通过后台我们会看到我们做了四次查询，因为我们还没有设置我们的t\_order和t\_order\_item为关联表，因此查询的时候是1\*4=4次查询的结果的合集。

![在这里插入图片描述][20201230152605493.png_pic_center]

## 5.5、设置两张表的绑定关系 ##

那这时候有没更好的方法来处理这个问题呢，我们很明显已经知道了我们的数据全部都是落在db0.t\_order\_item0这个表中，ShardingSphere已经为我们搞定了那就是将这两张表设置为绑定表，我们只需要在application.yml中增加两者为绑定表的配置即可，如下所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 4]

若你是有多组的绑定关系，那就扩展为binding-tables\[x\]即可。

## 5.6、验证我们的关联查询的最终结果 ##

直接启动程序，然后我们直接调用查询订单数据接口，我们这时候可以看到我们的控制台是否如我们所预料的一样只做了一次查询。  
![在这里插入图片描述][20201230152712116.png_pic_center]  
 完美，果然如我们所预料的一样只做了一次查询，那么到此处我们就完成了我们的关联查询的解决方案的讲解了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/04be88ffa99d4125b6e02ca63303c8ba.png
[20201230152255724.png_pic_center]: /images/20221120/e32ade148bb549b0ba8e81359042d131.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/c2532da3e972428d93c7e0b90dd211b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/7594ba4ad6ed4c88b3d375cb73b2ccea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/8cb79fa4a2c44e0bb5bfa0e892d0f869.png
[20201230152605493.png_pic_center]: /images/20221120/2e7f617a91524ad489c4609010244cb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbnpoZWZlbmc4OQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/4b425c0f70da43719409eb173bd71b68.png
[20201230152712116.png_pic_center]: /images/20221120/771e406889f5498fa3d18ab0b0614b43.png