How to intercept and modify SQL query dynamically in mybatis

Question

I am using mybatis to execute sql queries in my project. I need to intercept the sql query before executing in order to apply some changed dynamically. I read about @Intereptors like this:

@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 execution, but it is impossible to modify the SQL query, since the corresponding field is not writable. Do I have to create a new instance of the entire object manually to just replace the sql query? Where is the right place to intercept query execution for dynamic change? Thanks.

Answer 1

I hope this helps 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) { } } 

Answer 2

You can use the string template library (e.g. Velocity, Handlebars, Mustache) to help you

Today, there is even MyBatis-Velocity ( http://mybatis.imtqy.com/velocity-scripting/ ) to help you create scripts for sql.

Answer 3

Depending on the changes you want to make, you can use the dynamic sql function of mybatis 3

Answer 4

I made the Mybatis interceptor plugin just now. You can manage sql and parameter directly using the MetaObject provided by mybatis.

I insert my code fragment (I delete part of it, the fragment is not my source code, I just make a sample), you can configure the interceptor as you wish.

 @Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})}) public class OptimisticLocker implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { Object[] args = invocation.getArgs(); MappedStatement mappedStatement = (MappedStatement) args[0]; MetaObject parameter = SystemMetaObject.forObject(args[1]); SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType(); if (SqlCommandType.UPDATE.equals(sqlCommandType)) { // modify 'id' parameter to '1L' parameter.setValue("id", 1L); // modify sql // example: 'update ... where id = xx' to 'update ... where id = xx or 1 = 1' BoundSql boundSql = mappedStatement.getBoundSql(parameter); StringBuilder nextSql = new StringBuilder((String) boundSql.getSql()); nextSql.append(" OR 1 = 1 "); MetaObject metaObject = SystemMetaObject.forObject(boundSql); metaObject.setValue("sql", nextSql.toString()); } return invocation.proceed(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } }