Sharding-JDBC之 读写分离

一、简介

Sharding-JDBC通过sql语句语义分析，当sql语句有insert、update、delete时，Sharding-JDBC就把这次操作在主数据库上执行；当sql语句有select时，就会把这次操作在从数据库上执行，从而实现读写分离过程。但Sharding-JDBC并不会做数据同步，数据同步是配置MySQL后由MySQL自己完成的。

二、主从mysql环境准备

主服务器Master01(192.168.56.20)、从服务器Slave01(192.168.56.21)：

我们这次同步的策略是同步user_db库。

首先我们在user_db创建一张表：

create table t_user(
	`user_id` bigint(20) primary key,
	`username` varchar(100) not null,
	`ustatus` varchar(50) not null
)

---

三、使用yml配置读写分离策略

spring:
  shardingsphere:
    # 数据源配置
    datasource:
      # 数据源名称，多数据源以逗号分隔，名称可以随意起名
      names: master,slave
      master: #配置第一个数据源
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        password: xxxxxx
        username: dhapp
        url: jdbc:mysql://192.168.56.20:3306/user_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
      slave: #配置第一个数据源
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        password: xxxxxx
        username: dhapp
        url: jdbc:mysql://192.168.56.21:3306/user_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    # 规则配置
    rules:
      readwrite-splitting:
        data-sources:
          ds0: #主库从库逻辑数据源定义 ds0 为 user_db
            write-data-source-name: master
            read-data-source-names: slave
            load-balancer-name: round-robin
        load-balancers:
          round-robin: # 负载均衡算法
            type: ROUND_ROBIN # 轮询
    # 属性配置
    props:
      # 展示修改以后的sql语句
      sql-show: true

---

四、使用java测试代码进行测试

1、UserController

package com.sharding.demo.web;

import com.sharding.demo.model.User;
import com.sharding.demo.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;

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

    @Autowired
    private UserService userService;

    @RequestMapping(value = "addUser")
    public void addUser(){
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserId(Long.valueOf(i));
            user.setUsername("java"+i);
            user.setUstatus(i%2==0?String.valueOf(1):String.valueOf(0));
            userService.addUser(user);
        }
    }

    @RequestMapping(value = "queryUserByParams")
    public List<User> queryUserByParams(){
        return userService.queryUserByParams();
    }
}

2、UserService

package com.sharding.demo.service;

import com.sharding.demo.model.User;

import java.util.List;

public interface UserService {
    void addUser(User user);

    List<User> queryUserByParams();

}

3、UserServiceImpl

package com.sharding.demo.service.impl;

import com.sharding.demo.mapper.UserMapper;
import com.sharding.demo.model.User;
import com.sharding.demo.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

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

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public void addUser(User user) {
        userMapper.insert(user);
    }

    @Override
    public List<User> queryUserByParams() {
        return userMapper.selectList(null);
    }
}

4、UserMapper

package com.sharding.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sharding.demo.model.User;

public interface UserMapper extends BaseMapper<User> {
}

五、通过浏览器请求开始测试

1、http://localhost:8080/user/addUser新增结果测试

可以看到新增使用的是master数据库：

2、http://localhost:8080/user/queryUserByParams 查询结果测试

 可以通过日志发现查询使用的是slave服务器