Sharding-JDBC教程-5：Spring Boot整合Sharding-JDBC实现读写分离+分库分表

青旅半醒 2022-10-12 01:06 24阅读 0赞

### 1、前言 ###

> 这是Sharding-JDBC的第5篇教程，上篇文章我实现了读写分离+分表的案例，这次我准备来实现读写分离+分库分表，废话不多说，开搞。

首先我们先看个图，此图是方志朋作者画的，我觉得很清晰，拿来分享一下，图上画的架构，也是我下面要实现的配置。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70]

### 2、节点分布 ###

节点分布2个主库，4个从库，分布如下：

<table> 
 <thead> 
  <tr> 
   <th>数据库类型</th> 
   <th>数据库</th> 
   <th>IP</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主</td> 
   <td>cool</td> 
   <td>192.168.88.161</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool</td> 
   <td>192.168.88.162</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool</td> 
   <td>192.168.88.163</td> 
  </tr> 
  <tr> 
   <td>主</td> 
   <td>cool2</td> 
   <td>192.168.88.161</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool2</td> 
   <td>192.168.88.162</td> 
  </tr> 
  <tr> 
   <td>从</td> 
   <td>cool2</td> 
   <td>192.168.88.163</td> 
  </tr> 
 </tbody> 
</table>

### 3、主库建表，从库自动同步 ###

在192.168.88.161上，cool和cool2库上分别执行以下建表语句

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/> 
        </parent>
    
        <groupId>com.ckj</groupId>
        <artifactId>shardingsphere-jdbc-lwh</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>shardingsphere-jdbc-lwh</name>
    
        <description>分库分表测试项目</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>
            
        </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、目录结构如下 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 1]

### 6、主要配置 ###

#-----------------------------------------库表分片+读写分离---------------------------------------------------------------
    # 配置真实数据源
    spring.shardingsphere.datasource.names=ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-0-slave-1,ds-master-1-slave-0,ds-master-1-slave-1
    
    # 配置第1个主库的数据源
    spring.shardingsphere.datasource.ds-master-0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-0.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-0.jdbc-url=jdbc:mysql://node1:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-0.username=root
    spring.shardingsphere.datasource.ds-master-0.password=123456
    
    # 配置第1个主库的第1个从库的数据源
    spring.shardingsphere.datasource.ds-master-0-slave-0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-0-slave-0.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-0-slave-0.jdbc-url=jdbc:mysql://node2:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-0-slave-0.username=root
    spring.shardingsphere.datasource.ds-master-0-slave-0.password=123456
    
    # 配置第1个主库的第2个从库的数据源
    spring.shardingsphere.datasource.ds-master-0-slave-1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-0-slave-1.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-0-slave-1.jdbc-url=jdbc:mysql://node3:3306/cool?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-0-slave-1.username=root
    spring.shardingsphere.datasource.ds-master-0-slave-1.password=123456
    
    # 配置第2个主库的数据源
    spring.shardingsphere.datasource.ds-master-1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-1.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-1.jdbc-url=jdbc:mysql://node1:3306/cool2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-1.username=root
    spring.shardingsphere.datasource.ds-master-1.password=123456
    
    # 配置第2个主库的第1个从库的数据源
    spring.shardingsphere.datasource.ds-master-1-slave-0.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-1-slave-0.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-1-slave-0.jdbc-url=jdbc:mysql://node2:3306/cool2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-1-slave-0.username=root
    spring.shardingsphere.datasource.ds-master-1-slave-0.password=123456
    
    # 配置第2个主库的第2个从库的数据源
    spring.shardingsphere.datasource.ds-master-1-slave-1.type=com.zaxxer.hikari.HikariDataSource
    spring.shardingsphere.datasource.ds-master-1-slave-1.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.shardingsphere.datasource.ds-master-1-slave-1.jdbc-url=jdbc:mysql://node3:3306/cool2?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT
    spring.shardingsphere.datasource.ds-master-1-slave-1.username=root
    spring.shardingsphere.datasource.ds-master-1-slave-1.password=123456
    
    server.port= 8085
    #------------------------------mybatisPlus配置----------------------------
    mybatis-plus.mapper-locations=classpath*:/mappers/*.xml
    mybatis-plus.type-aliases-package=com.ckj.sharding.sphere.jdbc.lwh.entity
    #------------------------------mybatisPlus配置----------------------------
    
    #多个数据源必须要配置的，允许类型重复
    spring.main.allow-bean-definition-overriding=true
    #打印执行SQL
    spring.shardingsphere.props.sql.show=true
    
    #主键生成策略，目前采用雪花算法
    spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
    spring.shardingsphere.sharding.tables.user.key-generator.column=id
    
    #------------------------------表分片-----------------------------
    #指定物理库，物理表
    spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds-master-$->{ 0..1}.user_$->{ 0..3}
    #指定表分片字段，行表达式
    spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
    #指定表分片策略，行表达式，标准分片策略
    spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{ id % 4}
    spring.shardingsphere.sharding.binding-tables=user
    
    #-----------------------------库分片------------------------------
    spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
    spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds-master-$->{ id % 2}
    
    #配置主库和每个主库的从库，进行读写分离
    spring.shardingsphere.sharding.master-slave-rules.ds-master-0.master-data-source-name=ds-master-0
    spring.shardingsphere.sharding.master-slave-rules.ds-master-0.slave-data-source-names=ds-master-0-slave-0, ds-master-0-slave-1
    spring.shardingsphere.sharding.master-slave-rules.ds-master-1.master-data-source-name=ds-master-1
    spring.shardingsphere.sharding.master-slave-rules.ds-master-1.slave-data-source-names=ds-master-1-slave-0, ds-master-1-slave-1

### 7、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);
    }

### 8、接口代码 ###

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 { 
    
        @Autowired
        private 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);
        }
    }

### 9、启动类 ###

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);
        }
    
    }

### 10、执行插入 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 2]

### 11、查询 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 3]

### 12、结束语 ###

> 到这里Sharding-JDBC的教程就结束了，此系列文章是作者实操出来的，所以按照配置执行是没有问题的。

参考资料：  
https://forezp.blog.csdn.net/article/details/94343671

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70]: /images/20221005/8ac098e92a5e46bdb25feed67518249d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 1]: /images/20221005/fa6140133cc140a69cbf258dcc07c797.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 2]: /images/20221005/26251283ec6142769cf1fbc4d56efe79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2w4NDgxNjg_size_16_color_FFFFFF_t_70 3]: /images/20221005/a9d1709f6e73429386ec5ad028a592a3.png