10 案例演示
10.1 需求分析
模拟电商平台商品列表展示功能,每个列表中除了包含商品基本信息 、商品描述外,还包括商品所属的店铺信息。
需要实现的功能:
- 添加商品
- 商品分页查询
- 商品统计
10.2 数据库设计
数据库设计如下,商品信息与店铺信息进行垂直分库设计,分为product_db
(商品库)和store_db
(店铺库);商品信息进行垂直分表,分为商品基础信息表(t_product_info
)和商品描述信息(t_product_descript
),地理区域信息(t_sys_region)作为公共表,冗余在两库中;考虑到商品信息的数据增长性,对 product_db
进行水平水库,分片键使用店铺ID,分片策略采用store_id % 2 +1
,因此商品描述信息对所属店铺ID进行冗余;对商品基本信息表(t_product_info
)和商品描述信息表(t_product_descript
)进行水平分表,分片键使用商品ID,分片策略使用product_id % 2 + 1
,并将这两个表设为绑定表,避免笛卡尔积;
数据库信息:
-- 商品库1
DROP TABLE IF EXISTS `t_product_descript_1`;
CREATE TABLE `t_product_descript_1` (
`id` bigint(20) NOT NULL,
`product_id` bigint(20) NULL DEFAULT NULL,
`miaoshu` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`store_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_descript_2`;
CREATE TABLE `t_product_descript_2` (
`id` bigint(20) NOT NULL,
`product_id` bigint(20) NULL DEFAULT NULL,
`miaoshu` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`store_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_info_1`;
CREATE TABLE `t_product_info_1` (
`id` bigint(20) NOT NULL,
`store` bigint(20) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`guige` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`chandi` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`img` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_info_2`;
CREATE TABLE `t_product_info_2` (
`id` bigint(20) NOT NULL,
`store` bigint(20) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`guige` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`chandi` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`img` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_region`;
CREATE TABLE `t_region` (
`id` bigint(20) NOT NULL,
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`level` int(20) NULL DEFAULT NULL,
`p_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 商品库2
DROP TABLE IF EXISTS `t_product_descript_1`;
CREATE TABLE `t_product_descript_1` (
`id` bigint(20) NOT NULL,
`product_id` bigint(20) NULL DEFAULT NULL,
`miaoshu` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`store_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_descript_2`;
CREATE TABLE `t_product_descript_2` (
`id` bigint(20) NOT NULL,
`product_id` bigint(20) NULL DEFAULT NULL,
`miaoshu` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
`store_id` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_info_1`;
CREATE TABLE `t_product_info_1` (
`id` bigint(20) NOT NULL,
`store` bigint(20) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`guige` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`chandi` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`img` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_product_info_2`;
CREATE TABLE `t_product_info_2` (
`id` bigint(20) NOT NULL,
`store` bigint(20) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`guige` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`chandi` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
`img` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_region`;
CREATE TABLE `t_region` (
`id` bigint(20) NOT NULL,
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`level` int(20) NULL DEFAULT NULL,
`p_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 店铺库
DROP TABLE IF EXISTS `t_region`;
CREATE TABLE `t_region` (
`id` bigint(20) NOT NULL,
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`level` int(20) NULL DEFAULT NULL,
`p_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `t_store`;
CREATE TABLE `t_store` (
`store_id` bigint(20) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`xinyu` int(10) NULL DEFAULT NULL,
`addr` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`store_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
10.3 主从数据库搭建及表建立
参照前面的章节我们自己搭建MySQL的主从环境配置,搭建完成后在主库建立store_db
、product_db_1
、product_db_2
三个数据库,分别执行上述的SQL语句,建立表结构。
10.4 工程搭建
1、参照SpringBoot基础搭建基本框架,导入依赖pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion>
<artifactId>spring-boot-starter-tomcat</artifactId>
<groupId>org.springframework.boot</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-undertow</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.10</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
2、修改配置文件,编辑分片策略
server:
port: 18080
servlet:
context-path: /
undertow:
max-http-post-size: -1
buffer-size: 512
threads:
io: 8
worker: 256
direct-buffers: true
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
type-aliases-package: com.fyy.shardingjdbc.model
mapper-locations: classpath*:mapper/*Mapper.xml
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
#打开sql日志输出
props:
sql:
show: true
datasource:
#配置数据源
names: m1,m2,m3,s1,s2,s3
m1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33068/store_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
m2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33068/product_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
m3:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33068/product_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
s1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33058/store_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
s2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33058/product_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
s3:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:33058/product_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: hntl123@#
sharding:
#配置主从关系
master-slave-rules:
ms1:
masterDataSourceName: m1
slaveDataSourceNames: s1
ms2:
masterDataSourceName: m2
slaveDataSourceNames: s2
ms3:
masterDataSourceName: m3
slaveDataSourceNames: s3
#默认的分库策略,针对水平分库
default-database-strategy:
inline:
sharding-column: store_id
algorithm-expression: ms$->{store_id%2 + 2}
#绑定表配置
binding-tables:
- t_product_info,t_product_descript
#广播表配置
broadcast-tables: t_region
#配置分表策略
tables:
#店铺表策略:没有分库分表
t_store:
actualDataNodes: ms1.t_store
tableStrategy:
inline:
shardingColumn: store_id
algorithmExpression: t_store
#商品基本信息表策略: 分库分表
t_product_info:
actualDataNodes: ms$->{2..3}.t_product_info_$->{1..2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: t_product_info_$->{id%2 + 1}
keyGenerator:
column: id
type: SNOWFLAKE
#商品描述信息表策略: 分库分表,与基础信息表互为绑定表,分片键需要一致
t_product_descript:
actualDataNodes: ms$->{2..3}.t_product_descript_$->{1..2}
tableStrategy:
inline:
shardingColumn: product_id
algorithmExpression: t_product_descript_$->{product_id%2 + 1}
keyGenerator:
column: id
type: SNOWFLAKE
3、编写商品添加代码ProductDao.java
、ProductServiceImpl.java
和TestProduct.java
//ProductDao.java
public interface ProductDao {
@Insert("INSERT INTO t_product_info " +
"(store_id, name, guige, price) values (" +
"#{storeId}," +
"#{name}," +
"#{guige}," +
"#{price}" +
")")
@Options(useGeneratedKeys = true)
int insertProduct(ProductInfo productInfo);
@Insert("INSERT INTO t_product_descript " +
"(product_id, miaoshu, store_id) values (" +
"#{productId}," +
"#{miaoshu}," +
"#{storeId}" +
")")
int insertProductDes(ProductDescript productDescript);
}
//ProductServiceImpl.java
@Service
public class ProductServiceImpl implements IProductService {
@Resource
private ProductDao productDao;
@Override
public int insertProduct(ProductInfo info) {
ProductDescript descript = new ProductDescript();
descript.setMiaoshu(info.getMiaoshu());
productDao.insertProduct(info);
descript.setProductId(info.getId());
descript.setStoreId(info.getStoreId());
productDao.insertProductDes(descript);
return 0;
}
}
//TestProduct.java
@SpringBootTest
public class TestProduct {
@Autowired
private IProductService productService;
@Test
public void testProductAdd(){
ProductInfo info = new ProductInfo();
info.setStoreId(2L);
info.setName("《JAVA编程思想》");
info.setGuige("初级编程");
info.setPrice(new BigDecimal(75.00));
info.setMiaoshu("适合初学编程的同学");
productService.insertProduct(info);
}
}
4、查询商品代码编写
@Select("select p.*,d.miaoshu,r.name diyu " +
"from t_product_info p " +
"left join t_product_descript d ON d.product_id = p.id " +
"LEFT JOIN t_region r ON r.code = p.chandi " +
"ORDER BY p.price DESC " +
"LIMIT #{start},#{pageSize}")
List<ProductInfo> queryProduct(@Param("start")Integer start
,@Param("pageSize")Integer pageSize);
@Override
public List<ProductInfo> queryProduct(Integer start, Integer pageSize) {
return productDao.queryProduct(start,pageSize);
}
@Test
public void testQueryProduct(){
List<ProductInfo> productInfos = productService.queryProduct(0, 2);
for (ProductInfo productInfo : productInfos) {
System.out.println("************************************************");
System.out.println("商品名称:" + productInfo.getName());
System.out.println("商品价格:" + productInfo.getPrice());
System.out.println("商品描述:" + productInfo.getMiaoshu());
System.out.println("商品产地:" + productInfo.getChandi());
}
}
5、统计商品代码编写
@Select("select count(1) from t_product_info")
int getProductTotal();
@Select("select * from t_product_info group by store_id")
List<ProductInfo> queryProduct();
@Override
public int getProductTotal() {
return productDao.getProductTotal();
}
@Override
public List<ProductInfo> queryProduct() {
return productDao.queryProduct();
}
@Test
public void testQueryProductTotal(){
int productTotal = productService.getProductTotal();
System.out.println("商品总数:" + productTotal);
}
@Test
public void testQueryProduct2(){
List<ProductInfo> productInfos = productService.queryProduct();
for (ProductInfo productInfo : productInfos) {
System.out.println("************************************************");
System.out.println("商品名称:" + productInfo.getName());
System.out.println("商品价格:" + productInfo.getPrice());
System.out.println("商品描述:" + productInfo.getMiaoshu());
System.out.println("商品产地:" + productInfo.getChandi());
}
}