Mysql数据库读写分离Springboot+mybatis+druid+shardingsphere

一、添加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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>MySQL-Test</artifactId>
        <groupId>org.example</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>MasterSlave</artifactId>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.1.9.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <!--mybatis驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.16</version>
        </dependency>
        <!--shardingsphere最新版本-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <!--lombok实体工具-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--swagger2-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>
    </dependencies>

</project>

二、配置连接属性

server.port=8088
#指定mybatis信息
#mybatis.config-location=classpath:mybatis-config.xml
mybatis.type-aliases-package="com.test.mapper"

spring.shardingsphere.datasource.names=master,slave0
# 数据源 主库
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.187.128:33066/masterDB?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
# 数据源 从库
spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://192.168.187.128:33065/masterDB?characterEncoding=utf-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

# 读写分离
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
#打印sql
spring.shardingsphere.props.sql.show=true

三、代码

1、Application代码

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@SpringBootApplication
@EnableAutoConfiguration(exclude = {DruidDataSourceAutoConfigure.class})
@EnableSwagger2
public class Application {
    public static void main(String[] args){
        SpringApplication.run(Application.class, args);
    }
}

2、Mapper代码

import com.test.model.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserMapper {
    @Insert("INSERT INTO `user` (`id`,`name`, `age`) VALUES (#{user.id},#{user.name}, #{user.age});")
    public Boolean insert(@Param("user") User user);
    @Select("select * from user")
    public List<User> getAll();
    @Update("update user set name=#{user.name},age=#{user.age} where id=#{user.id};")
    void update(@Param("user") User user);
}

3、controller代码

import com.test.mapper.UserMapper;
import com.test.model.User;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Date;
import java.util.List;


@Api(value = "UserController")
@RestController
public class UserController {
    @Autowired
    UserMapper userMapper;

    @ApiOperation(value = "插入数据", notes = "")
    @PostMapping("/insert")
    public String insert() {
        User user=new User();
        user.setName("小明"+new Date().toString());
        user.setAge(11);
        userMapper.insert(user);
        return "插入数据成功";
    }

    @ApiOperation(value = "插入数据", notes = "")
    @PostMapping("/insert1")
    public String insert1(@ApiParam(value = "用户", required = true)User user) {
        userMapper.insert(user);
        return "插入数据成功";
    }

    @ApiOperation(value = "插入数据", notes = "")
    @GetMapping("/update")
    public String update(@ApiParam(value = "用户", required = true)User user) {
        userMapper.update(user);
        return "插入数据成功";
    }

    @ApiOperation(value = "分页查询", notes = "")
    @GetMapping(value="/page")
    public List<User> page(@RequestParam(value = "第几页", required = true) Integer pageIndex,
                               @RequestParam(value = "每页数据量", required = true) Integer pageSize) {
        List<User> list=userMapper.getAll();
        return list;
    }

    @ApiOperation(value = "查询所有", notes = "")
    @GetMapping(value="/getAll")
    public List<User> getAll() {
        List<User> list=userMapper.getAll();
        return list;
    }
}

4、model

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

@ApiModel
@Data
public class User implements Serializable {
    @ApiModelProperty(value="id",dataType="Integer",name="id",example="1")
    private Integer id;
    @ApiModelProperty(value="name",dataType="String",name="name",example="小明")
    private String name;
    @ApiModelProperty(value="age",dataType="Integer",name="age",example="1")
    private Integer age;
}

5、swagger

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;

@Configuration
public class SwaggerConfig {
    Boolean swaggerEnabled=true;
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_12).apiInfo(apiInfo())
                // 是否开启
                .enable(swaggerEnabled).select()
                // 扫描的路径包
                .apis(RequestHandlerSelectors.basePackage("com.test.controller"))
                // 指定路径处理PathSelectors.any()代表所有的路径
                .paths(PathSelectors.any()).build().pathMapping("/");
    }

    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("SpringBoot-Swagger3集成和使用-demo示例")
                .description("springboot | swagger")
                // 作者信息
                .contact(new Contact("name", "个人主页url", "email"))
                .version("1.0.0")
                .build();
    }
}

四、主从数据库部署

见博文：docker-compose安装mysql主从集群