Sharding-jdbc整合综合案例
数据库设计
数据库设计如下，其中商品与店铺信息之间进行了垂直分库，分为了PRODUCT_DB(商品库)和STORE_DB(店铺
库)；商品信息还进行了垂直分表，分为了商品基本信息(product_info)和商品描述信息(product_descript)，地理区
域信息(region)作为公共表，冗余在两库中：
考虑到商品信息的数据增长性，对PRODUCT_DB(商品库)进行了水平分库，分片键使用店铺id，分片策略为店铺
ID%2 + 1，因此商品描述信息对所属店铺ID进行了冗余；
对商品基本信息(product_info)和商品描述信息(product_descript)进行水平分表，分片键使用商品id，分片策略为
商品ID%2 + 1,并将为这两个表设置为绑定表，避免笛卡尔积join；
为避免主键冲突，ID生成策略采用雪花算法来生成全局唯一ID，最终数据库设计为下图：
初始化数据库
创建store_db数据库，并执行以下脚本创建表：
DROP TABLE IF EXISTS `region`; CREATE TABLE `region`( `id` bigint(20) NOT NULL COMMENT 'id', `region_code`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域编码', `region_name`varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域名称', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理', `parent_region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'上级地理区域编码', PRIMARY KEY (`id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; INSERT INTO `region` VALUES(1,'110000','北京',0,NULL); INSERT INTO `region` VALUES(2,'410000','河南省',0,NULL); INSERT INTO `region` VALUES(3,'110100','北京市',1,'110000'); INSERT INTO `region` VALUES(4,'410100','郑州市',1,'410000'); DROP TABLE IF EXISTS `store_info`; CREATE TABLE `store_info`( `id` bigint(20) NOT NULL COMMENT 'id', `store_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺名称', `reputation` int(11) NULL DEFAULT NULL COMMENT '信誉等级', `region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '店铺所在地', PRIMARY KEY (`id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; INSERT INTO `store_info` VALUES(1,'XX零食店',4,'110100'); INSERT INTO `store_info` VALUES(2,'XX饮品店',3,'410100');
创建product_db_1、product_db_2数据库，并分别对两库执行以下脚本创建表：
DROP TABLE IF EXISTS `product_descript_1`; CREATE TABLE `product_descript_1`( `id`bigint(20) NOT NULL COMMENT 'id', `product_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属商品id',`descript` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述', `store_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属店铺id', PRIMARY KEY(`id`) USING BTREE, INDEX`FK_Reference_2`(`product_info_id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; DROP TABLE IF EXISTS `product_descript_2`; CREATE TABLE `product_descript_2`( `id`bigint(20) NOT NULL COMMENT 'id', `product_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属商品id',`descript` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述', `store_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属店铺id', PRIMARY KEY(`id`) USING BTREE, INDEX`FK_Reference_2`(`product_info_id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; DROP TABLE IF EXISTS `product_info_1`; CREATE TABLE `product_info_1`( `product_info_id`bigint(20) NOT NULL COMMENT 'id', `store_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属店铺id', `product_name`varchar(100)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'商品名称', `spec`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格', `region_code`varchar(50)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地', `price`decimal(10,0) NULL DEFAULT NULL COMMENT '商品价格', `image_url`varchar(100)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片', PRIMARY KEY(`product_info_id`) USING BTREE, INDEX`FK_Reference_1`(`store_info_id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; DROP TABLE IF EXISTS `product_info_2`; CREATE TABLE `product_info_2`( `product_info_id`bigint(20) NOT NULL COMMENT 'id', `store_info_id`bigint(20) NULL DEFAULT NULL COMMENT '所属店铺id', `product_name`varchar(100)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'商品名称', `spec`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '规格', `region_code`varchar(50)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产地', `price`decimal(10,0) NULL DEFAULT NULL COMMENT '商品价格', `image_url`varchar(100)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品图片', PRIMARY KEY(`product_info_id`) USING BTREE, INDEX`FK_Reference_1`(`store_info_id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; DROP TABLE IF EXISTS `region`; CREATE TABLE `region`( `id` bigint(20) NOT NULL COMMENT 'id', `region_code`varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域编码', `region_name`varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地理区域名称', `level` tinyint(1) NULL DEFAULT NULL COMMENT '地理', `parent_region_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'上级地理区域编码', PRIMARY KEY (`id`) USING BTREE )ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic; INSERT INTO `region` VALUES(1,'110000','北京',0,NULL); INSERT INTO `region` VALUES(2,'410000','河南省',0,NULL); INSERT INTO `region` VALUES(3,'110100','北京市',1,'110000'); INSERT INTO `region` VALUES(4,'410100','郑州市',1,'410000');
搭建maven工程
（1）搭建工程maven工程shopping，导入资料中基础代码shopping，以dbsharding为总体父工程，并做好
spring boot相关配置。
（2）引入maven依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> </dependency> </dependencies>
配置：
server.port=56082 spring.application.name = shopping spring.profiles.active = local server.servlet.context-path = /shopping spring.http.encoding.enabled = true spring.http.encoding.charset = UTF-8 spring.http.encoding.force = true spring.main.allow-bean-definition-overriding = true mybatis.configuration.map-underscore-to-camel-case = true #sharding-jdbc分片规则 #配置数据源 m0,m1,m2,s0,s1,s2 spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2 spring.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://xxxxxxx:3306/store_db?useUnicode=true spring.shardingsphere.datasource.m0.username = root spring.shardingsphere.datasource.m0.password = 123456 spring.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://xxxxxxx:3306/product_db_1?useUnicode=true spring.shardingsphere.datasource.m1.username = root spring.shardingsphere.datasource.m1.password = 123456 spring.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://xxxxxxx:3306/product_db_2?useUnicode=true spring.shardingsphere.datasource.m2.username = root spring.shardingsphere.datasource.m2.password = 123456 spring.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://xxxxxx:3306/store_db?useUnicode=true spring.shardingsphere.datasource.s0.username = root spring.shardingsphere.datasource.s0.password = 123456 spring.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://xxxxxxxx:3306/product_db_1?useUnicode=true spring.shardingsphere.datasource.s1.username = root spring.shardingsphere.datasource.s1.password = 123456 spring.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://xxxxxxx:3306/product_db_2?useUnicode=true spring.shardingsphere.datasource.s2.username = root spring.shardingsphere.datasource.s2.password = 123456 #主从关系 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 #分库策略（水平） 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} #分表策略 # 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 # 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} 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} spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE #product_descript分表策略 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 # 设置product_info,product_descript为绑定表 spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript # 设置region为广播表(公共表)，每次更新操作会发送至所有数据源 spring.shardingsphere.sharding.broadcast-tables=region # 打开sql输出日志 spring.shardingsphere.props.sql.show = true swagger.enable = true logging.level.root = info logging.level.org.springframework.web = info logging.level.com.itheima.dbsharding = debug
主要就是配置，别的类就直接省略了，也比较简单，直接就测试
//添加商品 @Test public void testCreateProduct(){ for (int i=1;i<10;i++){ ProductInfo productInfo = new ProductInfo(); productInfo.setStoreInfoId(1L);//店铺id productInfo.setProductName("Java编程思想"+i);//商品名称 productInfo.setSpec("大号"); productInfo.setPrice(new BigDecimal(60)); productInfo.setRegionCode("110100"); productInfo.setDescript("Java编程思想不错！！！"+i);//商品描述 productService.createProduct(productInfo); } }
分库策略是根据store_info_id,当为奇数的时候，就在m2数据源
分库策略是根据store_info_id,当为偶数的时候，就在m1数据源
测试查询
//查询商品 @Test public void testQueryProduct(){ List<ProductInfo> productInfos = productService.queryProduct(2, 2); 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); }
分组统计
分组统计也是业务中常见的场景，分组功能的实现由Sharding-jdbc分组归并完成。分组归并的情况最为复杂，它
分为流式分组归并和内存分组归并。 流式分组归并要求SQL的排序项与分组项的字段必须保持一致，否则只能通过
内存归并才能保证其数据的正确性。
举例说明，假设根据科目分片，表结构中包含考生的姓名（为了简单起见，不考虑重名的情况）和分数。通过SQL
获取每位考生的总分，可通过如下SQL：
SELECT name, SUM(score) FROM t_score GROUP BY name ORDER BY name;
在分组项与排序项完全一致的情况下，取得的数据是连续的，分组所需的数据全数存在于各个数据结果集的当前游
标所指向的数据值，因此可以采用流式归并。如下图所示。
进行归并时，逻辑与排序归并类似。 下图展现了进行next调用的时候，流式分组归并是如何进行的。
通过图中我们可以看到，当进行第一次next调用时，排在队列首位的t_score_java将会被弹出队列，并且将分组值
同为“Jetty”的其他结果集中的数据一同弹出队列。 在获取了所有的姓名为“Jetty”的同学的分数之后，进行累加操
作，那么，在第一次next调用结束后，取出的结果集是“Jetty”的分数总和。 与此同时，所有的数据结果集中的游标
都将下移至数据值“Jetty”的下一个不同的数据值，并且根据数据结果集当前游标指向的值进行重排序。 因此，包含
名字顺着第二位的“John”的相关数据结果集则排在的队列的前列。
