# Sharding-JDBC 从入门到精通（10）- 综合案例（三）查询商品与测试及统计商品和总结

Sharding-JDBC 从入门到精通（10）- 综合案例（三）查询商品与测试及统计商品和总结

一、Sharding-JDBC 综合案例-查询商品-dao

1、查询商品：Dao 实现：在 ProductDao 中定义商品查询方法：

//查询商品@Select("select i.*, d.descript, r.region_name placeOfOrigin " +"from product_info i join product_descript d on i.product_info_id = d.product_info_id " +"join region r on i.region_code = r.region_code order by i.product_info_id desc limit #{start}, #{pageSize}")List<ProductInfo> selectProductList( @Param("start") int start, @Param("pageSize") int pageSize);

2、在 shopping 子工程（子模块）中，修改 dao 接口类 ProductDao.java，添加查询商品的方法。

/***   dbsharding\shopping\src\main\java\djh\it\shopping\dao\ProductDao.java**   2024-7-3 创建 dao 接口类 ProductDao.java*/
package djh.it.shopping.dao;import djh.it.shopping.entity.ProductDescript;
import djh.it.shopping.entity.ProductInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;import java.util.List;
import java.util.Map;@Mapper
@Component
public interface ProductDao {//添加商品基本信息@Insert("insert into product_info(store_info_id, product_name, spec, region_code, price) " +" values(#{storeInfoId},#{productName},#{spec},#{regionCode},#{price})")@Options(useGeneratedKeys = true, keyProperty = "productInfoId", keyColumn = "product_info_id")int insertProductInfo( ProductInfo productInfo );//添加商品描述信息@Insert("insert into product_descript(product_info_id, descript, store_info_id) " +" values(#{productInfoId}, #{descript}, #{storeInfoId})")@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")int insertProductDescript( ProductDescript productDescript );//查询商品@Select("select i.*, d.descript, r.region_name placeOfOrigin " +"from product_info i join product_descript d on i.product_info_id = d.product_info_id " +"join region r on i.region_code = r.region_code order by i.product_info_id desc limit #{start}, #{pageSize}")List<ProductInfo> selectProductList( @Param("start") int start, @Param("pageSize") int pageSize);}

二、Sharding-JDBC 综合案例-查询商品-service 及测试

1、在 shopping 子工程（子模块）中，修改 service 接口类 ProductService.java 添加查询方法。

/***   dbsharding\shopping\src\main\java\djh\it\shopping\service\ProductService.java**   2024-7-3 创建 service 接口类 ProductService.java*/
package djh.it.shopping.service;import djh.it.shopping.entity.ProductInfo;import java.util.List;public interface ProductService {//添加商品public void createProduct( ProductInfo productInfo );//查询商品public List<ProductInfo> queryProduct( int page, int pageSize);
}

2、在 shopping 子工程（子模块）中，修改 service 实现类 ProductServiceImpl.java 添加查询方法。

/***   dbsharding\shopping\src\main\java\djh\it\shopping\service\impl\ProductServiceImpl.java**   2024-7-3 创建 service 实现类 ProductServiceImpl.java*/
package djh.it.shopping.service.impl;import djh.it.shopping.dao.ProductDao;
import djh.it.shopping.entity.ProductDescript;
import djh.it.shopping.entity.ProductInfo;
import djh.it.shopping.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import java.util.List;@Service
public class ProductServiceImpl implements ProductService {@AutowiredProductDao productDao;@Override@Transactionalpublic void createProduct( ProductInfo productInfo ) {ProductDescript productDescript = new ProductDescript();//设置商品描述信息productDescript.setDescript(productInfo.getDescript());//调用dao 向商品信息表productDao.insertProductInfo(productInfo);//将商品信息id设置到 productDescript
//        productDescript.setId(productInfo.getProductInfoId());  //errorproductDescript.setProductInfoId(productInfo.getProductInfoId());//设置店铺idproductDescript.setStoreInfoId(productInfo.getStoreInfoId());//向商品描述信息表插入数据productDao.insertProductDescript(productDescript);}@Overridepublic List<ProductInfo> queryProduct( int page, int pageSize ) {int start = (page - 1) * pageSize;return productDao.selectProductList(start, pageSize);}
}

3、在 shopping 子工程（子模块）中，修改 测试类 ShardingTest.java 添加查询方法。

/***   dbsharding\shopping\src\test\java\djh\it\shopping\ShardingTest.java**   2024-7-3 创建 测试类 ShardingTest.java*/
package djh.it.shopping;import djh.it.shopping.entity.ProductInfo;
import djh.it.shopping.service.ProductService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import java.math.BigDecimal;
import java.util.List;@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShoppingBootstrap.class)
public class ShardingTest {@AutowiredProductService productService;@Test   //添加商品public void testCreateProduct(){for(int i=1; i<10; i++){ProductInfo productInfo = new ProductInfo();//productInfo.setStoreInfoId(1L);  //店铺id（向 product_db_2 中插入数据）productInfo.setStoreInfoId(2L);  //店铺id（向 product_db_1 中插入数据）productInfo.setProductName("Java编程思想");  //商品名称productInfo.setSpec("大号");  //商品规格productInfo.setPrice(new BigDecimal(60));  //商品价格productInfo.setRegionCode("110100");  //商品产地productInfo.setDescript("《java编程思想》这本书不错！！！");  //商品描述productService.createProduct(productInfo);}}@Test  //查询商品public void testQueryProduct(){List<ProductInfo> productInfos = productService.queryProduct(1, 10);System.out.println(productInfos);}
}

4、在 shopping 子工程（子模块）中，修改 application.properties 配置文件，完善绑定表。

#  dbsharding\shopping\src\main\resources\application.propertiesserver.port = 56082spring.application.name = shopping
spring.profiles.active = localserver.servlet.context-path = /shopping
spring.http.encoding.enabled = true
spring.http.encoding.charset = utf-8
spring.http.encoding.force = truespring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true# sharding-jdbc 分片规则配置：# 1）配置数据源：真实数据源定义 m为主库，s为从库。m0, m1, m2, s0, s1, s2
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2
#spring.shardingsphere.datasource.names = m0,m1,m2spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/store_db?useUnicode=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = 12311spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/product_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 12311spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/product_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 12311spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/store_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = 12311spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://localhost:3307/product_db_1?useUnicode=true
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = 12311spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://localhost:3307/product_db_2?useUnicode=true
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = 12311# 2）配置主、从关系数据库：
# 主库从库逻辑数据源定义 ds0为store_db ds1为product_db_1 ds2为product_db_2
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2# 3）配置 分库策略（水平分库）：
# 默认分库策略，以store_info_id为分片键，分片策略为store_info_id % 2 + 1,也就是store_info_id为双数的数据进入ds1, 为单数的进入ds2
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = store_info_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{store_info_id % 2 + 1}# 4）配置 分表策略（水平分表）
# 4.1) store_info 分表策略，固定分配至ds0的store_info真实表
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression = store_info# 4.2) product_info 分表策略
# 数据结点包括：ds1.product_info_1, ds1.product_info_2, ds2.product_info_1, ds2.product_info_2 四个节点。
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2}
# 分片策略（水平分表）：分片策略为product_info_id % 2 + 1
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression =  product_info_$->{product_info_id % 2 + 1}
# 主键策略（product_info_id 生成为雪花算法，为双数的数据进入 product_info_1表，为单数的进入 product_info_2表）
spring.shardingsphere.sharding.tables.product_info.key-generator.column = product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type = SNOWFLAKE# 4.3) product descript 分表策略
# 分布在 ds1,ds2 的 product_descript_1 product_descript_2表,分片策略为 product_info_id % 2 + 1,
# id 生成为雪花算法, product_info_id 为双数的数据进入 product_descript_1表,为单数的进入 product_descript_2表
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2}
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column =product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id % 2 + 1}
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE# 5）设置 product_info,product_descript为绑定表
#spring.shardingsphere.sharding.binding-tables = product_info,product_descript
# 修改绑定表（以数组方式显示）
spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript# 6）设置region为广播表(公共表),每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast-tables=region# 7）打开 sql 输出日志
spring.shardingsphere.props.sql.show = trueswagger.enable = truelogging.level.root = info
logging.level.org.springframework.web = info
logging.level.djh.it.dbsharding = debug
logging.level.druid.sql = debug

5、在 shopping 子工程（子模块）中， 测试类 ShardingTest.java 打断点，进行查询方法测试。

6、注意事项：

1）绑定表配置注意事项

# 5）设置 product_info,product_descript为绑定表
#spring.shardingsphere.sharding.binding-tables = product_info,product_descript
# 修改绑定表（以数组方式显示）
spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript

2）分页查询是业务中最常见的场景

• Sharding:jdbc 支持常用关系数据库的分页查询，不过 Sharding-jdbc 的分页功能比较容易让使用者误解，用户通常认为分页归并会占用大量内存。

• 在分布式的场景中，将 LIMIT 1088880010 改写为 LIMIT 8，10000010，才能保证其数据的正确性。

• 用户非常容易产生 Shardingsphere 会将大量无意义的数据加载至内存中，造成内存溢出风险的错觉。

• 其实大部分情况都通过流式归并获取数据结果集，因此 ShardingSphere 会通过结果集的 next 方法将无需取出的数据全部跳过，并不会将其存入内存。

3）排序功能注意事项

• 同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到 Sharding-Jdbc 的内存空间。 因此，采用LIMIT这种方式分页，并非最佳实践。

• 由于 LIMIT 并不能通过索引查询数据，因此如果可以保证 ID 的连续性，通过 ID 进行分页是比较好的解决方案，例如:

SELECT *FROM t_order WHERE id >100000 AND id<= 100010 ORDER BY id;

• 或通过记录上次查询结果的最后一条记录的ID进行下一页的查询，例如:

SELECT *FROM t_order WHERE id >10000000 LIMIT 10;

• 排序功能是由 Sharding-jdbc 的排序归并来完成，由于在 SQL 中存在 ORDER BY 语句，因此每个数据结果集自身是有序的，因此只需要将数据结果集当前游标指向的数据值进行排序即可。

• 这相当于对多个有序的数组进行排序，归并排序是最适合此场景的排序算法。

三、Sharding-JDBC 综合案例-统计商品

1、在 shopping 子工程（子模块）中，修改 dao 接口类 ProductDao.java 添加统计方法。

/***   dbsharding\shopping\src\main\java\djh\it\shopping\dao\ProductDao.java**   2024-7-3 创建 dao 接口类 ProductDao.java*/
package djh.it.shopping.dao;import djh.it.shopping.entity.ProductDescript;
import djh.it.shopping.entity.ProductInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;import java.util.List;
import java.util.Map;@Mapper
@Component
public interface ProductDao {//添加商品基本信息@Insert("insert into product_info(store_info_id, product_name, spec, region_code, price) " +" values(#{storeInfoId},#{productName},#{spec},#{regionCode},#{price})")@Options(useGeneratedKeys = true, keyProperty = "productInfoId", keyColumn = "product_info_id")int insertProductInfo( ProductInfo productInfo );//添加商品描述信息@Insert("insert into product_descript(product_info_id, descript, store_info_id) " +" values(#{productInfoId}, #{descript}, #{storeInfoId})")@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")int insertProductDescript( ProductDescript productDescript );//查询商品@Select("select i.*, d.descript, r.region_name placeOfOrigin " +"from product_info i join product_descript d on i.product_info_id = d.product_info_id " +"join region r on i.region_code = r.region_code order by i.product_info_id desc limit #{start}, #{pageSize}")List<ProductInfo> selectProductList( @Param("start") int start, @Param("pageSize") int pageSize);//统计商品总数@Select("select count(1) from product_info")int selectCount();//商品分组统计@Select("select t.region_code, count(1) as num from product_info t group by t.region_code having num > 1 order by region_code")List<Map> selectProductGroupList();
}

2、在 shopping 子工程（子模块）中，修改 测试类 ShardingTest.java 添加统计方法。

/***   dbsharding\shopping\src\test\java\djh\it\shopping\ShardingTest.java**   2024-7-3 创建 测试类 ShardingTest.java*/
package djh.it.shopping;import djh.it.shopping.dao.ProductDao;
import djh.it.shopping.entity.ProductInfo;
import djh.it.shopping.service.ProductService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import java.math.BigDecimal;
import java.util.List;
import java.util.Map;@RunWith(SpringRunner.class)
@SpringBootTest(classes = ShoppingBootstrap.class)
public class ShardingTest {@AutowiredProductService productService;@AutowiredProductDao productDao;@Test   //添加商品public void testCreateProduct(){for(int i=1; i<10; i++){ProductInfo productInfo = new ProductInfo();//productInfo.setStoreInfoId(1L);  //店铺id（向 product_db_2 中插入数据）productInfo.setStoreInfoId(2L);  //店铺id（向 product_db_1 中插入数据）productInfo.setProductName("Java编程思想");  //商品名称productInfo.setSpec("大号");  //商品规格productInfo.setPrice(new BigDecimal(60));  //商品价格productInfo.setRegionCode("110100");  //商品产地productInfo.setDescript("《java编程思想》这本书不错！！！");  //商品描述productService.createProduct(productInfo);}}@Test  //查询商品public void testQueryProduct(){List<ProductInfo> productInfos = productService.queryProduct(1, 10);System.out.println(productInfos);}@Test  //统计商品总数public void testSelectCount(){int i = productDao.selectCount();System.out.println(i);}@Test  //分组统计商品public void testSelectProductGroupList(){List<Map> maps = productDao.selectProductGroupList();System.out.println(maps);}}

3、在 shopping 子工程（子模块）中， 运行 测试类 ShardingTest.java 进行统计商品方法测试。

4、综合案例-统计商品 总结:

• 1）分组统计

分组统计也是业务中常见的场景，分组功能的实现由Sharding-jdbc分组归并完成。分组归并的情况最为复杂，它分为流式分组归并和内存分组归并。流式分组归并要求SQL的排序项与分组项的字段必须保持一致，否则只能通过内存归并才能保证其数据的正确性。

• 2）举例说明，假设根据科目分片，表结构中包含考生的姓名(为了简单起见，不考虑重名的情况)和分数。通过SQL获取每位考生的总分，可通过如下 SOL:

SELECT name, SuM(score)FROM t score GROup BY name ORDER BY name;

• 3）在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游标所指向的数据值，因此可以采用流式归并。

四、Sharding-JDBC 总结

1、为什么分库分表？

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成,使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

2、分库分表方式:

垂直分表、垂直分库、水平分库、水平分表。

3、分库分表带来问题:

由于数据分散在多个数据库，服务器导致了事务一致性问题、跨节点 join 问题、跨节点分页排序、函数，主键需要全局唯一，公共表。

4、Sharding-JDBC 基础概念:

逻辑表,真实表,数据节点,绑定表,广播表,分片键,分片算法,分片策略,主键生成策略。

5、Sharding-JDBC 核心功能:

数据分片，读写分离。

6、Sharding-JDBC 执行流程:

SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并。

7、Sharding-JDBC 最佳实践:

系统在设计之初就应该对业务数据的耦合松紧进行考量，从而进行垂直分库、垂直分表，使数据层架构清晰明了。若非必要，无需进行水平切分，,应先从缓存技术着手降低对数据库的访问压力。如果缓存使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。若当前数据库压力依然大，且业务数据持续增长无法估量最后可考虑水平分库、分表，单表拆分数据控制在1000万以内。

8、Sharding-JDBC 对 SQL 支持说明 详细参考：

https://shardingsphere.apache.org/document/current/cn/features/sharding/appendix/

SgardubgSogere 概览：
https://shardingsphere.apache.org/document/current/cn/overview/

上一节关联链接请点击
# Sharding-JDBC 从入门到精通（9）- 综合案例（二）添加商品