springboot+mysql+jpa+sharding-jdbc+druid读写分离

介绍：https://github.com/apache/shardingsphere

数据库：主库(写)+2从库(读)

db_write(写数据库master)

db_read1(读数据库slave1)

db_read2(读数据库slave2)

 

pom.xml

<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>com.zsk</groupId>
    <artifactId>springboot-read-write-separate</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.8.RELEASE</version>
        <relativePath />
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</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-devtools</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.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
         <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0.M1</version>
        </dependency>
        <!-- 添加Swagger2依赖,用于生成接口文档 -->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.7.0</version>
        </dependency>
        <!--end -->
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

application.yml

server:
  port: 8080
  
spring: 
  jpa:
    show-sql: false
    properties:
      hibernate:
        hbm2ddl:
          auto: none #update         
sharding:
  jdbc:
    dataSource:
      names: db-write,db-read1,db-read2
      # 配置主库
      db-write: #org.apache.tomcat.jdbc.pool.DataSource
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db_write?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: amsuser
        password: ams2018
        #最大连接数
        maxPoolSize: 20
      db-read1: # 配置第一个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db_read1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: amsuser
        password: ams2018
        maxPoolSize: 20
      db-read2: # 配置第二个从库
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/db_read2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
        username: amsuser
        password: ams2018
        maxPoolSize: 20
    config:
      masterslave: # 配置读写分离
        load-balance-algorithm-type: round_robin # 配置从库选择策略random（随机）round_robin（轮循）
        name: db-write-read
        master-data-source-name: db-write
        slave-data-source-names: db-read1,db-read2
    props:
      sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志
        show: true
          

效果:

post http://127.0.0.1:8080/api/user 数据写入了db_write库

get http://127.0.0.1:8080/api/users 多次访问轮询读取db_read1库,db_read2库的数据

返回数据如下

[{"id":"bc2bc1ce-c278-11ea-8c98-8c1645da95ec","userName":"db_read1","password":"123456","createTime":"2020-07-10T06:43:59.000+0000"}]

[{"id":"bc2bc1ce-c278-11ea-8c98-8c1645da95ec","userName":"db_read2","password":"123456","createTime":"2020-07-10T06:43:59.000+0000"}]

完整代码demo:https://gitee.com/zhangsike/springboot-learn/tree/master/springboot-read-write-separate，注意运行项目之前先执行sql.sql

 

配置mysql从库监听同步主库数据