网站建设及相关流程北京网站推广服务

Sharding-Springboot-mybatis-plus整合（三）

1.简介

本节目标，使用SpringBoot整合Sharding和Mybatis-Plus验证上节分片策略

从配置文件上看策略包括（ inline、standard、complex、hint）

环境搭建以inline策略演示

inline 策略

inline策略是简单的表达式策略，不支持特殊范围查询

代码地址

https://gitee.com/wn2019/sharding-study1

2.环境代码搭建

2.1Maven依赖

Mybatis-plus-3.5.3
Druid-1.1.22
Springboot-2.3.1.RELEASE

依赖POM

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>sharding-study1</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target><sharding-jdbc-version>4.1.1</sharding-jdbc-version></properties><!-- spring boot依赖 --><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.1.RELEASE</version></parent><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency><!-- mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3</version></dependency><!-- sharding-jdbc springboot的依赖包 --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>${sharding-jdbc-version}</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</artifactId><version>${sharding-jdbc-version}</version></dependency><!-- MySql驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.22</version></dependency></dependencies>
</project>

2.2SQL

新增两个订单表 t_order_1和t_order_2

CREATE TABLE `t_order_1` (`order_id` bigint NOT NULL COMMENT '主键',`order_name` varchar(50) DEFAULT NULL COMMENT '订单名称',`order_type` varchar(50) DEFAULT NULL COMMENT '订单类型',`order_desc` varchar(200) DEFAULT NULL COMMENT '订单详情',`create_user_id` int DEFAULT NULL COMMENT '创建人',`create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;CREATE TABLE `t_order_2` (`order_id` bigint NOT NULL COMMENT '主键',`order_name` varchar(50) DEFAULT NULL COMMENT '订单名称',`order_type` varchar(50) DEFAULT NULL COMMENT '订单类型',`order_desc` varchar(200) DEFAULT NULL COMMENT '订单详情',`create_user_id` int DEFAULT NULL COMMENT '创建人',`create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2.3代码部分

实体类

Order.java

package com.wnn.sd.pojo;import java.util.Date;import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;/*** 订单表*/
@Data
@TableName(value = "t_order")
public class Order {// 订单ID@TableId("order_id")private Long orderId;// 订单名称private String orderName;// 订单类型private String orderType;// 订单详情描述private String orderDesc;// 创建人private int createUserId;// 创建人姓名private String createUserName;// 创建时间private Date createTime;}

mapper

IOrderMapper.class

package com.wnn.sd.mapper;import org.springframework.stereotype.Repository;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wnn.sd.pojo.Order;@Repository
public interface IOrderMapper extends BaseMapper<Order> {}

启动类

MyShardingJdbcApplication

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

测试类

package com.wnn.sd;import java.util.List;import javax.annotation.Resource;import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import com.wnn.sd.mapper.IOrderMapper;
import com.wnn.sd.pojo.Order;@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingTestBoot {@Resourceprivate IOrderMapper orderMapper;/*** 添加数据*/@Testpublic void addOrder() {for (int i = 0; i < 10; i++) {Order order = new Order();order.setOrderName("wn" + i);orderMapper.insert(order);}}/*** 查询列表数据*/@Testpublic void queryList() {List<Order> orders = orderMapper.selectList(null);orders.forEach(o -> System.out.println(o));}}

2.4 配置文件

#  单库分表 配置
spring:shardingsphere:datasource:# 配置数据库名称 相当于给数据源取别名（可以配置多个库，以逗号隔开）names: m1# 配置具体数据库连接信息m1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.cj.jdbc.Driver# 配置 数据库 testurl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTCusername: rootpassword: root# 分片策略sharding:# 配置不同表的 分片策略tables:# 配置 具体的 逻辑表的 分片策略t_order:# t_order 订单表的 主键规则keyGenerator:# 主键列column: order_id# 主键生成规则 （SNOWFLAKE 雪花算法 生成分布式唯一ID）type: SNOWFLAKE# 未知作用#            props:#              worker:#                id: 1# 配置 t_order 订单表的 具体数据库物理表的映射关系 表达式actualDataNodes: m1.t_order_$->{1..2}# 表策略tableStrategy:inline:# 分片列sharding-column: order_id# 分片规则 表达式（映射到具体的物理表 ）algorithm-expression: t_order_$->{order_id % 2 + 1}# 配置是否打印SQLprops:sql.show: true# 解决一个bean映射到多张表问题main:allow-bean-definition-overriding: true

2.5 测试类代码

package com.wnn.sd;import java.util.List;import javax.annotation.Resource;import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import com.wnn.sd.mapper.IOrderMapper;
import com.wnn.sd.pojo.Order;@RunWith(SpringRunner.class)
@SpringBootTest
public class ShardingTestBoot {@Resourceprivate IOrderMapper orderMapper;/*** 添加数据*/@Testpublic void addOrder() {for (int i = 0; i < 10; i++) {Order order = new Order();order.setOrderName("wn" + i);orderMapper.insert(order);}}/*** 查询列表数据*/@Testpublic void queryList() {List<Order> orders = orderMapper.selectList(null);orders.forEach(o -> System.out.println(o));}}

至此代码完成

3.测试效果

3.1 运行测试类代码addOrder()

按照分片策略inline，新增的数据按照ID取摸+1分配到两个表中

执行效果

可以发现SQL执行的时候分为

Actual SQL（实际SQL）和Logic SQL（逻辑SQL）

查看t_order_1和t_order_2表中，t_order_1中有7条数据，主键都是偶数

3.2 运行测试类代码queryList()

可以看到从两个表中查询所有数据

3.3 简单条件查询

3.3.1使用等于查询

从表2取一个订单表按主键查询“1629805480188895233”

从日志发现，精确的路由到order_2只执行了一次查询

[           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  order_id,order_name,order_type,order_desc,create_user_id,create_user_name,create_time  FROM t_order_2 WHERE (order_id = ?) ::: [1629805480188895233]
Order(orderId=1629805480188895233, orderName=wn2, orderType=null, orderDesc=null, createUserId=0, createUserName=null, createTime=Mon Feb 27 03:28:01 CST 2023)

3.3.2使用范围IN查询

查询结果同3.3.1，从日志发现，精确的路由到order_2只执行了一次查询

3.3.3 使用范围

直接报错’Inline strategy cannot support this type ‘，说明inline策略不支持between范围查询

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalStateException: Inline strategy cannot support this type sharding:RangeRouteValue(columnName=order_id, tableName=t_order, valueRange=[1629805480188895233‥1629805480214061057])
### The error may exist in com/wnn/sd/mapper/IOrderMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  order_id,order_name,order_type,order_desc,create_user_id,create_user_name,create_time  FROM t_order     WHERE (order_id BETWEEN ? AND ?)
### Cause: java.lang.IllegalStateException: Inline strategy cannot support this type sharding:RangeRouteValue(columnName=order_id, tableName=t_order, valueRange=[1629805480188895233‥1629805480214061057])