SpringBoot整合Sharding-JDBC實現MySQL8讀寫分離
一.前言
這是一個基於SpringBoot整合Sharding-JDBC實現讀寫分離的極簡教程,筆者使用到的技術及版本如下:
- SpringBoot 2.5.2
- MyBatis-Plus 3.4.3
- Sharding-JDBC 4.1.1
- MySQL8集群(看筆者前一篇文章有部署教程)
二.項目目錄結構
三.pom文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.2</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>xyz.hcworld</groupId> <artifactId>sharding-jdbc-demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding-jdbc-demo</name> <description>多數據源切換實例</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- mybatis-plus依賴 --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3</version> </dependency> <!-- mysql驅動 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- sharding-jdbc(多數據源切換) --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
四.配置文件(基於YAML)及SQL建表語句
spring: main: allow-bean-definition-overriding: true #顯示sql shardingsphere: props: sql: show: true masterslave: #配置主從名稱 name: ms #置主庫master,負責數據的寫入 master-data-source-name: ds1 #配置從庫slave節點 slave-data-source-names: ds2,ds3 #配置slave節點的負載均衡均衡策略,采用輪詢機制,有兩種算法:round_robin(輪詢)和random(隨機) load-balance-algorithm-type: round_robin sharding: #配置默認數據源ds1 默認數據源,主要用於寫 default-data-source-name: ds1 # 配置數據源 datasource: names: ds1,ds2,ds3 #master-ds1數據庫連接信息 ds1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://192.168.2.142:3307/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 #slave-ds2數據庫連接信息 ds2: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://192.168.2.142:3308/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 #slave-ds3數據庫連接信息 ds3: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://192.168.2.142:3309/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 #mybatis-plus配置 mybatis-plus: type-aliases-package: xyz.hcworld.demo.model mapper-locations: classpath*:/mapper/**Mapper.xml
CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nickname` varchar(100) DEFAULT NULL, `password` varchar(100) DEFAULT NULL, `sex` int(11) DEFAULT NULL, `birthday` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
五.Mapper.xml文件及Mapper接口
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="xyz.hcworld.demo.mapper.UserMapper"> <update id="addUser"> INSERT INTO t_user(nickname,PASSWORD,sex,birthday) VALUES(#{nickname},#{password},#{sex},#{birthday}) </update> <select id="findUsers" resultType="xyz.hcworld.demo.model.User"> SELECT id, nickname, PASSWORD, sex, birthday FROM t_user; </select> </mapper>
package xyz.hcworld.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import org.springframework.stereotype.Component; import xyz.hcworld.demo.model.User; import java.util.List; /** * @ClassName: UserMapper * @Author: 張紅塵 * @Date: 2021-07-20 * @Version: 1.0 */ @Component public interface UserMapper extends BaseMapper<User> { void addUser(User user); List<User> findUsers(); }
六 .Controller及Mocel文件
package xyz.hcworld.demo.controller; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import xyz.hcworld.demo.mapper.UserMapper; import xyz.hcworld.demo.model.User; import java.security.SecureRandom; import java.util.List; /** * @ClassName: UserController * @Author: 張紅塵 * @Date: 2021-07-20 * @Version: 1.0 */ @RestController @RequestMapping("/api/user") public class UserController { @Autowired private UserMapper userMapper; @PostMapping("/save") public String addUser() { User user = new User(); user.setNickname("zhangsan" + new SecureRandom().nextInt()); user.setPassword("123456"); user.setSex(1); user.setBirthday("1997-12-03"); userMapper.addUser(user); return user.toString(); } @GetMapping("/findUsers") public List<User> findUsers() { return userMapper.findUsers(); } }
package xyz.hcworld.demo.model; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; import lombok.EqualsAndHashCode; /** * @ClassName: User * @Author: 張紅塵 * @Date: 2021-07-20 * @Version: 1.0 */ @Data @TableName("t_user") public class User { private static final long serialVersionUID = 1L; @TableId(value = "id", type = IdType.AUTO) private Integer id; private String nickname; private String password; private Integer sex; private String birthday; }
七.結果
從結果可以看出,寫入操作全部通過ds1數據庫(master)數據庫完成,而讀操作因為設置瞭輪詢的緣故,由ds2(slaver)、ds3(slaver2)數據庫完成。這樣就實現瞭基於一主二從的數據庫集群的讀寫分離操作。
八.Sharding-JDBC不同版本上的配置
網上Sharding-JDBC的教程多為4.0.0.RC1版本,筆者使用的是最新的4.1.1所以
在該部分數據庫地址在4.1.1為jdbc-url在4.0.0.RC1上需要改為url否則會啟動失敗
jdbc-url: jdbc:mysql://XXXX/XXXX
且網上教程多為properties文件,筆者將其轉變為YAML文件更加能直觀感受
項目下載
GitHub:https://github.com/z875479694h/sharding-jdbc-demo
到此這篇關於SpringBoot整合Sharding-JDBC實現MySQL8讀寫分離的文章就介紹到這瞭,更多相關SpringBoot Sharding-JDBC MySQL8讀寫分離內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 使用sharding-jdbc實現水平分表的示例代碼
- 使用IDEA配置Mybatis-Plus框架圖文詳解
- SpringBoot MyBatis簡單快速入門例子
- Sharding-JDBC自動實現MySQL讀寫分離的示例代碼
- 一篇超詳細的Spring Boot整合Mybatis文章