存储租户id
/**
* @author 18930
*/
public class TenantContextHolder {
private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();
/**
* 设置租户标识
* @param tenantId
*/
public static void setTenantId(String tenantId) {
CONTEXT.set(tenantId);
}
/**
* 获取租户标识
* @return
*/
public static String getTenantId() {
return CONTEXT.get();
}
/**
* 清除租户标识
*/
public static void clear() {
CONTEXT.remove();
}
}
mybatisplus
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;
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 org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import java.util.ArrayList;
import java.util.List;
@Configuration
@MapperScan({"Mapper权路径"})
public class MybatisPlusConfig {
/**
* 分页插件，自动识别数据库类型
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
List<ISqlParser> sqlParserList = new ArrayList<>();
TenantSqlParser tenantSqlParser = new TenantSqlParser();
tenantSqlParser.setTenantHandler(new TenantHandler() {
@Override
public Expression getTenantId(boolean where) {
// 从TenantContextHolder获取当前租户标识，通过解析器注入到SQL中
String tenantId = TenantContextHolder.getTenantId();
if(StrUtil.isBlank(tenantId)){
throw new UnsupportedOperationException("租户系统异常");
}
return new LongValue(tenantId);
}
@Override
public String getTenantIdColumn() {
return "tenant_id";
}
@Override
public boolean doTableFilter(String tableName) {
// 获取用户登录状态
Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
// 获取租户id
String tenantId = TenantContextHolder.getTenantId();
if(authentication==null || tenantId==null){
return true;
}
// 这里可以判断是否过滤表
for (int i = 0; i < TENANT_UNIFY_MENU.length; i++) {
if(TENANT_UNIFY_MENU[i].equals(tableName)){
return true;
}
}
return false;
}
});
sqlParserList.add(tenantSqlParser);
paginationInterceptor.setSqlParserList(sqlParserList);
return paginationInterceptor;
}
/*@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}*/
}
JPA
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.Token;
import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
import com.alibaba.druid.util.JdbcUtils;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.resource.jdbc.spi.StatementInspector;
import org.junit.Test;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
@Slf4j
@Component
public class JpaInterceptor implements StatementInspector {
@Override
public String inspect(String sql) {
//使用mysql解析
MySqlStatementParser sqlStatementParser = new MySqlStatementParser(sql);
Token token = sqlStatementParser.getExprParser().getLexer().token();
// 识别查询sql
if (!"SELECT".equals(token.name)) {
return sql;
}
//解析select查询
SQLSelectStatement sqlStatement = (SQLSelectStatement) sqlStatementParser.parseSelect();
SQLSelect sqlSelect = sqlStatement.getSelect();
//获取sql查询块
SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock) sqlSelect.getQuery();
StringBuffer out = new StringBuffer();
//创建sql解析的标准化输出
SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out, null, JdbcUtils.MYSQL);
//解析from
out.delete(0, out.length());
sqlSelectQuery.getFrom().accept(sqlastOutputVisitor);
// 获取用户登录状态
Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
// 获取租户id
String tenantId = TenantContextHolder.getTenantId();
if(authentication==null || tenantId==null){
return sql;
}
// 过滤表
for (int i = 0; i < TENANT_UNIFY_MENU.length; i++) {
if (out.indexOf(TENANT_UNIFY_MENU[i]) != -1) {
System.err.println("FROM---->");
System.err.println(out);
System.err.println("<----FROM");
return sql;
}
}
try {
String appendSql = "tenant_id = " + tenantId;
String[] where_s = sql.split("where ");
sql = where_s[0];
for (int i = 1; i < where_s.length; i++) {
sql += "WHERE " + appendSql + " AND " + where_s[i];
}
} catch (Exception e) {
System.err.println("报错---->");
System.err.println(sql);
System.err.println("<----报错");
e.printStackTrace();
}
/*//使用mysql解析
//获取sql查询块
SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock) sqlSelect.getQuery();
StringBuffer out = new StringBuffer();
//创建sql解析的标准化输出
SQLASTOutputVisitor sqlastOutputVisitor = SQLUtils.createFormatOutputVisitor(out, null, JdbcUtils.MYSQL);
//解析where
out.delete(0, out.length());
String where="";
sqlSelectQuery.getWhere().accept(sqlastOutputVisitor);
if(StrUtil.isNotBlank(out)){
where=" AND" + out;
}else{
where="tenant_id = (SELECT tenant_id FROM t_user WHERE username='"+ username +"')";
}*/
// SQLUtils.formatMySql(sql,)
return sql;
}
@Test
public void test() {
String str = "SELECT" +
" part0_.id AS id1_44_," +
" part0_.create_by AS create_b2_44_," +
" part0_.create_time AS create_t3_44_," +
" part0_.del_flag AS del_flag4_44_," +
" part0_.tenant_id AS tenant_i5_44_," +
" part0_.update_by AS update_b6_44_," +
" part0_.update_time AS update_t7_44_," +
" part0_.CODE AS code8_44_," +
" part0_.img AS img9_44_," +
" part0_.in_drawing AS in_draw10_44_," +
" part0_.is_parent AS is_pare11_44_," +
" part0_.node_nature AS node_na12_44_," +
" part0_.parent_id AS parent_13_44_," +
" part0_.path_code AS path_co14_44_," +
" part0_.sort_order AS sort_or15_44_," +
" part0_.STATUS AS status16_44_," +
" part0_.title AS title17_44_ " +
"FROM" +
" t_part part0_ " +
"WHERE" +
" part0_.parent_id IS NULL " +
"AND tenant_id = (SELECT tenant_id FROM t_user WHERE username='zhouyi')" +
"ORDER BY" +
" part0_.sort_order ASC";
String[] where_s = str.split("WHERE ");
int i = str.indexOf("(");
System.out.println(i);
int j = str.indexOf(")");
System.out.println(j);
String substring = str.substring(i + 1, j);
System.out.println(substring);
// int tenant_id = substring.indexOf("tenant_id");
// System.out.println(tenant_id);
// String substring1 = substring.substring(tenant_id, tenant_id + 9);
// System.out.println(substring1);
System.out.println("----------------------------------------");
String[] split = substring.split(",");
int flagInt = -1;
for (int i1 = 0; i1 < split.length; i1++) {
System.out.println(split[i1]);
if (split[i1].equals("tenant_id")) {
flagInt = i1;
}
}
System.out.println(flagInt);
System.out.println("----------------------------------------");
int i2 = str.indexOf("(", str.indexOf("(", 2) + 1);
System.out.println(i2);
int j2 = str.indexOf(")", str.indexOf(")", 2) + 1);
System.out.println(j2);
String substring2 = str.substring(i2 + 1, j2);
System.out.println(substring2);
String[] split1 = substring2.split("/?");
for (int i1 = 0; i1 < split1.length; i1++) {
System.out.println(split1[i1]);
}
}
}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。
