mybatis 多租户，新增时加注入字段，查改删添加where条件
多租户字段隔离级别 ，新增时加注入字段，查改删添加where条件。 如果是表隔离，或数据库隔离，很好改，在SqlConditionHelper收集表名，然后字符串替换表名即可
定义多租户注解 ， 添加多注解的contrler 或者接口 开启多租户模式
/**
* 开启 地区 多租户
* @author xiaopeng
* @date 2021-06-09
*/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSpace {
}
上下文参数传递工具类
public class ContextHolderUtil {
/**
* 获取request
* @return
*/
public static HttpServletRequest getRequest() {
var obj = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
return obj == null ? null : obj.getRequest();
}
/**
* 获取httpSession
* @return
*/
public static HttpSession getSession() {
var request = getRequest();
return request == null ? null : request.getSession();
}
/**
* 获取request中的值
* @param key request的key
* @return
*/
public static Object getRequestAttribute(String key) {
var request = getRequest();
return request == null ? null : request.getAttribute(key);
}
/**
* 获取 Token 中的用户 id
* @return
*/
public static Integer getAuthedUserId() {
var requestArr = getRequestAttribute("id");
return Integer.parseInt(requestArr.toString());
}
//获取角色id
public static Integer getAuthedUserRoleId() {
return Integer.parseInt(String.valueOf(getRequestAttribute("roleId")));
}
//获取角色权重
public static Integer getAuthedUserRoleWeight() {
return Integer.parseInt(String.valueOf(getRequestAttribute("roleWeight")));
}
//获取用户信息
public static UserInfo getAuthedUserInfo() {
return (UserInfo) getRequestAttribute("userInfo");
}
private static String openDataSpace = "open";
//开启 地区 多租户模式
public static void openDataSpace(){
HttpServletRequest request = getRequest();
request.setAttribute("openDataSpace", "open");
}
//判断是否开启 地区 多租户模式
public static boolean isDataSpace(){
HttpServletRequest request = getRequest();
return openDataSpace.equals(request.getAttribute("openDataSpace"));
}
//获取前端传过来的 地区id
public static String getDataSpaceId(){
HttpServletRequest request = getRequest();
String dataSpaceId = request.getHeader("SpaceId");
if (StringUtil.isEmpty(dataSpaceId)){
//请求头拿不到的话， 去url参数那里试一下
dataSpaceId = request.getParameter("SpaceId");
}
//未选择地区id 强制报错退出
CommonUtil.expressionThrowResponseCodeAndSetAttribute(StringUtil.isEmpty(dataSpaceId), ResponseCode.NOT_REGION);
return dataSpaceId;
}
}
注解切面， 将注解的信息保存进 上下文参数
@Component
@Aspect
@Slf4j
public class DataSpaceAspect {
@Pointcut("@annotation(com.xxxx.xxxx.common.annotation.DataSpace)")
public void pointcut(){
}
@Pointcut("execution(* com.xxxx.xxxx..*Controller.*(..))")
public void pointcutClass(){
}
@Before("pointcut()")
public void openDataSpace(){
ContextHolderUtil.openDataSpace();
}
/**
* 拦截所有control接口
*/
@Around("pointcutClass()")
public Object openDataSpace(ProceedingJoinPoint joinPoint) throws Throwable {
Class<?> clazz = joinPoint.getTarget().getClass();
//如果类上面带 DataSpace 注解，开启多租户模式
DataSpace annotation = clazz.getAnnotation(DataSpace.class);
if (annotation != null) {
ContextHolderUtil.openDataSpace();
}
return joinPoint.proceed();
}
}
multi-tenant.properties 配置文件 配置多租户字段，以及表（逗号分割）
tenant.id-field=region_id
tenant.table=tfmes_sales_order,tfmes_work_procedure_data
mybatis SqlSessionFactory 添加自己写的mybatis多租户拦截器
@Configuration
@PropertySource("classpath:multi-tenant.properties")
public class TfmesMybatisConfig {
/**
* 多租户字段名称
*/
@Value("${tenant.id-field}")
private String tenantIdField;
/**
* 需要识别多租户字段的表名称列表
*/
@Value("${tenant.table}")
private String tableSet;
@Bean
public String interceptorTfmes(SqlSessionFactory sqlSessionFactory) {
//如果多数据源 配置了多个sqlSessionFactory public String interceptorTfmes(@Qualifier("xxxxSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
MultiTenantPlugin multiTenantPlugin = new MultiTenantPlugin();
Properties properties = new Properties(4);
properties.put("tenantIdField", tenantIdField);
properties.put("tableNames", tableSet);
multiTenantPlugin.setProperties(properties);
// 可添加多个mybatis拦截器
sqlSessionFactory.getConfiguration().addInterceptor(multiTenantPlugin);
return "interceptorTfmes";
}
}
MultiTenantPlugin mybatis拦截器
@Slf4j
@Intercepts({@Signature(
type = Executor.class,
method = "update",
args = {MappedStatement.class, Object.class}
), @Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
//@Component 不要这个 MybatisConfig.java 已经注入了
public class MultiTenantPlugin implements Interceptor {
/**
* 多租户字段名称
*/
private String tenantIdField;
/**
* 需要识别多租户字段的表名称列表
*/
private Set<String> tableSet;
private SqlConditionHelper conditionHelper;
@Override
public Object intercept(Invocation invocation) throws Throwable {
//如果没有 地区 多租户 不生成新sql
if (!ContextHolderUtil.isDataSpace()){
return invocation.proceed();
}
//获取前端传过来的 地区id
String tenantFieldValue = ContextHolderUtil.getDataSpaceId();
//@Signature 上面拦截的方法 的参数
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameter = args[1];
//这个巨重要 不然会 新sql成功生成， 但mybatis运行的是旧sql
BoundSql boundSql;
if(args.length == 6){
// 6 个参数时
boundSql = (BoundSql) args[5];
} else {
boundSql = ms.getBoundSql(parameter);
}
String processSql = boundSql.getSql();
log.info("替换前SQL：{}", processSql);
//新sql
String newSql = processSql;
//如果是新增， 新增字段里加上 多租户保存
if (SqlCommandType.INSERT == ms.getSqlCommandType()) {
Statement statement = CCJSqlParserUtil.parse(processSql);
Insert insertStatement = (Insert) statement;
String tableName = insertStatement.getTable().getName();
// 去除反引号
tableName = tableName.replace("`", "");
if (tableSet != null && tableSet.contains(tableName)){
List<Column> columns = insertStatement.getColumns();
columns.add(new Column(tenantIdField));
insertStatement.setColumns(columns);
//单个插入 或者 批量插入
ItemsList itemsList = insertStatement.getItemsList();
if (itemsList instanceof ExpressionList){
ExpressionList expressionList = (ExpressionList) itemsList;
List<Expression> list = new ArrayList<>();
list.addAll(expressionList.getExpressions());
list.add(CCJSqlParserUtil.parseExpression(tenantFieldValue));
expressionList.setExpressions(list);
insertStatement.setItemsList(expressionList);
} else if (itemsList instanceof MultiExpressionList){
MultiExpressionList multiExpressionList = (MultiExpressionList) itemsList;
List<ExpressionList> expressionListList = multiExpressionList.getExpressionLists();
for (ExpressionList expressionList : expressionListList) {
List<Expression> list = new ArrayList<>();
list.addAll(expressionList.getExpressions());
list.add(CCJSqlParserUtil.parseExpression(tenantFieldValue));
expressionList.setExpressions(list);
}
insertStatement.setItemsList(multiExpressionList);
}
newSql = insertStatement.toString();
}
} else {
//查询、修改、删除 where条件添加多租户
newSql = addTenantCondition(processSql, tenantFieldValue);
}
//通过反射修改sql字段
MetaObject boundSqlMeta = MetaObject.forObject(boundSql, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
// 把新sql设置到boundSql
boundSqlMeta.setValue("sql", newSql);
// 重新new一个查询语句对象
BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql,
boundSql.getParameterMappings(), boundSql.getParameterObject());
// 把新的查询放到statement里
MappedStatement newMs = newMappedStatement(ms, new BoundSqlSqlSource(newBoundSql));
for (ParameterMapping mapping : boundSql.getParameterMappings()) {
String prop = mapping.getProperty();
if (boundSql.hasAdditionalParameter(prop)) {
newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}
args[0] = newMs;
log.info("替换后SQL：{}", newSql);
return invocation.proceed();
}
@Override
public void setProperties(Properties properties) {
tenantIdField = properties.getProperty("tenantIdField");
if (StringUtils.isBlank(tenantIdField)) {
throw new IllegalArgumentException("MultiTenantPlugin need tenantIdField property value");
}
String tableNames = properties.getProperty("tableNames");
if (!StringUtils.isBlank(tableNames)) {
tableSet = new HashSet<>(Arrays.asList(StringUtils.split(tableNames, ",")));
}
// 多租户条件字段决策器
TableFieldConditionDecision conditionDecision = new TableFieldConditionDecision() {
@Override
public boolean isAllowNullValue() {
return false;
}
@Override
public boolean adjudge(String tableName, String fieldName) {
// 去除反引号
tableName = tableName.replace("`", "");
return tableSet != null && tableSet.contains(tableName);
}
};
conditionHelper = new SqlConditionHelper(conditionDecision);
}
/**
* 定义一个内部辅助类，作用是包装 SQL
*/
static class BoundSqlSqlSource implements SqlSource {
private final BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
/**
* 根据原MappedStatement更新SqlSource生成新MappedStatement
*
* @param ms MappedStatement
* @param newSqlSource 新SqlSource
* @return
*/
private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new
MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
builder.keyProperty(ms.getKeyProperties()[0]);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
/**
* 给sql语句where添加租户id过滤条件
*
* @param sql 要添加过滤条件的sql语句
* @param tenantId 当前的租户id
* @return 添加条件后的sql语句
*/
private String addTenantCondition(String sql, String tenantId) {
if (StringUtils.isBlank(sql) || StringUtils.isBlank(tenantIdField)) {
return sql;
}
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
if (statementList.size() == 0) {
return sql;
}
SQLStatement sqlStatement = statementList.get(0);
conditionHelper.addStatementCondition(sqlStatement, tenantIdField, tenantId);
return SQLUtils.toSQLString(statementList, JdbcConstants.MYSQL);
}
}
sql where 条件 处理工具类
public class SqlConditionHelper {
private final TableFieldConditionDecision conditionDecision;
public SqlConditionHelper(TableFieldConditionDecision conditionDecision) {
this.conditionDecision = conditionDecision;
}
/**
* 为sql语句添加指定where条件
*
* @param sqlStatement
* @param fieldName
* @param fieldValue
*/
public void addStatementCondition(SQLStatement sqlStatement, String fieldName, String fieldValue) {
if (sqlStatement instanceof SQLSelectStatement) {
SQLSelectQueryBlock queryObject = (SQLSelectQueryBlock) ((SQLSelectStatement) sqlStatement).getSelect().getQuery();
addSelectStatementCondition(queryObject, queryObject.getFrom(), fieldName, fieldValue);
} else if (sqlStatement instanceof SQLUpdateStatement) {
SQLUpdateStatement updateStatement = (SQLUpdateStatement) sqlStatement;
addUpdateStatementCondition(updateStatement, fieldName, fieldValue);
} else if (sqlStatement instanceof SQLDeleteStatement) {
SQLDeleteStatement deleteStatement = (SQLDeleteStatement) sqlStatement;
addDeleteStatementCondition(deleteStatement, fieldName, fieldValue);
} else if (sqlStatement instanceof SQLInsertStatement) {
SQLInsertStatement insertStatement = (SQLInsertStatement) sqlStatement;
addInsertStatementCondition(insertStatement, fieldName, fieldValue);
}
}
/**
* 为insert语句添加where条件
*
* @param insertStatement
* @param fieldName
* @param fieldValue
*/
private void addInsertStatementCondition(SQLInsertStatement insertStatement, String fieldName, String fieldValue) {
if (insertStatement != null) {
SQLSelect sqlSelect = insertStatement.getQuery();
if (sqlSelect != null) {
SQLSelectQueryBlock selectQueryBlock = (SQLSelectQueryBlock) sqlSelect.getQuery();
addSelectStatementCondition(selectQueryBlock, selectQueryBlock.getFrom(), fieldName, fieldValue);
}
}
}
/**
* 为delete语句添加where条件
*
* @param deleteStatement
* @param fieldName
* @param fieldValue
*/
private void addDeleteStatementCondition(SQLDeleteStatement deleteStatement, String fieldName, String fieldValue) {
SQLExpr where = deleteStatement.getWhere();
//添加子查询中的where条件
addSQLExprCondition(where, fieldName, fieldValue);
SQLExpr newCondition = newEqualityCondition(deleteStatement.getTableName().getSimpleName(),
deleteStatement.getTableSource().getAlias(), fieldName, fieldValue, where);
deleteStatement.setWhere(newCondition);
}
/**
* where中添加指定筛选条件
*
* @param where 源where条件
* @param fieldName
* @param fieldValue
*/
private void addSQLExprCondition(SQLExpr where, String fieldName, String fieldValue) {
if (where instanceof SQLInSubQueryExpr) {
SQLInSubQueryExpr inWhere = (SQLInSubQueryExpr) where;
SQLSelect subSelectObject = inWhere.getSubQuery();
SQLSelectQueryBlock subQueryObject = (SQLSelectQueryBlock) subSelectObject.getQuery();
addSelectStatementCondition(subQueryObject, subQueryObject.getFrom(), fieldName, fieldValue);
} else if (where instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr opExpr = (SQLBinaryOpExpr) where;
SQLExpr left = opExpr.getLeft();
SQLExpr right = opExpr.getRight();
addSQLExprCondition(left, fieldName, fieldValue);
addSQLExprCondition(right, fieldName, fieldValue);
} else if (where instanceof SQLQueryExpr) {
SQLSelectQueryBlock selectQueryBlock = (SQLSelectQueryBlock) (((SQLQueryExpr) where).getSubQuery()).getQuery();
addSelectStatementCondition(selectQueryBlock, selectQueryBlock.getFrom(), fieldName, fieldValue);
}
}
/**
* 为update语句添加where条件
*
* @param updateStatement
* @param fieldName
* @param fieldValue
*/
private void addUpdateStatementCondition(SQLUpdateStatement updateStatement, String fieldName, String fieldValue) {
SQLExpr where = updateStatement.getWhere();
//添加子查询中的where条件
addSQLExprCondition(where, fieldName, fieldValue);
SQLExpr newCondition = newEqualityCondition(updateStatement.getTableName().getSimpleName(),
updateStatement.getTableSource().getAlias(), fieldName, fieldValue, where);
updateStatement.setWhere(newCondition);
}
/**
* 给一个查询对象添加一个where条件
*
* @param queryObject
* @param fieldName
* @param fieldValue
*/
private void addSelectStatementCondition(SQLSelectQueryBlock queryObject, SQLTableSource from, String fieldName, String fieldValue) {
if (StringUtils.isBlank(fieldName) || from == null || queryObject == null) {
return;
}
SQLExpr originCondition = queryObject.getWhere();
// 添加子查询中的where条件
addSQLExprCondition(originCondition, fieldName, fieldValue);
if (from instanceof SQLExprTableSource) {
// TODO 对于JOIN_TABLE支持有问题，待优化
String tableName = ((SQLIdentifierExpr) ((SQLExprTableSource) from).getExpr()).getName();
String alias = from.getAlias();
SQLExpr newCondition = newEqualityCondition(tableName, alias, fieldName, fieldValue, originCondition);
queryObject.setWhere(newCondition);
} else if (from instanceof SQLJoinTableSource) {
SQLJoinTableSource joinObject = (SQLJoinTableSource) from;
SQLTableSource left = joinObject.getLeft();
SQLTableSource right = joinObject.getRight();
addSelectStatementCondition(queryObject, left, fieldName, fieldValue);
addSelectStatementCondition(queryObject, right, fieldName, fieldValue);
} else if (from instanceof SQLSubqueryTableSource) {
SQLSelect subSelectObject = ((SQLSubqueryTableSource) from).getSelect();
SQLSelectQueryBlock subQueryObject = (SQLSelectQueryBlock) subSelectObject.getQuery();
addSelectStatementCondition(subQueryObject, subQueryObject.getFrom(), fieldName, fieldValue);
} else {
throw new NotImplementedException("未处理的异常");
}
}
/**
* 根据原来的condition创建一个新的condition
*
* @param tableName 表名称
* @param tableAlias 表别名
* @param fieldName 字段名
* @param fieldValue 字段值
* @param originCondition 原始条件
* @return
*/
private SQLExpr newEqualityCondition(String tableName, String tableAlias, String fieldName, String fieldValue, SQLExpr originCondition) {
//如果不需要设置条件
if (!conditionDecision.adjudge(tableName, fieldName)) {
return originCondition;
}
//如果条件字段不允许为空
if (fieldValue == null && !conditionDecision.isAllowNullValue()) {
return originCondition;
}
String filedName = StringUtils.isBlank(tableAlias) ? tableName + "." + fieldName : tableAlias + "." + fieldName;
// 生成查询条件使用IN进行查询
// var condition = new SQLInListExpr(new SQLIdentifierExpr(filedName), false);
// condition.addTarget(new SQLVariantRefExpr(fieldValue));
//生成查询条件使用 = 进行查询
var condition = new SQLBinaryOpExpr(
new SQLIdentifierExpr(filedName),
SQLBinaryOperator.Equality,
new SQLVariantRefExpr(fieldValue)
);
return SQLUtils.buildCondition(SQLBinaryOperator.BooleanAnd, condition, false, originCondition);
}
public static void main(String[] args) {
// String sql = "select * from user s ";
// String sql = "select * from user s where s.name='333'";
// String sql = "select * from (select * from tab t where id = 2 and name = 'wenshao') s where s.name='333'";
// String sql="select u.*,g.name from user u join user_group g on u.groupId=g.groupId where u.name='123'";
// String sql = "update user set name=? where id =(select id from user s)";
// String sql = "delete from user where id = ( select id from user s )";
String sql = "insert into user (id,name) select g.id,g.name from user_group g where id=1";
// String sql = "SELECT id, pw_id, warehouse_top, warehouse_level, warehouse_name , is_default, can_to, disabled, data_space_id, created_at , created_by, updated_at, updated_by FROM erp_warehouses_warehouse WHERE disabled = 0 AND is_default = 1 AND warehouse_top = ( SELECT id FROM erp_warehouses_warehouse WHERE is_default = 1 AND disabled = 0 )";
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
SQLStatement sqlStatement = statementList.get(0);
SQLInsertStatement sqlSelectStatement = (SQLInsertStatement) sqlStatement;
//决策器定义
SqlConditionHelper helper = new SqlConditionHelper(new TableFieldConditionDecision() {
@Override
public boolean adjudge(String tableName, String fieldName) {
return true;
}
@Override
public boolean isAllowNullValue() {
return false;
}
});
//添加多租户条件，domain是字段ignc，yay是筛选值
helper.addStatementCondition(sqlStatement, "data_space_id", "1");
System.out.println("源sql：" + sql);
System.out.println("修改后sql:" + SQLUtils.toSQLString(statementList, JdbcConstants.MYSQL));
}
}
版权声明：本文为weixin_41423378原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。