CODEWITHSUNDEEP
javamybatis
Mark
Mark

Reputation: 1924

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="queryBand"/> 
   ... 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?

Upvotes: 1

Views: 3647

Answers (2)

Mark
Mark

Reputation: 1924

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) {}
}

Upvotes: 2

Boris Pavlović
Boris Pavlović

Reputation: 64632

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.

Upvotes: 0

Related Questions