Spring boot integrates sharding JDBC to realize MySQL 8 read-write separation

Time:2021-11-28

1、 Foreword

This is a minimalist tutorial based on the integration of springboot and sharding JDBC to realize the separation of reading and writing. The technologies and versions used by the author are as follows:

2、 Project directory structure

image.png

3、 POM file

<?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 > multi data source switching instance < / 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 -- >
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3</version>
        </dependency>
        <!--     MySQL driver -- >
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--   Sharding JDBC (multi data source switching) - >
        <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>

4、 Configuration file (based on yaml) and SQL table creation statement

spring:
  main:
    allow-bean-definition-overriding: true
  #Show SQL
  shardingsphere:
    props:
      sql:
        show: true
    masterslave:
      #Configure master-slave name
      name: ms
      #Set the master database to be responsible for writing data
      master-data-source-name: ds1
      #Configuring slave slave nodes
      slave-data-source-names: ds2,ds3
      #Configure the load balancing strategy of the slave node and adopt the polling mechanism. There are two algorithms: round_ Robin (polling) and random (random)
      load-balance-algorithm-type: round_robin
    sharding:
      #Configure the default data source DS1 default data source, which is mainly used for writing
      default-data-source-name: ds1
    #Configure data sources
    datasource:
      names: ds1,ds2,ds3
      #Master-ds1 database connection information
      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 database connection information
      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 database connection information
      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 configuration
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;

5、 Mapper.xml file and mapper interface


<?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>
        INSERT INTO t_user(nickname,PASSWORD,sex,birthday) VALUES(#{nickname},#{password},#{sex},#{birthday})
    </update>

    <select 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: Zhang Hongchen
 * @Date: 2021-07-20
 * @Version: 1.0
 */
@Component
public interface UserMapper  extends BaseMapper<User> {


    void addUser(User user);


    List<User> findUsers();
}

6、 Controller and mocel files

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: Zhang Hongchen
 * @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: Zhang Hongchen
 * @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;
}

7、 Results

It can be seen from the results that all write operations are completed through the DS1 (Master) database, while read operations are completed by the DS2 (slave) and DS3 (slaver2) databases because polling is set. In this way, the read-write separation operation based on one master and two slave database clusters is realized.

image.png

8、 Configuration on different versions of sharding JDBC

Most of the online sharding JDBC tutorials are version 4.0.0.rc1, and the author uses the latest version 4.1.1, so
In this part, the database address is JDBC URL in 4.1.1 and needs to be changed to URL in 4.0.0.rc1, otherwise the startup will fail


jdbc-url: jdbc:mysql://XXXX/XXXX

And most of the online tutorials are properties files, so the author can change them into yaml files for more intuitive feeling

Project Download

GitHub:https://github.com/z875479694h/sharding-jdbc-demo

This is the end of this article about integrating spring boot sharding JDBC to realize mysql8 read-write separation. For more information about spring boot sharding JDBC mysql8 read-write separation, please search for previous articles of developpaer or continue to browse the relevant articles below. I hope you will support developpaer more in the future!

Recommended Today

Docker cannot stop or delete the solution to the container service problem

preface Today, a development classmate gave me feedback that a container service cannot stop, RM (docker RM – F) and kill operations, that is, the container service cannot be terminated ~ Operation steps (1) The directory of docker cannot be deleted by executing the delete command: # ll /var/lib/docker/containers | grep caf8ef20f3c1 # cd /var/lib/docker/containers […]