shardingsphere 实现读写分离

1、添加依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!-- 引入 Druid 数据源依赖：https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!--自启动Druid管理后台-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.35</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

2、项目结构

3、代码
controller

package com.spring.controller;

import com.alibaba.fastjson.JSONObject;
import com.spring.model.User;
import com.spring.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

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

    @Autowired
    UserService userService;

    @RequestMapping(value = "/list")
    public String getUserList(){
        List<User> userList =  userService.getUserList();
        return JSONObject.toJSONString(userList);
    }

    @RequestMapping(value = "/addUser")
    public String addUser(){
        int res = userService.addUser();
        return "ok,id:"+res;
    }

}

model

package com.spring.model;

public class User {

    Long userId;

    String name;

    Integer sex;

    Integer age;

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public User() {
    }
}

service

package com.spring.service;

import com.spring.mapper.UserMapper;
import com.spring.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    UserMapper userMapper;

    public List<User> getUserList(){
        return userMapper.selectUserList();
    }

    public int addUser(){
        User user = new User();
        user.setName("hello");
        int res = (int)(Math.random()*100);
        user.setAge(res);
        user.setSex(res%2);
        userMapper.addUser(user);
        return 1;
    }
}

mapper

package com.spring.mapper;

import com.spring.model.User;

import java.util.List;

/**
 * User 表数据库控制层接口
 */
public interface UserMapper{

    List<User> selectUserList();

    void addUser(User user);

}

mapper.xml

<?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="com.spring.mapper.UserMapper">
    <resultMap id="RM" type="com.spring.model.User">
        <id column="user_id" property="userId" jdbcType="INTEGER"/>
        <id column="name" property="name" jdbcType="VARCHAR"/>
        <id column="age" property="age" jdbcType="INTEGER"/>
    </resultMap>
    <!-- 通用查询结果列-->
    <sql id="Base_Column_List">
        user_id, name,age
    </sql>

    <select id="selectUserList" resultMap="RM" >
        SELECT <include refid="Base_Column_List" /> FROM t_user
    </select>
    
    <insert id="addUser" parameterType="com.spring.model.User" >
            insert into t_user values (#{name},#{sex},#{age})
    </insert>
</mapper>

启动类

package com.spring;

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

@SpringBootApplication
@MapperScan("com.spring.mapper")
public class Sharding1028Application {

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

}

application.properties 配置文件

server.port=8080

#mysql
spring.shardingsphere.datasource.names=master,slave1,slave2
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/user_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.shardingsphere.dataSource.master.username=root
spring.shardingsphere.dataSource.master.password=123456
spring.shardingsphere.dataSource.master.maxPoolSize=20
spring.shardingsphere.dataSource.master.driver-class-name=com.mysql.cj.jdbc.Driver


spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3307/user_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.shardingsphere.dataSource.slave1.username=root
spring.shardingsphere.dataSource.slave1.password=123456
spring.shardingsphere.dataSource.slave1.maxPoolSize=20
spring.shardingsphere.dataSource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.shardingsphere.datasource.slave2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave2.url=jdbc:mysql://localhost:3308/user_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8
spring.shardingsphere.dataSource.slave2.username=root
spring.shardingsphere.dataSource.slave2.password=123456
spring.shardingsphere.dataSource.slave2.maxPoolSize=20
spring.shardingsphere.dataSource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver


#根据id分表
# 指定t_user表的数据分布情况，配置数据节点 master.t_user0,master.t_user1
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=master.t_user$->{0..1}
# 指定t_user表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE


# 指定t_user表的分片策略，分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{user_id % 2}



# 读写分离配置
spring.shardingsphere.sharding.master-slave-rules.master.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.master.slave-data-source-names=slave1,slave2



mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
#打印sql到控制台

spring.shardingsphere.props.sql.show=true
#spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#设置访问druid监控页的账号和密码,默认没有
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
spring.main.allow-bean-definition-overriding=true