1. 首页
  2. 文章中心
  3. sharding-jdbc

多表关联 shardingsphere 实战详细步骤

48 阅读 0 评论
我是靠谱客的博主 成就硬币，最近开发中收集的这篇文章主要介绍多表关联 shardingsphere 实战详细步骤，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

多表关联
  • 项目源码
  • ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
  • mysql 垂直分表实战 | sharding-jdbc | 详细步骤
  • mysql 垂直分库 | sharding-jdbc 实战详细步骤
  • mysql 水平分表（不分库）| shardingsphere 实战详细步骤
  • mysql 水平分表（分库）| sharding-jdbc实战详细步骤
  • 多表关联 shardingsphere 实战详细步骤
  • 广播表 shardingsphere实战详细步骤

1.1数据库规划

mysql3的order_db0上有t_order_0、t_order_1、t_order_item_0、t_order_ item _1

mysql5的order_db1上有t_order_2、t_order_3、t_order_item_2、t_order_ item _3

根据user_id选择是哪台服务器的数据库，在根据order_id选择到具体的哪张表。order表、order_item表的分片规则是一样的。

1.2创建表

1.2.1 mysql3的order_db0

SET NAMES utf8mb4;
 SET FOREIGN_KEY_CHECKS = 0;
 -- ----------------------------
 -- Table structure for t_order_0
 -- ----------------------------
 DROP TABLE IF EXISTS `t_order_0`;
 CREATE TABLE `t_order_0` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  `merchant_id` bigint NULL DEFAULT NULL,
  `order_status_id` int,
  PRIMARY KEY (`order_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
 DROP TABLE IF EXISTS `t_order_1`;
 CREATE TABLE `t_order_1` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  `merchant_id` bigint NULL DEFAULT NULL,
  `order_status_id` int,
  PRIMARY KEY (`order_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
 
 SET FOREIGN_KEY_CHECKS = 1;

 

CREATE TABLE `t_order_item_0` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NULL,
  `user_id` bigint NULL,
  `merchant_id` bigint NULL,
  `amount` decimal(10, 2) NULL,
  PRIMARY KEY (`order_item_id`)
 );
 
 CREATE TABLE `t_order_item_1` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NULL,
  `user_id` bigint NULL,
  `merchant_id` bigint NULL,
  `amount` decimal(10, 2) NULL,
  PRIMARY KEY (`order_item_id`)
 );

###

1.2.2 mysql5的order_db1

SET NAMES utf8mb4;
 SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

 -- Table structure for t_order_2

-- ----------------------------

 DROP TABLE IF EXISTS `t_order_2`;
 CREATE TABLE `t_order_2` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  `merchant_id` bigint NULL DEFAULT NULL,
  `order_status_id` int,
  PRIMARY KEY (`order_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
 DROP TABLE IF EXISTS `t_order_3`;
 CREATE TABLE `t_order_3` (
  `order_id` bigint NOT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  `merchant_id` bigint NULL DEFAULT NULL,
  `order_status_id` int,
  PRIMARY KEY (`order_id`) USING BTREE
 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

 SET FOREIGN_KEY_CHECKS = 1;

 

CREATE TABLE `t_order_item_2` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NULL,
  `user_id` bigint NULL,
  `merchant_id` bigint NULL,
  `amount` decimal(10, 2) NULL,
  PRIMARY KEY (`order_item_id`)
 );
 
 CREATE TABLE `t_order_item_3` (
  `order_item_id` bigint NOT NULL,
  `order_id` bigint NULL,
  `user_id` bigint NULL,
  `merchant_id` bigint NULL,
  `amount` decimal(10, 2) NULL,
  PRIMARY KEY (`order_item_id`)
 );

1.3创建实体类

@TableName("t_order")
 @Data
 @AllArgsConstructor
 @NoArgsConstructor
 public class Order {
   @TableId(type = IdType.ASSIGN_ID)
   private Long orderId;
   private Long userId;
   private Long merchantId;
   private BigDecimal amount; 
   private Integer orderStatusId;
 }

1.4创建mapper

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 import com.example.readwrite.model.Order;
 
 public interface OrderMapper extends BaseMapper<Order> {
 }

 

public interface OrderItemMapper extends BaseMapper<OrderItem> {
 }

1.5配置分片

绑定表

指分片规则一致的一组分片表。 使用绑定表进行多表关联查询时，必须使用分片键进行关联，否则会出现笛卡尔积关联或跨库关联，从而影响查询效率。 例如：t_order 表和 t_order_item 表，均按照 order_id 分片，并且使用 order_id 进行关联，则此两张表互为绑定表关系。 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

# 运行模式
 spring.shardingsphere.mode.type=Memory
 
 # 配置真实数据源
 spring.shardingsphere.datasource.names=order_ds0,order_ds1
 
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.order_ds0.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.order_ds0.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.order_ds0.jdbc-url=jdbc:mysql://mysql3:3306/order_db0
 spring.shardingsphere.datasource.order_ds0.username=root
 spring.shardingsphere.datasource.order_ds0.password=
 
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.order_ds1.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.order_ds1.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.order_ds1.jdbc-url=jdbc:mysql://mysql5:3306/order_db1
 spring.shardingsphere.datasource.order_ds1.username=root
 spring.shardingsphere.datasource.order_ds1.password=
 
 # 标准分片表配置
 # 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。
 # 缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
 spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=order_ds0.t_order_0,order_ds0.t_order_1,order_ds1.t_order_2,order_ds1.t_order_3
 spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=order_ds0.t_order_item_0,order_ds0.t_order_item_1,order_ds1.t_order_item_2,order_ds1.t_order_item_3
 
 # 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
 # 用于单分片键的标准分片场景
 # 分片列名称
 spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=user_id
 # 分片算法名称
 spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=my_inline
 # 分片列名称
 spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-column=user_id
 # 分片算法名称
 spring.shardingsphere.rules.sharding.tables.t_order_item.database-strategy.standard.sharding-algorithm-name=my_inline
 
 # 分表策略
 # 分片列名称
 spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
 # 分片算法名称
 spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=my_split
 # 分片列名称
 spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_id
 # 分片算法名称
 spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=my_split
 
 
 # 分片算法配置
 # 分片算法类型
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_inline.type=INLINE
 # 分片算法属性配置
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_inline.props.algorithm-expression=order_ds$->{ user_id % 2}
 
 # 绑定表规则列表
 spring.shardingsphere.rules.sharding.binding-tables[0]=t_order,t_order_item
 
 # 分片算法配置
 # 分片算法类型
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.type=CLASS_BASED
 # 分片算法属性配置
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.sharding-count=2
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.strategy=STANDARD
 spring.shardingsphere.rules.sharding.sharding-algorithms.my_split.props.algorithmClassName=com.example.readwrite.alg.MyShardingAlgorithm
 
 
 
 
 # 打印SQl
 spring.shardingsphere.props.sql-show=true

1.6测试

1.6.1 插入

@SpringBootTest
 @Slf4j
 public class MultiJoinTest {
 
   @Autowired
   private OrderMapper orderMapper;
 
   @Autowired
   private OrderItemMapper orderItemMapper;
 
   @Test
   public void testInert() {
     for (long i = 0; i < 12; i++) {
       Order order = new Order(null, i + 200, 1L, new BigDecimal(0),1);
       orderMapper.insert(order);
       for (long j = 0; j < 3; j++) {
         OrderItem orderItem = new OrderItem(null, order.getOrderId(), order.getUserId(), order.getMerchantId(), new BigDecimal(2));
         order.setAmount(order.getAmount().add(orderItem.getAmount()));
         orderItemMapper.insert(orderItem);
       }
       Order order1 = new Order();
       order1.setOrderId(order.getOrderId());
       order1.setAmount(order.getAmount());
       orderMapper.updateById(order1);
     }
   }
 
 }

输出
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

1.6.2 关联查询

1.6.2.1 创建实体类

@Data
 public class OrderVo {
   private Long orderId;
   private BigDecimal amount;
 }

1.6.2.2 修改mapper

public interface OrderMapper extends BaseMapper<Order> {
   List<OrderVo> selectAllOrderVo();
 }

1.6.2.3 增加xml

在resources下创建mapper文件夹，在mapper下创建OrderMapper.xml

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lcNt6IW5-1669027659102)(file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg)]

写入

<?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.example.readwrite.mapper.OrderMapper">
 
   <select id="selectAllOrderVo" resultType="com.example.readwrite.model.OrderVo">
     select o.order_id, sum(oi.amount) as amount
     from t_order as o join t_order_item as oi on o.order_id = oi.order_id
     group by o.order_id
   </select>
 
 </mapper>

1.6.2.4 测试

@Test
 public void testJoin() {
   List<OrderVo> orderVos = orderMapper.selectAllOrderVo();
   *log*.info(String.*valueOf*(orderVos));
 }

输出显示，order表与order_item表一一对应。

2022-11-21 12:45:38.731 INFO 16192 --- [      main] ShardingSphere-SQL            : Logic SQL: select o.order_id, sum(oi.amount) as amount

    from t_order as o join t_order_item as oi on o.order_id = oi.order_id

    group by o.order_id

2022-11-21 12:45:38.731 INFO 16192 --- [      main] ShardingSphere-SQL            : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)

2022-11-21 12:45:38.732 INFO 16192 --- [      main] ShardingSphere-SQL            : Actual SQL: order_ds0 ::: select o.order_id, sum(oi.amount) as amount

    from t_order_0 as o join t_order_item_0 as oi on o.order_id = oi.order_id

    group by o.order_id ORDER BY o.order_id ASC 

2022-11-21 12:45:38.732 INFO 16192 --- [      main] ShardingSphere-SQL            : Actual SQL: order_ds0 ::: select o.order_id, sum(oi.amount) as amount

    from t_order_1 as o join t_order_item_1 as oi on o.order_id = oi.order_id

    group by o.order_id ORDER BY o.order_id ASC 

2022-11-21 12:45:38.732 INFO 16192 --- [      main] ShardingSphere-SQL            : Actual SQL: order_ds1 ::: select o.order_id, sum(oi.amount) as amount

    from t_order_2 as o join t_order_item_2 as oi on o.order_id = oi.order_id

    group by o.order_id ORDER BY o.order_id ASC 

2022-11-21 12:45:38.732 INFO 16192 --- [      main] ShardingSphere-SQL            : Actual SQL: order_ds1 ::: select o.order_id, sum(oi.amount) as amount

    from t_order_3 as o join t_order_item_3 as oi on o.order_id = oi.order_id

    group by o.order_id ORDER BY o.order_id ASC 

2022-11-21 12:45:38.849 INFO 16192 --- [      main] com.example.readwrite.MultiJoinTest   : [OrderVo(orderId=1594547494870622210, amount=6.00), OrderVo(orderId=1594547500436463618, amount=6.00), OrderVo(orderId=1594547500566487042, amount=6.00), OrderVo(orderId=1594547500692316161, amount=6.00), OrderVo(orderId=1594547500818145281, amount=6.00), OrderVo(orderId=1594547500943974401, amount=6.00), OrderVo(orderId=1594547501069803522, amount=6.00), OrderVo(orderId=1594547501195632642, amount=6.00), OrderVo(orderId=1594547501258547202, amount=6.00), OrderVo(orderId=1594547501384376323, amount=6.00), OrderVo(orderId=1594547501510205442, amount=6.00), OrderVo(orderId=1594547501636034561, amount=6.00)]

最后

以上就是成就硬币为你收集整理的多表关联 shardingsphere 实战详细步骤的全部内容，希望文章能够帮你解决多表关联 shardingsphere 实战详细步骤所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(32)

相关文章

基于springboot二手交易平台
基于springboot二手交易平台
花3个小时用Java和MySQL手撸一个简易的手机通讯录管理系统来复习JDBC，有兴趣的朋友可以点个赞文件
花3个小时用Java和MySQL手撸一个简易的手机通讯录管理系统来复习JDBC，有兴趣的朋友可以点个赞文件
Sharding-JDBC主子表（绑定表）关联
Sharding-JDBC主子表（绑定表）关联
ShardingSphere水平分片、多表关联、绑定表、广播表
ShardingSphere水平分片、多表关联、绑定表、广播表
多表关联 shardingsphere 实战详细步骤
多表关联 shardingsphere 实战详细步骤
基于ShardingSphere分库分表
基于ShardingSphere分库分表
项目使用Sharding-Sphere分表测试经验总结背景问题
项目使用Sharding-Sphere分表测试经验总结背景问题
mysql-分库分表1、为什么要分库分表2、分库分表设计思路3、分库分表问题4、分库分表步骤5、分库分表工具
mysql-分库分表1、为什么要分库分表2、分库分表设计思路3、分库分表问题4、分库分表步骤5、分库分表工具

评论列表共有 0 条评论

立即
投稿
返回
顶部