【分库分表】ShardingSphere-JDBC：水平分表操作

CREATE TABLE course_1(
  cid bigint(20) primary key,
  cname varchar(50) not null,
  user_id bigint(20) not null,
  cstatus varchar(10) not null
);

CREATE TABLE course_2(
  cid bigint(20) primary key,
  cname varchar(50) not null,
  user_id bigint(20) not null,
  cstatus varchar(10) not null
);
           

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.example.mapper")
public class ShardingDbApplication {
	public static void main(String[] args) {
		SpringApplication.run(ShardingDbApplication.class, args);
	}

}
           

package com.example.mapper;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;

//@Data  
public class Course {
	private Long cid;
	private String cname;
	private Long userId;
	private String cstatus;
           

@Repository  
public interface CourseMapper extends BaseMapper<Course> {

} 
           

# shardingjdbc分片策略
# 水平分库，配置两个数据源，数据源名
spring.shardingsphere.datasource.names=m1
# 一个实体类对应两张表，覆盖
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:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root


#指定数据库course表的分布情况
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

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}
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
           

@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingDbApplicationTests {
    @Autowired
    private CourseMapper courseMapper;
    //添加
    @Test
    public void addCourse() { 
        for(int i=1;i<=10;i++) {
            Course course = new Course();
            course.setCname("java"+i);
            course.setUserId(100L);
            course.setCstatus("Normal"+i);
            courseMapper.insert(course);
        }
    }
    //查询
    @Test
    public void findCourse() {
        QueryWrapper<Course>  wrapper = new QueryWrapper<>(); 
        wrapper.eq("cid",597110947517038593L); 
        Course course = courseMapper.selectOne(wrapper);
        System.out.println(course.getCname());
    }


}
           

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>shardingDB</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
           

据主键值奇偶性，分别把记录加入到不同表内，实现水平分表