sharding-jdbc垂直分表

结论：垂直分表的保存是在service层完成的保存多个表的工作的，查询利用关联键

1.建表语句

c_user表只存一个user_id，一个user_name
c_user_info表存info_id,user_info,user_id
user_id和info_id都由sharding-jdbc生成，其中user_id用于两个表之间关联关系

CREATE TABLE `c_user`  (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `c_user_info`  (
  `info_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `user_info` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`info_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2.配置文件

#指定表数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.c_user.actual-data-nodes=db0.c_user
spring.shardingsphere.sharding.tables.c_user_info.actual-data-nodes=db0.c_user_info
#主键生成策略:雪花算法
spring.shardingsphere.sharding.tables.c_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.c_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.c_user_info.key-generator.column=info_id
spring.shardingsphere.sharding.tables.c_user_info.key-generator.type=SNOWFLAKE
# 绑定表用于解决分库分表查询笛卡尔积的问题
spring.shardingsphere.sharding.binding-tables[0]=c_user,c_user_info

3.实体类

@Data
public class UserDO {

    private Long userId;
    private String userName;
}

@Data
public class UserInfoDO {

    private Long userInfoId;
    private Long userId;
    private String userInfo;

}

4.mapper接口

	@Insert("insert into c_user (user_name) value(#{userName})")
    @Options(useGeneratedKeys = true, keyProperty = "userId", keyColumn = "user_id")
    void insertCUser(UserDO userDO);

    @Insert("insert into c_user_info (user_id,user_info) value (#{userId},#{userInfo})")
    void insertCUserInfo(UserInfoDO userInfoDO);

    @Select("select * from c_user u join c_user_info i on u.user_id = i.user_id where u.user_id = #{userId}")
    Map selectCUser(@Param("userId") Long userId);

5.在service里完成垂直分表的保存工作

@Service
public class CUserServiceImpl implements CUserService {

    @Resource
    CUserMapper cUserMapper;

    @Override
    @Transactional
    public void insert(String userName, String userInfo) {
        UserDO userDO = new UserDO();
        userDO.setUserName(userName);
        cUserMapper.insertCUser(userDO);
        UserInfoDO userInfoDO= new UserInfoDO();
        userInfoDO.setUserId(userDO.getUserId());
        userInfoDO.setUserInfo(userInfo);
        cUserMapper.insertCUserInfo(userInfoDO);
    }

    @Override
    public Map select(Long userId) {
        Map map = cUserMapper.selectCUser(userId);
        return map;
    }
}

6.测试类

@Test
    public void testUserService(){
        for (int i = 0; i<10; i++){
            cUserService.insert("张三"+i,"张三是个好人"+i);
        }
    }

    @Test
    public void testUserServiceSelect(){
        Map select = cUserService.select(600704769543110656L);
        System.out.println(select);
    }