ShardingSphere学习（4）- Sharding-JDBC 实现垂直分库

垂直分库
其实就是配置两个数据源，在代码书写中对这两个数据库的表可以不用手动切换数据源，无感操作。

1. 数据库结构

2. 配置文件

# shardingjdbc 分片策略
# 配置数据源，给数据源起名称,
# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=m1,m2

# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3333/course_db?serverTimezone=GMT%2B8
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.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3333/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

# 配置数据库里面专库专表
spring.shardingsphere.sharding.tables.t_course.actual-data-nodes=m$->{1}.t_course
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{2}.t_user

# 配置主键 id 生成策略 SNOWFLAKE 雪花算法
spring.shardingsphere.sharding.tables.t_course.key-generator.column=id
spring.shardingsphere.sharding.tables.t_course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

3. 创建实体类

package com.wuk.shardingspheredemo.model;

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

/**
 * @author wuk
 * @description:
 * @menu
 * @date 2021/10/22 14:07
 */
@Data
@TableName(value = "t_course")
public class Course {

    private Long id;

    private String cname;

    private Long userId;

    private String cstatus;
}

package com.wuk.shardingspheredemo.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * @author wuk
 * @description:
 * @menu
 * @date 2021/10/14 16:31
 */
@Data
@TableName(value = "t_user")
public class User {

    private Long id;

    private String name;

    private int age;
}

4. 测试

package com.wuk.shardingspheredemo;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.wuk.shardingspheredemo.mapper.CourseMapper;
import com.wuk.shardingspheredemo.mapper.UserMapper;
import com.wuk.shardingspheredemo.model.Course;
import com.wuk.shardingspheredemo.model.User;
import com.wuk.shardingspheredemo.service.CourseService;
import com.wuk.shardingspheredemo.service.UserService;
import com.wuk.shardingspheredemo.service.impl.UserServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@SpringBootTest
class ShardingSphereDemoApplicationTests {

    @Resource
    private CourseMapper courseMapper;

    @Resource
    private CourseService courseService;

    @Resource
    private UserMapper userMapper;

    @Resource
    private UserService userService;

    //添加操作
    @Test
    public void addCourseDb() {
        List<Course> list = new ArrayList<>();
        for (int i=1;i<10;i++){
            Course course = new Course();
            course.setCname("javademo3");
            course.setUserId(Long.parseLong(String.valueOf(i)));
            course.setCstatus("Normal3");
            list.add(course);
        }
        courseService.saveBatch(list);

        List<User> userList = new ArrayList<>();
        for (int i=1;i<10;i++){
            User user = new User();
            user.setName("javademo3");
            user.setAge(10+i);
            userList.add(user);
        }
        userService.saveBatch(userList);
    }
    //查询操作
    @Test
    public void findCourseDb() {
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        //设置 userid 值
        wrapper.eq("user_id",1452902324366213125L);
        //设置 cid 值
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course);
    }


}