1. 实现目标
以不侵入业务代码的方式实现数据权限控制,且能控制任意字段。
2. 实现思路
维护接口(Mapper中的标签id或者BaseMapper中的方法名)以及where条件,然后利用mybatis的拦截器读取配置并追加where条件。
3. 模块设计
分为两个模块:数据规则维护与数据权限分配
数据规则维护用来维护mapper名称、标签id以及where条件
数据权限分配用来为角色分配where条件
3.1 数据规则维护
3.1.1 查询页面
左侧为菜单树,右侧上方为mapper管理,下方维护where条件。
3.1.2 新增Mapper
先选则mapper,再选则mapper中对应的标签id,同时也可以维护BaseMapper中的方法。默认规则指的是一些通用规则,这些规则一般是过滤每张表都有的字段,比如 create_user_id,create_dept_id 等。
3.1.3 新增规则
规则类型分为字段和自定义类两种,为字段时,自己写where条件,为自定义类时,实现接口并注入到IOC容器中,配置beanId
3.2 权限分配
选角色、选刚刚创建的mapper、勾选规则,公式可以对规则进行组装
4. 拦截器
拦截器中通过mapper+方法+登录人角色读取对应的公式,并将公式解析成 sql 片段,再利用 jsqlparser 进行拼接SQL。
5. 部分代码
5.1 获取全部的mapper
public static Set<MapperDTO> getAllMapperList() {
Set<MapperNameDTO> set = new HashSet<MapperNameDTO>();
try {
SqlSessionFactory sqlSessionFactory = SpringContextUtils.getBean(SqlSessionFactory.class);
Collection<MappedStatement> mappedStatements = sqlSessionFactory.getConfiguration().getMappedStatements();
for (Object mapped : mappedStatements) {
if (mapped instanceof MappedStatement) {
MappedStatement mappedStatement = (MappedStatement) mapped;
String resource = mappedStatement.getResource();
if (resource.contains("(best guess)")) {
continue;
}
if (resource.startsWith("file")) {
String id = mappedStatement.getId();
String daoName = id.substring(0, id.lastIndexOf("."));
daoName = daoName.substring(daoName.lastIndexOf(".") + 1);
int i = resource.lastIndexOf(File.separator);
set.add(new MapperDTO(resource.substring(i + 1, resource.length() - 1), daoName));
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return set;
}
5.2 SQL 预览
public static Map<String, String> getSqlByMapperName(String mapperName) {
Map<String, String> sqlMap = new HashMap<String, String>();
try {
SqlSessionFactory sqlSessionFactory = SpringContextUtils.getBean(SqlSessionFactory.class);
Collection<MappedStatement> mappedStatements = sqlSessionFactory.getConfiguration().getMappedStatements();
for (Object mapped : mappedStatements) {
if (mapped instanceof MappedStatement) {
MappedStatement mappedStatement = (MappedStatement) mapped;
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
if ("SELECT".equals(sqlCommandType.toString())) {
String id = mappedStatement.getId();
String resource = mappedStatement.getResource();
if (resource.contains("(best guess)")) {
continue;
}
if (resource.startsWith("file")) {
int i = resource.lastIndexOf(File.separator);
String name = resource.substring(i + 1, resource.length() - 1);
if (name.equals(mapperName)) {
try {
sqlMap.put(id.substring(id.lastIndexOf(".") + 1), mappedStatement.getBoundSql(null).getSql());
} catch (Exception e) {
continue;
}
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return sqlMap;
}
5.3 拦截器
@Override
public Object intercept(Invocation invocation) throws Exception {
try {
loginUserId = Static.sysUserAPI.getLoginUserId();
if (!StringUtils.hasLength(loginUserId)) {
return invocation.proceed();
}
Object[] obj = invocation.getArgs();
MappedStatement ms = (MappedStatement) obj[0];
mapperName = ms.getResource();
if (mapperName.startsWith("file")) {
mapperName = mapperName.substring(mapperName.lastIndexOf(File.separator) + 1, mapperName.length() - 1);
} else {
return invocation.proceed();
}
// 获得方法名称
String sqlid = ms.getId();
methodName = sqlid.substring(sqlid.lastIndexOf(".") + 1);
// 校验模块是否需要被拦截,通过mapper + method + 用户ID,需要拦截则返回公式+方法id
List<Map<String, Object>> formulaList = checkMapper(mapperName, methodName, loginUserId);
if (!CollectionUtils.isEmpty(formulaList)) {
// 拼接where条件
splicingSelectSql(invocation, formulaList);
}
return invocation.proceed();
} catch (Exception e) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw));
errorMsg = sw.toString();
return invocation.proceed();
}
}
5.4 拼接where条件
public static String splicingDefaulSelectSql(String oldSql, String whereSql) {
try {
Select select = (Select) CCJSqlParserUtil.parse(new StringReader(oldSql));
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
Expression where = plainSelect.getWhere();
if (null == where) {
plainSelect.setWhere(new Column(whereSql));
} else {
plainSelect.setWhere(new AndExpression(new Parenthesis(where), new Column("(" + whereSql + ")")));
}
return select.toString();
} catch (JSQLParserException e) {
e.printStackTrace();
}
return oldSql;
}