Sharding-JDBC + springBoot 简单水平分库分表

单库分表

pom文件中加入依赖

<!-- mysql 依赖-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<!-- shardingsphere springboot 依赖-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

<!-- Mybatis Plus 依赖 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.0.5</version>
</dependency>

<!--lombok 用于生成getter和setter-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

配置数据源和分表策略

在application.properties 中添加配置

# 配置数据源，c1为数据源名称,后边会用到
spring.shardingsphere.datasource.names=c1
# 一个实体类对应两张表，水平分表必须加
spring.main.allow-bean-definition-overriding=true

#数据源具体配置，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.c1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.c1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.c1.url=jdbc:mysql://192.168.31.9:3306/school_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.c1.username=root
spring.shardingsphere.datasource.c1.password=123456

#指定 course 表分布情况，配置表在哪个数据库里面，表名称都是什么 c1.course_1 ,c1.course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=c1.course_$->{1..2}

# 指定 course 表里面主键 id 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定分片策略 id值偶数添加到course_1表,  id是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2 + 1}

# sql打印
spring.shardingsphere.props.sql.show=true

SpringBoot 启动类修改

增加mapper扫描路径

@SpringBootApplication
@MapperScan("com.example.mapper")	//mapper扫描路径
public class MainApplication {

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

}

创建数据表

create table course_1(
    id  bigint default 0 not null comment '主键' primary key,
    name    varchar(100)     null,
    user_id bigint           null,
    status  int              null
);

create table course_2(
    id  bigint default 0 not null comment '主键' primary key,
    name    varchar(100)     null,
    user_id bigint           null,
    status  int              null
);

实体类配置

package com.example.demo;
import lombok.Data;

/**
 * @author wx
 */
@Data
public class Course {

    private Long id;
    private String name;
    private Long userId;
    private int status;

    public Course(){}

    public Course(String name, Long userId, int status) {
        this.name = name;
        this.userId = userId;
        this.status = status;
    }
}

增加mapper类

package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.Course;
import org.springframework.stereotype.Repository;

/**
 * @author wx
 */
@Repository
public interface CourseMapper extends BaseMapper<Course> {

}

测试类

package com.example.demo;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.segments.MergeSegments;
import com.example.mapper.CourseMapper;
import com.example.mapper.DictMapper;
import com.example.mapper.UserMapper;
import org.junit.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;

/**
 * @author wx
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingjdbcTest {

    @Autowired
    private CourseMapper courseMapper;

    @Test
    public void addCourse(){
        for (int i = 0; i < 10; i++) {
            courseMapper.insert(new Course("class_1", new Long(i), 1));
        }
    }

    @Test
    public void findCourse(){
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.eq("id", 569286041035341825L);
        Course course = courseMapper.selectOne(wrapper);
        System.err.println(course);
    }
}

增加分库策略

修改application.properties：

根据userId分库，userId为偶数到c1库, userId为奇数到c2库

# 配置多个数据源, c1和c2
spring.shardingsphere.datasource.names=c1,c2
# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

#配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
spring.shardingsphere.datasource.c1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.c1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.c1.url=jdbc:mysql://192.168.31.9:3306/school_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.c1.username=root
spring.shardingsphere.datasource.c1.password=123456

spring.shardingsphere.datasource.c2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.c2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.c2.url=jdbc:mysql://192.168.31.9:3306/school_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.c2.username=root
spring.shardingsphere.datasource.c2.password=123456

#指定数据库的分库情况  数据库:c1 c2  数据表:course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=c$->{1..2}.course_$->{1..2}
# 指定 course 表里面主键 id 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# 指定表的分片策略 id值偶数添加到course_1表,  id是奇数添加到course_2表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=idc
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2 + 1}

#指定数据库的分片策略. userId为偶数到c1库, userId为奇数到c2库
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=c$->{user_id % 2 + 1}


# sql打印
spring.shardingsphere.props.sql.show=true

新增test方法

@Test
public void findCourse2(){
     QueryWrapper<Course> wrapper = new QueryWrapper<>();
     wrapper.eq("user_id", 3L);
     List<Course> courses = courseMapper.selectList(wrapper);
     courses.forEach(course -> System.err.println(course));
 }

如下打印日志中可以看到查询使用了哪个数据库（ Actual SQL: c2 ）