sharding-jdbc垂直分库

1.新建一个数据库user-db执行以下sql语句生成表

-- ----------------------------
-- Table structure for cc_user
-- ----------------------------
DROP TABLE IF EXISTS `cc_user`;
CREATE TABLE `cc_user`  (
  `id` bigint(20) NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.在配置文件中添加用户数据库的设置

# 添加用户数据源名称
spring.shardingsphere.datasource.names=m1,m2,u1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.230.145:3306/order_db_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.230.145:3306/order_db_2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
# 配置用户数据源
spring.shardingsphere.datasource.u1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.u1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.u1.url=jdbc:mysql://192.168.230.145:3306/user-db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.u1.username=root
spring.shardingsphere.datasource.u1.password=root

spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=m$->{user_id%2+1}

spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=m$->{1..2}.c_order_$->{1..2}

# 配置用户表节点
spring.shardingsphere.sharding.tables.cc_user.actual-data-nodes=u1.cc_user

spring.shardingsphere.sharding.tables.c_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

# 配置用户主键自动生成策略，也可注释掉，手动传值
spring.shardingsphere.sharding.tables.cc_user.key-generator.column=id
spring.shardingsphere.sharding.tables.cc_user.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order_$->{order_id%2+1}

spring.shardingsphere.props.sql.show=true

spring.shardingsphere.sharding.broadcast-tables=c_dict

3.mapper添加语句

@Insert("insert into cc_user (username,nickname,age) value (#{username},#{nickname},#{age})")
void insertUser(@Param("username")String username,@Param("nickname")String nickname,@Param("age")Integer age);

4.测试类测试

@Test
public void testInsertUser(){
    orderMapper.insertUser("王五","老五",20);
}

5.执行结果

021-05-09 22:15:10.476  INFO 18820 --- [           main] ShardingSphere-SQL                       : Actual SQL: u1 ::: insert into cc_user  (username, nickname, age, id) VALUES (?, ?, ?, ?) ::: [王五, 老五, 20, 598275985850236929]