本篇内容主要讲解“怎么用MyBatis进行数据权限验证”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用MyBatis进行数据权限验证”吧!

首先先创建表

CREATETABLE`dataprivilegeconfig`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`project`varchar(32)DEFAULTNULLcomment'项目名称',`module`varchar(32)NOTNULLcomment'模块名称',`tableName`varchar(32)NOTNULLcomment'表名',`statement`varchar(512)NOTNULLcomment'配置的SQL片段',PRIMARYKEY(`id`));

使用一个自定义annotation来实现不同模块，拼接不同的SQL文本

packagecom.bj58.mis.datapriv.plugin.mybatis;importjava.lang.annotation.*;importstaticjava.lang.annotation.ElementType.METHOD;importstaticjava.lang.annotation.ElementType.TYPE;importstaticjava.lang.annotation.RetentionPolicy.RUNTIME;@Documented@Inherited@Retention(RUNTIME)@Target({TYPE,METHOD})public@interfaceDataPrivilege{Stringmodule()default"all";}

上文的SQL解析代码

SQLDataPrivilege类

packagecom.bj58.mis.datapriv.core;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importcom.alibaba.druid.sql.ast.SQLExpr;importcom.alibaba.druid.sql.ast.SQLStatement;importcom.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;importcom.alibaba.druid.sql.ast.expr.SQLBinaryOperator;importcom.alibaba.druid.sql.ast.expr.SQLQueryExpr;importcom.alibaba.druid.sql.ast.statement.SQLExprTableSource;importcom.alibaba.druid.sql.ast.statement.SQLJoinTableSource;importcom.alibaba.druid.sql.ast.statement.SQLSelect;importcom.alibaba.druid.sql.ast.statement.SQLSelectItem;importcom.alibaba.druid.sql.ast.statement.SQLSelectQuery;importcom.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;importcom.alibaba.druid.sql.ast.statement.SQLSelectStatement;importcom.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource;importcom.alibaba.druid.sql.ast.statement.SQLTableSource;importcom.alibaba.druid.sql.ast.statement.SQLUnionQuery;importcom.alibaba.druid.sql.parser.SQLExprParser;importcom.alibaba.druid.sql.parser.SQLParserUtils;importcom.alibaba.druid.sql.parser.SQLStatementParser;importcom.alibaba.druid.util.JdbcUtils;/***Helloworld!**/publicclassSQLDataPrivilege{publicstaticvoidmain(String[]args){}//单例.该对象用于给已经存在的SQL增加数据权限privatestaticSQLDataPrivilegeINSTANCE=newSQLDataPrivilege();publicstaticSQLDataPrivilegegetInstance(){returnINSTANCE;}//从数据库中获取配置信息privateSQLDataPrivilege(){try{Class.forName("com.mysql.jdbc.Driver");Connectioncon=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root","laohuali@58");Stringsql="selectproject,module,tableName,group_concat(statementseparator'and')statement";sql=sql+"fromDataPrivilegeConfigwhereProject='测试'";sql=sql+"groupbyproject,module,tableName";PreparedStatementps=con.prepareStatement(sql);ResultSetrs=ps.executeQuery();while(rs.next()){Privilegep=newPrivilege();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(ClassNotFoundExceptione){e.printStackTrace();}catch(SQLExceptione){e.printStackTrace();}}//保存本项目的数据权限配置信息privateList<Privilege>privList=newArrayList<Privilege>();//在SQL上拼接数据权限publicStringaddPrivilege(finalStringmodule,finalStringsql,Map<String,String>varMap){//SQLParserUtils.createSQLStatementParser可以将sql装载到Parser里面SQLStatementParserparser=SQLParserUtils.createSQLStatementParser(sql,JdbcUtils.MYSQL);//parseStatementList的返回值SQLStatement本身就是druid里面的语法树对象List<SQLStatement>stmtList=parser.parseStatementList();SQLStatementstmt=stmtList.get(0);//如果不是查询,则返回if(!(stmtinstanceofSQLSelectStatement)){returnsql;}SQLSelectStatementselectStmt=(SQLSelectStatement)stmt;//拿到SQLSelect通过在这里打断点看对象我们可以看出这是一个树的结构SQLSelectsqlselect=selectStmt.getSelect();SQLSelectQueryBlockquery=(SQLSelectQueryBlock)sqlselect.getQuery();parseSubQuery(module,query.getSelectList(),varMap);parseTable(module,query,varMap);System.out.println(sqlselect.toString());returnsqlselect.toString();}//给子查询增加数据权限privatevoidparseSubQuery(finalStringmodule,finalList<SQLSelectItem>fieldList,finalMap<String,String>varMap){for(SQLSelectItemitem:fieldList){if(item.getExpr()instanceofSQLQueryExpr){SQLQueryExprexpr=(SQLQueryExpr)item.getExpr();parseTable(module,expr.getSubQuery().getQueryBlock(),varMap);}}}//递归处理嵌套表privatevoidparseTable(finalStringmodule,finalSQLSelectQueryBlockquery,finalMap<String,String>varMap){if(query==null){return;}SQLTableSourcetableSource=query.getFrom();if(tableSourceinstanceofSQLExprTableSource){//如果是普通的表,则在where中增加数据权限SQLExprTableSourcetable=((SQLExprTableSource)tableSource);StringtableName=table.getName().getSimpleName();StringaliasName=table.getAlias();SQLExprsqlExpr=createSQLExpr(module,tableName,aliasName,varMap);createWhereSQLExpr(query,varMap,sqlExpr);}elseif(tableSourceinstanceofSQLSubqueryTableSource){//如果是嵌套表,则递归到内层SQLSubqueryTableSourcetable=((SQLSubqueryTableSource)tableSource);parseTable(module,table.getSelect().getQueryBlock(),varMap);}elseif(tableSourceinstanceofSQLJoinTableSource){//如果是两个表关联.则在on条件中增加数据权限。并且在左右表中分别判断是否是unionall的情况SQLJoinTableSourcetable=((SQLJoinTableSource)tableSource);SQLTableSourceleft=table.getLeft();SQLTableSourceright=table.getRight();SQLExpronExpr=table.getCondition();if(leftinstanceofSQLSubqueryTableSource){SQLSubqueryTableSourceleftTable=((SQLSubqueryTableSource)left);parseUnion(module,leftTable.getSelect().getQuery(),varMap);parseTable(module,leftTable.getSelect().getQueryBlock(),varMap);}elseif(leftinstanceofSQLExprTableSource){SQLExprTableSourcejoinTable=((SQLExprTableSource)left);onExpr=createOnExpr(module,joinTable,onExpr,varMap);}if(rightinstanceofSQLSubqueryTableSource){SQLSubqueryTableSourcerightTable=((SQLSubqueryTableSource)right);parseUnion(module,rightTable.getSelect().getQuery(),varMap);parseTable(module,rightTable.getSelect().getQueryBlock(),varMap);}elseif(rightinstanceofSQLExprTableSource){SQLExprTableSourcejoinTable=((SQLExprTableSource)right);onExpr=createOnExpr(module,joinTable,onExpr,varMap);}table.setCondition(onExpr);}}//如果是unionall的情况,则通过递归进入内层privatebooleanparseUnion(finalStringmodule,finalSQLSelectQueryquery,finalMap<String,String>varMap){if(queryinstanceofSQLUnionQuery){SQLUnionQueryunionQuery=(SQLUnionQuery)query;if(unionQuery.getLeft()instanceofSQLUnionQuery){parseUnion(module,unionQuery.getLeft(),varMap);}elseif(unionQuery.getLeft()instanceofSQLSelectQueryBlock){SQLSelectQueryBlockqueryBlock=(SQLSelectQueryBlock)unionQuery.getLeft();parseTable(module,queryBlock,varMap);}if(unionQuery.getRight()instanceofSQLUnionQuery){parseUnion(module,unionQuery.getRight(),varMap);}elseif(unionQuery.getRight()instanceofSQLSelectQueryBlock){SQLSelectQueryBlockqueryBlock=(SQLSelectQueryBlock)unionQuery.getRight();parseTable(module,queryBlock,varMap);}returntrue;}returnfalse;}//在连接的on条件中拼接权限privateSQLExprcreateOnExpr(finalStringmodule,SQLExprTableSourcejoinTable,SQLExpronExpr,finalMap<String,String>varMap){StringtableName=joinTable.getName().getSimpleName();StringaliasName=joinTable.getAlias();SQLExprsqlExpr=createSQLExpr(module,tableName,aliasName,varMap);if(sqlExpr!=null){SQLBinaryOpExprnewWhereExpr=newSQLBinaryOpExpr(onExpr,SQLBinaryOperator.BooleanAnd,sqlExpr);onExpr=newWhereExpr;}returnonExpr;}//根据配置获取拼接好的权限SQLprivateSQLExprcreateSQLExpr(Stringmodule,StringtableName,StringaliasName,finalMap<String,String>varMap){StringBufferconstraintsBuffer=newStringBuffer("");for(Privilegep:privList){if(tableName.equals(p.getTableName())&&module.equals(p.getModule())){constraintsBuffer.append(p.toString(aliasName,varMap));}}if("".equals(constraintsBuffer.toString())){returnnull;}SQLExprParserconstraintsParser=SQLParserUtils.createExprParser(constraintsBuffer.toString(),JdbcUtils.MYSQL);SQLExprconstraintsExpr=constraintsParser.expr();returnconstraintsExpr;}//拼接where中的权限信息privatevoidcreateWhereSQLExpr(finalSQLSelectQueryBlockquery,finalMap<String,String>varMap,SQLExprsqlExpr){if(sqlExpr==null){return;}SQLExprwhereExpr=query.getWhere();//修改where表达式if(whereExpr==null){query.setWhere(sqlExpr);}else{SQLBinaryOpExprnewWhereExpr=newSQLBinaryOpExpr(whereExpr,SQLBinaryOperator.BooleanAnd,sqlExpr);query.setWhere(newWhereExpr);}}}

Privilege类

packagecom.bj58.mis.datapriv.core;importjava.util.HashMap;importjava.util.Map;importjava.util.Map.Entry;importjava.util.regex.Matcher;importjava.util.regex.Pattern;publicclassPrivilege{privateStringproject=null;privateStringmodule=null;privateStringtableName=null;privateStringstatement=null;privateMap<String,String>varDef=newHashMap<String,String>();privatePatternpattern=Pattern.compile("\\{.*?\\}");publicStringgetProject(){returnproject;}publicvoidsetProject(Stringproject){if(this.project==null){this.project=project;}}publicStringgetModule(){returnmodule;}publicvoidsetModule(Stringmodule){if(this.module==null){this.module=module;}}publicStringgetTableName(){returntableName;}publicvoidsetTableName(StringtableName){if(this.tableName==null){this.tableName=tableName;}}publicStringgetStatement(){returnstatement;}publicvoidsetStatement(Stringstatement){if(this.statement==null){this.statement=statement;Matcherm=pattern.matcher(this.statement);while(m.find()){Stringvar=m.group().replaceAll("(\\{|\\})","").trim();this.varDef.put(var,"\\{"+var+"\\}");}}}publicStringtoString(StringaliasName,Map<String,String>varMap){if(aliasName==null||"".equals(aliasName)){aliasName=tableName;}StringsqlString=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{thrownewRuntimeException("缺少必要信息");}}returnsqlString;}}

增加一个MyBatis拦截器实现拼接SQL的功能

packagecom.bj58.mis.datapriv.plugin.mybatis;importcom.bj58.mis.datapriv.core.SQLDataPrivilege;importorg.apache.ibatis.executor.Executor;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.mapping.MappedStatement;importorg.apache.ibatis.mapping.SqlSource;importorg.apache.ibatis.plugin.Intercepts;importorg.apache.ibatis.plugin.Invocation;importorg.apache.ibatis.plugin.Signature;importorg.apache.ibatis.reflection.MetaObject;importorg.apache.ibatis.reflection.SystemMetaObject;importorg.apache.ibatis.session.ResultHandler;importorg.apache.ibatis.session.RowBounds;importorg.apache.ibatis.plugin.Interceptor;importorg.apache.ibatis.plugin.Plugin;importjava.lang.reflect.Field;importjava.lang.reflect.Method;importjava.util.*;importjava.util.concurrent.ConcurrentHashMap;@Intercepts({@Signature(type=Executor.class,method="query",args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})publicclassMapperInterceptorimplementsInterceptor{privatePropertiesproperties;privateMap<String,String>moduleMapping=newConcurrentHashMap<String,String>();@OverridepublicObjectintercept(Invocationinvocation)throwsThrowable{Object[]args=invocation.getArgs();MappedStatementmappedStatement=(MappedStatement)args[0];Objectparameter=args[1];finalBoundSqlboundSql=mappedStatement.getBoundSql(parameter);MappedStatementnewMs=copyFromMappedStatement(mappedStatement,boundSql,parameter);System.out.println(newMs.getBoundSql(parameter).getSql());longstart=System.currentTimeMillis();ListreturnValue=(List)invocation.proceed();longend=System.currentTimeMillis();returnreturnValue;}privateStringconcatSQL(StringmapperId,Stringsql,Objectparameter){Stringmodule=moduleMapping.get(mapperId);if(module==null){initModule(mapperId);module=moduleMapping.get(mapperId);}if("".equals(module)){returnsql;}Map<String,String>newParameterMap=newHashMap<String,String>();for(Map.Entry<String,Object>entry:((Map<String,Object>)parameter).entrySet()){if(entry.getValue()instanceofArrayList){StringBuildersb=newStringBuilder(128);sb.append("(");for(Objectobj:(ArrayList)entry.getValue()){if(objinstanceofString){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()));}}SQLDataPrivileges=SQLDataPrivilege.getInstance();returns.addPrivilege(module,sql,newParameterMap);}privatevoidinitModule(StringmapperId){StringclazzName=mapperId.substring(0,mapperId.lastIndexOf("."));try{Classclazz=Class.forName(clazzName);DataPrivilegeclazzDataPrivilege=(DataPrivilege)clazz.getAnnotation(DataPrivilege.class);for(Methodmethod:clazz.getMethods()){Stringkey=clazzName+"."+method.getName();DataPrivilegemethodDataPrivilege=method.getAnnotation(DataPrivilege.class);if(methodDataPrivilege!=null){moduleMapping.put(key,methodDataPrivilege.module());}elseif(clazzDataPrivilege!=null){moduleMapping.put(key,clazzDataPrivilege.module());}else{moduleMapping.put(key,"");}}}catch(ClassNotFoundExceptione){e.printStackTrace();}}publicstaticclassBoundSqlSqlSourceimplementsSqlSource{privateBoundSqlboundSql;publicBoundSqlSqlSource(BoundSqlboundSql){this.boundSql=boundSql;}publicBoundSqlgetBoundSql(ObjectparameterObject){returnboundSql;}}privateMappedStatementcopyFromMappedStatement(MappedStatementms,BoundSqlboundSql,Objectparameter){Stringsql=concatSQL(ms.getId(),boundSql.getSql(),parameter);BoundSqlnewBoundSql=newBoundSql(ms.getConfiguration(),sql,boundSql.getParameterMappings(),boundSql.getParameterObject());MetaObjectboundSqlObject=SystemMetaObject.forObject(boundSql);MetaObjectnewBoundSqlObject=SystemMetaObject.forObject(newBoundSql);newBoundSqlObject.setValue("metaParameters",boundSqlObject.getValue("metaParameters"));try{FieldadditionalParametersField=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());}FieldsqlSource=MappedStatement.class.getDeclaredField("sqlSource");sqlSource.setAccessible(true);sqlSource.set(ms,newBoundSqlSqlSource(newBoundSql));}catch(NoSuchFieldExceptione){e.printStackTrace();}catch(IllegalAccessExceptione){e.printStackTrace();}returnms;}@OverridepublicObjectplugin(Objecttarget){returnPlugin.wrap(target,this);}@OverridepublicvoidsetProperties(Propertiesproperties0){this.properties=properties0;}}

使用的时候，先配置数据库的SQL片段

然后配置MyBatis拦截器插件

@SpringBootApplication@EnableSwagger2publicclassStatisticsApplication{publicstaticvoidmain(String[]args){SpringApplication.run(StatisticsApplication.class,args);}@Bean(name="sqlSessionFactory")publicSqlSessionFactorysqlSessionFactory(DataSourcedataSource)throwsException{SqlSessionFactoryBeanfactory=newSqlSessionFactoryBean();factory.setDataSource(dataSource);factory.setPlugins(newInterceptor[]{mapperInterceptor()});ResourcePatternResolverresolver=newPathMatchingResourcePatternResolver();factory.setMapperLocations(resolver.getResources("classpath*:/mapper/*.mapper.xml"));returnfactory.getObject();}@BeanpublicMapperInterceptormapperInterceptor(){MapperInterceptormapperInterceptor=newMapperInterceptor();returnmapperInterceptor;}}

最后在Mapper接口上增加Annotation

到此，相信大家对“怎么用MyBatis进行数据权限验证”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！