本文所有配置:基于Sharding Jdbc-4.0.0-RC1
介绍
定位为轻量级 Java 框架,在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库,以 jar 包形式 提供服务,无需额外部署和依赖,可理解为增强版的 JDBC 驱动,完全兼容 JDBC 和各种 ORM 框架。
-
适用于任何基于 JDBC 的 ORM 框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template 或直接 使用 JDBC。
-
支持任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP 等。
-
支持任意实现 JDBC 规范的数据库,目前支持 MySQL,Oracle,SQLServer,PostgreSQL 以及任何 遵循 SQL92 标准的数据库。
ShardingJdbc不是做分库分表。
主要做两个功能:数据分片和读写分离。简化对分库分表之后的操作。
ShardingJdbc实现水平分表
搭建环境
SpringBoot+MybatisPlus+ShardingJdbc+Druid连接池
创建一个SpringBoot项目,pom依赖如下:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<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>
创建数据库course_db,数据库中创建两张表course_1和course_2
约定规则:如果添加课程id是偶数就把数据添加到course_1,奇数添加到course_2
create database course_db;
use course_db;
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
);
实体类和mapper接口
@Data
public class Course {
@TableId
private Long cid;
private String cname;
private Long userId;
private String cstatus;
}
public interface CourseMapper extends BaseMapper<Course> {
}
主启动类:
@MapperScan("com.wj.bootshardingjdbc.mapper")
@SpringBootApplication
public class BootShardingjdbcApplication {
public static void main(String[] args) {
SpringApplication.run(BootShardingjdbcApplication.class, args);
}
}
表分片策略
修改application.properties
# 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/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=1234
#指定course表分布情况
#$->{1..2} 表示从1到2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#指定course表里面主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
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
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
#输出sql
spring.shardingsphere.props.sql.show=true
测试
@SpringBootTest
class BootShardingjdbcApplicationTests {
@Autowired private CourseMapper courseMapper;
@Test
void addCourse() {
for (int i = 0; i < 100; i++) {
Course course = new Course();
course.setCname("java");
course.setCstatus("1");
course.setUserId(1L);
courseMapper.insert(course);
}
}
}
测试查询:
@Test
void findOne(){
courseMapper.selectById(583694236335996928L);
}
测试排序:最终结果(奇数和偶数)已经排序过了
@Test
void findCourse(){
List<Course> courses =
courseMapper.selectList(new QueryWrapper<Course>().orderByAsc("cid"));
for (Course course : courses) {
System.out.println(course);
}
}
ShardingJdbc实现水平分库
搭建环境
约定规则:
-
数据库规则:userid为偶数添加到edu_db_1数据库,为奇数添加到edu_db_2数据库
-
cid为偶数添加到course_1表,奇数添加到course_2表
库分片策略
application.properties
# ShardingJdbc分片策略
#数据源名称
spring.shardingsphere.datasource.names=m1,m2
#一个实体类对应两张表( 后定义的bean会覆盖之前定义的相同名称的bean。)
spring.main.allow-bean-definition-overriding=true
#数据源m1配置
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/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=1234
#数据源m2配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=1234
#指定course表分布情况
#$->{1..2} 表示从1到2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
#指定course表里面主键生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
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
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
#执行数据库分片策略 userid为偶数添加到edu_db_1数据库,为奇数添加到edu_db_2数据库
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
#指定course表的规则,而不是数据库中的所有表
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}
#输出sql
spring.shardingsphere.props.sql.show=true
测试
测试新增
@Test
void addCourseDB() {
for (Integer i = 0; i < 100; i++) {
Course course = new Course();
course.setCname("java");
course.setCstatus("1");
course.setUserId(i.longValue());
courseMapper.insert(course);
}
}
测试排序
@Test
void findCourse(){
List<Course> courses =
courseMapper.selectList(new QueryWrapper<Course>().orderByAsc("cid","user_id"));
for (Course course : courses) {
System.out.println(course);
}
}
测试查询
@Test
void findOne(){
courseMapper.selectById(583694236335996928L);
}
测试更新
@Test
void updateOne(){
Long cid = 1376824846326964226L;
Course course = new Course();
course.setCid(cid);
course.setCname("spring");
courseMapper.updateById(course);
}
更新成功: