分库分表之_读写分离
前言
Github:https://github.com/HealerJean
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、数据库图文
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>
id | city | name | status | create_time | update_time |
---|---|---|---|---|---|
1244531596683517954 | a | 1 | 10 | 2020-03-30 15:47:09 | 2020-03-30 15:47:09 |
1244531609899769858 | b | 2 | 10 | 2020-03-30 15:47:12 | 2020-03-30 15:47:12 |
1244531634532917249 | c | 3 | 10 | 2020-03-30 15:47:18 | 2020-03-30 15:47:18 |
1244531650509021186 | d | 4 | 10 | 2020-03-30 15:47:22 | 2020-03-30 15:47:22 |
1244531672914993153 | c | 5 | 10 | 2020-03-30 15:47:27 | 2020-03-30 15:47:27 |
1244531711733276673 | d | 6 | 10 | 2020-03-30 15:47:37 | 2020-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_0
的user
表中,因为是读写分离,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、验证归纳
为了验证,我将id
为1244531596683517954
的数据放到ds_1
的user
表中进行测试,查询成功
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
中的,读写分离测试成功