数据权限设计之Mybatis拦截器追加sql的where条件
缘起
需求:数据权限设计要求使用mybatis拦截器拦截Statement的prepare,对即将执行的sql进行拦截并添加权限需要的条件。
解决方案
对sql的where部分进行追加条件,需要重写sql。工具类使用Duird包中包含的工具类,如下:
private static String contactConditions(String sql, Map<String, Object> columnMap) {
SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);
List<SQLStatement> stmtList = parser.parseStatementList();
SQLStatement stmt = stmtList.get(0);
if (stmt instanceof SQLSelectStatement) {
StringBuffer constraintsBuffer = new StringBuffer();
Set<String> keys = columnMap.keySet();
Iterator<String> keyIter = keys.iterator();
if (keyIter.hasNext()) {
String key = keyIter.next();
constraintsBuffer.append(key).append(" = " + getSqlByClass(columnMap.get(key)));
}
while (keyIter.hasNext()) {
String key = keyIter.next();
constraintsBuffer.append(" AND ").append(key).append(" = " + getSqlByClass(columnMap.get(key)));
}
SQLExprParser constraintsParser = SQLParserUtils.createExprParser(constraintsBuffer.toString(), JdbcUtils.MYSQL);
SQLExpr constraintsExpr = constraintsParser.expr();
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
// 拿到SQLSelect
SQLSelect sqlselect = selectStmt.getSelect();
SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();
SQLExpr whereExpr = query.getWhere();
// 修改where表达式
if (whereExpr == null) {
query.setWhere(constraintsExpr);
} else {
SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr(whereExpr, SQLBinaryOperator.BooleanAnd, constraintsExpr);
query.setWhere(newWhereExpr);
}
sqlselect.setQuery(query);
return sqlselect.toString();
}
return sql;
}
private static String getSqlByClass(Object value){
if(value instanceof Number){
return value + "";
}else if(value instanceof String){
return "'" + value + "'";
}
return "'" + value.toString() + "'";
}