ShardingJdbc的配置及读写分离

主库master：192.168.31.16

从库slave：192.168.31.17

数据库表设计：

CREATE TABLE `sharding_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `nickname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '昵称',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

---

一、新建一个springboot工程

1. 引入相关sharding依赖、ssm依赖、数据库驱动 

<?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.1.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.sun</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>sharding-jdbc</description>
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
    </properties>

    <dependencies>
        <!-- 依赖web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- 依赖mybatis和mysql驱动 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--依赖lombok-->
        <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>
        <!--依赖sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <!--依赖数据源druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</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>

2. 定义配置application.yml

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.31.16:3306/user?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.31.17:3306/user?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.31.17:3306/user?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: root
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把两个个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds1
    # 配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave节点
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡均衡策略，采用轮询机制
      load-balance-algorithm-type: round_robin
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.sun.shardingJdbc.entity
#  configuration:
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
  level:
    com.sun.shardingJdbc.mapper: DEBUG

3. 定义entity、mapper、controller、ShardingJdbcApplication

package com.sun.shardingJdbc.entity;

import lombok.Data;

@Data
public class User {

    // 主键
    private Integer id;
    // 昵称
    private String nickname;
    // 密码
    private String password;
}

package com.sun.shardingJdbc.mapper;

import com.sun.shardingJdbc.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface UserMapper {

    /**
     * 新增
     * @param user
     */
    @Insert("insert into sharding_user(nickname,password) values(#{nickname},#{password})")
    void addUser(User user);

    /**
     * 查询
     * @return
     */
    @Select("select * from sharding_user")
    List<User> findUsers();

}

package com.sun.shardingJdbc.controller;

import com.sun.shardingJdbc.entity.User;
import com.sun.shardingJdbc.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;
import java.util.Random;

@RestController
@RequestMapping("/user")
public class UserController {

    @Resource
    private UserMapper userMapper;

    /**
     * 新增
     * @return
     */
    @GetMapping("/insert")
    public String insert() {
        User user = new User();
        user.setNickname("wang"+ new Random().nextInt());
        user.setPassword("1234567");
        userMapper.addUser(user);
        return "success";
    }

    /**
     * 查询
     * @return
     */
    @GetMapping("/findUsers")
    public List<User> findUsers() {
        return userMapper.findUsers();
    }
}

package com.sun.shardingJdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
@MapperScan("com.sun.shardingJdbc.mapper")
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }

}

二、测试读写分离

1. 读测试

接口地址：http://localhost:8085/user/findUsers

 2. 写测试

接口地址：http://localhost:8085/user/insert