spring结合mybatis多租户实现单库分表

实现单库分表

思路：student表数据量大，所以将其进行分表处理。一共有三个分表，分别是student0，student1，student2，在新增数据的时候，根据请求头中的meta-tenant参数决定数据存在哪张表表。

数据库

1. 建立数据库study1

2. 在数据库中建表，分别为student，student0，student1，student2，四个表结构都一样，只是表名不一样

CREATE TABLE `student` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`age` int DEFAULT NULL,`credit` varchar(14) DEFAULT NULL,`tenant_id` int DEFAULT NULL COMMENT '租户id',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

引入pom

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter -->
<dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId><version>5.1.1</version>
</dependency>

配置文件application.yml

#单库分表
spring:main:allow-bean-definition-overriding: trueshardingsphere:datasource:names: ds1ds1:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/study1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8username: rootpassword: root1234mode:type: Memoryoverwrite: truerules:sharding:tables:student:actual-data-nodes: ds1.student$->{0..2}key-generate-strategy:column: idkey-generator-name: snowflakebinding-tables:- studentdefault-table-strategy:standard:sharding-algorithm-name: custom_inlinesharding-column: tenant_iddefault-sharding-column: tenant_idsharding-algorithms:custom_inline:type: CLASS_BASEDprops:strategy: STANDARDalgorithmClassName: com.cyy.config.TablePreciseShardingAlgorithmprops:sql-show: true
tenant:enable: true #启用多租户column: tenant_id

实现标准分片算法接口

package com.cyy.config;import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;import java.util.Collection;/*** 标准分表算法*/
@Component
public class TablePreciseShardingAlgorithm implements StandardShardingAlgorithm<Integer> {@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {String tableName = preciseShardingValue.getLogicTableName().toLowerCase() + (preciseShardingValue.getValue() %10);return tableName;}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) {return collection;}@Overridepublic void init() {}@Overridepublic String getType() {return null;}
}

配置租户上下文相关内容

用到的常量

package com.cyy.constant;/*** 多租户相关的常量*/
public class TenantConstant {public static final String META_TENANT_ID = "meta-tenant";public static final String META_TENANT_ID_PARAM = "tenantId";public static final Long TENANT_ID_DEFAULT = 1l;
}

定义租户上下文

package com.cyy.config;/*** 定义租户上下文，通过上下文保存当前租户的信息*/
public class TenantContextHolder {private static final ThreadLocal<Long> CONETXT_HOLDER = new ThreadLocal<>();public static void set(Long l){CONETXT_HOLDER.set(l);}public static Long getTenantId(){return CONETXT_HOLDER.get();}public static void remove(){CONETXT_HOLDER.remove();}
}

设置多租户的相关的属性

package com.cyy.config;import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;import java.util.HashSet;
import java.util.Set;@Data
/*** 将配置文件中的属性自动映射都Java对象的字段中* 指定配置文件中的配置项的前缀为tenant，简化@Vlue注解，不需要加很多的@Value*/
@ConfigurationProperties(prefix = "tenant")
@Component
public class TenantProperties {//不需要加tenantid的mapper方法名public static Set<String> NOT_PROCEED = new HashSet<>();//不需要加tenentid的表名public static Set<String> NOT_TABLES = new HashSet<>();//是否开启多租户，读取的是配置文件中的tenant.enable的值，等价于@Value(${tenant.enable})private boolean enable = false;//多租户字段private String column = "tenant_id";
}

配置租户上下文拦截器

package com.cyy.interceptor;import com.cyy.config.TenantContextHolder;
import com.cyy.config.TenantProperties;
import com.cyy.constant.TenantConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;/*** HandlerInterceptor是在handler处理请求之前或者之后执行的拦截器，可以对请求做预处理或者对响应结果做统一处理，实现日志记录或者权限认证等功能* HandlerInterceptor可以拦截所有的请求，也可以只拦截特定的亲故*/
@Slf4j
@Component
public class TenantContextHandlerInterceptor implements HandlerInterceptor {@Resourceprivate TenantProperties tenantProperties;public TenantContextHandlerInterceptor(){}/*** 在请求处理前设置租户上下文* @param request current HTTP request* @param response current HTTP response* @param handler chosen handler to execute, for type and/or instance evaluation* @return* @throws Exception*/@Overridepublic boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {log.info("=====对请求进行统一拦截=====");if (tenantProperties.isEnable()){//从请求头中获取tenantIdString tenantId = request.getHeader(TenantConstant.META_TENANT_ID);if (StringUtils.isNotBlank(tenantId)){TenantContextHolder.set(Long.parseLong(tenantId));} else {TenantContextHolder.set(TenantConstant.TENANT_ID_DEFAULT);}log.info("获取到的租户id为：【{}】",TenantContextHolder.getTenantId());}return true;}/*** 请求处理完成之后的回调* @param request current HTTP request* @param response current HTTP response* @param handler handler (or {@link HandlerMethod}) that started asynchronous* execution, for type and/or instance examination* @param ex exception thrown on handler execution, if any* @throws Exception*/@Overridepublic void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {TenantContextHolder.remove();}
}

将租户上下文拦截器添加到springmvc配置中

对服务器的所有请求进行拦截，从请求头的meta-tenant参数中获取tenant_id值，并设置租户id

package com.cyy.config;import com.cyy.interceptor.TenantContextHandlerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter;
import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;import javax.annotation.Resource;
import java.util.List;/*** 通过实现WebMvcConfigurer接口，可以自定义springmvc的配置，例如添加拦截器*/
@Configuration
public class CustomeConfig implements WebMvcConfigurer {@Resourceprivate MappingJackson2HttpMessageConverter mappingJackson2HttpMessageConverter;/*** TenantContextHandlerInterceptor类本身就是一个Bean，在这块再次声明一个相同Bean的时候，会报错* 可以在配置文件中添加配置allow-bean-definition-overriding: true，允许bean定义覆盖* @return*/@Bean("tenantContextHandlerInterceptor")public HandlerInterceptor customerInterceptor(){return new TenantContextHandlerInterceptor();}/*** 添加拦截器* @param registry*/@Overridepublic void addInterceptors(InterceptorRegistry registry) {registry.addInterceptor(customerInterceptor());}@Overridepublic void configureMessageConverters(List<HttpMessageConverter<?>> converters) {converters.add(0,mappingJackson2HttpMessageConverter);}
}

配置mybatis的插件

继承多租户拦截器TenantLineInnerInterceptor

package com.cyy.interceptor;import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.cyy.config.TenantContextHolder;
import com.cyy.config.TenantProperties;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;import java.sql.SQLException;
import java.util.Objects;/*** 继承多租户拦截器*/
public class CustomTenantLineInnerInterceptor extends TenantLineInnerInterceptor {private TenantProperties tenantProperties;public TenantProperties getTenantProperties(){return tenantProperties;}public void setTenantProperties(TenantProperties tenantProperties){this.tenantProperties = tenantProperties;}@Overridepublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {if (TenantProperties.NOT_PROCEED.stream().anyMatch(s -> s.equalsIgnoreCase(ms.getId()))){return;}if (Objects.isNull(TenantContextHolder.getTenantId())){return;}super.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);}public CustomTenantLineInnerInterceptor(final TenantProperties tenantProperties, final TenantLineHandler tenantLineHandler){super(tenantLineHandler);this.setTenantProperties(tenantProperties);this.setTenantLineHandler(tenantLineHandler);}
}

添加多租户拦截器到mybatisplus拦截器中

将多租户拦截器CustomTenantLineInnerInterceptor添加到MybatisPlusInterceptor的拦截器中

package com.cyy.config;import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.cyy.interceptor.CustomTenantLineInnerInterceptor;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.annotation.Resource;
import java.util.Objects;@Configuration
@MapperScan("com.cyy.mapper")
public class MybatisPlusConfig {@Resourceprivate TenantProperties tenantProperties;@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor(){MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();//添加多租户拦截器if (tenantProperties.isEnable()){mybatisPlusInterceptor.addInnerInterceptor(tenantLineInnerInterceptor());}//分页插件mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//防止全表更新与删除插件mybatisPlusInterceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());//乐观锁插件mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());return mybatisPlusInterceptor;}public TenantLineInnerInterceptor tenantLineInnerInterceptor(){return new CustomTenantLineInnerInterceptor(tenantProperties, new TenantLineHandler() {/*** 获取租户id* @return*/@Overridepublic Expression getTenantId() {Long tenantId = TenantContextHolder.getTenantId();if (Objects.nonNull(tenantId)){return new LongValue(tenantId);}return null;}/*** 获取多租户的字段名* @return*/@Overridepublic String getTenantIdColumn() {return tenantProperties.getColumn();}/*** 根据表名判断是否忽略拼接多租户条件* @param tableName 表名* @return*/@Overridepublic boolean ignoreTable(String tableName) {return tenantProperties.NOT_TABLES.stream().anyMatch((t) -> t.equalsIgnoreCase(tableName));}});}
}

测试代码 

测试数据插入的controller

package com.cyy.controller;import com.cyy.domain.Student;
import com.cyy.service.StudentService;
import com.cyy.util.RoundRobinUtil;
import com.cyy.config.TenantContextHolder;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.annotation.Resource;/*** 学生类控制器*/
@RestController
@RequestMapping("/student")
public class StudentController {@Resourceprivate StudentService studentService;/*** 多租户测试-插入一个用户*/@PostMapping("/insert")public ResponseEntity insert(@RequestBody Student student){student.setTenantId(TenantContextHolder.getTenantId().intValue());int i = studentService.insert(student);if (i > 0){return ResponseEntity.ok("插入成功");}return ResponseEntity.ok("插入失败");}
}

请求参数

{"id": 3,"name": "孙三","age": 3,"credit": "3"
}

请求头

运行结果