spring boot:shardingsphere+druid+mysql主从复制的读写分离(未分库分表)(spring boot 2.3.4)

一，如何实现mysql数据库的读写分离?

1,这个需要先实现mysql数据库的主从复制(master/slave)
请参考:

https://www.cnblogs.com/architectforest/p/12579847.html

说明：刘宏缔的架构森林是一个专注架构的博客，地址：https://www.cnblogs.com/architectforest

         对应的源码可以访问这里获取： https://github.com/liuhongdi/

说明：作者:刘宏缔 邮箱: 371125307@qq.com

二，演示项目的相关信息
1，项目地址:

https://github.com/liuhongdi/masterslave

2,项目功能说明：

        演示了shardingsphere整合druid的无分库分表的读写分离

3,项目结构：如图:

三，配置文件说明

1,pom.xml

        <!--mybatis begin-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <!--druid begin-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.23</version>
        </dependency>

        <!--log4j2 begin-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
        </dependency>
        <dependency>
            <groupId>com.lmax</groupId>
            <artifactId>disruptor</artifactId>
            <version>3.4.2</version>
        </dependency>

        <!--mysql begin-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--pagehelper begin-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

        <!--shardingsphere begin-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

2,application.properties

#shardingsphere
spring.shardingsphere.datasource.names=master0,slave0

spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://127.0.0.1:3306/ebusiness?characterEncoding=utf-8
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=password

spring.shardingsphere.datasource.master0.initial-size=5
spring.shardingsphere.datasource.master0.min-idle=5
spring.shardingsphere.datasource.master0.maxActive=20
spring.shardingsphere.datasource.master0.maxWait=56789
spring.shardingsphere.datasource.master0.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.master0.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.master0.validationQuery=SELECT 1 
spring.shardingsphere.datasource.master0.testWhileIdle=true
spring.shardingsphere.datasource.master0.testOnBorrow=false
spring.shardingsphere.datasource.master0.testOnReturn=false
spring.shardingsphere.datasource.master0.poolPreparedStatements=true
spring.shardingsphere.datasource.master0.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.master0.filters=stat,wall,log4j2
spring.shardingsphere.datasource.master0.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://127.0.0.1:3307/ebusiness?characterEncoding=utf-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=password

#打印sql
spring.shardingsphere.props.sql.show=true

#master slave
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master0
spring.shardingsphere.masterslave.slave-data-source-names=slave0
#有多个从库用逗号隔开,例:
#spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

#mybatis
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis.type-aliases-package=com.example.demo.mapper
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#error
server.error.include-stacktrace=always
#error
logging.level.org.springframework.web=trace

3，演示用的数据表:

CREATE TABLE `goods` (
 `goodsId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
 `goodsName` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name',
 `stock` int(11) NOT NULL DEFAULT '0' COMMENT 'stock',
 PRIMARY KEY (`goodsId`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表'

插入一条测试数据:

INSERT INTO `goods` (`goodsId`, `goodsName`, `stock`) VALUES
(3, 'green cup1', 70);

四，java代码说明

1,Goods.java

public class Goods {
    //商品id
    Long goodsId;
    public Long getGoodsId() {
        return this.goodsId;
    }
    public void setGoodsId(Long goodsId) {
        this.goodsId = goodsId;
    }

    //商品名称
    private String goodsName;
    public String getGoodsName() {
        return this.goodsName;
    }
    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    //库存
    int stock;
    public int getStock() {
        return this.stock;
    }
    public void setStock(int stock) {
        this.stock = stock;
    }

    //tostring
    public String toString(){
        return " Goods:goodsId=" + goodsId +" goodsName=" + goodsName+" stock=" + stock;
    }
}

2,GoodsMapper.java

@Repository
@Mapper
public interface GoodsMapper {
    Goods selectOneGoods(Long goodsId);
    int updateGoodsStock(@Param("goodsId") Long goodsId, @Param("changeAmount") int changeAmount);
}

3,GoodsController.java

@RestController
@RequestMapping("/goods")
public class GoodsController {

    private static final String SUCCESS = "SUCCESS";
    private static final String FAIL = "FAIL";

    @Resource
    private GoodsMapper goodsMapper;

    //更新商品库存 参数:商品id,增加的库存数量
    @RequestMapping("/goodsstock/{goodsId}/{count}")
    @ResponseBody
    public String goodsStock(@PathVariable Long goodsId,
                            @PathVariable int count) {
         int res = goodsMapper.updateGoodsStock(goodsId,count);
         System.out.println("res:"+res);
         if (res>0) {
             return SUCCESS;
         } else {
             return FAIL;
         }
    }
    //商品详情 参数:商品id
    @GetMapping("/goodsinfo")
    @ResponseBody
    public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) {
        Goods goods = goodsMapper.selectOneGoods(goodsId);
        return goods;
    }
}

4,DruidConfig.java

@Configuration
public class DruidConfig {

    /**
     * Druid监控
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();//这是配置的druid监控的登录密码
        initParams.put("loginUsername","root");
        initParams.put("loginPassword","root");
        //默认就是允许所有访问
        initParams.put("allow","127.0.0.1,192.168.3.4");
        //黑名单IP
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }

    /**
     * web监控的filter
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//过滤掉需要监控的文件
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return  bean;
    }
}

说明：配置druid管理ui的访问

5,GoodsMapper.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.masterslave.demo.mapper.GoodsMapper">
    <select id="selectOneGoods" parameterType="long" resultType="com.masterslave.demo.pojo.Goods">
        select * from goods where goodsId=#{goodsId}
    </select>
    <update id="updateGoodsStock">
        UPDATE goods SET
        stock = stock+#{changeAmount,jdbcType=INTEGER}
        WHERE goodsId = #{goodsId,jdbcType=BIGINT}
    </update>
</mapper>

6,DemoApplication.java

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

说明：排除了druid的自动配置

五，效果测试

1,测试增加数据:访问:

http://127.0.0.1:8080/goods/goodsstock/3/5

这个访问为goodsId为3的商品的库存加5

查看数据库:主库:

从库:

可见从库的主从同步是生效的

2,读取数据，数据应该由从数据库得到:

访问:

http://127.0.0.1:8080/goods/goodsinfo?goodsid=3

返回:

 手动修改从库的数据，验证数据是否由从库查询得到:

 再次查询:

 查看主库数据库：

 说明数据是由从库查询得到，
读写分离已生效

3，查看druid的管理ui:

http://127.0.0.1:8080/druid/index.html

返回:

六，查看spring boot的版本:

  .   ____          _            __ _ _
 /\ / ___'_ __ _ _(_)_ __  __ _    
( ( )\___ | '_ | '_| | '_ / _` |    
 \/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.4.RELEASE)