springboot整合mybatisplus+shardingsphere
1.简介
Mycat 和 ShardingSphere 都是流行的分布式数据库中间件，都可以用于实现数据分片、读写分离和分布式事务等功能，但它们在设计理念、特点和功能实现上有一些区别
1.1.mycat与shardingsphere
1.1.1.mycat
基于 MySQL 协议的代理式架构，主要提供分片、读写分离、负载均衡等功能。
设计简单，对业务透明度较高。适用于对分布式数据库中间件要求不高、场景相对简单的应用。适用于中小型项目或对数据库规模和性能要求不高的场景。
1.1.2.shardingsphere
功能更加全面、可扩展性更强的分布式数据库中间件生态系统，支持多种数据库类型和 NoSQL 数据库及多种分布式事务实现方式。提供了丰富的功能组件，可定制性更强。适用于对性能、灵活性和功能要求较高的分布式数据库场景。
适用于大型企业级应用、高并发场景以及需要水平扩展的应用场景。
2.springboot整合mybatisplus+shardingsphere
2.1.开发环境
springboot+mybatis-plus+mysql+shardingSphere-jdbc（5.0.0-beta），shardingSphere-jdbc必须是5.0.0-beta，使用其他版本可能会出现动态数据源启动报错：Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required
2.2.建表语句
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
`id` int NOT NULL AUTO_INCREMENT,
`order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
`id` int NOT NULL AUTO_INCREMENT,
`order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_3`;
CREATE TABLE `t_order_3` (
`id` int NOT NULL AUTO_INCREMENT,
`order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_4`;
CREATE TABLE `t_order_4` (
`id` int NOT NULL AUTO_INCREMENT,
`order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_order_5`;
CREATE TABLE `t_order_5` (
`id` int NOT NULL AUTO_INCREMENT,
`order_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_user_2`;
CREATE TABLE `t_user_2` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_user_3`;
CREATE TABLE `t_user_3` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_user_4`;
CREATE TABLE `t_user_4` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `t_user_5`;
CREATE TABLE `t_user_5` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
2.3.引入依赖
<!-- mysql依赖 -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<!-- lombok依赖 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- mybatis-plus 依赖 -->
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-beta</version>
</dependency>
<!-- 数据库连接池依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
2.4.编写分表yml文件
server:
port: 8999
spring:
shardingsphere:
props:
sql-show: true #是否打印sql语句
datasource:
names: ds2023 #定义数据源
ds2023:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.76.128:3306/db_test_2023?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
# url: jdbc:mysql://localhost:3306/db_test_2023?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
rules:
sharding:
binding-tables: t_user, t_order #定义需要进行分库分表的表
tables: #配置各个表的分片规则
t_user: #t_user的分片规则
actual-data-nodes: ds2023.t_user_$->{1..5} #这里的ds2023是上面配置的数据源，必须与上面的一致， t_user_1到t_user_5必须是数据库中存在的真实的表
table-strategy: #分表策略
standard: #表示使用标准分片策略
sharding-column: create_time #用来分片的列名，即根据 create_time 列的值进行分片
sharding-algorithm-name: sharding-user-by-month #分表的算法名称，对应了具体的分表算法的实现
t_order:
actual-data-nodes: ds2023.t_order_$->{1..5}
table-strategy:
standard:
sharding-column: create_time
sharding-algorithm-name: sharding-order-by-month
sharding-algorithms: #配置分片算法
sharding-user-by-month: #算法名称，与上面的对应
type: INLINE #内联的分片算法
props:
algorithm-expression: t_user_$->{create_time.monthValue} #算法的表达式，表示根据 create_time 列的月份确定数据存储在哪个订单表中（要与数据库的表名一致），这里不能使用month，month获取出来的是英文的月份
allow-range-query-with-inline-sharding: true #表示允许使用范围查询（例如按月份范围查询）与内联分片一起使用
sharding-order-by-month:
type: INLINE
props:
algorithm-expression: t_order_$->{create_time.monthValue}
allow-range-query-with-inline-sharding: true
2.5.编写controller，entity，mapper，service
controller
package com.hush.shardingsphere.controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* <p>
* 前端控制器
* </p>
*
* @author hush
* @since 2024-04-19
*/
@RestController
@RequestMapping("/t-order")
public class TOrderController {
}
package com.hush.shardingsphere.controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* <p>
* 前端控制器
* </p>
*
* @author hush
* @since 2024-04-19
*/
@RestController
@RequestMapping("/t-user")
public class TUserController {
}
entity
package com.hush.shardingsphere.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* <p>
*
* </p>
*
* @author hush
* @since 2024-04-19
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class TOrder implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String orderNumber;
private LocalDateTime createTime;
}
package com.hush.shardingsphere.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* <p>
*
* </p>
*
* @author hush
* @since 2024-04-19
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class TUser implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private String userName;
private LocalDateTime createTime;
}
mapper
package com.hush.shardingsphere.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hush.shardingsphere.entity.TOrder;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
/**
* <p>
* Mapper 接口
* </p>
*
* @author hush
* @since 2024-04-19
*/
public interface TOrderMapper extends BaseMapper<TOrder> {
}
package com.hush.shardingsphere.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hush.shardingsphere.entity.TUser;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
/**
* <p>
* Mapper 接口
* </p>
*
* @author hush
* @since 2024-04-19
*/
public interface TUserMapper extends BaseMapper<TUser> {
}
service
package com.hush.shardingsphere.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.hush.shardingsphere.entity.TOrder;
/**
* <p>
* 服务类
* </p>
*
* @author hush
* @since 2024-04-19
*/
public interface TOrderService extends IService<TOrder> {
}
package com.hush.shardingsphere.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.hush.shardingsphere.entity.TUser;
/**
* <p>
* 服务类
* </p>
*
* @author hush
* @since 2024-04-19
*/
public interface TUserService extends IService<TUser> {
}
service.impl
package com.hush.shardingsphere.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hush.shardingsphere.entity.TOrder;
import com.hush.shardingsphere.mapper.TOrderMapper;
import com.hush.shardingsphere.service.TOrderService;
import org.springframework.stereotype.Service;
/**
* <p>
* 服务实现类
* </p>
*
* @author hush
* @since 2024-04-19
*/
@Service
public class TOrderServiceImpl extends ServiceImpl<TOrderMapper, TOrder> implements TOrderService {
}
package com.hush.shardingsphere.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hush.shardingsphere.entity.TUser;
import com.hush.shardingsphere.mapper.TUserMapper;
import com.hush.shardingsphere.service.TUserService;
import org.springframework.stereotype.Service;
/**
* <p>
* 服务实现类
* </p>
*
* @author hush
* @since 2024-04-19
*/
@Service
public class TUserServiceImpl extends ServiceImpl<TUserMapper, TUser> implements TUserService {
}
resource/mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hush.shardingsphere.mapper.TOrderMapper">
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hush.shardingsphere.mapper.TUserMapper">
</mapper>
2.6.编写测试语句
package com.hush.shardingsphere;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.hush.shardingsphere.entity.TOrder;
import com.hush.shardingsphere.entity.TUser;
import com.hush.shardingsphere.service.TOrderService;
import com.hush.shardingsphere.service.TUserService;
import org.apache.catalina.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.lang.annotation.Retention;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
@SpringBootTest
class ShardingsphereApplicationTests {
@Autowired
private TUserService userService;
@Autowired
private TOrderService orderService;
private ZoneId chinaZoneId = ZoneId.of("Asia/Shanghai");// 指定中国时区
private DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); // 时间格式
@Test
void contextLoads() {
}
@Test
void queryTest() {
List<TUser> userList = userService.list();
userList.forEach(user-> System.out.println(user));
System.out.println("=========================================");
List<TOrder> orderList = orderService.list();
orderList.forEach(order -> System.out.println(order));
}
@Test
void queryByTimeTest() {
LocalDateTime beginTime = LocalDateTime.of(2023, 1, 1, 0, 0, 0);
LocalDateTime endTime = LocalDateTime.of(2023, 12, 1, 0, 0, 0);
List<TUser> userList = userService.query().between("create_time", beginTime.format(dateTimeFormatter), endTime.format(dateTimeFormatter)).list();
userList.forEach(user -> System.out.println(user));
System.out.println("=========================================");
List<TOrder> orderList = orderService.query().between("create_time", beginTime.format(dateTimeFormatter), endTime.format(dateTimeFormatter)).list();
orderList.forEach(order -> System.out.println(order));
}
@Test
void addUserTest() {
LocalDateTime time1 = LocalDateTime.of(2023, 1, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TUser user = new TUser();
user.setUserName("user20230101");
user.setCreateTime(time1);
userService.save(user);
}
@Test
void addOrderTest() {
LocalDateTime time1 = LocalDateTime.of(2023, 1, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TOrder order = new TOrder();
order.setOrderNumber("order20230101");
order.setCreateTime(time1);
orderService.save(order);
}
@Test
void addBatchUserTest() {
List<TUser> userList = new ArrayList<>();
for (int i = 2; i < 6; i++) {
LocalDateTime time1 = LocalDateTime.of(2023, i, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TUser user = new TUser();
user.setCreateTime(time1);
user.setUserName("user20230"+i+"01");
userList.add(user);
}
userService.saveBatch(userList);
}
@Test
void addBatchOrderTest() {
List<TOrder> orderList = new ArrayList<>();
for (int i = 2; i < 6; i++) {
LocalDateTime dateTime = LocalDateTime.of(2023, i, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TOrder order = new TOrder();
order.setOrderNumber("order20230"+i+"01");
order.setCreateTime(dateTime);
orderList.add(order);
}
orderService.saveBatch(orderList);
}
@Test
void addBatchUser_2024Test() {
List<TUser> userList = new ArrayList<>();
for (int i = 1; i < 6; i++) {
LocalDateTime time1 = LocalDateTime.of(2024, i, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TUser user = new TUser();
user.setCreateTime(time1);
user.setUserName("user20240"+i+"01");
userList.add(user);
}
userService.saveBatch(userList);
}
@Test
void addBatchOrder_2024Test() {
List<TOrder> orderList = new ArrayList<>();
for (int i = 1; i < 6; i++) {
LocalDateTime dateTime = LocalDateTime.of(2024, i, 1, 0, 0, 0).atZone(chinaZoneId).toLocalDateTime();
TOrder order = new TOrder();
order.setOrderNumber("order20240"+i+"01");
order.setCreateTime(dateTime);
orderList.add(order);
}
orderService.saveBatch(orderList);
}
}
2.7.执行结果
执行addUserTest
shardingsphere会按照我们设置的分片规则根据create_time中的月份插入到对应的user表中
执行addBatchUserTest
执行addBatchOrderTest> 执行queryByTimeTest
shardingsphere会自己进行联表查询，我们只需写好查询规则即可
>
2.8.编写分库分表yml文件
server:
port: 8999
spring:
shardingsphere:
props:
sql-show: true #是否打印sql语句
datasource:
names: ds2023, ds2024 #定义数据源
ds2023:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.76.128:3306/db_test_2023?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
# url: jdbc:mysql://localhost:3306/db_test_2023?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
ds2024:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.76.128:3306/db_test_2024?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
# url: jdbc:mysql://localhost:3306/db_test_2024?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
username: root
password: root
rules:
sharding:
binding-tables: t_user, t_order #定义需要进行分库分表的表
tables: #配置各个表的分片规则
t_user: #t_user的分片规则
actual-data-nodes: ds$->{2023..2024}.t_user_$->{1..5} #这里的ds2023,ds2024是上面配置的数据源，必须与上面的一致， t_user_1到t_user_5必须是数据库中存在的真实的表
database-strategy: #分库策略
standard: #表示使用标准分片策略
sharding-column: create_time #用来分片的列名，即根据 create_time 列的值进行分片
sharding-algorithm-name: sharding-database-by-year #分库的算法名称，对应了具体的分库算法的实现
table-strategy: #分表策略
standard: #表示使用标准分片策略
sharding-column: create_time #用来分片的列名，即根据 create_time 列的值进行分片
sharding-algorithm-name: sharding-table-by-user-month #分表的算法名称，对应了具体的分表算法的实现
t_order:
actual-data-nodes: ds$->{2023..2024}.t_order_$->{1..5}
database-strategy:
standard:
sharding-column: create_time
sharding-algorithm-name: sharding-database-by-year
table-strategy:
standard:
sharding-column: create_time
sharding-algorithm-name: sharding-table-by-order-month
sharding-algorithms: #配置分片算法
sharding-database-by-year: #算法名称，与上面的对应
type: INLINE #内联的分片算法
props:
algorithm-expression: ds$->{create_time.year} #算法的表达式，表示根据 create_time 列的年份来确定数据存储在哪个数据库中，这里运算后会是ds2023，ds2024,必须与上面配置的数据源一致
allow-range-query-with-inline-sharding: true #表示允许使用范围查询（例如按月份范围查询）与内联分片一起使用
sharding-table-by-user-month:
type: INLINE
props:
algorithm-expression: t_user_$->{create_time.monthValue} #算法的表达式，表示根据 create_time 列的月份确定数据存储在哪个订单表中（要与数据库的表名一致），这里不能使用month，month获取出来的是英文的月份
allow-range-query-with-inline-sharding: true
sharding-table-by-order-month:
type: INLINE
props:
algorithm-expression: t_order_$->{create_time.monthValue}
allow-range-query-with-inline-sharding: true
2.9.测试分库分表
执行addBatchUser_2024Test
shardingsphere会按照我们设置的分片规则根据create_time中的年份插入到对应的库中，再根据create_time中的月份插入到对应的user表中
执行addBatchOrder_2024Test
执行queryTest
shardingsphere会自己进行联库联表查询
1366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
