A preface.
This is a simple tutorial based on SpringBoot integration Sharding-JDBC to achieve read and write separation, the author used the technology and version as follows:
SpringBoot 2.5.2
MyBatis – Plus rule 3.4.3
Sharding – JDBC 4.4.1
MySQL8 cluster (see my previous article for a deployment tutorial)
Ii. Project directory structure
3. Pom file
<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 - the 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 dependencies -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>Rule 3.4.3</version>
</dependency>
<! -- Mysql driver -->
<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>
Copy the codeConfiguration files (based on YAML) and SQL building statements
spring:
main:
allow-bean-definition-overriding: true
# according to SQL
shardingsphere:
props:
sql:
show: true
masterslave:
Configure the primary and secondary names
name: ms
Select * from 'master'; select * from 'master'
master-data-source-name: ds1
Configure the slave node
slave-data-source-names: ds2,ds3
# Configure load balancing policies for slave nodes. Round robin is adopted. There are two algorithms: round_robin and Random.
load-balance-algorithm-type: round_robin
sharding:
Ds1 default data source, mainly used for writing
default-data-source-name: ds1
# configure data source
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
Copy the codeCREATE 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;
Copy the codeMapper. XML file and Mapper interface
<! DOCTYPEmapper 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>
Copy the codepackage 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(a);
}
Copy the code6.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(a) {
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(a) {
returnuserMapper.findUsers(); }}Copy the codepackage 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;
}
Copy the codeResults seven.
As can be seen from the results, all write operations are completed by ds1 database (master), while read operations are completed by DS2 (slaver) and DS3 (slaver2) databases because of polling. In this way, the read-write separation operation based on one master and two slave database cluster is realized.
8. Configuration on different versions of Sharding-JDBC
The Sharding JDBC tutorial on the Internet is mostly 4.0.0.RC1 version, the author uses the latest 4.1.1, so in this part of the database address in 4.1.1 is jDBC-URL, in 4.0.0.RC1 need to be changed to URL, otherwise the startup will fail
jdbc-url: jdbc:mysql://XXXX/XXXX
Copy the codeAnd most of the online tutorials are properties files, the author will convert them into YAML files more intuitive feeling
The project download
GitHub:github.com/z875479694h…
contact
Official account: Qingshan Youlu Github: github.com/z875479694h Blog: www.hcworld.xyz