Using SET Statements with MyBatis
(I am using MyBatis v3, Java SE v6, Tomcat v6 and Spring v3 all over Teradata v12.)
One of the technical requirements for my current project is to use the query banding feature in Teradata. This is done by running a statement like the following whenever required:
SET QUERY_BAND='someKey=someValue;' FOR TRANSACTION;
I want to have a query band for all of my calls. However, I am unsure how to add this functionality in a clean and reusable manner without having to add it to each of my <select>
statements in my mapper file like the following:
<sql id="queryBand">
SET QUERY_BAND='k=v;' FOR TRANSACTION;
</sql>
<select ...>
<include refid="que开发者_运维知识库ryBand"/>
... some SQL performing a SELECT
</select>
My issues with the above are:
1) The format of the query band is identical across all my mapper XML files with the exception of k & v, which I would want to customise on a per <select>
(etc.) basis. I'm not sure how I can do this customisation without having to pass in the k and v values, which muddies my mapper interface.
2) There is duplication in the above code that makes me uneasy. Developers have to remember to include the queryBand SQL, which someone will forget at some stage (Murphy's Law).
Can someone point me in the direction of the solution to implementing the query banding in a cleaner way?
The solution is to use MyBatis Interceptor plug-ins. For example, the following:
import java.sql.Connection;
import java.sql.Statement;
import java.util.Properties;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
@Intercepts({@Signature(
type=StatementHandler.class,
method = "prepare",
args={ Connection.class })})
public class StatementInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Connection conn = (Connection) invocation.getArgs()[0];
Statement stmt = conn.createStatement();
stmt.executeUpdate("SET QUERY_BAND = 'k=v;' FOR TRANSACTION;");
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {}
}
Let's say that every SQL string should be appended to a query band. I would try to find a method inside myBatis/Spring which does it. Using Spring's AOP this method could be intercepted and its result appended to the query band and returned for further computation.
Finding a method to intercept can be hard but not impossible. Download all dependency sources and link them properly (using Maven this should be trivial nonetheless in Eclipse is not that hard, too), run the code in debugging mode and look for an appropriate method.
精彩评论