Sharding-JDBC教程-4:Spring Boot整合Sharding-JDBC实现读写分离+分表

1、前言

这是第4篇Sharding-JDBC教程,前面实现了多个库之间的读写分离教程,这次我准备实现多库之间读写分离以及写入到一个主库不同的分表当中的教程。

2、分片相关介绍


在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

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>分库分表测试项目</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、目录结构


在这里插入图片描述

6、标准分片类

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: 标准分片策略
 **/
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
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(shardingValue.getValue() % 4 + "")) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }
}

7、主要配置

# 配置真实数据源
spring.shardingsphere.datasource.names=ds0,ds1,ds2

# 配置第 1 个数据源
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 个数据源
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 个数据源
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

#---------------------配置读写分离---------------------------------------
#设置读的时候采用那种算法,round_robin代表轮询
#spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
#spring.shardingsphere.masterslave.name=ms
#设置主库的名称,来源于上面的配置
#spring.shardingsphere.masterslave.master-data-source-name=ds0
#设置从库的名称,来源于上面的配置
#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=id

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.actual-data-nodes=ds0.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.tables.user.table-strategy.standard.sharding-column=id
#标准分片类
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 {

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

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、执行插入


在这里插入图片描述

12、查询日志


在这里插入图片描述

13、结束语

至此到这里就实现了读写分离+分表的过程,下篇文章会实现分库分表+读写分离的教程。

白嫖到这,我劝你善良,点个赞再走吧

  • 3
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

技术武器库

一句真诚的谢谢,胜过千言万语

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值