2.4、Sharding-JDBC 实现垂直分库
涉及代码 Don212
- Spring Boot 2.5.2
- Druid 1.2.6
- Mybatis Plus 3.4.3
- Sharding jdbc 4.1.1
1、需求
专库专表,操作 t_user
表时,就到 user_db
库。
2、创建数据库和表
数据库:user_db
CREATE TABLE `t_user` (
`USER_ID` bigint NOT NULL,
`USERNAME` varchar(255),
`U_STATUS` varchar(255),
PRIMARY KEY (`USER_ID`) USING BTREE
);
3、创建实体类
com.learn.springboot.entity.User
com.learn.springboot.mapper.UserMapper
4、配置垂直分库策略
# sharding-jdbc 数据分片
## 配置数据源,给数据源起名称
spring.shardingsphere.datasource.names=m1,m2,m3
## ...
## 配置数据源3具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m3.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m3.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m3.url=jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m3.username=root
spring.shardingsphere.datasource.m3.password=root
## 指定 user_db 库里面 t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m3.t_user
## 指定分片策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=USER_ID
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
## 指定 t_user 表里面主键 USER_ID 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=USER_ID
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
## ...
5、编写测试代码
@Test
public void addUserDb() {
for (int i = 1; i <= 10; i++) {
User user = User.builder()
.username("Don程序员")
.uStatus("Java")
.build();
userMapper.insert(user);
}
}
运行测试:
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: Table 'user_db.user' doesn't exist
### The error may exist in com/learn/springboot/mapper/UserMapper.java (best guess)
### The error may involve com.learn.springboot.mapper.UserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO user ( user_id, username, u_status ) VALUES ( ?, ?, ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'user_db.user' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'user_db.user' doesn't exist
找不到表,在实体类指明 t_user
表:
@TableName(value = "t_user")
再次测试:
成功插入!
查询方法:
@Test
public void findUserb() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_id", 1411851843604099073L);
User user = userMapper.selectOne(wrapper);
System.out.println(user);
}
成功查询!
2.5、Sharding-JDBC 操作公共表
1、公共表
- 存储固定数据的表,表数据很少发生变化,查询时候经常进行关联
- 在每个数据库中创建出相同结构公共表
2、创建相同结构公共表
分别在三个库 edu_db_1
、edu_db_2
、user_db
创建 t_udict
CREATE TABLE T_UDICT (
DICT_ID BIGINT(20) PRIMARY KEY,
USTATUS VARCHAR(100) NOT NULL,
UVALUE VARCHAR(100) NOT NULL
)
3、配置公共表
## 配置公共表
spring.shardingsphere.sharding.broadcast-tables=t_udict
spring.shardingsphere.sharding.tables.t_udict.key-generator.column=DICT_ID
spring.shardingsphere.sharding.tables.t_udict.key-generator.type=SNOWFLAKE
4、创建实体类
com.learn.springboot.entity.Udict
com.learn.springboot.mapper.UdictMapper
5、编写测试代码
测试添加:
@Test
public void addDict() {
Udict udict = new Udict();
udict.setUstatus("Don程序猿");
udict.setUvalue("已启用");
udictMapper.insert(udict);
}
成功添加:
测试删除:
@Test
public void deleteDict() {
QueryWrapper<Udict> wrapper = new QueryWrapper<>();
//设置 DICT_ID 值
wrapper.eq("DICT_ID", 1412322845521944577L);
udictMapper.delete(wrapper);
}
成功删除: