1、需求分析
2、创建数据库和表
CREATE TABLE `user_db`.`t_user_0` (
`user_id` bigint(20) NOT NULL,
`username` varchar(50) NULL,
`ustatus` varchar(50) NULL,
PRIMARY KEY (`user_id`)
);
CREATE TABLE `user_db`.`t_user_1` (
`user_id` bigint(20) NOT NULL,
`username` varchar(50) NULL,
`ustatus` varchar(50) NULL,
PRIMARY KEY (`user_id`)
);
3、编写操作代码
(1)创建 user 实体类和 mapper
package com.weianlai.shardingjdbc.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_user")
public class User {
private Long userId;
private String username;
private String ustatus;
}
package com.weianlai.shardingjdbc.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.weianlai.shardingjdbc.entity.User;
public interface UserMapper extends BaseMapper<User> {
}
(2)配置垂直分库策略
# 水平分库,配置两个数据源
spring.shardingsphere.datasource.names=m0
# 一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#配置数据源
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
# 配置 user_db 数据库里面 t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{0}.t_user_$->{0..1}
# 指定 course 表里面主键 cid 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
# 指定表分片策略 约定 cid 值偶数添加到 course_1 表,如果 cid 是奇数添加到course_2 表
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_$->{user_id % 2}
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
(3)编写测试代码
//添加操作
@Test
public void addUserDb() {
for (int i = 0; i < 10; i++) {
User user = new User();
user.setUsername("lucy");
user.setUstatus("a");
userMapper.insert(user);
}
}