1、前言

這是第4篇Sharding-JDBC教程，前面實(shí)現(xiàn)了多個(gè)庫之間的讀寫分離教程，這次我準(zhǔn)備實(shí)現(xiàn)多庫之間讀寫分離以及寫入到一個(gè)主庫不同的分表當(dāng)中的教程。

2、分片相關(guān)介紹

3、主庫建表，從庫自動同步

DROP TABLE IF EXISTS `user_0`; CREATE TABLE `user_0` (`id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx-username`(`username`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` (`id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx-username`(`username`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` (`id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx-username`(`username`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;DROP TABLE IF EXISTS `user_3`; CREATE TABLE `user_3` (`id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`username` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx-username`(`username`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

4、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>com.ckj</groupId><artifactId>shardingsphere-jdbc-lwh</artifactId><version>0.0.1-SNAPSHOT</version><name>shardingsphere-jdbc-lwh</name><description>分庫分表測試項(xiàng)目</description><properties><java.version>1.8</java.version><sharding-sphere.version>4.0.0-RC1</sharding-sphere.version><hutool-all.version>5.4.7</hutool-all.version><guava.version>19.0</guava.version><HikariCP.version>4.0.3</HikariCP.version><mybatis-plus.version>3.1.2</mybatis-plus.version></properties><dependencies><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><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>${hutool-all.version}</version></dependency><dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>${guava.version}</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><!-- 分庫分表--><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>${sharding-sphere.version}</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-core-common</artifactId><version>${sharding-sphere.version}</version></dependency><!-- 分庫分表 end--></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>

5、目錄結(jié)構(gòu)

6、標(biāo)準(zhǔn)分片類

package com.ckj.sharding.sphere.jdbc.lwh.config;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import java.util.Collection;/*** @Author: lwh* @Date: 2021/6/26* @Description: 標(biāo)準(zhǔn)分片策略**/ public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {/*** Sharding.** @param availableTargetNames available data sources or tables's names* @param shardingValue sharding value* @return sharding result for data source or table's name*/@Overridepublic String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {for (String tableName : availableTargetNames) {if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {return tableName;}}throw new IllegalArgumentException();} }

7、主要配置

# 配置真實(shí)數(shù)據(jù)源 spring.shardingsphere.datasource.names=ds0,ds1,ds2# 配置第 1 個(gè)數(shù)據(jù)源 spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://node1:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT spring.shardingsphere.datasource.ds0.username=root spring.shardingsphere.datasource.ds0.password=123456# 配置第 2 個(gè)數(shù)據(jù)源 spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://node2:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=123456# 配置第 3 個(gè)數(shù)據(jù)源 spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds2.jdbc-url=jdbc:mysql://node3:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT spring.shardingsphere.datasource.ds2.username=root spring.shardingsphere.datasource.ds2.password=123456#---------------------配置讀寫分離--------------------------------------- #設(shè)置讀的時(shí)候采用那種算法，round_robin代表輪詢 #spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin #spring.shardingsphere.masterslave.name=ms #設(shè)置主庫的名稱，來源于上面的配置 #spring.shardingsphere.masterslave.master-data-source-name=ds0 #設(shè)置從庫的名稱，來源于上面的配置 #spring.shardingsphere.masterslave.slave-data-source-names=ds1,ds2 #---------------------配置讀寫分離---------------------------------------#主鍵生成策略，目前采用雪花算法 spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.user.key-generator.column=idserver.port= 8085 #------------------------------mybatisPlus配置---------------------------- mybatis-plus.mapper-locations=classpath*:/mappers/*.xml mybatis-plus.type-aliases-package=com.ckj.sharding.sphere.jdbc.lwh.entity #------------------------------mybatisPlus配置----------------------------#多個(gè)數(shù)據(jù)源必須要配置的，允許類型重復(fù) spring.main.allow-bean-definition-overriding=true #打印執(zhí)行SQL spring.shardingsphere.props.sql.show=true#分表加讀寫分離 spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{0..3} #分片策略-行表達(dá)式 #spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id #spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4} #---------------------------分片策略-標(biāo)準(zhǔn)分片策略----------------------------------- #分片字段 spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id #標(biāo)準(zhǔn)分片類 spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name= com.ckj.sharding.sphere.jdbc.lwh.config.MyPreciseShardingAlgorithm #主庫 spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=ds0 #從庫 spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=ds1,ds2

8、XML和dao代碼

<?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="com.ckj.sharding.sphere.jdbc.lwh.dao.UserMapper"><resultMap id="baseResultMap" type="com.ckj.sharding.sphere.jdbc.lwh.entity.User"><result column="id" property="id" jdbcType="VARCHAR" /><result column="username" property="username" jdbcType="VARCHAR" /><result column="password" property="password" jdbcType="VARCHAR" /></resultMap><insert id="addUser">INSERT INTO user (username, password)VALUES (#{username,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR})</insert><select id="list" resultMap="baseResultMap">SELECT u.* FROM user u<where><if test="username != null and username != ''">username = #{username}</if></where></select></mapper> package com.ckj.sharding.sphere.jdbc.lwh.dao;import com.ckj.sharding.sphere.jdbc.lwh.entity.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param;import java.util.List;/*** @Author: lwh* @Date: 2021/6/25* @Description:**/ @Mapper public interface UserMapper {Long addUser(User user);List<User> list(@Param("username") String username); }

9、接口代碼

package com.ckj.sharding.sphere.jdbc.lwh.controller;import com.ckj.sharding.sphere.jdbc.lwh.entity.User; import com.ckj.sharding.sphere.jdbc.lwh.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController;/*** @Author: lwh* @Date: 2021/6/26* @Description:**/ @RestController public class UserController {@Autowiredprivate UserService userService;@GetMapping("/users")public Object list( @RequestParam String username) {return userService.list(username);}@GetMapping("/add")public Object add( @RequestParam String username, @RequestParam String password) {User user = new User();user.setUsername(username);user.setPassword(password);return userService.addUser(user);} }

10、啟動類

package com.ckj.sharding.sphere.jdbc.lwh;import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication @MapperScan(basePackages="com.ckj.sharding.sphere.jdbc.lwh.dao.*") public class ShardingsphereJdbcLwhApplication {public static void main(String[] args) {SpringApplication.run(ShardingsphereJdbcLwhApplication.class, args);}}

11、執(zhí)行插入

12、查詢?nèi)罩?/h3>

13、結(jié)束語

至此到這里就實(shí)現(xiàn)了讀寫分離+分表的過程，下篇文章會實(shí)現(xiàn)分庫分表+讀寫分離的教程。

白嫖到這，我勸你善良，點(diǎn)個(gè)贊再走吧

總結(jié)

以上是生活随笔為你收集整理的Sharding-JDBC教程-4：Spring Boot整合Sharding-JDBC实现读写分离+分表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。