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>
据主键值奇偶性,分别把记录加入到不同表内,实现水平分表