sharding jdbc入门-springboot整合sharding jdbc

创建springboot项目
项目目录结构：

pom.xml文件

<dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

resources下的table.sql
我们创建两个表，字段完全一样，表名结尾是1和2

-- 分表
CREATE TABLE `course_1` (
  `cid` bigint(20) NOT NULL,
  `cname` varchar(50) NOT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `cstatus` varchar(10) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

最关键的就是分表的逻辑，如下：

#垂直分表策略
# 配置真实数据源
spring.shardingsphere.datasource.names=m1
# 配置第 1 个数据源
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:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 指定表的分布情况 配置表在哪个数据库里，表名是什么。水平分表，分两个表：m1.course_1,m1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# 指定表的主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
# 采用雪花算法生成id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略 约定cid值为偶数添加到course_1表。如果是奇数添加到course_2表。
# 选定计算的字段
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
# 根据计算的字段算出对应的表名。cid除2取余，得0或1，加1就是1或2，就是我们的course_1或course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}

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

spring.main.allow-bean-definition-overriding=true

测试

/**
 * 插入多条，分配到不同的表中
 */
@Test
void insert() {
    for (int i = 0; i < 10; i++) {
        Course record = new Course();
        record.setCname("java"+i);
        record.setCstatus("1");
        System.out.println(courseMapper.insert(record));
    }
}

/**
 * 测试从不同数据库查询
 */
@Test
void queryAll() {
    List<Course> courses = courseMapper.queryAllCourse();
    System.out.println(courses);
}

/**
 * 传两个id用in查询（两个id分别在两个表）
 */
@Test
void queryByCids() {
    ArrayList<Long> cids = Lists.newArrayList(720664335210254336L, 720664334933430273L);
    List<Course> courses1 = courseMapper.queryByCids(cids);
    System.out.println(courses1);
}

源码地址：shardingjdbcdemo