分享之前先参考:数据分离数据源配置
今天紧接着分享数据分表和读写分离,也是我们常用的业务场景之一:
1、相关jar包和三个数据源同上一篇保持一致:
2、相关建表语句/实体类/mapper/xml配置:
2.1、建表语句:
CREATE TABLE `tbl_user0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(32) DEFAULT NULL COMMENT '电话',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_user1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL COMMENT '名称',
`city_id` int(12) DEFAULT NULL COMMENT '城市',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(32) DEFAULT NULL COMMENT '电话',
`email` varchar(32) DEFAULT NULL COMMENT '邮箱',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8;
2.2、实体类:
import java.io.Serializable;
import java.util.Date;
public class TblUser implements Serializable {
private Long id;
private String name;
private Integer cityId;
private Boolean sex;
private String phone;
private String email;
private Date createTime;
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
public Integer getCityId() {
return cityId;
}
public void setCityId(Integer cityId) {
this.cityId = cityId;
}
public Boolean getSex() {
return sex;
}
public void setSex(Boolean sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone == null ? null : phone.trim();
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email == null ? null : email.trim();
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password == null ? null : password.trim();
}
}
2.2、mapper:
public interface TblUserMapper {
int insertOne(TblUser record);
}
2.3、xml配置:
<insert id="insertOne" parameterType="com.nandao.admin.db.entity.TblUser" >
insert into tbl_user (id, name, city_id,
sex, phone, email, create_time,
password)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{cityId,jdbcType=INTEGER},
#{sex,jdbcType=BIT}, #{phone,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP},
#{password,jdbcType=VARCHAR})
</insert>
注意:保存时一定要采用mybatis原声的xml,如果采用mybatis-plus封装的保存接口,会读取不到后面的分表策略类,最终导致每张表保村相同的全量数据,导致分表失败!
3、分表和读写分离配置策略:数据源参考上篇文章,这里省略了
config:
sharding:
tables:
tbl_user:
actual-data-nodes: ds_0.tbl_user$->{0..1}
table-strategy:
standard:
sharding-column: id
precise-algorithm-class-name: com.nandao.admin.db.rule.MyPreciseShardingAlgorithm
default-data-source-name: ds_0
binding-tables: tbl_user #绑定表规则列表
master-slave-rules:
ds_0:
master-data-source-name: user0
slave-data-source-names: user1,user2
props:
sql:
show: true #是否开启SQL显示,默认值: false
executor:
size: 20 #工作线程数量,默认值: CPU核数
check:
table:
metadata:
enabled: true #是否在启动时检查分表元数据一致性,默认值: false
4、数据分表算法类:
/**
* @author wanghuainan
* @date 2022/1/22
*/
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(shardingValue.getValue() % 2 + "")) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}
注意:PreciseShardingAlgorithm<Long> 接口的范型类型一定要和 数据分表的字段的类型保持一致(比如id 的类型是Long,这里也要这样),否则保存时报错;
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.lang.NullPointerException
### The error may involve com.nandao.admin.db.mapper.TblUserMapper.insertOne-Inline
### The error occurred while setting parameters
### SQL: insert into tbl_user (id, name, city_id, sex, phone, email, create_time, password) values (?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.lang.NullPointerException
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1013)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
5、测试API
for(Long i=10L;i<15;i++) {
TblUser user = new TblUser();
user.setId(i);
user.setName("nandao"+i+"");
user.setCityId(1 % 2 == 0 ? 1 : 2);
user.setCreateTime(new Date());
user.setSex(true);
user.setPhone("11111111");
user.setEmail("xxxxx");
user.setCreateTime(new Date());
user.setPassword("eeeeeeeeeeee");
tblUserMapper.insertOne(user);
}
执行的部分日志:
2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO ShardingSphere-SQL - Rule Type: sharding
2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO ShardingSphere-SQL - Logic SQL: insert into tbl_user (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?)
2022-01-26 16:28:44.106 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO ShardingSphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=io.shardingsphere.core.parsing.parser.sql.dml.insert.InsertStatement@1576be0c), columns=[Column(name=id, tableName=tbl_user), Column(name=name, tableName=tbl_user), Column(name=city_id, tableName=tbl_user), Column(name=sex, tableName=tbl_user), Column(name=phone, tableName=tbl_user), Column(name=email, tableName=tbl_user), Column(name=create_time, tableName=tbl_user), Column(name=password, tableName=tbl_user)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=(?, ?, ?,
?, ?, ?, ?,
?), parametersCount=8)]), columnsListLastPosition=94, generateKeyColumnIndex=-1, insertValuesListLastPosition=144)
2022-01-26 16:28:44.107 [QchP9p9hJSESSIONID=7171eb71-4b8e-4998-b9e6-21a2999c17ea;] [http-nio-23888-exec-1] INFO ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into tbl_user0 (id, name, city_id,
sex, phone, email, create_time,
password)
values (?, ?, ?,
?, ?, ?, ?,
?) ::: [[14, nandao14, 2, true, 11111111, xxxxx, 2022-01-26 16:28:26.094, eeeeeeeeeeee]]
6、执行后数据库两张表的数据显示:
tbl_user0:
tbl_user1:
7、批量查询时每个表依次查。 前几篇文章已经分享过,可以去参考!到此分享完成。下篇我们分享分库分表加读写分离,敬请期待!