ShardingSphere-JDBC(5.1.1版本)读写分离示例

最新推荐文章于 2024-05-27 17:51:24 发布

宋不争

最新推荐文章于 2024-05-27 17:51:24 发布

阅读量1.3k

点赞数

分类专栏： ShardingSphere ShardingJDBC 文章标签： java spring boot

本文链接：https://blog.csdn.net/m0_46216744/article/details/125252370

版权

ShardingSphere 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

ShardingJDBC

2 篇文章 0 订阅

订阅专栏

1. 创建Mysql读写库表

目标：读操作均在读数据库rw_slave的t_content表，写操作均在写数据库rw_master的t_content表。

1.1 创建主库表(写操作)

1.1.1 创建写数据库

create database rw_master;

1.1.2在rw_master写数据库中创建数据表t_content

use rw_master;
CREATE TABLE `t_content` (
  `id` int UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL,
  `author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

1.2 创建从库表(读操作)

1.2.1 创建读数据库

create database rw_slave;

1.2.2 在rw_slave写库中创建数据表t_content

use rw_slave;
CREATE TABLE `t_content` (
  `id` int UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL,
  `author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2. 创建SpringBoot项目并引入依赖

通过IDEA或官网创建SpringBoot项目，并在pom中引入shardingsphere-jdbc-core-spring-boot-starter依赖，这里引入的是5.1.1版本。

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>

完整的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.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.mrssz</groupId>
    <artifactId>readwrite-splitting</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>readwrite-splitting</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-namespace</artifactId>
            <version>5.1.1</version>
        </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>

3. 配置数据源以及读写库

在application.yml配置文件中配置数据源以及读写库，具体配置详见官方文档ShardingSphere-JDBC官方文档

本文配置如下:

server:
  port: 8080

spring:
  shardingsphere:
    datasource:
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: 'jdbc:mysql://127.0.0.1:3306/rw_master?characterEncoding=utf-8&useSSL=false'
        password: ***
        username: root
      names: master,slave
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: 'jdbc:mysql://127.0.0.1:3306/rw_slave?characterEncoding=utf-8&useSSL=false'
        password: ***
        username: root
    enabled: true
    props:
      sql:
        show: true
      sql-show: true
    rules:
      readwrite-splitting:
        data-sources:
          load-balancers:
            roundRobin:
              type: ROUND_ROBIN
          master-slave:
            props:
              auto-aware-data-source-name: master
              load-balancer-name: roundRobin
              read-data-source-names: slave
              write-data-source-name: master
            type: Static

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    use-generated-keys: true
  type-aliases-package:

4. 实现具体业务代码

4.1 controller层

@RestController
public class ReadWriteContentController {

    @Autowired
    ReadWriteContentService readWriteContentService;

    @GetMapping("/query")
    public ReadWriteContentEntity getContent(@RequestParam int id) {
        return readWriteContentService.getContent(id);
    }

    @GetMapping("/write")
    public int writeContent() {
        return readWriteContentService.writeContent();
    }
}

4.2 service层

@Service
public class ReadWriteContentService {
    @Autowired
    ReadWriteContentMapper readWriteContentMapper;

    public ReadWriteContentEntity getContent(int id) {
        return readWriteContentMapper.getContent(id);
    }

    public int writeContent() {
        ReadWriteContentEntity content = new ReadWriteContentEntity();
        content.setAuthor("ssz");
        content.setTitle("Write-Content");

        return readWriteContentMapper.writeContent(content);
    }
}

4.3 dao层

@Mapper
public interface ReadWriteContentMapper {
    ReadWriteContentEntity getContent(@Param("id") int id);

    int writeContent(@Param("entity") ReadWriteContentEntity content);
}

4.4 mapper层

<?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="cn.mrssz.readwritesplitting.mapper.ReadWriteContentMapper">
    <select id="getContent" resultType="cn.mrssz.readwritesplitting.entity.ReadWriteContentEntity">
        SELECT * FROM t_content
        WHERE id = #{id}
    </select>

    <insert id="writeContent">
        INSERT INTO t_content(title, author) VALUES(#{entity.title}, #{entity.author});
    </insert>
</mapper>