怎么用MyBatis实行数据权限验证
发布时间：2021-12-21 22:17:53 所属栏目：MySql教程 来源：互联网
导读：本篇内容主要讲解怎么用MyBatis进行数据权限验证，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习怎么用MyBatis进行数据权限验证吧! 首先先创建表 CREATE TABLE `dataprivilegeconfig` ( `id` bigint(20) NOT NU
|
本篇内容主要讲解“怎么用MyBatis进行数据权限验证”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用MyBatis进行数据权限验证”吧! 首先先创建表 CREATE TABLE `dataprivilegeconfig` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `project` varchar(32) DEFAULT NULL comment '项目名称', `module` varchar(32) NOT NULL comment '模块名称', `tableName` varchar(32) NOT NULL comment '表名', `statement` varchar(512) NOT NULL comment '配置的SQL片段', PRIMARY KEY (`id`) ) ; 怎么用MyBatis进行数据权限验证 使用一个自定义annotation来实现不同模块，拼接不同的SQL文本 package com.bj58.mis.datapriv.plugin.mybatis; import java.lang.annotation.*; import static java.lang.annotation.ElementType.METHOD; import static java.lang.annotation.ElementType.TYPE; import static java.lang.annotation.RetentionPolicy.RUNTIME; @Documented @Inherited @Retention(RUNTIME) @Target({ TYPE, METHOD }) public @interface DataPrivilege{ String module() default "all"; } 上文的SQL解析代码 SQLDataPrivilege类 package com.bj58.mis.datapriv.core; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import com.alibaba.druid.sql.ast.SQLExpr; import com.alibaba.druid.sql.ast.SQLStatement; import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr; import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator; import com.alibaba.druid.sql.ast.expr.SQLQueryExpr; import com.alibaba.druid.sql.ast.statement.SQLExprTableSource; import com.alibaba.druid.sql.ast.statement.SQLJoinTableSource; import com.alibaba.druid.sql.ast.statement.SQLSelect; import com.alibaba.druid.sql.ast.statement.SQLSelectItem; import com.alibaba.druid.sql.ast.statement.SQLSelectQuery; import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock; import com.alibaba.druid.sql.ast.statement.SQLSelectStatement; import com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource; import com.alibaba.druid.sql.ast.statement.SQLTableSource; import com.alibaba.druid.sql.ast.statement.SQLUnionQuery; import com.alibaba.druid.sql.parser.SQLExprParser; import com.alibaba.druid.sql.parser.SQLParserUtils; import com.alibaba.druid.sql.parser.SQLStatementParser; import com.alibaba.druid.util.JdbcUtils; /** * Hello world! * */ public class SQLDataPrivilege { public static void main(String[] args) { } //单例.该对象用于给已经存在的SQL增加数据权限 private static SQLDataPrivilege INSTANCE = new SQLDataPrivilege(); public static SQLDataPrivilege getInstance() { return INSTANCE; } //从数据库中获取配置信息 private SQLDataPrivilege() { try { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "laohuali@58"); String sql="select project,module,tableName,group_concat(statement separator ' and ') statement "; sql=sql+" from DataPrivilegeConfig where Project='测试' "; sql=sql+" group by project,module,tableName"; PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); while (rs.next()) { Privilege p = new Privilege(); p.setProject(rs.getString("project")); p.setModule(rs.getString("module")); p.setTableName(rs.getString("tableName")); p.setStatement(rs.getString("statement")); privList.add(p); } rs.close(); ps.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } //保存本项目的数据权限配置信息 private List<Privilege> privList = new ArrayList<Privilege>(); //在SQL上拼接数据权限 public String addPrivilege(final String module,final String sql, Map<String, String> varMap) { // SQLParserUtils.createSQLStatementParser可以将sql装载到Parser里面 SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL); // parseStatementList的返回值SQLStatement本身就是druid里面的语法树对象 List<SQLStatement> stmtList = parser.parseStatementList(); SQLStatement stmt = stmtList.get(0); //如果不是查询,则返回 if (!(stmt instanceof SQLSelectStatement)) { return sql; } SQLSelectStatement selectStmt = (SQLSelectStatement) stmt; // 拿到SQLSelect 通过在这里打断点看对象我们可以看出这是一个树的结构 SQLSelect sqlselect = selectStmt.getSelect(); SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery(); parseSubQuery(module,query.getSelectList(), varMap); parseTable(module,query, varMap); System.out.println(sqlselect.toString()); return sqlselect.toString(); } //给子查询增加数据权限 private void parseSubQuery(final String module,final List<SQLSelectItem> fieldList, final Map<String, String> varMap) { for (SQLSelectItem item : fieldList) { if (item.getExpr() instanceof SQLQueryExpr) { SQLQueryExpr expr = (SQLQueryExpr) item.getExpr(); parseTable(module,expr.getSubQuery().getQueryBlock(), varMap); } } } //递归处理嵌套表 private void parseTable(final String module,final SQLSelectQueryBlock query, final Map<String, String> varMap) { if (query == null) { return; } SQLTableSource tableSource = query.getFrom(); if (tableSource instanceof SQLExprTableSource) { //如果是普通的表,则在where中增加数据权限 SQLExprTableSource table = ((SQLExprTableSource) tableSource); String tableName = table.getName().getSimpleName(); String aliasName = table.getAlias(); SQLExpr sqlExpr = createSQLExpr(module,tableName, aliasName, varMap); createWhereSQLExpr(query, varMap, sqlExpr); } else if (tableSource instanceof SQLSubqueryTableSource) { //如果是嵌套表,则递归到内层 SQLSubqueryTableSource table = ((SQLSubqueryTableSource) tableSource); parseTable(module,table.getSelect().getQueryBlock(), varMap); } else if (tableSource instanceof SQLJoinTableSource) { //如果是两个表关联.则在on条件中增加数据权限。并且在左右表中分别判断是否是union all的情况 SQLJoinTableSource table = ((SQLJoinTableSource) tableSource); SQLTableSource left = table.getLeft(); SQLTableSource right = table.getRight(); SQLExpr onExpr = table.getCondition(); if (left instanceof SQLSubqueryTableSource) { SQLSubqueryTableSource leftTable = ((SQLSubqueryTableSource) left); parseUnion(module,leftTable.getSelect().getQuery(), varMap); parseTable(module,leftTable.getSelect().getQueryBlock(), varMap); } else if (left instanceof SQLExprTableSource) { SQLExprTableSource joinTable = ((SQLExprTableSource) left); onExpr = createOnExpr(module,joinTable, onExpr, varMap); } if (right instanceof SQLSubqueryTableSource) { SQLSubqueryTableSource rightTable = ((SQLSubqueryTableSource) right); parseUnion(module,rightTable.getSelect().getQuery(), varMap); parseTable(module,rightTable.getSelect().getQueryBlock(), varMap); } else if (right instanceof SQLExprTableSource) { SQLExprTableSource joinTable = ((SQLExprTableSource) right); onExpr = createOnExpr(module,joinTable, onExpr, varMap); } table.setCondition(onExpr); } } //如果是union all的情况,则通过递归进入内层 private boolean parseUnion(final String module,final SQLSelectQuery query, final Map<String, String> varMap) { if (query instanceof SQLUnionQuery) { SQLUnionQuery unionQuery = (SQLUnionQuery) query; if (unionQuery.getLeft() instanceof SQLUnionQuery) { parseUnion(module,unionQuery.getLeft(), varMap); } else if (unionQuery.getLeft() instanceof SQLSelectQueryBlock) { SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) unionQuery.getLeft(); parseTable(module,queryBlock, varMap); } if (unionQuery.getRight() instanceof SQLUnionQuery) { parseUnion(module,unionQuery.getRight(), varMap); } else if (unionQuery.getRight() instanceof SQLSelectQueryBlock) { SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) unionQuery.getRight(); parseTable(module,queryBlock, varMap); } return true; } return false; } //在连接的on条件中拼接权限 private SQLExpr createOnExpr(final String module,SQLExprTableSource joinTable, SQLExpr onExpr, final Map<String, String> varMap) { String tableName = joinTable.getName().getSimpleName(); String aliasName = joinTable.getAlias(); SQLExpr sqlExpr = createSQLExpr(module,tableName, aliasName, varMap); if (sqlExpr != null) { SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(onExpr, SQLBinaryOperator.BooleanAnd, sqlExpr); onExpr = newWhereExpr; } return onExpr; } //根据配置获取拼接好的权限SQL private SQLExpr createSQLExpr(String module,String tableName, String aliasName, final Map<String, String> varMap) { StringBuffer constraintsBuffer = new StringBuffer(""); for (Privilege p : privList) { if (tableName.equals(p.getTableName()) && module.equals(p.getModule())) { constraintsBuffer.append(p.toString(aliasName, varMap)); } } if ("".equals(constraintsBuffer.toString())) { return null; } SQLExprParser constraintsParser = SQLParserUtils .createExprParser(constraintsBuffer.toString(), JdbcUtils.MYSQL); SQLExpr constraintsExpr = constraintsParser.expr(); return constraintsExpr; } //拼接where中的权限信息 private void createWhereSQLExpr(final SQLSelectQueryBlock query, final Map<String, String> varMap, SQLExpr sqlExpr) { if (sqlExpr == null) { return; } SQLExpr whereExpr = query.getWhere(); // 修改where表达式 if (whereExpr == null) { query.setWhere(sqlExpr); } else { SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(whereExpr, SQLBinaryOperator.BooleanAnd, sqlExpr); query.setWhere(newWhereExpr); } } } Privilege类 package com.bj58.mis.datapriv.core; import java.util.HashMap; import java.util.Map; import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; public class Privilege { private String project = null; private String module = null; private String tableName = null; private String statement = null; private Map<String,String> varDef = new HashMap<String,String>(); private Pattern pattern = Pattern.compile("{.*?}"); public String getProject() { return project; } public void setProject(String project) { if (this.project == null) { this.project = project; } } public String getModule() { return module; } public void setModule(String module) { if (this.module == null) { this.module = module; } } public String getTableName() { return tableName; } public void setTableName(String tableName) { if (this.tableName == null) { this.tableName = tableName; } } public String getStatement() { return statement; } public void setStatement(String statement) { if (this.statement == null) { this.statement = statement; Matcher m = pattern.matcher(this.statement); while (m.find()) { String var = m.group().replaceAll("({|})", "").trim(); this.varDef.put(var, "{" + var + "}"); } } } public String toString(String aliasName, Map<String, String> varMap) { if (aliasName == null || "".equals(aliasName)) { aliasName = tableName; } String sqlString = this.statement.replaceAll("#tab#", aliasName); for (Entry<String,String> entry: varDef.entrySet()) { if (varMap.containsKey(entry.getKey())) { sqlString = sqlString.replaceAll(entry.getValue(), varMap.get(entry.getKey())); } else { throw new RuntimeException("缺少必要信息"); } } return sqlString; } } 增加一个MyBatis拦截器实现拼接SQL的功能 package com.bj58.mis.datapriv.plugin.mybatis; import com.bj58.mis.datapriv.core.SQLDataPrivilege; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlSource; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Plugin; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.*; import java.util.concurrent.ConcurrentHashMap; @Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}) }) public class MapperInterceptor implements Interceptor { private Properties properties; private Map<String,String> moduleMapping=new ConcurrentHashMap<String,String>(); @Override public Object intercept(Invocation invocation) throws Throwable { Object[] args = invocation.getArgs(); MappedStatement mappedStatement = (MappedStatement) args[0]; Object parameter = args[1]; final BoundSql boundSql = mappedStatement.getBoundSql(parameter); MappedStatement newMs = copyFromMappedStatement(mappedStatement, boundSql,parameter); System.out.println(newMs.getBoundSql(parameter).getSql()); long start = System.currentTimeMillis(); List returnValue = (List) invocation.proceed(); long end = System.currentTimeMillis(); return returnValue; } private String concatSQL(String mapperId, String sql, Object parameter) { String module=moduleMapping.get(mapperId); if(module==null){ initModule(mapperId); module=moduleMapping.get(mapperId); } if("".equals(module)){ return sql; } Map<String,String> newParameterMap=new HashMap<String,String>(); for(Map.Entry<String,Object> entry : ((Map<String,Object>)parameter).entrySet()){ if(entry.getValue() instanceof ArrayList){ StringBuilder sb=new StringBuilder(128); sb.append(" ( "); for(Object obj:(ArrayList)entry.getValue()) { if(obj instanceof String) { sb.append("'"); sb.append(obj); sb.append("',"); }else { sb.append(obj); sb.append(","); } } sb.deleteCharAt(sb.length()-1); sb.append(" ) "); newParameterMap.put(entry.getKey(),sb.toString()); }else{ newParameterMap.put(entry.getKey(), String.valueOf( entry.getValue())); } } SQLDataPrivilege s =SQLDataPrivilege.getInstance(); return s.addPrivilege(module,sql,newParameterMap); } private void initModule(String mapperId){ String clazzName = mapperId.substring(0, mapperId.lastIndexOf(".")); try { Class clazz = Class.forName(clazzName); DataPrivilege clazzDataPrivilege= (DataPrivilege) clazz.getAnnotation(DataPrivilege.class); for(Method method:clazz.getMethods()){ String key=clazzName+"."+method.getName(); DataPrivilege methodDataPrivilege=method.getAnnotation(DataPrivilege.class); if(methodDataPrivilege!=null){ moduleMapping.put(key,methodDataPrivilege.module()); }else if(clazzDataPrivilege!=null){ moduleMapping.put(key,clazzDataPrivilege.module()); }else{ moduleMapping.put(key,""); } } } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static class BoundSqlSqlSource implements SqlSource { private BoundSql boundSql; public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; } public BoundSql getBoundSql(Object parameterObject) { return boundSql; } } private MappedStatement copyFromMappedStatement(MappedStatement ms, BoundSql boundSql, Object parameter) { String sql = concatSQL(ms.getId(), boundSql.getSql(),parameter); BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), sql,boundSql.getParameterMappings(), boundSql.getParameterObject()); MetaObject boundSqlObject = SystemMetaObject.forObject(boundSql); MetaObject newBoundSqlObject = SystemMetaObject.forObject(newBoundSql); newBoundSqlObject.setValue("metaParameters",boundSqlObject.getValue("metaParameters")); try { Field additionalParametersField=BoundSql.class.getDeclaredField("additionalParameters"); additionalParametersField.setAccessible(true); Map<String, Object> boundSqlAdditionalParametersField= (Map<String, Object>) additionalParametersField.get(boundSql); Map<String, Object> newBoundSqlObjectSqlAdditionalParametersField= (Map<String, Object>) additionalParametersField.get(newBoundSql); for(Map.Entry<String,Object> entry:boundSqlAdditionalParametersField.entrySet()){ newBoundSqlObjectSqlAdditionalParametersField.put(entry.getKey(),entry.getValue()); } Field sqlSource=MappedStatement.class.getDeclaredField("sqlSource"); sqlSource.setAccessible(true); sqlSource.set(ms,new BoundSqlSqlSource(newBoundSql)); } catch (NoSuchFieldException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } return ms; } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties0) { this.properties = properties0; } } 使用的时候，先配置数据库的SQL片段 然后配置MyBatis拦截器插件 @SpringBootApplication @EnableSwagger2 public class StatisticsApplication { public static void main(String[] args) { SpringApplication.run(StatisticsApplication.class, args); } @Bean(name = "sqlSessionFactory") public SqlSessionFactory sqlSessionFactory( DataSource dataSource) throws Exception { SqlSessionFactoryBean factory = new SqlSessionFactoryBean(); factory.setDataSource(dataSource); factory.setPlugins(new Interceptor[]{mapperInterceptor()}); ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); factory.setMapperLocations(resolver.getResources("classpath*:/mapper/*.mapper.xml")); return factory.getObject(); } @Bean public MapperInterceptor mapperInterceptor() { MapperInterceptor mapperInterceptor=new MapperInterceptor(); return mapperInterceptor; } } 最后在Mapper接口上增加Annotation 到此，相信大家对“怎么用MyBatis进行数据权限验证”有了更深的了解，不妨来实际操作一番吧！ （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号