为什么我做的视频网站播放不了,网站搭建南京,私人做医院的网站,做网站 怎么备案实现单库分表
思路#xff1a;student表数据量大#xff0c;所以将其进行分表处理。一共有三个分表#xff0c;分别是student0#xff0c;student1#xff0c;student2#xff0c;在新增数据的时候#xff0c;根据请求头中的meta-tenant参数决定数据存在哪张表表。
数…实现单库分表
思路student表数据量大所以将其进行分表处理。一共有三个分表分别是student0student1student2在新增数据的时候根据请求头中的meta-tenant参数决定数据存在哪张表表。
数据库
1. 建立数据库study1
2. 在数据库中建表分别为studentstudent0student1student2四个表结构都一样只是表名不一样
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)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;
引入pom
!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc-core-spring-boot-starter --
dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core-spring-boot-starter/artifactIdversion5.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?serverTimezoneUTCuseUnicodetruecharacterEncodingutf-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 StandardShardingAlgorithmInteger {Overridepublic String doSharding(CollectionString collection, PreciseShardingValueInteger preciseShardingValue) {String tableName preciseShardingValue.getLogicTableName().toLowerCase() (preciseShardingValue.getValue() %10);return tableName;}Overridepublic CollectionString doSharding(CollectionString collection, RangeShardingValueInteger 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 ThreadLocalLong 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)
public class TenantProperties {//不需要加tenantid的mapper方法名public static SetString NOT_PROCEED new HashSet();//不需要加tenentid的表名public static SetString 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(ListHttpMessageConverter? 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
}
请求头 运行结果
