sharding-jdbc垂直分表

逃离我推掉我的手 2023-01-21 13:23 3阅读 0赞

`结论：垂直分表的保存是在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);
        }