ShardingSphere(七) 讀寫分離配置，實現分庫讀寫操作
liuhenghui5201
於 2021-02-04 10:27:05 釋出
閱讀量3.1k
收藏 9
點贊數 1
分類專欄： ShardingSphere 文章標籤： Shardingjdbc 讀寫分離
版權
ShardingSphere
專欄收錄該內容
8 篇文章11 訂閱
訂閱專欄
概述：本章透過介紹使用ShardingSphere實現資料庫的讀寫分離操作。在實現讀寫分離之前，資料庫的主從同步需要提前配置完成，主從同步實現不由Sharding提供。主從同步可參考上一章節《ShardingSphere(六) 讀寫分離之mysql 主從同步配置》。
環境：SpringBoot 2.2 + mybatis plus3.0 + Sharding jdbc4.0
需求：實現資料庫新增修改刪除操作操作3306埠的主庫（3306/user_db），查詢操作讀取3307埠的從庫（3307/user_db）。
專案目錄結構：
步驟：
準備資料庫環境
idea建立maven工程，引入依賴
建立主啟動類
編寫業務程式碼
新增配置檔案
編寫測試程式碼測試驗證
一、準備資料庫環境
準備兩個資料庫伺服器，實現主從同步配置，兩個庫user_db中都建立t_user表,表結構如下：
CREATE TABLE `t_user` (
`user_id` bigint(20) NOT NULL,
`username` varchar(50) NOT NULL,
`ustatus` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
二、建立maven工程，引入依賴
建立maven工程，引入Shardingjdbc以及其他相關依賴，pom.xml 如下
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="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">
<modelVersion>4.0.0</modelVersion>
<groupId>org.sharding</groupId>
<artifactId>sharding</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
三、建立主啟動類
src/main/java/com/xiaohui/ShardingApplication.java
package com.xiaohui;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.xiaohui.mapper")
public class ShardingApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingApplication.class,args);
}
}
四、編寫業務程式碼
主要為資料庫實體類：src/main/java/com/xiaohui/entity/User.java
資料庫層介面類：src/main/java/com/xiaohui/mapper/UserMapper.java
package com.xiaohui.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value="t_user")
public class User {
private Long userId;
private String username;
private String ustatus;
}
package com.xiaohui.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaohui.entity.User;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper extends BaseMapper<User> {
}
五、新增配置檔案application.properties（重點）
#sharding-jdbc 讀寫分離
#=================================資料來源配置部分=====================================
#資料來源名稱，多資料來源以逗號分隔
spring.shardingsphere.datasource.names=ds2,slave0
#配置 ds0 ds1資料來源具體內容
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://127.0.0.1:3306/user_db
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=root
#從庫
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://127.0.0.1:3307/user_db
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root
#=================================主從資料庫宣告部分====================================
#配置指定主從資料庫
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds2
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0
#=================================資料節點配置部分======================================
#設定表資料節點（最後的ms0 一定要注意，不能寫ds2.t_user）
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ms0.t_user
#=================================資料庫分表策略========================================
#表策略table-strategy
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
#==================================資料庫欄位生成策略====================================
#欄位生成策略key-generator
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
#==================================其他配置=============================================
#解決報錯 Consider renaming one of the beans or enabling overriding...
spring.main.allow-bean-definition-overriding=true
#開啟sql日誌輸出
spring.shardingsphere.props.sql.show=true
主從同步主要配置的為spring.shardingsphere.sharding.master-slave-rules 配置
spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds2
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-names=slave0
為主從同步部取一個名稱為ms0，並指定其主資料庫ds2，和從資料庫slave0。
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ms0.t_user
在設定表的資料節點時主要注意的是不能寫主庫或從庫的資料庫節點名稱如ds2.t_user。。需要使用主從同步配置設定的節點名稱ms0。
六、編寫測試程式碼測試驗證
src/test/java/com/xiaohui/MainTest.java
package com.xiaohui;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xiaohui.entity.User;
import com.xiaohui.mapper.UserMapper;
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.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class MainTest {
@Autowired
private UserMapper userMapper;
@Test
public void testUserAdd(){
User u = new User();
u.setUsername("張三");
u.setUstatus("1");
userMapper.insert(u);
}
@Test
public void testUserGet(){
QueryWrapper wrapper = new QueryWrapper<User>();
wrapper.eq("user_id", 564030748839903233L);
User user = userMapper.selectOne(wrapper);
System.out.println(user);
}
}
執行儲存操作testUserAdd()，列印日誌如下： 透過實際的sql 我們可以看到其操作的為ds2 即我們的主資料庫。
2021-02-04 10:16:49.743 INFO 9428 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2021-02-04 10:16:49.746 INFO 9428 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( username,ustatus ) VALUES ( ?,? )
2021-02-04 10:16:49.746 INFO 9428 --- [ main] ShardingSphere-SQL : Actual SQL: ds2 ::: INSERT INTO t_user (username, ustatus, user_id) VALUES (?, ?, ?) ::: [張三, 1, 564030748839903233]
執行查詢操作，列印日誌如下：透過實際的sql 我們可以看到查詢讀取的為 slave0 從庫，即已實現資料讀寫分離。
2021-02-04 10:20:13.929 INFO 1544 --- [ main] ShardingSphere-SQL : Rule Type: sharding
2021-02-04 10:20:13.931 INFO 1544 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT user_id,username,ustatus FROM t_user WHERE user_id = ?
2021-02-04 10:20:13.931 INFO 1544 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=user_id, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=SELECT user_id,username,ustatus FROM t_user WHERE user_id = ?)), containStar=false, firstSelectItemStartIndex=8, selectListStopIndex=31, groupByLastIndex=0, items=[CommonSelectItem(expression=user_id, alias=Optional.absent()), CommonSelectItem(expression=username, alias=Optional.absent()), CommonSelectItem(expression=ustatus, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-02-04 10:20:13.932 INFO 1544 --- [ main] ShardingSphere-SQL : Actual SQL: slave0 ::: SELECT user_id,username,ustatus FROM t_user WHERE user_id = ? ::: [564030748839903233]
————————————————
版權宣告：本文為博主原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處連結和本宣告。
原文連結：https://blog.csdn.net/liuhenghui5201/article/details/113603730