前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

1、开始Demo

1.1、hlj-01-read-write.sql

drop database if exists ds_0;
create database `ds_0` character set 'utf8' collate 'utf8_general_ci';
use ds_0;

drop table if exists user;
create table `user`
(
  `id`        bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (`id`)
) engine = innodb
  default charset = utf8;


drop database if exists ds_1;
create database `ds_1` character set 'utf8' collate 'utf8_general_ci';
use ds_1;
drop table if exists user;
create table `user`
(
  `id`        bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (`id`)
) engine = innodb
  default charset = utf8;

1.1.1、数据库图文

1585554177489

1.2、依赖


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

1.3、配置文件:application.properties

server.port=8888


# 配置 mybatis的一些配置,也可以在 application.properties 中配置,如果配置了就不需要了mybatis.xml
#mybatis-plus.config-location=classpath:mybatis.xml
#Maven 多模块项目的扫描路径需以 classpath*: 开头 (即加载多个 jar 包下的 XML 文件)
mybatis-plus.mapper-locations=classpath*:mapper/*.xml
mybatis-plus.type-aliases-package=com.healerjean.proj.pojo
##主键类型  0:"数据库ID自增,非常大", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
mybatis-plus.id-type: 0
#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
mybatis-plus.field-strategy: 2
#数据库大写下划线转换
mybatis-plus.capital-mode: true
mybatis-plus.refresh-mapper: true




# 显示SQL
spring.shardingsphere.props.sql.show=true
# #当遇到同样名字的时候,是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true

# 读写分离 数据源定义(会向主数据库中插入数据,但是查询的话是从从表查的(不会出现主从复制的情况哦))
spring.shardingsphere.datasource.names=master,slave
# 主数据源
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://localhost:3306/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456
# 从数据源
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://localhost:3306/ds_1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

# 读写分离配置
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
# 最终的数据源名称
spring.shardingsphere.masterslave.name=dataSource
# 主库数据源名称
spring.shardingsphere.masterslave.master-data-source-name=master
# 从库数据源名称列表,多个逗号分隔
spring.shardingsphere.masterslave.slave-data-source-names=slave

1.4、具体测试方法和类

1.4.1、User.java

@Data
@Accessors(chain = true)
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    /** 主键  */
    private Long id;
    private String name;
    private String city;
    private String status;
    private Date createTime;
    private Date updateTime;
}

1.4.2、UserDTO.java

@Data
@Accessors(chain = true)
@ApiModel(value = "demo实体类")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class UserDTO {

    @ApiModelProperty(value = "主键", hidden = true)
    @JsonSerialize(using = JsonLongSerializer.class )
    private Long id;

    @ApiModelProperty(value = "姓名")
    @NotBlank(message = "姓名不能为空", groups = ValidateGroup.HealerJean.class)
    private String name;

    @ApiModelProperty(value = "城市")
    private String city;

    @ApiModelProperty(value = "状态", hidden = true)
    private String status;

    @ApiModelProperty(value = "创建时间", hidden = true)
    @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
    private Date createTime;

    @ApiModelProperty(value = "修改时间", hidden = true)
    @JsonFormat(pattern = DateUtils.YYYY_MM_dd_HH_mm_ss, timezone = "GMT+8")
    private Date updateTime;
}

1.4.3、UserMapper.java

public interface UserMapper extends BaseMapper<User> {


}

1.4.4、UserService.java

public interface UserService {


    UserDTO insert(UserDTO userDTO);

    UserDTO findById(Long id);

    List<UserDTO> list();

}

1.4.5、UserServiceImpl.java

@Service
@Slf4j
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;


    @Override
    public UserDTO insert(UserDTO userDTO) {
        User user = BeanUtils.dtoToUserDTO(userDTO);
        user.setStatus(StatusEnum.生效.code);
        userMapper.insert(user);
        userDTO.setId(user.getId());
        return userDTO;
    }

    @Override
    public UserDTO findById(Long id) {
        User user = userMapper.selectById(id);
        return user == null ? null : BeanUtils.userToDTO(user);
    }

    @Override
    public List<UserDTO> list() {
        List<User> users = userMapper.selectList(null);
        List<UserDTO> list = null;
        if (!EmptyUtil.isEmpty(users)) {
            list = users.stream().map(BeanUtils::userToDTO).collect(Collectors.toList());
        }
        return list;
    }

}

1.4.6、UserController.java

@ApiResponses(value = {
        @ApiResponse(code = 200, message = "访问正常"),
        @ApiResponse(code = 301, message = "逻辑错误"),
        @ApiResponse(code = 500, message = "系统错误"),
        @ApiResponse(code = 401, message = "未认证"),
        @ApiResponse(code = 403, message = "禁止访问"),
        @ApiResponse(code = 404, message = "url错误")
})
@Api(description = "demo控制器")
@Controller
@RequestMapping("hlj/demo")
@Slf4j
public class UserController {



    @Autowired
    private UserService userService;

    @ApiOperation(value = "insert",
            notes = "insert",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = UserDTO.class)
    @PostMapping(value = "insert", produces = "application/json; charset=utf-8")
    @ResponseBody
    public ResponseBean insert(UserDTO userDTO) {
        log.info("样例--------mybaits-plus添加demo实体------数据信息{}", userDTO);
        String validate = ValidateUtils.validate(userDTO, ValidateGroup.HealerJean.class);
        if (!validate.equals(CommonConstants.COMMON_SUCCESS)) {
            throw new BusinessException(ResponseEnum.参数错误, validate);
        }
        return ResponseBean.buildSuccess(userService.insert(userDTO));
    }


    @ApiOperation(notes = "findById",
            value = "findById",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = UserDTO.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id", value = "demo主键", required = true, paramType = "path", dataType = "long"),
    })
    @GetMapping("findById/{id}")
    @ResponseBody
    public ResponseBean findById(@PathVariable Long id) {
        log.info("样例--------findById------数据:id:{}", id);
        return ResponseBean.buildSuccess(userService.findById(id));
    }

    @ApiOperation(notes = "list",
            value = "list",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = UserDTO.class)
    @GetMapping("list")
    @ResponseBody
    public ResponseBean list() {
        log.info("样例--------list------");
        return ResponseBean.buildSuccess(userService.list());
    }


}

1.5、开始测试

1.5.1、插入数据: /hlj/demo/insert

1.5.1.1、测试数据

name city
1 a
2 b
3 c
4 d
5 c
6 d

1.5.1.2、查询数据库中插入的数据

1.5.1.2.1、查询数据库ds_0、user表
sql> select * from ds_0.user
[2020-03-30 15:49:57] 6 rows retrieved starting from 1 in 431 ms (execution: 9 ms, fetching: 422 ms)

<!DOCTYPE html>

idcitynamestatuscreate_timeupdate_time
1244531596683517954a1102020-03-30 15:47:092020-03-30 15:47:09
1244531609899769858b2102020-03-30 15:47:122020-03-30 15:47:12
1244531634532917249c3102020-03-30 15:47:182020-03-30 15:47:18
1244531650509021186d4102020-03-30 15:47:222020-03-30 15:47:22
1244531672914993153c5102020-03-30 15:47:272020-03-30 15:47:27
1244531711733276673d6102020-03-30 15:47:372020-03-30 15:47:37
1.5.1.2.2、查询数据库ds1_1、user表
sql> select * from ds_1.user
[2020-03-30 15:50:31] 0 rows retrieved in 63 ms (execution: 8 ms, fetching: 55 ms)

发现为空

1.5.1.3、归纳总结

我们发现所有的数据全部到了ds_0user表中,因为是读写分离,ds_0是主表,用来写的表。而读表则是ds_1中的user表,读写分离测试成功

1.5.2、根据Id查询:/hlj/demo/findById

1.5.2.1、测试数据

1.5.2.1.1、Id:1244531596683517954
http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954

{
  "success": true,
  "result": null,
  "msg": "",
  "code": 200,
  "date": "1585554914827"
}


1.5.2.1.2、Id:1244531609899769858
http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954


{
  "success": true,
  "result": null,
  "msg": "",
  "code": 200,
  "date": "1585554965412"
}

1.5.2.2、归纳总结

可以看到查询出来的数据是空的,所以可以验证我们的读写分离配置正确,因为我们没有网ds_1库的user表放入数据,所以查不到数据 ,读写分离测试成功

1.5.2.2.1、验证归纳

为了验证,我将id1244531596683517954的数据放到ds_1user表中进行测试,查询成功

http://127.0.0.1:8888/hlj/demo/findById/1244531596683517954


{
  "success": true,
  "result": {
    "id": "1244531596683517954",
    "name": "1",
    "city": "a",
    "status": "10",
    "createTime": "2020-03-30 15:47:09",
    "updateTime": "2020-03-30 15:47:09"
  },
  "msg": "",
  "code": 200,
  "date": "1585555387337"
}

1.5.3、List查询:/hlj/demo/list

1.5.3.1、测试

继续使用1.5.2.2.1中的数据,在数据库ds_1的表user中插入的数据,id为:1244531596683517954

http://127.0.0.1:8888/hlj/demo/list

{
  "success": true,
  "result": [
    {
      "id": "1244531596683517954",
      "name": "1",
      "city": "a",
      "status": "10",
      "createTime": "2020-03-30 15:47:09",
      "updateTime": "2020-03-30 15:47:09"
    }
  ],
  "msg": "",
  "code": 200,
  "date": "1585555442095"
}

1.5.3.2、归纳总结

发现只有一条数据,而这条数据使我手动放到数据库ds_1的表user中的,读写分离测试成功

ContactAuthor