Sharding-JDBC基本使用，整合Springboot實(shí)現(xiàn)分庫分表，讀寫分離
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號(hào)”
優(yōu)質(zhì)文章，第一時(shí)間送達(dá)
一、Sharding-JDBC介紹
1、這里引用官網(wǎng)上的介紹：
??定位為輕量級(jí)Java框架，在Java的JDBC層提供的額外服務(wù)。它使用客戶端直連數(shù)據(jù)庫，以jar包形式提供服務(wù)，無需額外部署和依賴，可理解為增強(qiáng)版的JDBC驅(qū)動(dòng)，完全兼容JDBC和各種ORM框架。
??適用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
??支持任何第三方的數(shù)據(jù)庫連接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
??支持任意實(shí)現(xiàn)JDBC規(guī)范的數(shù)據(jù)庫。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92標(biāo)準(zhǔn)的數(shù)據(jù)庫。
2、自己的理解：
??增強(qiáng)版的JDBC驅(qū)動(dòng)，客戶端使用的時(shí)候，就像正常使用JDBC驅(qū)動(dòng)一樣， 引入Sharding-JDBC依賴包，連接好數(shù)據(jù)庫，配置好分庫分表規(guī)則，讀寫分離配置，然后客戶端的sql 操作 Sharding-JDBC會(huì)自動(dòng)根據(jù)配置完成 分庫分表和讀寫分離操作。
二、實(shí)現(xiàn)效果
1、下圖展示了我們通過Sharding-JDBC實(shí)現(xiàn)的分庫分表及讀寫分離效果圖
??分庫分表：結(jié)合上一篇的主從，這里我們使用上次搭建的主從數(shù)據(jù)庫，3307的app1是主數(shù)據(jù)庫，3308的app1是對(duì)應(yīng)的從數(shù)據(jù)庫。同時(shí)，我們?cè)?307新建app2庫和user2表，這里的app2庫需要和app1庫一樣，user2表和user1表結(jié)構(gòu)一樣，主從會(huì)自動(dòng)幫我們建表同步到3308，然后我們?cè)陧?xiàng)目中使用Sharding-JDBC 配置響應(yīng)的分庫分表策略，使得插入數(shù)據(jù)的時(shí)候 根據(jù)配置字段的分片規(guī)則將數(shù)據(jù)打入對(duì)應(yīng)的庫和表。在我們這里主要是 根據(jù)分庫的分片規(guī)則決定數(shù)據(jù)進(jìn)入3307的app1庫還是app2庫，然后再根據(jù)分表的分片規(guī)則決定進(jìn)入user1表還是user2表。
??讀寫分離：讀寫分離 在我們這里主要指的是 我們項(xiàng)目DQL會(huì)根據(jù)Sharding-JDBC配置的master-slave-rule走的3308的數(shù)據(jù)源，而項(xiàng)目的DML會(huì)根據(jù)master-slave-rule走3307的數(shù)據(jù)源
三、Spring-Boot項(xiàng)目整合Sharding-JDBC實(shí)現(xiàn)分庫分表、讀寫分離
1、這里創(chuàng)建一個(gè)maven項(xiàng)目，首先引入依賴，pom.xml文件如下。
"1.0"?encoding="UTF-8"?>
"http://maven.apache.org/POM/4.0.0"
?????????xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
?????????xsi:schemaLocation="http://maven.apache.org/POM/4.0.0?http://maven.apache.org/xsd/maven-4.0.0.xsd">
????4.0.0
????
????????org.springframework.boot
????????spring-boot-starter-parent
????????2.3.3.RELEASE
????
????com.cgg
????sharding-jdbc-test
????1.0-SNAPSHOT
????
????????1.8
????
????
????????
????????????org.springframework.boot
????????????spring-boot-starter-web
????????
????????
????????????org.springframework.boot
????????????spring-boot-starter-test
????????
????????
????????????mysql
????????????mysql-connector-java
????????????8.0.15
????????
????????
????????????com.alibaba
????????????druid
????????????1.1.21
????????
????????
????????????com.baomidou
????????????mybatis-plus-boot-starter
????????????3.1.1
????????
????????
????????????com.baomidou
????????????mybatis-plus-extension
????????????3.1.1
????????
????????
????????????org.apache.shardingsphere
????????????sharding-jdbc-spring-boot-starter
????????????4.0.0-RC1
????????
????????
????????????org.projectlombok
????????????lombok
????????
????
????
????????
????????????
????????????????org.springframework.boot
????????????????spring-boot-maven-plugin
????????????
????????
????
注意：這里使用的是4.0的sharding-jdbc，spring-boot的版本是2.x的，在整合過程中遇見了許多問題，后面會(huì)有錯(cuò)誤的解決步驟。
2、application.yml文件如下
spring:
??jpa:
????properties:
??????hibernate:
????????hbm2ddl:
??????????auto:?create
????????dialect:?org.hibernate.dialect.MySQL5Dialect
????????show_sql:?true
??shardingsphere:
????props:
??????sql:
????????show:?true
????datasource:
??????names:?master0,master0slave0,master1,master1slave0
??????master0:
????????type:?com.alibaba.druid.pool.DruidDataSource
????????driver-class-name:?com.mysql.cj.jdbc.Driver
????????url:?jdbc:mysql://127.0.0.1:3307/app1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
????????username:?root
????????password:?654321
??????master1:
????????type:?com.alibaba.druid.pool.DruidDataSource
????????driver-class-name:?com.mysql.cj.jdbc.Driver
????????url:?jdbc:mysql://127.0.0.1:3307/app2?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
????????username:?root
????????password:?654321
??????master0slave0:
????????type:?com.alibaba.druid.pool.DruidDataSource
????????driver-class-name:?com.mysql.cj.jdbc.Driver
????????url:?jdbc:mysql://127.0.0.1:3308/app1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
????????username:?root
????????password:?654321
??????master1slave0:
????????type:?com.alibaba.druid.pool.DruidDataSource
????????driver-class-name:?com.mysql.cj.jdbc.Driver
????????url:?jdbc:mysql://127.0.0.1:3308/app2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
????????username:?root
????????password:?654321
????sharding:
??????default-database-strategy:
????????inline:
??????????sharding-column:?id
??????????algorithm-expression:?app$->{(id?%?2)+1}
??????tables:
????????user:
??????????actual-data-nodes:?app$->{1..2}.user$->{1..2}
??????????table-strategy:
????????????inline:
??????????????sharding-column:?id
??????????????algorithm-expression:?user$->{((""+id)[2..10].toInteger()?%?2)+1}
??????????key-generator:
????????????column:?id
????????????type:?SNOWFLAKE
??????master-slave-rules:
????????app1:
??????????master-data-source-name:?master0
??????????slave-data-source-names:?master0slave0
????????app2:
??????????master-data-source-name:?master1
??????????slave-data-source-names:?master1slave0
sharding:
??jdbc:
????config:
??????masterslave:
????????load-balance-algorithm-type:?random
3、application.properties文件
spring.main.allow-bean-definition-overriding=true
mybatis-plus.mapper-locations=?classpath:/mapper/*.xml
mybatis-plus.configuration.log-impl=?org.apache.ibatis.logging.stdout.StdOutImpl
4、分庫分表實(shí)現(xiàn)
?4.1、先說下數(shù)據(jù)源，結(jié)合之前mysql主從的文章，我本地127.0.0.1:3307端口是主，127.0.0.1:3308端口是從。
???在3307下建立兩個(gè)庫app1和app2，同時(shí)每個(gè)庫里面建立兩張表user1和user2表，用來完成分庫分表。
???下面是app1庫SQL語句：
SET?NAMES?utf8mb4;
SET?FOREIGN_KEY_CHECKS?=?0;
--?----------------------------
--?Table?structure?for?user1
--?----------------------------
DROP?TABLE?IF?EXISTS?`user1`;
CREATE?TABLE?`user1`??(
??`id`?bigint(11)?NOT?NULL?COMMENT?'主鍵id',
??`name`?varchar(255)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci?NULL?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE?=?InnoDB?CHARACTER?SET?=?utf8mb4?COLLATE?=?utf8mb4_general_ci?ROW_FORMAT?=?Dynamic;
--?----------------------------
--?Table?structure?for?user2
--?----------------------------
DROP?TABLE?IF?EXISTS?`user2`;
CREATE?TABLE?`user2`??(
??`id`?bigint(11)?NOT?NULL?COMMENT?'主鍵id',
??`name`?varchar(255)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci?NULL?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE?=?InnoDB?CHARACTER?SET?=?utf8mb4?COLLATE?=?utf8mb4_general_ci?ROW_FORMAT?=?DYNAMIC;
SET?FOREIGN_KEY_CHECKS?=?1;
???下面是app2庫SQL語句：
SET?NAMES?utf8mb4;
SET?FOREIGN_KEY_CHECKS?=?0;
--?----------------------------
--?Table?structure?for?user1
--?----------------------------
DROP?TABLE?IF?EXISTS?`user1`;
CREATE?TABLE?`user1`??(
??`id`?bigint(11)?NOT?NULL?COMMENT?'主鍵id',
??`name`?varchar(255)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci?NULL?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE?=?InnoDB?CHARACTER?SET?=?utf8mb4?COLLATE?=?utf8mb4_general_ci?ROW_FORMAT?=?DYNAMIC;
--?----------------------------
--?Table?structure?for?user2
--?----------------------------
DROP?TABLE?IF?EXISTS?`user2`;
CREATE?TABLE?`user2`??(
??`id`?bigint(11)?NOT?NULL?COMMENT?'主鍵id',
??`name`?varchar(255)?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci?NULL?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE?=?InnoDB?CHARACTER?SET?=?utf8mb4?COLLATE?=?utf8mb4_general_ci?ROW_FORMAT?=?Dynamic;
SET?FOREIGN_KEY_CHECKS?=?1;
?4.2、這里我們解釋一下配置的分庫分表規(guī)則實(shí)現(xiàn)將數(shù)據(jù)插入到app1和app2庫，user1和user2表
????sharding:
??????default-database-strategy:
????????inline:
??????????sharding-column:?id?#分片的字段是id主鍵
??????????algorithm-expression:?app$->{(id?%?2)+1}?#分片的算法是?id對(duì)2求余然后加1
??????tables:
????????user:
??????????actual-data-nodes:?app$->{1..2}.user$->{1..2}??#實(shí)際的數(shù)據(jù)節(jié)點(diǎn)是(app1/app2).(user1/user2)
??????????table-strategy:
????????????inline:
??????????????sharding-column:?id?#分表的分片字段是主鍵id
??????????????algorithm-expression:?user$->{((""+id)[2..10].toInteger()?%?2)+1}?#分表的算法是取id的2-10位對(duì)2求余然后加1
??????????key-generator:
????????????column:?id?#?自動(dòng)生成主鍵
????????????type:?SNOWFLAKE?#?生成主鍵的規(guī)則是雪花算法
???上面配置的規(guī)則指的是，當(dāng)有數(shù)據(jù)要插入數(shù)據(jù)庫，或者進(jìn)行查詢時(shí)，sharding-jdbc通過分片配置的字段id的值，去根據(jù)配置的算法 進(jìn)行運(yùn)算，得到結(jié)果，例如上述分庫規(guī)則，拿到id值 對(duì)2求余加1，那么不管id怎么變化算法返回的值永遠(yuǎn)是1和2，即app$->{(id % 2)+1} 對(duì)應(yīng)的就是app1和app2庫，分表的規(guī)則是同樣道理。
???說明：這里只是配置了簡(jiǎn)單的分片規(guī)則來演示sharding-jdbc如何完成分庫分表，我們也可以使用代碼重寫
方法來實(shí)現(xiàn)更復(fù)雜的分片策略。最后，這里的$->{(id % 2)+1} 的{}中實(shí)際上是一個(gè)Groovy語法的表達(dá)式，sharding-jdbc是通過Groovy語法糖來解析分片策略的。所以想要配置更為復(fù)雜的策略，建議學(xué)一下Groovy語法。
?4.3、接下來我們介紹配置的讀寫分離規(guī)則，如何實(shí)現(xiàn)讀寫分離
??????master-slave-rules:
????????app1:?#分區(qū)?app1
??????????master-data-source-name:?master0?#分區(qū)?app1的主數(shù)據(jù)源
??????????slave-data-source-names:?master0slave0?#分區(qū)?app1的從數(shù)據(jù)源
????????app2:?#分區(qū)?app2
??????????master-data-source-name:?master1?#分區(qū)?app2的主數(shù)據(jù)源
??????????slave-data-source-names:?master1slave0?#分區(qū)?app2的從數(shù)據(jù)源
???上面讀寫分離的規(guī)則指的是，分區(qū)app1的主從數(shù)據(jù)源，分區(qū)app2的主從數(shù)據(jù)源。至于這里的分區(qū)為什么是app1和app2？這里說明一下，我自己配置的時(shí)候，配置了幾次都沒有成功，一開始參照官網(wǎng)手冊(cè)配置，以為分區(qū)名稱可以自定義，于是配置的是ds0和ds1，但是項(xiàng)目啟動(dòng)報(bào)錯(cuò)了。報(bào)錯(cuò)信息是：Cannot find data source in sharding rule, invalid actual data node is: 'app1.user1'
開始以為是使用的sharding-jdbc版本問題，但是換了版本還是有問題，于是開始調(diào)試了一下源碼：
???
??? 從上面的截圖中很明顯就能發(fā)現(xiàn)，這里是要判斷我們配置的分區(qū)集合也就是ds0和ds1是否包含 實(shí)際節(jié)點(diǎn)的數(shù)據(jù)源名稱，也就是數(shù)據(jù)庫名稱。所以這里的分區(qū)名稱是和我們上面配置的分片策略的數(shù)據(jù)庫名稱有關(guān)系的。
?4.4、驗(yàn)證
??? 接下來我們驗(yàn)證實(shí)際的效果。這里貼一下單元測(cè)試的代碼。
/**
?*?@author?cgg
?*?@version?1.0.0
?*?@date?2021/10/25
?*/
@SpringBootTest(classes?=?ShardingJdbcApp.class)
@RunWith(SpringRunner.class)
public?class?AppTest?{
????@Resource
????private?IUserService?userService;
????/**
?????*?測(cè)試sharding-jdbc添加數(shù)據(jù)
?????*/
????@Test
????public?void?testShardingJdbcInsert()?{
????????userService.InsertUser();
????}
????/**
?????*?測(cè)試sharding-jdbc查詢數(shù)據(jù)
?????*/
????@Test
????public?void?testShardingJdbcQuery()?{
????????//全部查詢
????????userService.queryUserList();
????????//根據(jù)指定條件查詢
????????userService.queryUserById(1452619866473324545L);
????}
}
/**
?*?@author?cgg
?*?@version?1.0.0
?*?@date?2021/10/25
?*/
@Service
@Slf4j
public?class?UserServiceImpl?implements?IUserService?{
????@Resource
????private?UserMapper?userMapper;
????@Resource
????private?DataSource?dataSource;
????@Override
????public?List?queryUserList()?{
????????List?userList?=?userMapper.queryUserList();
????????userList.forEach(user?->?System.out.println(user.toString()));
????????return?userList;
????}
????@Override
????public?User?queryUserById(Long?id)?{
????????User?user?=?userMapper.selectOne(Wrappers.lambdaQuery().eq(User::getId,?id));
????????System.out.println(user.toString());
????????return?user;
????}
????@Override
????public?void?InsertUser()?{
????????for?(int?i?=?20;?i?????????????User?user?=?new?User();
????????????user.setName("XX-"?+?i);
????????????int?count?=?userMapper.insert(user);
????????????System.out.println(count);
????????}
????}
}
????4.4.1、首先看全部查詢的結(jié)果
????4.4.2、再看下單條查詢的結(jié)果
????4.4.3、再看下新增結(jié)果（實(shí)際插入到了主數(shù)據(jù)源的app1庫user1表，并且后續(xù)每條插入都是走的主數(shù)據(jù)源，沒有slave的操作）
??作者?|??coffeebabe
來源 |??cnblogs.com/wa1l-E/p/15465884.html
