【SpringBoot】Spring data JPA整合ShardingSphere-JDBC静态读写分离实现

发布时间：2023年12月22日

大佬栽树，我乘凉

许大仙老师：【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));
    }

测试查下看是否还是从库?
查询的均为主库
在这里插入图片描述

文章来源:https://blog.csdn.net/qq_36762765/article/details/135148260
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！