一、简介
定位为轻量级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标准的数据库。
——摘自官方网站
二、数据分片——水平切分
1. 创建数据库和表
CREATE DATABASE `order-db1`;
CREATE TABLE `t_order_1` (
`oid` bigint(20) NOT NULL COMMENT '订单id',
`comment` varchar(100) CHARACTER DEFAULT NULL COMMENT '订单备注',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `t_order_2` (
`oid` bigint(20) NOT NULL COMMENT '订单id',
`comment` varchar(100) CHARACTER DEFAULT NULL COMMENT '订单备注',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
根据订单号的奇偶对数据进行分片。
2. java代码
pom.xml:
<?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.3.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>sharding-jdbc-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-demo</name>
<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>
<!--不能使用druid-spring-boot-starter-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.yml:
spring:
shardingsphere:
datasource:
names: d0
d0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/order-db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
sharding:
tables:
t_order:
actualDataNodes: d0.t_order_$->{1..2}
#如果添加订单的id是偶数把数据添加t_order_1,如果奇数添加到t_order_2
tableStrategy:
inline:
shardingColumn: oid
algorithmExpression: t_order_$->{oid % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: oid
props:
sql.show: true
# 一个实体类对应两张表,覆盖
main:
allowBeanDefinitionOverriding: true
主启动类:
@SpringBootApplication
@MapperScan("com.example.shardingJdbcDemo.mapper")
public class ShardingJdbcDemoApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcDemoApplication.class, args);
}
}
实体类:
@Data
@TableName("t_order")
public class Order {
private Long oid;
private String comment;
private Long userId;
}
注意:加上@TableName注解
Mapper接口:
@Repository
public interface OrderMapper extends BaseMapper<Order> {
}
测试类:
@RunWith(SpringRunner.class)
@SpringBootTest
class ShardingJdbcDemoApplicationTests {
@Autowired
private OrderMapper orderMapper;
/**
* 测试添加订单
*/
@Test
public void addOrder() {
for (int i = 1; i <= 10; i++) {
Order order = new Order();
//使用雪花算法生产id
//order.setOid((long) i);
order.setComment("course" + i);
order.setUserId((long) i);
orderMapper.insert(order);
}
}
/**
* 测试查询订单
*/
@Test
public void getOrders() {
List<Order> orders = orderMapper.selectList(null);
System.out.println(orders.size());
}
}
三、数据分片——垂直切分
1. 创建数据库和表
CREATE DATABASE `user-db`;
CREATE TABLE `user-db`.t_user (
uid BIGINT NOT NULL COMMENT '用户id',
uname varchar(100) NOT NULL COMMENT '用户姓名'
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
将用户表存放在order-db的t_user表中
2. java代码
application.yml
spring:
shardingsphere:
datasource:
names: d0, d1
d0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/order-db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
d1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/user-db?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
sharding:
tables:
t_order:
actualDataNodes: d$->{0}.t_order_$->{1..2}
#如果添加课程id是偶数把数据添加course_1,如果奇数添加到course_2
tableStrategy:
inline:
shardingColumn: oid
algorithmExpression: t_order_$->{oid % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: oid
#配置user-db数据库里面t_user专库专表
t_user:
actualDataNodes: d$->{1}.t_user
props:
sql.show: true
# 一个实体类对应两张表,覆盖
main:
allowBeanDefinitionOverriding: true
增加分库配置
User实体类:
package com.example.shardingJdbcDemo.entity;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* @author Castle
* @Date 2021/4/26 8:49
*/
@Data
@TableName("t_user")
public class User {
private Long uid;
private String uname;
}
UserMapper:
@Repository
public interface UserMapper extends BaseMapper<User> {
}
测试类:
/**
* 测试添加用户
*/
@Test
public void addUser() {
for (int i = 1; i <= 10; i++) {
User user = new User();
user.setUid((long) i);
user.setUname("user" + i);
userMapper.insert(user);
}
}
四、公共表
1. 建表
在order-db1和user-db中分别创建字典表:
CREATE TABLE t_dict (
tid BIGINT NOT NULL COMMENT '字典id',
tname varchar(100) NOT NULL COMMENT '字典名称',
CONSTRAINT t_dict_pk PRIMARY KEY (tid)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
字典表的数据将同时存储在2个数据库中
2.代码
修改application.yml:
spring:
shardingsphere:
datasource:
names: d0, d1
d0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/order-db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
d1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/user-db?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
sharding:
tables:
t_order:
actualDataNodes: d$->{0}.t_order_$->{1..2}
#如果添加课程id是偶数把数据添加course_1,如果奇数添加到course_2
tableStrategy:
inline:
shardingColumn: oid
algorithmExpression: t_order_$->{oid % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: oid
#配置user-db数据库里面t_user专库专表
t_user:
actualDataNodes: d$->{1}.t_user
#配置公共表
broadcast-tables: t_dict
props:
sql.show: true
# 一个实体类对应两张表,覆盖
main:
allowBeanDefinitionOverriding: true
实体类:
@Data
@TableName("t_dict")
public class Dict {
private Long tid;
private String tname;
}
mapper:
@Repository
public interface DictMapper extends BaseMapper<Dict> {
}
测试类:
/**
* 测试添加字典
*/
@Test
public void addDict() {
for (int i = 1; i <= 10; i++) {
Dict dict = new Dict();
dict.setTid((long) i);
dict.setTname("name" + i);
dictMapper.insert(dict);
}
}
五、 读写分离
参考MySQL(10)——主从复制搭建主从数据库。
主库:192.168.30.132,从库:192.168.30.133
在test库中创建user表:
CREATE TABLE t_course (
id BIGINT NOT NULL COMMENT '用户id',
name varchar(100) NOT NULL COMMENT '用户姓名'
)
insert into t_course values (1,@@hostname);
修改application.yml:
spring:
shardingsphere:
datasource:
names: d0, d1,m0,s0
d0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/order-db1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
d1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/user-db?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
m0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.30.132:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
s0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.30.133:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username: root
password: root
##配置读写分离
masterslave:
name: ms
master-data-source-name: m0
slave-data-source-names: s0
sharding:
tables:
t_order:
actualDataNodes: d$->{0}.t_order_$->{1..2}
#如果添加课程id是偶数把数据添加course_1,如果奇数添加到course_2
tableStrategy:
inline:
shardingColumn: oid
algorithmExpression: t_order_$->{oid % 2 + 1}
keyGenerator:
type: SNOWFLAKE
column: oid
#配置user-db数据库里面t_user专库专表
t_user:
actualDataNodes: d$->{1}.t_user
#配置读写分离的t_course专库专表
t_course:
actualDataNodes: m$->{0}.t_course
#配置公共表
broadcast-tables: t_dict
props:
sql.show: true
# 一个实体类对应两张表,覆盖
main:
allowBeanDefinitionOverriding: true
实体类:
@Data
@TableName("t_course")
public class Course {
private Long id;
private String name;
}
mapper:
@Repository
public interface CourseMapper extends BaseMapper<Course> {
}
测试类:
/**
* 测试添加课程
*/
@Test
public void addCourse() {
for (int i = 5; i <= 10; i++) {
Course course = new Course();
course.setId((long) i);
course.setName("name" + i);
courseMapper.insert(course);
}
}
/**
* 测试获取课程
*/
@Test
public void getCourse() {
QueryWrapper<Course> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1L);
List<Course> courses = courseMapper.selectList(wrapper);
System.out.println(courses.get(0));
}
由于建表后的insert语句中的@@hostname,会被主从解析成不同的值。通过查询id为1的值,获取到来自从库的信息:
Course(id=1, name=server4)