在之前经理的某家公司中，经历了一个saas服务的某些功能的数据量不断变大的过程，因为各种功能和性能的原因想到的方法就是直接按saas租户做分库和按租户对某些数据量大的表做分表。但是在我离职之前这两种方式都未能实现。不过，最近刚好看到Mybatis-Plus的多租户的拦截器功能，想到可以用来做第二种方案的问题的解决方法，因此来尝试一番。 使用最新版Mybatis-Plus
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.4.1</version>
</dependency>
需要配合使用DynamicTableNameInnerInterceptor和TenantLineInnerInterceptor
DynamicTableNameInnerInterceptor
利用DynamicTableNameInnerInterceptor主要是用来对某些数据量大的表做分表查询的，这个拦截器可以在执行sql语句的时候动态的修改查询的表名。使用方法如下
//这里我将租户id写死了。真实的实现中应当从当前登录的数据中获取
private static final String tenant_id = "zhao";
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor =new MybatisPlusInterceptor();
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor= new DynamicTableNameInnerInterceptor();
TableNameHandler tableNameHandler = new TableNameHandler() {
@Override
public String dynamicTableName(String sql, String tableName) {
//tenantProperty中动态标识的是哪些表是分表的表，就在哪些分表的表添加租户的表后缀
//可以将tenantProperty的配置修改为数据库配置也可以，改动更灵活
if (tenantProperty.getShardingTables().contains(tableName)){
return tableName+"_"+tenant_id;
}
return tableName;
}
};
dynamicTableNameInnerInterceptor.setTableNameHandler(tableNameHandler);
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
TenantLineInnerInterceptor
Mybatis自带的无自定义的租户的拦截器会在所有的sql后面加上对应的租户条件，但是我们可以自定义对应的处理租户信息相关的Handler.
public class MultiTenantLineHandler implements TenantLineHandler {
private TenantProperty tenantProperty;
public MultiTenantLineHandler(TenantProperty tenantProperty){
this.tenantProperty =tenantProperty;
}
@Override
public Expression getTenantId() {
//此处直接使用给定租户，实际实现从登录信息中取出
return new StringValue("zhao");
}
@Override
public String getTenantIdColumn() {
//租户列名
return tenantProperty.getTenantColumn();
}
//需要忽略的表的配置
@Override
public boolean ignoreTable(String tableName) {
List<String> ignoreTables = tenantProperty.getIgnoreTables();
if (ignoreTables.contains(tableName)){
return true;
}
return false;
}
// 不处理的非租户列的insert
// @Override
// public boolean ignoreInsert(List<Column> columns, String tenantIdColumn) {
// return TenantLineHandler.super.ignoreInsert(columns, tenantIdColumn);
// }
}
自定义配置类TenantProperty和拦截器整合
配置类
@Data
@Configuration
@ConfigurationProperties(prefix = "tenant")
public class TenantProperty {
private Boolean enable =true;
private String tenantColumn="tenant_id";
private List<String> ignoreTables;
private List<String> shardingTables;
}
配置信息
tenant:
enable: true
ignoreTables:
- sharding
shardingTables:
- sharding
整合拦截器
@Configuration
@MapperScan("com.zhao.sbsc17.dao")
public class TableTenantConfig {
// @Bean
// public MybatisPlusInterceptor mybatisPlusInterceptor(){
// MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
// return interceptor;
// }
private static final String tenant_id = "zhao";
@Autowired
TenantProperty tenantProperty;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor =new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new MultiTenantLineHandler(tenantProperty)));
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor= new DynamicTableNameInnerInterceptor();
TableNameHandler tableNameHandler = new TableNameHandler() {
@Override
public String dynamicTableName(String sql, String tableName) {
if (tenantProperty.getShardingTables().contains(tableName)){
return tableName+"_"+tenant_id;
}
return tableName;
}
};
dynamicTableNameInnerInterceptor.setTableNameHandler(tableNameHandler);
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
}
需要说明的是两个拦截器添加的顺序如果不同会有不同的效果，那么也就需要做对应的处理。当前demo我是达到了刚好我要使用的效果。
效果
新建表tenant和sharding_zhao
查询
@RestController
@RequestMapping("tenant")
public class TenantController {
@Autowired
ShardingMapper shardingMapper;
@Autowired
TenantMapper tenantMapper;
@GetMapping("tenant")
public Tenant master(){
return tenantMapper.selectById(1L);
}
@GetMapping("sharding")
public Sharding sharding(){
return shardingMapper.selectById(1L);
}
}
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@213354a5] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@70191485] will not be managed by Spring
==> Preparing: SELECT id, goods_id, goods_name, num, version, tenant_id FROM tenant WHERE id = ? AND tenant_id = 'zhao'
==> Parameters: 1(Long)
<== Columns: id, goods_id, goods_name, num, version, tenant_id
<== Row: 1, 1, 测试master, 1, 1, zhao
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@213354a5]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7aebce73] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@70191485] will not be managed by Spring
==> Preparing: SELECT id, goods_id, goods_name, num, version FROM sharding_zhao WHERE id = ?
==> Parameters: 1(Long)
<== Columns: id, goods_id, goods_name, num, version
<== Row: 1, 1, 1, 1, 1
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7aebce73]
新增
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@14a9086f] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@48087898] will not be managed by Spring
==> Preparing: INSERT INTO sharding_zhao (id, goods_name, num, version) VALUES (?, ?, ?, ?)
==> Parameters: 2(Long), 测试(String), 1(Long), 1(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@14a9086f]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53a2e535] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@2054630b] will not be managed by Spring
==> Preparing: INSERT INTO tenant (id, goods_name, num, version, tenant_id) VALUES (?, ?, ?, ?, 'zhao')
==> Parameters: 2(Long), 测试(String), 1(Long), 1(Integer)
<== Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53a2e535]
本文由 mdnice 多平台发布
