Mybatis自定义插件动态修改当前执行sql语句

需求

详见上一篇文章

参考

(323) 651-9041分析

分页插件和我们的需求要解决的其实是一类问题:即如何动态的修改当前执行的查询SQL语句,那么我们可以看一下分页插件是怎么实现的

  • 继承 implements Interceptor
  • 通过ibatis的注解拦截 Executor.query(args...)方法
  • 在intercept中增加我们的相应的处理逻辑

  • 我们看一下执行的sql是在哪

    • sql<-boundSql.getSql()
    • boundSql <-mappedStatement.getBoundSql(parameter)
      • mappedStatement,parameter 皆为 Executor#query的入参
  • 那么我们的修改逻辑应该如下

    1
    sql->修改过的sql->newBoundSql->newMappedStatement
    • 修改过的sql->newBoundSql

      通过BoundSql#BoundSql(Configuration configuration, String sql, List parameterMappings, Object parameterObject)

    • newBoundSql->mappedStatement

      通过 MappedStatement.Builder#Builder(Configuration configuration, String id, SqlSource sqlSource, SqlCommandType sqlCommandType)

    • 需要一个sqlSoucre
    • BoundSql 是从sqlSource.getBoundSql(parameterObject)

2897689728代码实现

主要是对mybatis自定义插件动态修改sql语句增加一些注释说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
@Intercepts({@Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
type = Executor.class,
method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
public class MybaitsAdptorInterceptor implements Interceptor {


public static final String COUNT = "_COUNT";
private static final List<ResultMapping> EMPTY_RESULTMAPPING = new ArrayList<ResultMapping>(0);


static int MAPPED_STATEMENT_INDEX = 0;/ 这是对应上面的args的序号
static int PARAMETER_INDEX = 1;




@Override
public Object intercept(Invocation invocation) throws Throwable {

final Object[] queryArgs = invocation.getArgs();
final MappedStatement mappedStatement = (MappedStatement) queryArgs[MAPPED_STATEMENT_INDEX];
final Object parameter = queryArgs[PARAMETER_INDEX];
final BoundSql boundSql = mappedStatement.getBoundSql(parameter);

String sql = convert(boundSql.getSql());


/ 重新new一个查询语句对像
BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), sql, boundSql.getParameterMappings(), boundSql.getParameterObject());

/因为上面的构造中(this.additionalParameters = new HashMap<String, Object>();)
/ additionalParameters被重新初始化了,需要单独赋值
for (ParameterMapping mapping : boundSql.getParameterMappings()) {
String prop = mapping.getProperty();
if (boundSql.hasAdditionalParameter(prop)) {
newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
}
}


/ 把新的查询放到statement里
MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
queryArgs[MAPPED_STATEMENT_INDEX] = newMs;
return invocation.proceed();
}



private String convert(String oldSql){
String newSql = NewPgQueryAdapter.getNewSql(oldSql);
return newSql;
}




@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}


@Override
public void setProperties(Properties properties) {

}





private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.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());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
builder.keyProperty(ms.getKeyProperties()[0]);
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}


public class BoundSqlSqlSource implements SqlSource {

private BoundSql boundSql;

public BoundSqlSqlSource(BoundSql boundSql){
this.boundSql = boundSql;
}


@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}

###配置

可以参考上面的文章,因为项目中有多数据源的配置,我这块主要是针对自己项目做的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<bean id="sqlSessionFactory_postgresql" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource_postgresql" />
<property name="configLocation" value="classpath:mybatis-postgresql.xml" />
<property name="mapperLocations">
<list>
<value>classpath:com/{your package}/mapper/*-mapper.xml</value>
</list>
</property>
<!-- 增加自定义拦截器 -->
<property name="plugins">
<list>
<ref bean="pgAdptorInterceptor" />
</list>
</property>
</bean>
<bean name="pgAdptorInterceptor" class="com.{your package}.PgAdptorInterceptor"></bean>