# Sharding-JDBC 同庫分表 >>**導航** [TOC] ## 本節代碼地址 GitHub:[https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-table](https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-table) ***** 本節主要介紹Sharding-JDBC同庫分表，并提供代碼樣例，在分布式項目架構中，同庫分表也是常用的一種方案。 ## 項目技術棧 | 技術棧 | 版本 | | --- | --- | | jdk | 1.8.0_161 | | SpringBoot | 2.2.2.RELEASE | | Shardingsphere | 4.0.1 | | Mybatis-plus | 3.3.0 | | Hutool | 5.0.6 | | HikariCP | 2.7.9 | * SpringBoot 基于SpringBoot 2.2.2.RELEASE 構建我們的應用 * Shardingsphere 使用最新版的4.0.1來支撐讀寫分離 * Mybatis-plus來作為我們的持久層框架，介紹代碼的開發量 * Hutool作為java工具包 * HikariCP 項目中的連接池，性能要比Druid要高，目前SpringBoot 默認支持的數據連接池。 * lombok 使代碼簡介，介紹getter\setter\構造器的創建，需要在idea中安裝lombok插件 ## 1. 數據庫創建 ### 1.1 新建數據庫 mysql 數據庫的搭建可以**參考容器化章節**（docker run 一個） ### 1.2 執行腳本 執行以下腳本將創建數據庫master0，并分別在庫里面創建`sys_user0`、`sys_user1`表 ~~~ CREATE DATABASE master0; USE master0; DROP TABLE IF EXISTS `sys_user0`; CREATE TABLE `sys_user0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; DROP TABLE IF EXISTS `sys_user1`; CREATE TABLE `sys_user1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間', `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間', `create_user` varchar(100) NOT NULL COMMENT '創建人編碼', `update_user` varchar(100) NOT NULL COMMENT '修改人編碼', `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '刪除標記(1 刪除 0未刪除)', `pos_code` varchar(50) DEFAULT NULL COMMENT '職位編碼', `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '啟用標記(1 禁用 0啟用)', `avatar` varchar(100) DEFAULT NULL COMMENT '頭像地址', `email` varchar(50) DEFAULT NULL COMMENT '郵箱', `password` varchar(100) DEFAULT NULL COMMENT '密碼', `user_name` varchar(50) DEFAULT NULL COMMENT '用戶名', `real_name` varchar(50) DEFAULT NULL COMMENT '真實姓名', `dept_code` varchar(50) DEFAULT NULL COMMENT '部門編碼', `user_phone` varchar(15) DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '郵箱索引', UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用戶名索引', KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部門編碼索引', KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '職位編碼索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系統用戶表'; ~~~ ## 2. 新建讀寫分離模塊shardingsphere-learning-sub-table 上面我們已經配置了公共的針對用戶表的基本操作，這些暫時已經夠我們演示用了，如果不夠，可以自定義自己的實現 ### 2.1 添加依賴 這里我們將`shardingsphere-learning-common`的包依賴進來，方便我們操作用戶表的增刪改查 ~~~ <dependencies> <dependency> <groupId>com.yisu.fwcloud</groupId> <artifactId>shardingsphere-learning-common</artifactId> <version>${version}</version> </dependency> </dependencies> ~~~ ### 2.2 應用配置 >這里筆者將默認的端口修改為了8900，然后就是配置Sharding-JDBC的數據源，我們本節演示的Sharding-JDBC讀寫分離需要兩個庫（真實項目中需要兩個數據庫實例）。 1. 筆者在配置中先設置數據源的名稱 2. 配置數據庫連接信息，如`spring.shardingsphere.datasource.{數據源名稱}.type` 3. 根據id配置分表規則 4. 將sql日志信息打印出來 ~~~ server.port=8900 #數據源定義 spring.shardingsphere.datasource.names=master0 # 數據源 主庫0 spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://${dbIp}:3306/master0?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false spring.shardingsphere.datasource.master0.username=root spring.shardingsphere.datasource.master0.password=123456 #根據id分表 spring.shardingsphere.sharding.tables.sys_user.actual-data-nodes=master0.sys_user$->{0..1} spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.algorithm-expression=sys_user$->{id % 2} #打印sql spring.shardingsphere.props.sql.show=true spring.main.allow-bean-definition-overriding=true #調整日志為debug logging.level.com.yisu= debug ~~~ ### 2.3 新建啟動類 ~~~ /** * @description 啟動類 * @author xuyisu * @date '2020-03-22' */ @SpringBootApplication public class FwShardingsphereSubTable{ public static void main(String[] args) { SpringApplication.run(FwShardingsphereSubTable.class, args); } } ~~~ ### 2.4 新建單元測試 新建單元測試，用于測試同庫分表的功能 ~~~ @RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT) public class SysUserServiceImplSubTable{ @Autowired private SysUserService sysUserService; /** * 測試數量 */ @Test public void testSelect(){ int count = sysUserService.count(); System.out.println(count); } /** * 測試單個新增 */ @Test public void testInsert(){ SysUser sysUser=new SysUser(); sysUser.setAvatar("/home/avatar"); sysUser.setCreateTime(DateUtil.date()); sysUser.setCreateUser("sys"); sysUser.setDeleteFlag(0); sysUser.setDeptCode("depe"); sysUser.setDisableFlag(0); sysUser.setEmail("***@123.com"); sysUser.setPassword("123456"); sysUser.setPosCode("pos"); sysUser.setRealName("realname"); sysUser.setUpdateTime(DateUtil.date()); sysUser.setUserName("fwcloud"); sysUser.setUpdateUser("sys"); sysUser.setUserPhone("12345678912"); sysUserService.save(sysUser); } /** * 測試批量新增 */ @Test public void testBatch(){ List<SysUser> list=new ArrayList<>(); for (int i = 0; i <100 ; i++) { SysUser sysUser=new SysUser(); sysUser.setAvatar("/home/avatar"); sysUser.setCreateTime(DateUtil.date()); sysUser.setCreateUser("sys"+i); sysUser.setDeleteFlag(0); sysUser.setDeptCode("depe"+i); sysUser.setDisableFlag(0); sysUser.setEmail("***@123.com"+i); sysUser.setPassword("123456"); sysUser.setPosCode("pos"); sysUser.setRealName("realname"+i); sysUser.setUpdateTime(DateUtil.date()); sysUser.setUserName("fwcloud"+i); sysUser.setUpdateUser("sys"+i); sysUser.setUserPhone("12345678912"+i); list.add(sysUser); } boolean saveBatch = sysUserService.saveBatch(list); Assert.assertEquals(true,saveBatch); } } ~~~ #### 2.4.1 測試新增 先配置數據庫的ip地址，變量是dbIp，值是ip地址，也可以不用變量直接用真實的ip地址  運行testBatch方法，可以看到控制臺的日志如下，可以看到走的實例是master0 ~~~ 2020-03-23 15:34:23 INFO main ShardingSphere-SQL Actual SQL: master0 ::: INSERT INTO sys_user0 ( id, user_phone, update_user, update_time, avatar, user_name, delete_flag, real_name, password, create_time, disable_flag, pos_code, create_user, dept_code, email ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241991664637906946, 123456789120, sys0, 2020-03-23 15:34:22.466, /home/avatar, fwcloud0, 0, realname0, 123456, 2020-03-23 15:34:22.463, 0, pos, sys0, depe0, ***@123.com0] 。。。。 2020-03-23 15:34:23 INFO main ShardingSphere-SQL Actual SQL: master0 ::: INSERT INTO sys_user0 ( id, user_phone, update_user, update_time, avatar, user_name, delete_flag, real_name, password, create_time, disable_flag, pos_code, create_user, dept_code, email ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241991669247447042, 1234567891264, sys64, 2020-03-23 15:34:22.467, /home/avatar, fwcloud64, 0, realname64, 123456, 2020-03-23 15:34:22.467, 0, pos, sys64, depe64, ***@123.com64] 。。。 ~~~ 一共批量插入了100條數據，可以看到分別落入兩個表里面   #### 2.4.2 測試查詢 運行testSelect方法，可以看到控制臺的日志如下，可以看到查詢的是sys_user0和sys_user1兩張表，查詢結果為100 ~~~ 2020-03-23 15:43:09 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-23 15:43:09 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 2020-03-23 15:43:10 INFO main ShardingSphere-SQL Rule Type: sharding 2020-03-23 15:43:10 INFO main ShardingSphere-SQL Logic SQL: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-23 15:43:10 INFO main ShardingSphere-SQL SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@58932d08, tablesContext=TablesContext(tables=[Table(name=sys_user, alias=Optional.absent())], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=( 1 ), alias=Optional.absent(), derivedAggregationProjections=[], index=-1)], columnLabels=[COUNT( 1 )]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@45648e75, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@e344f14, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@2d117280, containsSubquery=false) 2020-03-23 15:43:10 INFO main ShardingSphere-SQL Actual SQL: master0 ::: SELECT COUNT( 1 ) FROM sys_user0 WHERE delete_flag=0 2020-03-23 15:43:10 INFO main ShardingSphere-SQL Actual SQL: master0 ::: SELECT COUNT( 1 ) FROM sys_user1 WHERE delete_flag=0 2020-03-23 15:43:10 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <== Total: 1 100 ~~~ 至此，同庫分表已完成