大佬栽树,我乘凉
许大仙老师:【yuque.com/fairy-era/yg511q/ud9uli67b6gxgdh7】
开整
数据库准备
一主两从
数据库脚本
CREATE DATABASE IF NOT EXISTS dbtest CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
USE dbtest;
-- 创建表
CREATE TABLE t_user (
id INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR (30)
);
创建SpringBoot工程
- SpringBoot 2.7.3
- ShardingSphere 5.2.1
项目结构
依赖引入
父工程
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>pers.kw</groupId>
<artifactId>ShardingSphere</artifactId>
<version>1.0-SNAPSHOT</version>
<modules>
<module>ShardingSphere-hello</module>
<module>ShardingSphere-mybatis-plus</module>
</modules>
<packaging>pom</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<skipTests>true</skipTests>
<spring-boot.version>2.7.3</spring-boot.version>
<minio.version>8.2.2</minio.version>
</properties>
<!-- 依赖声明 -->
<dependencyManagement>
<dependencies>
<!-- SpringBoot 依赖配置 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<!--服务监控-->
<dependency>
<groupId>de.codecentric</groupId>
<artifactId>spring-boot-admin-starter-server</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>de.codecentric</groupId>
<artifactId>spring-boot-admin-starter-client</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-jwt</artifactId>
<version>1.0.9.RELEASE</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.20</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<!--链路追踪-->
<dependency>
<groupId>org.apache.skywalking</groupId>
<artifactId>apm-toolkit-logback-1.x</artifactId>
<version>8.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.skywalking</groupId>
<artifactId>apm-toolkit-trace</artifactId>
<version>8.8.0</version>
</dependency>
<!--数据源依赖-->
<!-- <dependency>-->
<!-- <groupId>com.alibaba</groupId>-->
<!-- <artifactId>druid-spring-boot-starter</artifactId>-->
<!-- <version>1.2.6</version>-->
<!-- </dependency>-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.7</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.8</version>
<scope>compile</scope>
</dependency>
<!--swagger配置-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.20</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-models</artifactId>
<version>1.5.20</version>
</dependency>
<!--bean转换-->
<dependency>
<groupId>net.sf.dozer</groupId>
<artifactId>dozer-spring</artifactId>
<version>5.5.1</version>
</dependency>
<dependency>
<groupId>net.sf.dozer</groupId>
<artifactId>dozer</artifactId>
<version>5.5.1</version>
</dependency>
<dependency>
<groupId>org.redisson</groupId>
<artifactId>redisson</artifactId>
<version>3.11.6</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
<dependency>
<artifactId>commons-lang3</artifactId>
<groupId>org.apache.commons</groupId>
<version>3.12.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.6.0</version>
<configuration>
<source>${java.version}</source>
<target>${java.version}</target>
<encoding>${project.build.sourceEncoding}</encoding>
</configuration>
</plugin>
</plugins>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.7.7</version>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
子工程
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>ShardingSphere</artifactId>
<groupId>pers.kw</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<packaging>jar</packaging>
<artifactId>ShardingSphere-hello</artifactId>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.skywalking</groupId>
<artifactId>apm-toolkit-logback-1.x</artifactId>
</dependency>
<dependency>
<groupId>org.apache.skywalking</groupId>
<artifactId>apm-toolkit-trace</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
</dependencies>
</project>
yaml配置
server:
port: 8080
spring:
application:
name: ShardingSphere-hello
jpa:
database-platform: org.hibernate.dialect.MySQL8Dialect
show-sql: true
hibernate:
ddl-auto: none
shardingsphere:
mode:
type: Standalone # 单机模式
repository:
type: JDBC # JDBC
datasource:
names: m1,s1,s2
m1: # 配置第一个主机
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://10.211.55.88:3306/dbtest?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
s1: # 配置第一个从机
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://10.211.55.88:3307/dbtest?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
s2: # 配置第二个从机
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://10.211.55.88:3309/dbtest?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
rules:
readwrite-splitting:
data-sources:
readwrite_ds: # 读写分离逻辑数据源名称
staticStrategy: # 读写分离类型为静态读写分离
writeDataSourceName: m1
readDataSourceNames:
- s1
- s2
loadBalancerName: alg_round # 负责均衡算法的名称
load-balancers:
alg_round: # 负载均衡算法的名称
type: ROUND_ROBIN # 轮询负载均衡算法
props:
sql-show: true # 打印 SQL
#分页插件
#pagehelper:
# helper-dialect: mysql
# reasonable: true
# support-methods-arguments: true
# params: count=countSql
#mybatis:
# type-aliases-package: pers.kw.**.dto
# mapper-locations: classpath*:mapper/**/*.xml
# configuration:
# map-underscore-to-camel-case: true
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
实体类
package pers.kw.persist.entity;
import lombok.Data;
import javax.persistence.*;
@Data
@Entity
@Table(name = "t_user")
public class User {
@Basic
@Column(name = "id")
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "uname")
private String uname;
}
dao
package pers.kw.persist.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import pers.kw.persist.entity.User;
import java.util.List;
public interface UserDao extends JpaRepository<User, Long> {
@Query("from User")
List<User> listUser();
}
service
package pers.kw.service;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import pers.kw.persist.dao.UserDao;
import pers.kw.persist.entity.User;
import javax.annotation.Resource;
import java.util.List;
@Slf4j
@Service
public class UserService {
@Resource
private UserDao userDao;
@Transactional(rollbackFor = Exception.class)
public void addUser() {
User user = new User();
user.setUname("kw");
userDao.save(user);
}
public void readWrite1() {
User user = new User();
user.setUname("杜甫");
userDao.save(user);
List<User> all = userDao.listUser();
log.info("list:{}", JSON.toJSONString(all));
}
@Transactional(rollbackFor = Exception.class)
public void readWrite2() {
User user = new User();
user.setUname("李白");
userDao.save(user);
List<User> all = userDao.listUser();
log.info("list:{}", JSON.toJSONString(all));
}
public void readLb() {
List<User> all = userDao.listUser();
log.info("list1 listUser:{}", JSON.toJSONString(all));
List<User> l2 = userDao.listUser();
log.info("list2 listUser:{}", JSON.toJSONString(l2));
List<User> l3 = userDao.listUser();
log.info("list3 listUser:{}", JSON.toJSONString(l3));
}
public void readLb2() {
//查询的还是主库 SimpleJpaRepository
List<User> all = userDao.findAll();
log.info("list1 listUser:{}", JSON.toJSONString(all));
List<User> l2 = userDao.findAll();
log.info("list2 listUser:{}", JSON.toJSONString(l2));
List<User> l3 = userDao.findAll();
log.info("list3 listUser:{}", JSON.toJSONString(l3));
}
}
controller
package pers.kw.controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import pers.kw.service.UserService;
import javax.annotation.Resource;
@RestController
public class UserController {
@Resource
private UserService userService;
@RequestMapping("/addUser")
public String addUser() {
userService.addUser();
return "ok";
}
@RequestMapping("/readWrite1")
public String readWrite1() {
userService.readWrite1();
return "ok";
}
@RequestMapping("/readWrite2")
public String readWrite2() {
userService.readWrite2();
return "ok";
}
@RequestMapping("/readLb")
public String readLb() {
userService.readLb();
return "ok";
}
@RequestMapping("/readLb2")
public String readLb2() {
userService.readLb2();
return "ok";
}
}
http脚本
### 读写分离
POST http://localhost:8080/addUser
Content-Type: application/x-www-form-urlencoded
### 无事务,读写
POST http://localhost:8080/readWrite1
Content-Type: application/x-www-form-urlencoded
### 有事务读写
POST http://localhost:8080/readWrite2
Content-Type: application/x-www-form-urlencoded
### 自写查询方法,负载均衡
POST http://localhost:8080/readLb
Content-Type: application/x-www-form-urlencoded
### jpa自带查询方法,负载均衡
POST http://localhost:8080/readLb2
Content-Type: application/x-www-form-urlencoded
###
测试
读写分离
事务
无事务注解
没加事务注解的情况,save方法是主库,查询方法是从库
有事务注解
加事务注解的情况,save方法是主库,查询方法也是主库
负载均衡
自写查询方法
可以看到,分别查询了s1,s2,s1
Jpa自带的查询方法
查询的均是主库
原因是自带的查询方法是SimpleJpaRepository实现的,此类默认开启只读事务。
自写查询方法,增加事务注解
@Transactional(readOnly = true)
public void readLb() {
List<User> all = userDao.listUser();
log.info("list1 listUser:{}", JSON.toJSONString(all));
List<User> l2 = userDao.listUser();
log.info("list2 listUser:{}", JSON.toJSONString(l2));
List<User> l3 = userDao.listUser();
log.info("list3 listUser:{}", JSON.toJSONString(l3));
}
测试查下看是否还是从库?
查询的均为主库