问题场景:
最近项目中所有查询需要通过创建时间倒叙排序,因为集成了mybatis-plus,所以想直接通过拦截sql 并改造统一实现该功能。
解决方案
首先百度找到相关资料,链接:mybatis-plus添加数据权限过滤 当然 这篇博客没有实现此功能,只给出了部分代码。
从博客中可以看出,通过实现 Interceptor 可以拦截到sql
于是我们可以在此篇博客代码上稍稍改造下,实现查询时通过创建时间倒叙排序
上代码:
@Slf4j
@AllArgsConstructor
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Component
public class DataScopeInterceptor extends AbstractSqlParserHandler implements Interceptor {
private DataSource dataSource;
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
// 先判断是不是SELECT操作 不是直接过滤
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
return invocation.proceed();
}
this.sqlParser(metaObject);
//
appendOrderBy(metaObject);
return invocation.proceed();
}
/**
* 生成拦截对象的代理
*
* @param target 目标对象
* @return 代理对象
*/
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
/**
* mybatis配置的属性
*
* @param properties mybatis配置的属性
*/
@Override
public void setProperties(Properties properties) {
}
private void appendOrderBy(MetaObject metaObject) {
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
// 执行的SQL语句
String originalSql = boundSql.getSql();
// SQL语句的参数
//Object parameterObject = boundSql.getParameterObject();
int index = originalSql.indexOf("LIMIT");
if ( index != -1) {
String[] ary = originalSql.split("LIMIT");
originalSql = ary [0] + " order by create_time desc limit" + ary[1];
} else {
originalSql += " order by create_time desc";
}
metaObject.setValue("delegate.boundSql.sql", originalSql);
}
}
plus版本:
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>