讀寫分離：為了確保資料庫產品的穩定性，很多資料庫擁有雙機熱備功能。也就是，第一臺資料庫伺服器，是對外提供增刪改業務的生產伺服器；第二臺資料庫伺服器，主要進行讀的操作。
目前有多種方式實現讀寫分離，一種是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/releases
2、上傳到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刷進去
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=default
service.default.grouplist=127.0.0.1:8091
store.mode=db
store.db.url=jdbc:mysql://127.0.0.1:3306/seata?useUnicode=true
store.db.user=root
store.db.password=root
chmod 777 nacos-config.sh
sh 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: root
seata:
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是',
`individual_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("測試異常");
備註：
1、sharding-jdbc啟動時報錯java.sql.SQLFeatureNotSupportedException: isValid
解決： 這個是4.x版本的問題，官方會在5.x結局這個問題，目前解決方案是關閉sql健康檢查。
本文原始碼在https://gitee.com/wmz1930/GitEgg 的chapter-27（未使用shardingsphere-jdbc分庫分表）和chapter-27-shardingsphere-jdbc（使用shardingsphere-jdbc分庫分表）分支。
原始碼地址:
Gitee: https://gitee.com/wmz1930/GitEgg
GitHub: https://github.com/wmz1930/GitEgg