java 如何在mybatis中动态拦截和更改sql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16152812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to intercept and change sql query dynamically in mybatis
提问by sochi
I use mybatis to perform sql queries in my project. I need to intercept sql query before executing to apply some changed dynamically. I've read about @Interseptors like this:
我使用 mybatis 在我的项目中执行 sql 查询。我需要在执行之前拦截 sql 查询以动态应用一些更改。我读过这样的@Interseptors：
@Intercepts({@Signature(type= Executor.class, method = "query", args = {...})})
public class ExamplePlugin implements Interceptor {
public Object intercept(Invocation invocation) throws Throwable {
return invocation.proceed();
}
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
public void setProperties(Properties properties) {
}
}
And it really intercepts executions, but there is no way to change sql query since appropriate field is not writable. Should I build new instance of whole object manually to just replace sql query? Where is the right place to intercept query execution to change it dynamically? Thank.
它确实拦截了执行，但是由于相应的字段不可写，因此无法更改 sql 查询。我应该手动构建整个对象的新实例来替换 sql 查询吗？拦截查询执行以动态更改它的正确位置在哪里？感谢。
回答by RookieCy
I hope it will help you:
我希望它能帮助你：
@Intercepts( { @Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class
})
})
public class SelectCountSqlInterceptor2 implements Interceptor
{
public static String COUNT = "_count";
private static int MAPPED_STATEMENT_INDEX = 0;
private static int PARAMETER_INDEX = 1;
@Override
public Object intercept(Invocation invocation) throws Throwable
{
processCountSql(invocation.getArgs());
return invocation.proceed();
}
@SuppressWarnings("rawtypes")
private void processCountSql(final Object[] queryArgs)
{
if (queryArgs[PARAMETER_INDEX] instanceof Map)
{
Map parameter = (Map) queryArgs[PARAMETER_INDEX];
if (parameter.containsKey(COUNT))
{
MappedStatement ms = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
BoundSql boundSql = ms.getBoundSql(parameter);
String sql = ms.getBoundSql(parameter).getSql().trim();
BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),
getCountSQL(sql), boundSql.getParameterMappings(),
boundSql.getParameterObject());
MappedStatement newMs = copyFromMappedStatement(ms,
new OffsetLimitInterceptor.BoundSqlSqlSource(newBoundSql));
queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
}
}
}
// see: MapperBuilderAssistant
@SuppressWarnings({ "unchecked", "rawtypes" })
private MappedStatement copyFromMappedStatement(MappedStatement ms,
SqlSource newSqlSource)
{
Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms
.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
// setStatementTimeout()
builder.timeout(ms.getTimeout());
// setParameterMap()
builder.parameterMap(ms.getParameterMap());
// setStatementResultMap()
List<ResultMap> resultMaps = new ArrayList<ResultMap>();
String id = "-inline";
if (ms.getResultMaps() != null)
{
id = ms.getResultMaps().get(0).getId() + "-inline";
}
ResultMap resultMap = new ResultMap.Builder(null, id, Long.class,
new ArrayList()).build();
resultMaps.add(resultMap);
builder.resultMaps(resultMaps);
builder.resultSetType(ms.getResultSetType());
// setStatementCache()
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
private String getCountSQL(String sql)
{
String lowerCaseSQL = sql.toLowerCase().replace("\n", " ").replace("\t", " ");
int index = lowerCaseSQL.indexOf(" order ");
if (index != -1)
{
sql = sql.substring(0, index);
}
return "SELECT COUNT(*) from ( select 1 as col_c " + sql.substring(lowerCaseSQL.indexOf(" from ")) + " ) cnt";
}
@Override
public Object plugin(Object target)
{
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties)
{
}
}
回答by Ian Lim
You may consider using a string template library (eg Velocity, Handlebars, Mustache) to help you
您可以考虑使用字符串模板库（例如 Velocity、Handlebars、Mustache）来帮助您
As of to date, there is even MyBatis-Velocity (http://mybatis.github.io/velocity-scripting/) to help you to do scripting for the sql.
迄今为止，甚至还有 MyBatis-Velocity ( http://mybatis.github.io/velocity-scripting/) 来帮助您为 sql 编写脚本。
回答by Christian Achilli
Depending on the changes you want to make, you may want to use the dynamic sqlfeature of mybatis 3
根据您要进行的更改，您可能需要使用mybatis 3的动态 sql功能
