java mysql MyBatis Plus 拦截器实现数据权限控制

一、介绍

上篇文章介绍的MyBatis Plus 插件实际上就是用拦截器实现的，MyBatis Plus拦截器对MyBatis的拦截器进行了包装处理，操作起来更加方便

二、自定义拦截器

2.1、InnerInterceptor

MyBatis Plus提供的InnerInterceptor接口提供了如下方法，主要包括：在查询之前执行，在更新之前执行，在SQL准备之前执行

2.2、编写简易拦截器

package com.xx.config;

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;

import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;

import lombok.extern.slf4j.Slf4j;

import net.sf.jsqlparser.expression.StringValue;

import net.sf.jsqlparser.expression.operators.conditional.AndExpression;

import net.sf.jsqlparser.expression.operators.relational.EqualsTo;

import net.sf.jsqlparser.expression.operators.relational.ExpressionList;

import net.sf.jsqlparser.expression.operators.relational.ItemsList;

import net.sf.jsqlparser.schema.Column;

import net.sf.jsqlparser.statement.delete.Delete;

import net.sf.jsqlparser.statement.insert.Insert;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.Select;

import net.sf.jsqlparser.statement.select.SelectBody;

import net.sf.jsqlparser.statement.update.Update;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.mapping.MappedStatement;

import org.springframework.stereotype.Component;

import java.sql.Connection;

/**

* @author aqi

* @date 2023/5/17 15:07

*/

@Slf4j

@Component

public class TestInterceptor extends JsqlParserSupport implements InnerInterceptor {

@Override

public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {

// 这里固定这么写就可以了

PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);

MappedStatement ms = mpSh.mappedStatement();

if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {

return;

}

PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();

mpBs.sql(parserMulti(mpBs.sql(), null));

}

/**

* 该方法由JsqlParserSupport提供,主要用于通过API的方式操作SQL

* 思路:通过API构建出新的条件,并将新的条件和之前的条件拼接在一起

*/

@Override

protected void processSelect(Select select, int index, String sql, Object obj) {

// 解析SQL

SelectBody selectBody = select.getSelectBody();

PlainSelect plainSelect = (PlainSelect) selectBody;

// 构建eq对象

EqualsTo equalsTo = new EqualsTo(new Column("name"), new StringValue("tom"));

// 将原来的条件和新构建的条件合在一起

AndExpression andExpression = new AndExpression(plainSelect.getWhere(), equalsTo);

// 重新封装where条件

plainSelect.setWhere(andExpression);

}

@Override

protected void processInsert(Insert insert, int index, String sql, Object obj) {

insert.getColumns().add(new Column("name"));

((ExpressionList) insert.getItemsList()).getExpressions().add(new StringValue("tom"));

}

@Override

protected void processUpdate(Update update, int index, String sql, Object obj) {

update.addUpdateSet(new Column("name"), new StringValue("tom"));

}

@Override

protected void processDelete(Delete delete, int index, String sql, Object obj) {

// 删除新增条件和查询一样,不做演示

}

}

2.3、将拦截器添加到MyBatis Plus拦截器中

package com.xx.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

/**

* @author aqi

* @date 2023/5/15 14:05

*/

@Configuration

public class MybatisPlusConfig {

@Bean

public MybatisPlusInterceptor mybatisPlusInterceptor() {

// 初始化Mybatis Plus拦截器

MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

interceptor.addInnerInterceptor(new TestInterceptor());

return interceptor;

}

}

2.4、编写测试用例

@Test

void save() {

AirlinesInfo airlinesInfo = new AirlinesInfo();

airlinesInfo.setInfo("remark");

airlinesInfoService.save(airlinesInfo);

}

@Test

void update() {

AirlinesInfo airlinesInfo = new AirlinesInfo();

airlinesInfo.setId(1L);

airlinesInfo.setInfo("remark, remark");

airlinesInfoService.updateById(airlinesInfo);

}

@Test

void select() {

airlinesInfoService.list();

}

2.5、执行结果

三、自定义拦截器实现数据权限控制

3.1、编写拦截器

package com.xx.config;

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;

import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;

import com.xx.entity.Permission;

import com.xx.utils.ExpressionUtils;

import com.xx.utils.UserUtils;

import lombok.extern.slf4j.Slf4j;

import net.sf.jsqlparser.expression.Expression;

import net.sf.jsqlparser.statement.select.PlainSelect;

import net.sf.jsqlparser.statement.select.Select;

import net.sf.jsqlparser.statement.select.SelectBody;

import org.apache.ibatis.executor.statement.StatementHandler;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.mapping.SqlCommandType;

import org.springframework.stereotype.Component;

import java.sql.Connection;

/**

* @author xiaxing

*/

@Slf4j

@Component

public class DataScopeInterceptor extends JsqlParserSupport implements InnerInterceptor {

@Override

public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {

log.info("[DataScopeInterceptor]beforePrepare...");

PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);

MappedStatement ms = mpSh.mappedStatement();

SqlCommandType sct = ms.getSqlCommandType();

if (sct == SqlCommandType.INSERT || sct == SqlCommandType.SELECT) {

if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {

return;

}

PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();

mpBs.sql(parserMulti(mpBs.sql(), null));

}

}

/**

* 查询

*/

@Override

protected void processSelect(Select select, int index, String sql, Object obj) {

SelectBody selectBody = select.getSelectBody();

PlainSelect plainSelect = (PlainSelect) selectBody;

// 获取表名/别名(如果是关联查询是取第一个join左侧的表名/别名)

String tableName = ExpressionUtils.getTableName(plainSelect);

// 构建用户权限控制条件

Expression userPermissionExpression = this.buildUserPermissionSql(tableName);

if (null != userPermissionExpression) {

// 将sql原本就有得where条件和新构建出来的条件拼接起来

plainSelect.setWhere(ExpressionUtils.appendExpression(plainSelect.getWhere(), userPermissionExpression));

}

}

/**

* 构建用户权限控制条件

* @param tableName 表名/别名(join查询左侧表名)

*/

private Expression buildUserPermissionSql(String tableName) {

// 获取当前用户信息(这里的数据都是模拟的,实际上可能得从缓存或者session中获取)

Permission permission = UserUtils.getUserPermission();

return null != permission ? ExpressionUtils.buildInSql(tableName + "." + permission.getField(), permission.getValue()) : null;

}

}

3.2、编写构建SQL工具类

package com.xx.utils;

import net.sf.jsqlparser.expression.Alias;

import net.sf.jsqlparser.expression.Expression;

import net.sf.jsqlparser.expression.StringValue;

import net.sf.jsqlparser.expression.operators.conditional.AndExpression;

import net.sf.jsqlparser.expression.operators.relational.EqualsTo;

import net.sf.jsqlparser.expression.operators.relational.ExpressionList;

import net.sf.jsqlparser.expression.operators.relational.InExpression;

import net.sf.jsqlparser.expression.operators.relational.ItemsList;

import net.sf.jsqlparser.schema.Column;

import net.sf.jsqlparser.schema.Table;

import net.sf.jsqlparser.statement.select.PlainSelect;

import java.util.Set;

import java.util.stream.Collectors;

/**

* @author aqi

* @date 2023/5/17 10:16

* @describe JSqlParser工具类,用于通过API的方式操作SQL语句

*/

public class ExpressionUtils {

/**

* 构建in sql

* @param columnName 字段名称

* @param params 字段值

* @return InExpression

*/

public static InExpression buildInSql(String columnName, Set params) {

// 把集合转变为JSQLParser需要的元素列表

ItemsList itemsList = new ExpressionList(params.stream().map(StringValue::new).collect(Collectors.toList()));

// 创建IN表达式对象，传入列名及IN范围列表

return new InExpression(new Column(columnName), itemsList);

}

/**

* 构建eq sql

* @param columnName 字段名称

* @param value 字段值

* @return EqualsTo

*/

public static EqualsTo buildEq(String columnName, String value) {

return new EqualsTo(new Column(columnName), new StringValue(value));

}

/**

* 获取表名/别名

* @param plainSelect plainSelect

* @return 表名/别名

*/

public static String getTableName(PlainSelect plainSelect) {

// 获取别名

Table table= (Table) plainSelect.getFromItem();

Alias alias = table.getAlias();

return null == alias ? table.getName() : alias.getName();

}

/**

* 将2个where条件拼接到一起

* @param where 条件

* @param appendExpression 待拼接条件

* @return Expression

*/

public static Expression appendExpression(Expression where, Expression appendExpression) {

return null == where ? appendExpression : new AndExpression(where, appendExpression);

}

}

3.3、模拟用户信息工具类

package com.xx.utils;

import com.xx.config.Globle;

import com.xx.entity.Permission;

import com.xx.entity.User;

import lombok.extern.slf4j.Slf4j;

import org.springframework.web.context.request.RequestAttributes;

import org.springframework.web.context.request.RequestContextHolder;

import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;

import java.util.*;

/**

* @author aqi

* @date 2023/5/17 14:20

*/

@Slf4j

public class UserUtils {

public static User currentUser;

static {

// 构建测试数据

List permissionList = new ArrayList<>();

// demo/test接口权限

Permission permission = new Permission();

permission.setField("id");

permission.setUri("/demo/test");

Set set = new HashSet<>();

set.add("1");

set.add("2");

set.add("3");

permission.setValue(set);

permissionList.add(permission);

// demo/test1接口权限

Permission permission1 = new Permission();

permission1.setField("id");

permission1.setUri("/demo/test1");

Set set1 = new HashSet<>();

set1.add("4");

set1.add("5");

set1.add("6");

permission1.setValue(set1);

permissionList.add(permission1);

User user = new User();

user.setPermissionList(permissionList);

user.setTenantId("1");

currentUser = user;

}

public static Permission getUserPermission() {

User currentUser = Globle.currentUser;

String uri = UserUtils.getUri();

List permissionList = currentUser.getPermissionList();

return permissionList.stream().filter(e -> Objects.equals(e.getUri(), uri)).findFirst().orElse(null);

}

/**

* 获取本次请求的uri

* @return uri

*/

private static String getUri() {

// 获取此次请求的uri

String uri = "";

RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();

if (null != requestAttributes) {

HttpServletRequest request = ((ServletRequestAttributes) requestAttributes).getRequest();

uri = request.getRequestURI();

}

log.info("[DataScopeInterceptor]此次请求uri:{}", uri);

return uri;

}

}

3.4、将拦截器添加到MyBatis Plus蓝机器中

package com.xx.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

/**

* @author aqi

* @date 2023/5/15 14:05

*/

@Configuration

public class MybatisPlusConfig {

@Bean

public MybatisPlusInterceptor mybatisPlusInterceptor() {

// 初始化Mybatis Plus拦截器

MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

interceptor.addInnerInterceptor(new DataScopeInterceptor());

return interceptor;

}

}

3.5、测试

package com.xx.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import com.github.pagehelper.PageHelper;

import com.xx.entity.AirlinesInfo;

import com.xx.service.AirlinesInfoService;

import lombok.extern.slf4j.Slf4j;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

/**

* @author aqi

* @date 2023/5/18 11:01

*/

@Slf4j

@RestController

@RequestMapping("/demo")

public class DemoController {

@Resource

private AirlinesInfoService airlinesInfoService;

@GetMapping("/test")

public void test() {

log.info("进入test接口,测试权限控制在基础的sql语句是否能生效");

airlinesInfoService.list();

// 执行结果:(SELECT * FROM airlines_info WHERE state = 0 AND airlines_info.id IN ('1', '2', '3'))

}

@GetMapping("/test1")

public void test1() {

log.info("进入test1接口,测试权限控制在使用MyBatis Plus 的分页插件之后能否生效");

Page page = new Page<>(1, 5);

airlinesInfoService.page(page, new QueryWrapper().eq("name", "tom"));

// 执行结果:(SELECT * FROM airlines_info WHERE state = 0 AND (name = ?) AND airlines_info.id IN ('4', '5', '6') LIMIT ?)

}

@GetMapping("/test2")

public void test2() {

log.info("进入test2接口,测试权限控制在使用PageHelper之后能否生效");

PageHelper.startPage(1, 5);

airlinesInfoService.list(new LambdaQueryWrapper().eq(AirlinesInfo::getName, "tom"));

// 执行结果:(SELECT * FROM airlines_info WHERE state = 0 AND (name = ?) AND airlines_info.id IN ('7', '8', '9') LIMIT ?)

}

@GetMapping("/test3")

public void test3() {

log.info("进入test3接口,测试权限控制在使用自定义复杂关联查询之后能否生效");

airlinesInfoService.innerSql();

// 原始SQL:(select * from airlines_info t1 INNER JOIN t_config on t1.id = t_config.id where t1.name = 'tom' and t_config.name = 'jack' limit 5)

// 执行结果:(SELECT * FROM airlines_info t1 INNER JOIN t_config ON t1.id = t_config.id WHERE t1.name = 'tom' AND t_config.name = 'jack' AND t1.id IN ('11', '12', '10') LIMIT 5)

}

@GetMapping("/test4")

public void test4() {

log.info("进入test4接口,测试该接口没有设计权限限制是否可以不生效");

airlinesInfoService.list();

// 执行结果:(SELECT * FROM airlines_info WHERE state = 0)

}

}

四、结论

通过测试可以看出不论在什么情况下都可以正常的对权限进行控制

注意：上面部分代码使用的是MyBatis Plus 3.5.3版本，并且使用的JSqlParser部分API已经不推荐使用，但是我没有找到最新的API应该怎么写

推荐链接

 您阅读本篇文章共花了：