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
• 创建数据表 course

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;

3. application.properties文件配置

spring.application.name=sharding_demo01

spring.shardingsphere.datasource.names=ds0

spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/course_db?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.sharding.tables.course.actual-data-nodes=ds0.course_$->{1..2}
# 水平分表
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}
# course表主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.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;

    @Test
    void testAdd() {
        Course course = new Course();
        course.setCname("test");
        course.setCstatus("normal");
        course.setUserId(2L);
        courseMapper.insert(course);
    }

    @Test
    void testQuery() {
        Course course = courseMapper.selectByPrimaryKey(546270575232811008L);
        System.out.println(course);
    }

    @Test
    void testDelete() {
        courseMapper.deleteByPrimaryKey(546270575232811008L);
    }
}

• github地址

  sharding_demo01

5. 注意事项

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