3、Sharding-JDBC----垂直分库----使用案例---专库专表

1、Sharding-JDBC----水平分库----使用案例

2、Sharding-JDBC----水平分表----使用案例

3、Sharding-JDBC----垂直分库----使用案例

4、Sharding-JDBC----垂直分表----使用案例

---

垂直分库实际上就是专库专表
垂直分裤就是将表单独存一个库

创建数据库

CREATE DATABASE user_db;
USE user_db;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `user_id` bigint(20) NOT NULL,
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ustatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

entity层

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "t_user")//mabtisPlus的注解，映射实际的表名
public class User {
    private Long userId;
    private String username;
    private String ustatus;
}

dao层

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.shardingjdbcdemo.entity.User;
import org.springframework.stereotype.Repository;

@Repository
public interface UserMapper extends BaseMapper<User> {
}

专库专表的properties配置

#专库专表配置数据源,user_db
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456
#表的位置 在那个数据源（数据库），那个表。 下面的tables.course中的course是表名以什么开头
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds2.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

#表的分片策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
#表示加入到course_1或者course_2表中
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

测试代码

import com.example.shardingjdbcdemo.entity.User;
import com.example.shardingjdbcdemo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
class ShardingJdbcDemoApplicationTests {
    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        User user = new User();
        user.setUsername("张三");
        user.setUstatus("test");
        userMapper.insert(user);
    }

}