Sharding-JDBC垂直分库

1. 环境介绍

• JDK 1.8
• SpringBoot 2.4.0
• MyBatis 2.1.4
• Druid 1.2.3
• Sharding-JDBC 4.1.1

2. 数据库创建

• 创建数据库 course_db user_db
• 创建数据表 course user

CREATE TABLE `course_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) COLLATE utf8_bin NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `course_2` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) COLLATE utf8_bin NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `cstatus` varchar(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `ustatus` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3. application.properties文件配置

spring.application.name=sharding_demo03

spring.shardingsphere.datasource.names=ds0,ds1,ds2

spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/course_db_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3307/course_db_2?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
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.username=root
spring.shardingsphere.datasource.ds2.password=123456

# 库表信息
spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{0..1}.course_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds2.t_user
# 水平分表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 +1}
# 水平分库
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# 垂直分库
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
# course表主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# t_user表主键生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

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

mybatis.mapper-locations=classpath:/mapper/*.xml

4. 测试代码

@SpringBootTest
class ShardingDemoApplicationTests {

    @Autowired
    private CourseMapper courseMapper;
    @Autowired
    private UserMapper userMapper;

    @Test
    void testAddCourse() {
        Course course = new Course();
        course.setCname("testCourse");
        course.setCstatus("nomal");
        course.setUserId(4L);
        courseMapper.insert(course);
    }

    @Test
    void testAddUser() {
        User user = new User();
        user.setUserName("cs01");
        user.setUstatus("aaa");
        userMapper.insert(user);
    }

}

• github地址

  sharding_demo03

5. 注意事项

1. SpringBoot+ShardingJDBC+Druid，使用了sharding-jdbc-spring-boot-starter这个jar包，不能用druid-spring-boot-starter包，会报错找不到url
2. ShardingJDBC+MyBatis时，insert方法去掉主键，否则主键不会生成，要自己插入