当前位置: 首页 > 图灵资讯 > 技术篇> SpringCloud微服务实战——搭建企业级开发框架(二十七):集成多数据源+Seata分布式事务+读写分离+分库分表

SpringCloud微服务实战——搭建企业级开发框架(二十七):集成多数据源+Seata分布式事务+读写分离+分库分表

来源:图灵教育
时间:2023-10-20 17:54:01

 

  读写分离:为了确保数据库产品的稳定性,很多数据库拥有双机热备功能。也就是,第一台数据库服务器,是对外提供增删改业务的生产服务器;第二台数据库服务器,主要进行读的操作。  目前有多种方式实现读写分离,一种是Mycat这种数据库中间件,需要单独部署服务,通过配置来实现读写分离,不侵入到业务代码中;还有一种是dynamic-datasource/shardingsphere-jdbc这种,需要在业务代码引入jar包进行开发。  本框架集成 dynamic-datasource(多数据源+读写分离+分库)+ druid(数据库连接池)+ seata(分布式事务)+ mybatis-plus+shardingsphere-jdbc(分库分表), dynamic-datasource可以实现简单的分库操作,目前还不支持分表。复杂的分库分表需要用到shardingsphere-jdbc,本文参考dynamic-datasource中的实例,模拟用户下单,扣商品库存,扣用户余额操作,初步可分为订单服务+商品服务+用户服务。

一、Seata安装配置

1、我们将服务安装到CentOS环境上,所以这里我们下载tar.gz版本,下载地址:https://github.com/seata/seata/releasesseata-server-1.4.1.tar.gz2、上传到CentOS服务器,执行解压命令

tar -zxvf seata-server-1.4.1.tar.gz

3、下载Seata需要的SQL脚本,新建Seata数据库并将需要使用的数据库脚本seata-1.4.1\seata-1.4.1\script\server\db\mysql.sql刷进去seata数据库4、修改Seata配置文件,将seata服务端的注册中心和配置中心设置为Nacos

vi /bigdata/soft_home/seata/conf/registry.conf
registry {  # file 、nacos 、eureka、redis、zk、consul、etcd3、sofa  type = "nacos"  loadBalance = "RandomLoadBalance"  loadBalanceVirtualNodes = 10  nacos {    application = "seata-server"    serverAddr = "127.0.0.1:8848"    group = "SEATA_GROUP"    namespace = ""    cluster = "default"    username = "nacos"    password = "nacos"  }  eureka {    serviceUrl = "http://localhost:8761/eureka"    application = "default"    weight = "1"  }  redis {    serverAddr = "localhost:6379"    db = 0    password = ""    cluster = "default"    timeout = 0  }  zk {    cluster = "default"    serverAddr = "127.0.0.1:2181"    sessionTimeout = 6000    connectTimeout = 2000    username = ""    password = ""  }  consul {    cluster = "default"    serverAddr = "127.0.0.1:8500"  }  etcd3 {    cluster = "default"    serverAddr = "http://localhost:2379"  }  sofa {    serverAddr = "127.0.0.1:9603"    application = "default"    region = "DEFAULT_ZONE"    datacenter = "DefaultDataCenter"    cluster = "default"    group = "SEATA_GROUP"    addressWaitTime = "3000"  }  file {    name = "file.conf"  }}config {  # file、nacos 、apollo、zk、consul、etcd3  type = "nacos"  nacos {    serverAddr = "127.0.0.1:8848"    namespace = ""    group = "SEATA_GROUP"    username = "nacos"    password = "nacos"  }  consul {    serverAddr = "127.0.0.1:8500"  }  apollo {    appId = "seata-server"    apolloMeta = "http://192.168.1.204:8801"    namespace = "application"    apolloAccesskeySecret = ""  }  zk {    serverAddr = "127.0.0.1:2181"    sessionTimeout = 6000    connectTimeout = 2000    username = ""    password = ""  }  etcd3 {    serverAddr = "http://localhost:2379"  }  file {    name = "file.conf"  }}

5、在Nacos添加Seata配置文件,修改script/config-center/config.txt,将script目录上传到CentOS服务器,执行script/config-center/nacos/nacos-config.sh命令

service.vgroupMapping.gitegg_seata_tx_group=defaultservice.default.grouplist=127.0.0.1:8091store.mode=dbstore.db.url=jdbc:mysql://127.0.0.1:3306/seata?useUnicode=truestore.db.user=rootstore.db.password=root
chmod 777 nacos-config.shsh nacos-config.sh -h 127.0.0.1 -p 8848

设置成功6、在CentOS上进去到Seata安装目录的bin目录执行命令,启动Seata服务端

nohup ./seata-server.sh -h 127.0.0.1 -p 8091 >log.out 2>1 &
如果服务器有多网卡,存在多个ip地址,-h后面一定要加可以访问的ip地址

7、在Nacos上可以看到配置文件和服务已经注册成功配置服务

二、Seata安装成功后,我们需要在微服务中集成Seata客户端

1、因为我们在微服务中使用Seata,所以,我们将Seata客户端的依赖添加在gitegg-plaform-cloud中

        <!-- Seata 分布式事务管理 -->        <dependency>            <groupId>com.alibaba.cloud</groupId>            <artifactId>spring-cloud-starter-alibaba-seata</artifactId>        </dependency>

2、我们这里打算使用多数据源,所以这里也把动态多数据源组件Dynamic Datasource加入到gitegg-plaform-mybatis依赖中

        <!-- 动态数据源 -->        <dependency>            <groupId>com.baomidou</groupId>            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>        </dependency>

3、配置Nacos数据库多数据源及Seata

spring:  datasource:     druid:      stat-view-servlet:        enabled: true        loginUsername: admin        loginPassword: 123456    dynamic:      # 设置默认的数据源或者数据源组,默认值即为master      primary: master      # 设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.      strict: false      # 开启seata代理,开启后默认每个数据源都代理,如果某个不需要代理可单独关闭      seata: true      #支持XA及AT模式,默认AT      seata-mode: AT      druid:        initialSize: 1        minIdle: 3        maxActive: 20        # 配置获取连接等待超时的时间        maxWait: 60000        # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒        timeBetweenEvictionRunsMillis: 60000        # 配置一个连接在池中最小生存的时间,单位是毫秒        minEvictableIdleTimeMillis: 30000        validationQuery: select 'x'        testWhileIdle: true        testOnBorrow: false        testOnReturn: false        # 打开PSCache,并且指定每个连接上PSCache的大小        poolPreparedStatements: true        maxPoolPreparedStatementPerConnectionSize: 20        # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙        filters: config,stat,slf4j        # 通过connectProperties属性来打开mergeSql功能;慢SQL记录        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;        # 合并多个DruidDataSource的监控数据        useGlobalDataSourceStat: true      datasource:         master:           url: jdbc:mysql://127.0.0.1/gitegg_cloud?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai          username: root          password: root        mall_user:          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_user?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai          username: root          password: root        mall_goods:          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_goods?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai          username: root          password: root        mall_order:          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai          username: root          password: root        mall_pay:          url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_pay?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&alowMultiQueries=true&serverTimezone=Asia/Shanghai          username: root          password: rootseata:  enabled: true  application-id: ${spring.application.name}  tx-service-group: gitegg_seata_tx_group  # 一定要是false  enable-auto-data-source-proxy: false  service:    vgroup-mapping:      #key与上面的gitegg_seata_tx_group的值对应      gitegg_seata_tx_group: default  config:    type: nacos    nacos:      namespace:      serverAddr: 127.0.0.1:8848      group: SEATA_GROUP      userName: "nacos"      password: "nacos"  registry:    type: nacos    nacos:      #seata服务端(TC)在nacos中的应用名称      application: seata-server      server-addr: 127.0.0.1:8848      namespace:      userName: "nacos"      password: "nacos"
三、数据库表设计

    这里参考Dynamic Datasource官方提供的示例项目,并结合电商项目数据库设计,新建四个数据库,gitegg_cloud_mall_goods(商品数据库),gitegg_cloud_mall_order(订单数据库),gitegg_cloud_mall_pay(支付数据库),gitegg_cloud_mall_user(账户数据库)四个数据库,下面是具体表结构和简要说明:

1、商品数据库表设计

表设计:

  • 商品分类表:t_mall_goods_category
  • 商品品牌表: t_mall_goods_brand
  • 分类品牌关联关系表:t_mall_goods_category_brand
  • 商品规格参数组表: t_mall_goods_spec_group
  • 商品规格参数表:t_mall_goods_spec_param
  • 商品SPU表: t_mall_goods_spu
  • 商品SPU详情表: t_mall_goods_spu_detail
  • 商品SKU表: t_mall_goods_sku

关系:

  • 一个分类有多个品牌,一个品牌属于多个分类,所以是多对多
  • 一个分类有多个规格组,一个规格组有多个规格参数,所以是一对多
  • 一个分类下有多个SPU,所以是一对多
  • 一个品牌下有多个SPU,所以是一对多
  • 一个SPU下有多个SKU,所以是一对多
DROP TABLE IF EXISTS `t_mall_goods_brand`;CREATE TABLE `t_mall_goods_brand`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '品牌id',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '品牌名称',  `image` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '品牌图片地址',  `letter` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '品牌的首字母',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '品牌表,一个品牌下有多个商品(spu),一对多关系' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_brand_category-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_brand_category`;CREATE TABLE `t_mall_goods_brand_category`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '品牌id',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `brand_id` bigint(20) NOT NULL COMMENT '品牌id',  `category_id` bigint(20) NOT NULL COMMENT '商品类目id',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,  INDEX `key_category_id`(`category_id`) USING BTREE,  INDEX `key_brand_id`(`brand_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品分类和品牌的中间表,两者是多对多关系' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_category-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_category`;CREATE TABLE `t_mall_goods_category`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '类目id',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类目名称',  `parent_id` bigint(20) NOT NULL COMMENT '父类目id,顶级类目填0',  `is_parent` tinyint(2) NOT NULL COMMENT '是否为父节点,0为否,1为是',  `sort` tinyint(2) NOT NULL COMMENT '排序指数,越小越靠前',  `comments` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_parent_id`(`parent_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品类目表,类目和商品(spu)是一对多关系,类目与品牌是多对多关系' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_sku-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_sku`;CREATE TABLE `t_mall_goods_sku`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `spu_id` bigint(20) NOT NULL COMMENT 'spu id',  `title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品标题',  `images` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '商品的图片,多个图片以‘,’分割',  `stock` int(8) UNSIGNED NULL DEFAULT 0 COMMENT '库存',  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '销售价格',  `indexes` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '特有规格属性在spu属性模板中的对应下标组合',  `own_spec` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT 'sku的特有规格参数键值对,json格式,反序列化时请使用linkedHashMap,保证有序',  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否有效,0无效,1有效',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_spu_id`(`spu_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'sku表,该表表示具体的商品实体' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_spec_group-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_spec_group`;CREATE TABLE `t_mall_goods_spec_group`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `category_id` bigint(20) NOT NULL COMMENT '商品分类id,一个分类下有多个规格组',  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '规格组的名称',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,  INDEX `key_category_id`(`category_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '规格参数的分组表,每个商品分类下有多个规格参数组' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_spec_param-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_spec_param`;CREATE TABLE `t_mall_goods_spec_param`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `category_id` bigint(20) NOT NULL COMMENT '商品分类id',  `group_id` bigint(20) NOT NULL COMMENT '所属组的id',  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '参数名',  `numeric` tinyint(1) NOT NULL COMMENT '是否是数字类型参数,true或false',  `unit` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数字类型参数的单位,非数字类型可以为空',  `generic` tinyint(1) NOT NULL COMMENT '是否是sku通用属性,true或false',  `searching` tinyint(1) NOT NULL COMMENT '是否用于搜索过滤,true或false',  `segments` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数值类型参数,如果需要搜索,则添加分段间隔值,如CPU频率间隔:0.5-1.0',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,  INDEX `key_category_id`(`category_id`) USING BTREE,  INDEX `key_group_id`(`group_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '规格参数组下的参数名' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_spu-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_spu`;CREATE TABLE `t_mall_goods_spu`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `brand_id` bigint(20) NOT NULL COMMENT '商品所属品牌id',  `category_id` bigint(20) NOT NULL COMMENT '商品分类id',  `name` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '商品名称',  `sub_title` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '副标题,一般是促销信息',  `on_sale` tinyint(2) NOT NULL DEFAULT 1 COMMENT '是否上架,0下架,1上架',  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '售价',  `use_spec` tinyint(2) NOT NULL DEFAULT 1 COMMENT '是否使用规格:0=不使用,1=使用',  `spec_groups` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品规格组',  `goods_stock` int(11) NOT NULL DEFAULT 0 COMMENT '商品库存',  `virtual_sales` int(11) NOT NULL DEFAULT 0 COMMENT '虚拟销售数量',  `confine_count` int(11) NOT NULL DEFAULT -1 COMMENT '购物数量限制',  `pieces` int(11) NOT NULL DEFAULT 0 COMMENT '满件包邮',  `forehead` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '满额包邮',  `freight_id` int(11) NOT NULL COMMENT '运费模板ID',  `give_integral` int(11) NOT NULL DEFAULT 0 COMMENT '赠送积分',  `give_integral_type` tinyint(2) NOT NULL DEFAULT 1 COMMENT '赠送积分类型1固定值 2百分比',  `deductible_integral` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '可抵扣积分',  `deductible_integral_type` tinyint(2) NOT NULL DEFAULT 1 COMMENT '可抵扣积分类型1固定值 2百分比',  `accumulative` tinyint(2) NOT NULL DEFAULT 0 COMMENT '允许多件累计折扣 0否 1是',  `inpidual_share` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否单独分销设置:0否 1是',  `share_setting_type` tinyint(2) NOT NULL DEFAULT 0 COMMENT '分销设置类型 0普通设置 1详细设置',  `share_commission_type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '佣金配比 0 固定金额 1 百分比',  `membership_price` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否享受会员价购买',  `membership_price_single` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否单独设置会员价',  `share_image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '自定义分享图片',  `share_title` varchar(65) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '自定义分享标题',  `is_default_services` tinyint(2) NOT NULL DEFAULT 1 COMMENT '默认服务 0否  1是',  `sort` int(11) NOT NULL DEFAULT 100 COMMENT '排序',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_tenant_id`(`tenant_id`) USING BTREE,  INDEX `key_category_id`(`category_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'spu表,该表描述的是一个抽象性的商品' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_goods_spu_detail-- ----------------------------DROP TABLE IF EXISTS `t_mall_goods_spu_detail`;CREATE TABLE `t_mall_goods_spu_detail`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `spu_id` bigint(20) NOT NULL,  `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述信息',  `generic_spec` varchar(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '通用规格参数数据',  `special_spec` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '特有规格参数及可选值信息,json格式',  `packing_list` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '包装清单',  `after_service` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '售后服务',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `key_tenant_id`(`tenant_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for undo_log-- ----------------------------DROP TABLE IF EXISTS `undo_log`;CREATE TABLE `undo_log`  (  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',  `rollback_info` longblob NOT NULL COMMENT 'rollback info',  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
2、订单数据库表设计
-- ------------------------------ Table structure for t_mall_order-- ----------------------------DROP TABLE IF EXISTS `t_mall_order`;CREATE TABLE `t_mall_order`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `user_id` bigint(20) NOT NULL COMMENT '主键',  `store_id` int(11) NOT NULL DEFAULT 0 COMMENT '店铺id',  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '订单号',  `total_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '订单总金额(含运费)',  `total_pay_price` decimal(10, 2) NOT NULL COMMENT '实际支付总费用(含运费)',  `express_original_price` decimal(10, 2) NOT NULL COMMENT '运费',  `express_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '修改后运费',  `total_goods_original_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '订单商品总金额',  `total_goods_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '优惠后订单商品总金额',  `store_discount_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '商家改价优惠',  `member_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '会员优惠价格',  `coupon_id` int(11) NULL DEFAULT NULL COMMENT '优惠券id',  `coupon_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '优惠券优惠金额',  `integral` int(11) NULL DEFAULT NULL COMMENT '使用的积分数量',  `integral_deduction_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '积分抵扣金额',  `name` varchar(65) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人姓名',  `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人手机号',  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '收件人地址',  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户订单备注',  `order_form` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '自定义表单(JSON)',  `leaving_message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '留言',  `store_comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '商家订单备注',  `pay_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否支付:0.未支付 1.已支付',  `pay_type` tinyint(2) NULL DEFAULT 1 COMMENT '支付方式:1.在线支付 2.货到付款 3.余额支付',  `pay_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '支付时间',  `deliver_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否发货:0.未发货 1.已发货',  `deliver_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发货时间',  `express` varchar(65) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '物流公司',  `express_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '物流订单号',  `confirm_receipt` tinyint(2) NULL DEFAULT 0 COMMENT '收货状态:0.未收货 1.已收货',  `confirm_receipt_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '确认收货时间',  `cancel_status` tinyint(2) NULL DEFAULT 0 COMMENT '订单取消状态:0.未取消 1.已取消 2.申请取消',  `cancel_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单取消时间',  `recycle_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否加入回收站 0.否 1.是',  `offline` tinyint(2) NULL DEFAULT 0 COMMENT '是否到店自提:0.否 1.是',  `offline_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '核销码',  `verifier_id` int(11) NULL DEFAULT 0 COMMENT '核销员ID',  `verifier_store_id` int(11) NULL DEFAULT 0 COMMENT '自提门店ID',  `support_pay_types` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '支持的支付方式',  `evaluation_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否评价 0.否 1.是',  `evaluation_time` timestamp(0) NULL DEFAULT '0000-00-00 00:00:00',  `after_sales_out` tinyint(2) NULL DEFAULT 0 COMMENT '是否过售后时间 0.否 1.是',  `after_sales_status` tinyint(2) NULL DEFAULT 0 COMMENT '是否申请售后 0.否 1.是',  `status` tinyint(2) NULL DEFAULT 1 COMMENT '订单状态 1.已完成 0.进行中',  `auto_cancel_time` timestamp(0) NULL DEFAULT NULL COMMENT '自动取消时间',  `auto_confirm_verifier_time` timestamp(0) NULL DEFAULT NULL COMMENT '自动确认收货时间',  `auto_after_sales_time` timestamp(0) NULL DEFAULT NULL COMMENT '自动售后时间',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE,  INDEX `INDEX_STORE_ID`(`store_id`) USING BTREE,  INDEX `INDEX_ORDER_NO`(`order_no`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_order_sku-- ----------------------------DROP TABLE IF EXISTS `t_mall_order_sku`;CREATE TABLE `t_mall_order_sku`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `order_id` bigint(20) NOT NULL COMMENT '订单id',  `goods_sku_id` bigint(20) NULL DEFAULT NULL COMMENT '购买商品id',  `goods_sku_number` int(11) NULL DEFAULT NULL COMMENT '购买商品数量',  `goods_sku_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品单价',  `total_original_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '商品总价',  `total_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '优惠后商品总价',  `member_discount_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '会员优惠金额',  `store_discount_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '商家改价优惠',  `goods_sku_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '购买商品信息',  `refund_status` tinyint(1) NULL DEFAULT 0 COMMENT '是否退款',  `after_sales_status` tinyint(1) NULL DEFAULT 0 COMMENT '售后状态 0--未售后 1--售后中 2--售后结束',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,  INDEX `INDEX_ORDER_ID`(`order_id`) USING BTREE,  INDEX `INDEX_GOODS_SKU_ID`(`goods_sku_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for undo_log-- ----------------------------DROP TABLE IF EXISTS `undo_log`;CREATE TABLE `undo_log`  (  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',  `rollback_info` longblob NOT NULL COMMENT 'rollback info',  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
3、支付数据库表设计
DROP TABLE IF EXISTS `t_mall_pay_record`;CREATE TABLE `t_mall_pay_record`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `user_id` bigint(20) NOT NULL COMMENT '用户id',  `order_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0',  `amount` decimal(9, 2) NOT NULL,  `pay_status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '支付状态:0=未支付,1=已支付, 2=已退款',  `pay_type` tinyint(2) NOT NULL DEFAULT 3 COMMENT '支付方式:1=微信支付,2=货到付款,3=余额支付,4=支付宝支付,  5=银行卡支付',  `title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '',  `refund` decimal(9, 2) NOT NULL DEFAULT 0.00 COMMENT '已退款金额',  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for undo_log-- ----------------------------DROP TABLE IF EXISTS `undo_log`;CREATE TABLE `undo_log`  (  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',  `rollback_info` longblob NOT NULL COMMENT 'rollback info',  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
4、账户数据库表设计
-- ------------------------------ Table structure for t_mall_user_account-- ----------------------------DROP TABLE IF EXISTS `t_mall_user_account`;CREATE TABLE `t_mall_user_account`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `user_id` bigint(20) NOT NULL COMMENT '用户id',  `integral` bigint(20) NOT NULL DEFAULT 0 COMMENT '积分',  `balance` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '余额',  `account_status` tinyint(2) NULL DEFAULT 1 COMMENT '账户状态 \'0\'禁用,\'1\' 启用',  `comments` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户账户表' ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for t_mall_user_balance_record-- ----------------------------DROP TABLE IF EXISTS `t_mall_user_balance_record`;CREATE TABLE `t_mall_user_balance_record`  (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户id',  `user_id` bigint(20) NOT NULL COMMENT '用户id',  `type` tinyint(2) NOT NULL COMMENT '类型:1=收入,2=支出',  `amount` decimal(10, 2) NOT NULL COMMENT '变动金额',  `comments` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注',  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',  `operator` bigint(20) NULL DEFAULT NULL COMMENT '更新者',  `del_flag` tinyint(2) NULL DEFAULT 0 COMMENT '1:删除 0:不删除',  PRIMARY KEY (`id`) USING BTREE,  INDEX `INDEX_TENANT_ID`(`tenant_id`) USING BTREE,  INDEX `INDEX_USER_ID`(`user_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Table structure for undo_log-- ----------------------------DROP TABLE IF EXISTS `undo_log`;CREATE TABLE `undo_log`  (  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',  `rollback_info` longblob NOT NULL COMMENT 'rollback info',  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
5、上面的脚本中,每个数据都需要刷入了Seata分布式事务回滚需要的表脚本,在下载Seata包的seata-1.4.1\seata-1.4.1\script\client\at\db路径下
-- ------------------------------ Table structure for undo_log-- ----------------------------DROP TABLE IF EXISTS `undo_log`;CREATE TABLE `undo_log`  (  `branch_id` bigint(20) NOT NULL COMMENT 'branch transaction id',  `xid` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'global transaction id',  `context` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'undo_log context,such as serialization',  `rollback_info` longblob NOT NULL COMMENT 'rollback info',  `log_status` int(11) NOT NULL COMMENT '0:normal status,1:defense status',  `log_created` datetime(6) NOT NULL COMMENT 'create datetime',  `log_modified` datetime(6) NOT NULL COMMENT 'modify datetime',  UNIQUE INDEX `ux_undo_log`(`xid`, `branch_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'AT transaction mode undo table' ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
四、测试代码

  在GitEgg-Cloud工程下,新建gitegg-mall和gitegg-mall-client子工程,client子工程用于fegin调用1、订单服务

    @DS("mall_order")//每一层都需要使用多数据源注解切换所选择的数据库    @Transactional(propagation = Propagation.REQUIRES_NEW)    @GlobalTransactional //重点 第一个开启事务的需要添加seata全局事务注解    @Override    public void order(List<OrderSkuDTO> orderSkuList, Long userId) {        //获取商品的详细信息        Result<Object> goodsSkuResult = mallGoodsFeign.queryByIds(orderSkuList.stream()                .map(OrderSkuDTO::getGoodsSkuId)                .collect(Collectors.toList()));        List<Object> resultSkuList = (List<Object>) goodsSkuResult.getData();        List<GoodsSkuDTO> goodsSkuList = new ArrayList<>();        if(CollectionUtils.isEmpty(resultSkuList) || resultSkuList.size() != orderSkuList.size()) {            throw new BusinessException("商品不存在");        }        else {            resultSkuList.stream().forEach(goodsSku -> {                GoodsSkuDTO goodsSkuDTO = BeanUtil.fillBeanWithMap((Map<?, ?>) goodsSku, new GoodsSkuDTO(), false);                goodsSkuList.add(goodsSkuDTO);            });        }        //扣商品库存        List<ReduceStockDTO> reduceStockDtoList = orderSkuList.stream()                .map(t -> new ReduceStockDTO(t.getGoodsSkuId(),t.getGoodsSkuNumber()))                .collect(Collectors.toList());        mallGoodsFeign.reduceStock(reduceStockDtoList);//        //支付        BigDecimal totalMoney = new BigDecimal(0.0d);        for(OrderSkuDTO orderSkuDTO: orderSkuList) {            for(GoodsSkuDTO goodsSkuDTO: goodsSkuList) {                if(orderSkuDTO.getGoodsSkuId().equals(goodsSkuDTO.getId())) {                    BigDecimal skuNumber = new BigDecimal(orderSkuDTO.getGoodsSkuNumber());                    totalMoney = totalMoney.add(goodsSkuDTO.getPrice().multiply(skuNumber));                    break;                }            }        }        mallPayFeign.pay(userId, totalMoney);        //主订单表插入数据        Order order = new Order();        order.setTotalPrice(totalMoney);        order.setTotalPayPrice(totalMoney);        order.setExpressOriginalPrice(totalMoney);        order.setStatus(1);        order.setUserId(userId);        this.save(order);        //子订单表插入数据        ArrayList<OrderSku> orderSkus = new ArrayList<>();        orderSkuList.forEach(payOrderReq -> {            OrderSku orderSku = new OrderSku();            orderSku.setOrderId(order.getId());            orderSku.setGoodsSkuNumber(payOrderReq.getGoodsSkuNumber());            orderSku.setGoodsSkuId(payOrderReq.getGoodsSkuId());            for(GoodsSkuDTO goodsSkuDTO : goodsSkuList) {                if(payOrderReq.getGoodsSkuId().equals(goodsSkuDTO.getId())) {                    orderSku.setGoodsSkuPrice(goodsSkuDTO.getPrice());                    break;                }            }            orderSkus.add(orderSku);        });        orderSkuService.saveBatch(orderSkus);    }

2、商品服务

    @DS("mall_goods")    @Override    public List<GoodsSku> queryGoodsByIds(List<Long> idList) {        return goodsSkuMapper.queryGoodsByIds(idList);    }    /**     * 事务传播特性设置为 REQUIRES_NEW 开启新的事务  重要!!!!一定要使用REQUIRES_NEW     */    @DS("mall_goods")    @Transactional(propagation = Propagation.REQUIRES_NEW)    @Override    public void reduceStock(List<ReduceStockDTO> reduceStockReqList) {        reduceStockReqList.forEach(sku -> {            Integer line = goodsSkuMapper.reduceStock(sku.getNumber(), sku.getSkuId());            if(line == null || line == 0) {                throw new BusinessException("商品不存在或库存不足");            }        });    }

3、支付服务

    /**     * 事务传播特性设置为 REQUIRES_NEW 开启新的事务    重要!!!!一定要使用REQUIRES_NEW     */    @DS("mall_pay")    @Transactional(propagation = Propagation.REQUIRES_NEW)    @Override    public Long pay(Long userId, BigDecimal payMoney) {        //调用gitegg-mall-user的账户扣除余额接口        mallUserFeign.accountDeduction(userId, payMoney);        // 插入支付记录表        PayRecord payRecord = new PayRecord();        payRecord.setUserId(userId);        payRecord.setAmount(payMoney);        payRecord.setPayStatus(GitEggConstant.Number.ONE);        payRecord.setPayType(GitEggConstant.Number.FIVE);        payRecordService.save(payRecord);        return payRecord.getId();    }

4、账户服务

    /**     * 事务传播特性设置为 REQUIRES_NEW 开启新的事务    重要!!!!一定要使用REQUIRES_NEW     */    @DS("mall_user")    @Transactional(propagation = Propagation.REQUIRES_NEW)    @Override    public void deduction(Long userId, BigDecimal amountOfMoney) {        //查看账户余额是否满足扣款        QueryUserAccountDTO queryUserAccountDTO = new QueryUserAccountDTO();        queryUserAccountDTO.setUserId(userId);        UserAccountDTO userAccount = this.queryUserAccount(queryUserAccountDTO);        if(userAccount == null) {            throw new BusinessException("用户未开通个人账户");        }        if(amountOfMoney.compareTo(userAccount.getBalance()) > GitEggConstant.Number.ZERO) {            throw new BusinessException("账户余额不足");        }        //执行扣款        userAccountMapper.deductionById(userAccount.getId(), amountOfMoney);        //加入账户变动记录        UserBalanceRecord userBalanceRecord = new UserBalanceRecord();        userBalanceRecord.setUserId(userId);        userBalanceRecord.setAmount(amountOfMoney);        userBalanceRecord.setType(GitEggConstant.Number.TWO);        userBalanceRecordService.save(userBalanceRecord);    }

5、使用Postman测试,发送请求,然后查看数据库是否都增加了数据,正常情况下,几个数据库的表都有新增或更新请求头请求参数6、测试异常情况,在代码中抛出异常,然后进行debug,查看在异常之前数据库数据是否入库,异常之后,入库数据是否已回滚。同时可观察undo_log表的数据情况。

#### 在订单服务中添加throw new BusinessException("测试异常回滚");
五、整合数据库分库分表

  首先在我们整合dynamic-datasource和shardingsphere-JDBC之前,需要了解它们的异同点:dynamic-datasource从字面意思可以看出,它是动态多数据源,其主要功能是支持多数据源及数据源动态切换不支持数据分片,shardingsphere-jdbc主要功能是数据分片、读写分离,当然也支持多数据源,但是到目前为止如果要支持多数据源动态切换的话,需要自己实现,所以,这里结合两者的优势,使用dynamic-datasource的动态多数据源切换+shardingsphere-jdbc的数据分片、读写分离。1、在gitegg-platform-bom和gitegg-platform-db中引入shardingsphere-jdbc的依赖,重新install。(注意这里使用了5.0.0-alpha版本,正式环境请使用最新发布版。)

        <!-- shardingsphere-jdbc -->        <sharding.jdbc.version>5.0.0-alpha</sharding.jdbc.version>            <!-- Shardingsphere-jdbc -->            <dependency>                <groupId>org.apache.shardingsphere</groupId>                <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>                <version>${shardingsphere.jdbc.version}</version>            </dependency>            <dependency>                <groupId>org.apache.shardingsphere</groupId>                <artifactId>shardingsphere-jdbc-core-spring-namespace</artifactId>                <version>${shardingsphere.jdbc.version}</version>            </dependency>

2、在gitegg-platform-db中,新建DynamicDataSourceProviderConfig类,自定义DynamicDataSourceProvider完成与shardingsphere的集成

/** * @author GitEgg * @date 2021-04-23 19:06:51 **/@Configuration@AutoConfigureBefore(DynamicDataSourceAutoConfiguration.class)public class DynamicDataSourceProviderConfig {    @Resource    private DynamicDataSourceProperties properties;    /**     * shardingSphereDataSource     */    @Lazy    @Resource(name = "shardingSphereDataSource")    private DataSource shardingSphereDataSource;    @Bean    public DynamicDataSourceProvider dynamicDataSourceProvider() {        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();        return new AbstractDataSourceProvider() {            @Override            public Map<String, DataSource> loadDataSources() {                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);                dataSourceMap.put("sharding", shardingSphereDataSource);                return dataSourceMap;            }        };    }    /**     * 将动态数据源设置为首选的     * 当spring存在多个数据源时, 自动注入的是首选的对象     * 设置为主要的数据源之后,就可以支持shardingsphere-jdbc原生的配置方式了     */    @Primary    @Bean    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();        dataSource.setPrimary(properties.getPrimary());        dataSource.setStrict(properties.getStrict());        dataSource.setStrategy(properties.getStrategy());        dataSource.setProvider(dynamicDataSourceProvider);        dataSource.setP6spy(properties.getP6spy());        dataSource.setSeata(properties.getSeata());        return dataSource;    }}

3、新建用来分库的数据库表gitegg_cloud_mall_order0和gitegg_cloud_mall_order1,复制gitegg_cloud_mall_order中的表结构。4、在Nacos中分别配置shardingsphere-jdbc和多数据源

  # shardingsphere 配置  shardingsphere:    props:      sql:        show: true    datasource:      common:        type: com.alibaba.druid.pool.DruidDataSource        validationQuery: SELECT 1 FROM DUAL              names: shardingorder0,shardingorder1      shardingorder0:        type: com.alibaba.druid.pool.DruidDataSource        url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order0?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai        username: root        password: root      shardingorder1:        type: com.alibaba.druid.pool.DruidDataSource        url: jdbc:mysql://127.0.0.1/gitegg_cloud_mall_order1?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf8&all owMultiQueries=true&serverTimezone=Asia/Shanghai        username: root        password: root    rules:      sharding:        tables:          t_mall_order:            actual-data-nodes: shardingorder$->{0..1}.t_mall_order$->{0..1}            # 配置分库策略            databaseStrategy:              standard:                shardingColumn: id                shardingAlgorithmName: database-inline            table-strategy:              standard:                sharding-column: id                sharding-algorithm-name: table-inline-order            key-generate-strategy:              column: id              key-generator-name: snowflake          t_mall_order_sku:            actual-data-nodes: shardingorder$->{0..1}.t_mall_order_sku$->{0..1}            # 配置分库策略            databaseStrategy:              standard:                shardingColumn: id                shardingAlgorithmName: database-inline            table-strategy:              standard:                sharding-column: id                sharding-algorithm-name: table-inline-order-sku            key-generate-strategy:              column: id              key-generator-name: snowflake        sharding-algorithms:          database-inline:            type: INLINE            props:              algorithm-expression: shardingorder$->{id % 2}          table-inline-order:            type: INLINE            props:              algorithm-expression: t_mall_order$->{id % 2}          table-inline-order-sku:            type: INLINE            props:              algorithm-expression: t_mall_order_sku$->{id % 2}        key-generators:          snowflake:            type: SNOWFLAKE            props:              worker-id: 123

5、修改OrderServiceImpl.java的下单方法order注解,数据源选择sharding

    @DS("sharding")//每一层都需要使用多数据源注解切换所选择的数据库    @Transactional(propagation = Propagation.REQUIRES_NEW)    @GlobalTransactional //重点 第一个开启事务的需要添加seata全局事务注解    @Override    public void order(List<OrderSkuDTO> orderSkuList, Long userId) {       ......    }

6、postman模拟测试调用下单接口,观察数据库gitegg_cloud_mall_order0和gitegg_cloud_mall_order1里面的order表数据变化,我们发现,数据记录根据id取余存放到对应的库和表。这里的配置是使用order表的id,在实际生产环境中,需要根据实际情况来选择合适的分库分表策略。订单数据根据分库分表策略存储订单数据根据分库分表策略存储7、测试引入shardingsphere-jdbc后分布式事务是否正常,在OrderServiceImpl.java的下单方法order中的最后主动抛出异常,saveBatch之后打断点,使用postman模拟测试调用下单接口,到达断点时,查看数据是否入库,放开断点,抛出异常,然后再查看数据是否被回滚。

        orderSkuService.saveBatch(orderSkus);        throw new BusinessException("测试异常");

断点gitegg-mall-pay最新数据记录已入库抛出异常后gitegg-mall-pay入库数据被回滚

备注:1、sharding-jdbc启动时报错java.sql.SQLFeatureNotSupportedException: isValid解决: 这个是4.x版本的问题,官方会在5.x结局这个问题,目前解决方案是关闭sql健康检查。