jdbc shadring 扩容_SringBoot集成Sharding-Jdbc 实践

一、数据库规划

192.128.127.129和192.128.127.134

192.128.127.129 创建数据库，数据库名为sharding_test

192.128.127.134 创建数据库，数据库名为sharding_test2

如下图所示

                  

在每个分库下建立一样的分表，用户表，用户地址表，订单表，订单明细表和商品表。同一个用户的用户表数据和订单数据在一个库中。除了商品表不需要分表以外，其他表都需要分表(商品表作为全局表放在每个库里)以提高查询性能。建表语句如下：

SET NAMES utf8mb4;

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

-- Table structure for t_order_0

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

DROP TABLE IF EXISTS `t_order_0`;

CREATE TABLE `t_order_0` (

`order_id` bigint(32) NOT NULL COMMENT '主键',

`order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

`order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',

`order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',

`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',

PRIMARY KEY (`order_id`),

INDEX `idx_order_user_id`(`user_id`),

INDEX `idx_order_order_no`(`order_no`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';

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

-- Table structure for t_order_1

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

DROP TABLE IF EXISTS `t_order_1`;

CREATE TABLE `t_order_1` (

`order_id` bigint(32) NOT NULL COMMENT '主键',

`order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '订单号',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

`order_amount` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',

`order_status` int(4) NOT NULL DEFAULT 1 COMMENT '订单状态，1-进行中，2-已完成，3-已取消',

`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '留言',

PRIMARY KEY (`order_id`),

INDEX `idx_order_user_id`(`user_id`),

INDEX `idx_order_order_no`(`order_no`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表';

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

-- Table structure for t_order_item_0

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

DROP TABLE IF EXISTS `t_order_item_0`;

CREATE TABLE `t_order_item_0` (

`order_item_id` bigint(32) NOT NULL COMMENT '主键',

`order_id` bigint(32) NOT NULL COMMENT '订单id',

`product_id` bigint(32) NOT NULL COMMENT '商品id',

`item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',

`total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',

`total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',

`order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

PRIMARY KEY (`order_item_id`),

INDEX `idx_order_item_order_id`(`order_id`),

INDEX `idx_order_item_user_id`(`user_id`),

INDEX `idx_order_item_product_id`(`product_id`),

INDEX `idx_order_item_order_time`(`order_time`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';

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

-- Table structure for t_order_item_1

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

DROP TABLE IF EXISTS `t_order_item_1`;

CREATE TABLE `t_order_item_1` (

`order_item_id` bigint(32) NOT NULL COMMENT '主键',

`order_id` bigint(32) NOT NULL COMMENT '订单id',

`product_id` bigint(32) NOT NULL COMMENT '商品id',

`item_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '单价',

`total_num` int(4) NOT NULL DEFAULT 1 COMMENT '数量',

`total_price` decimal(20, 2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',

`order_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

PRIMARY KEY (`order_item_id`),

INDEX `idx_order_item_order_id`(`order_id`),

INDEX `idx_order_item_user_id`(`user_id`),

INDEX `idx_order_item_product_id`(`product_id`),

INDEX `idx_order_item_order_time`(`order_time`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单明细表';

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

-- Table structure for t_product

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

DROP TABLE IF EXISTS `t_product`;

CREATE TABLE `t_product` (

`product_id` bigint(32) NOT NULL COMMENT '主键',

`code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品编码',

`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',

`desc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品描述',

PRIMARY KEY (`product_id`),

INDEX `idx_user_product_code`(`code`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '商品表';

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

-- Table structure for t_user_0

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

DROP TABLE IF EXISTS `t_user_0`;

CREATE TABLE `t_user_0` (

`user_id` bigint(32) NOT NULL COMMENT '主键',

`id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',

`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',

`age` int(4) DEFAULT NULL COMMENT '年龄',

`gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',

`birth_date` date DEFAULT NULL COMMENT '出生日期',

PRIMARY KEY (`user_id`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';

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

-- Table structure for t_user_1

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

DROP TABLE IF EXISTS `t_user_1`;

CREATE TABLE `t_user_1` (

`user_id` bigint(32) NOT NULL COMMENT '主键',

`id_number` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份证号码',

`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',

`age` int(4) DEFAULT NULL COMMENT '年龄',

`gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',

`birth_date` date DEFAULT NULL COMMENT '出生日期',

PRIMARY KEY (`user_id`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表';

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

-- Table structure for t_user_address_0

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

DROP TABLE IF EXISTS `t_user_address_0`;

CREATE TABLE `t_user_address_0` (

`address_id` bigint(32) NOT NULL COMMENT '主键',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

`province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',

`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',

`district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',

`detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',

`sort` int(4) DEFAULT 1 COMMENT '排序',

`gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',

PRIMARY KEY (`address_id`),

INDEX `idx_user_address_user_id`(`user_id`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';

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

-- Table structure for t_user_address_1

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

DROP TABLE IF EXISTS `t_user_address_1`;

CREATE TABLE `t_user_address_1` (

`address_id` bigint(32) NOT NULL COMMENT '主键',

`user_id` bigint(32) NOT NULL COMMENT '用户id',

`province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省',

`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市',

`district` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',

`detail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '详细地址',

`sort` int(4) DEFAULT 1 COMMENT '排序',

`gender` int(2) DEFAULT 1 COMMENT '性别：1-男；2-女',

PRIMARY KEY (`address_id`),

INDEX `idx_user_address_user_id`(`user_id`)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址表';

二、工程创建

整个工程结构如下下图所示

1、创建Spring Boot工程

工程名为my-sharding-jdbc-demo

Spring Boot 版本为2.2.11，选择的依赖如下图所示

2、MyBatis-Generator生成相关代码

(1) mapping文件

OrderItemMapping.xml

order_item_id, order_id, product_id, item_price, total_num, total_p