Detailed introduction to the extension and application of SqlMapper configuration in Mybatis (1)

After working hard for several nights to debug the program, writing several blogs, and finally establishing the expansion mechanism for Mybatis configuration. Although the extension mechanism is important, it is at least not so inspiring if there is no truly practical extension function. This blog will give you a few examples of extensions.

The reason for this study of source code is the compatibility between Oracle and MySQL databases. For example, using double vertical lines as connectors in Oracle, and using CONCAT function in MySQL; for example, using DECODE function in Oracle, while only using standard CASE WHEN in MySQL; for example, DELETE FORM TABLE WHERE FIELD1 IN (SELECT FIELD1 FORM TABLE WHERE FIELD2=?) can be executed, but exceptions will be thrown in MySQL, etc.

Let’s start by solving these compatibility issues. First, you need to add database identity-related configurations to the configuration:

 <!-- Build Configuration object by yourself--> <bean id="mybatisConfig"/><bean id="sqlSessionFactory" p:dataSource-ref="dataSource"><!-- Inject mybatis configuration object--><property name="configuration" ref="mybatisConfig"/><!-- Automatically scan SqlMapper configuration file--><property name="mapperLocations"><array><value>classpath*:**/*.sqlmapper.xml</value></array></property><!-- Database product identification configuration--><property name="databaseIdProvider"><bean><property name="properties"><props><!-- It means that if the database product description contains the keyword MYSQL, mysql is used as the databaseId in Configuration. Mybatis native implementation keywords are case sensitive. I did not test Oracle and DB2 --><prop key="MySQL">mysql</prop><prop key="oracle">oracle</prop><prop key="H2">h2</prop><prop key="db2">db2</prop></props></property></bean>

1. Connector problem

1. Write SQL configuration function implementation class

 public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{//The default order level is set in the abstract parent class @Overridepublic String getName() {return "concat";}@Overridepublic String eval(String databaseId, String[] args) {if(args.length < 2){Throw.throwException("the concat function requires at least two arguments.");}if("mysql".equalsIgnoreCase(databaseId)){return "CONCAT("+Tool.STRING.join(args, ",")+")";}else{return Tool.STRING.join(args, "||");}}}

2. Register in the static code block of the SchemaHandlers class, or call the SchemaHandlers method in the startup initialization class.

 static {//Register StatementHandlerregister("cache-ref", new CacheRefStatementHandler());register("cache", new CacheStatementHandler());register("parameterMap", new ParameterMapStatementHandler());register("resultMap", new ResultMapStatementHandler());register("sql", new SqlStatementHandler());register("select|insert|update|delete", new CRUDStatementHandler());//Register the default namespace ScriptHandlerregister("trim", new TrimScriptHandler());register("where", new WhereScriptHandler());register("set", new SetScriptHandler());register("foreach", new ForEachScriptHandler());register("if|when", new IfScriptHandler());register("choose", new ChooseScriptHandler());//register("when", new IfScriptHandler());register("otherwise", new OtherwiseScriptHandler());register("bind", new BindScriptHandler());// Register the processor registerExtend("db", new DbStatementHandler(), new DbScriptHandler());// Register SqlConfigFunctionregister(new DecodeSqlConfigFunction());register(new ConcatSqlConfigFunction());// Register SqlConfigFunctionFactoryregister(new LikeSqlConfigFunctionFactory());}

In addition to registering ConcatSQLConfigFunction, the above code also has some other registration codes, which are given here and will be omitted below.

3. Modify the SqlMapper configuration

 <select id="selectString" resultType="string">select PARAM_NAME, $concat{PARAM_CODE, PARAM_NAME} AS CODE_NAME from BF_PARAM_ENUM_DEF<if test="null != paramName and '' != paramName">where PARAM_NAME LIKE $CONCAT{'%', #{paramName, jdbcType=VARCHAR}, '%'}</if></select>

4. Write interface class

 @Repositorypublic interface IExampleDao {public String selectString(@Param("paramName")String paramName);}

5. Write test classes

 @RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations={"classpath:spring/applicationContext.xml" })@Componentpublic class ExampleDaoTest {@Resourceprivate IExampleDao dao;@Testpublic void testSelectString(){String a = dao.selectString("Show");Assert.assertEquals("Show area", a);}}

6. Run as follows in MySQL and H2 respectively (adjust the mybatis log level to TRACE)

(1)MySQL

 20161108 00:12:55,235 [main]-[DEBUG] ==> Preparing: select PARAM_NAME, CONCAT(PARAM_CODE,PARAM_NAME) AS CODE_NAME from BF_PARAM_ENUM_DEF where PARAM_NAME LIKE CONCAT('%',?,'%') 20161108 00:12:55,269 [main]-[DEBUG] ==> Parameters: Display (String)20161108 00:12:55,287 [main]-[TRACE] <== Columns: PARAM_NAME, CODE_NAME20161108 00:12:55,287 [main]-[TRACE] <== Row: Display area, DISPLAY_AREA Display area20161108 00:12:55,289 [main]-[DEBUG] <== Total: 1

(2)H2

 20161108 00:23:08,348 [main]-[DEBUG] ==> Preparing: select PARAM_NAME, PARAM_CODE||PARAM_NAME AS CODE_NAME from BF_PARAM_ENUM_DEF where PARAM_NAME LIKE '%'||?||'%' 20161108 00:23:08,364 [main]-[DEBUG] ==> Parameters: Display (String)20161108 00:23:08,411 [main]-[TRACE] <== Columns: PARAM_NAME, CODE_NAME20161108 00:23:08,411 [main]-[TRACE] <== Row: Display area, DISPLAY_AREA Display area20161108 00:23:08,411 [main]-[DEBUG] <== Total: 1

As you can see, the compatibility issue of connectors has been solved.

In addition, we also found that writing is more troublesome when using LIKE keywords, so let's give it a new set of SQL configuration functions:

 public class LikeSqlConfigFunctionFactory implements ISqlConfigFunctionFactory{@Overridepublic Collection<ISqlConfigFunction> getSqlConfigFunctions() {return Arrays.asList(getLeftLikeSqlConfigFunction(), getRightLikeSqlConfigFunction(), getLikeSqlConfigFunction());}private ISqlConfigFunction getLeftLikeSqlConfigFunction(){return new AbstractLikeSqlConfigFunction(){@Overridepublic String getName() {return "llike";}@Overridepublic String eval(String arg) {return "LIKE $concat{'%',"+arg+"}";}};} private ISqlConfigFunction getRightLikeSqlConfigFunction(){return new AbstractLikeSqlConfigFunction(){@Overridepublic String getName() {return "rlike";}@Overrideprotected String eval(String arg) {return "LIKE $concat{"+arg+", '%'}";}};} private ISqlConfigFunction getLikeSqlConfigFunction(){return new AbstractLikeSqlConfigFunction(){@Overridepublic String getName() {return "like";}@Overrideprotected String eval(String arg) {return "LIKE $concat{'%',"+arg+", '%'}";}};} private abstract class AbstractLikeSqlConfigFunction extends AbstractSqlConfigFunction{@Overridepublic String eval(String databaseId, String[] args) {if(args.length != 1){Throw.throwException("the like function requires one and only one argument.");}return eval(args[0]);}protected abstract String eval(String arg);}}

Here, a set of SQL configuration functions are defined, with left similarity, right similarity and middle similarity matching, and the SQL configuration functions can also be nested. Therefore, the configuration file of SqlMapper is simplified to:

 <select id="selectString" resultType="string">select PARAM_NAME, $concat{PARAM_CODE, PARAM_NAME} AS CODE_NAME from BF_PARAM_ENUM_DEF<if test="null != paramName and '' != paramName">where PARAM_NAME $like{#{paramName, jdbcType=VARCHAR}}</if></select>

The run results are exactly the same.

If you still find it troublesome, because PARAM_NAME and paramName are camel-like correspondences, you can even add a fieldLike function and modify the configuration to

 where $fieldLike{#{PARAM_NAME, jdbcType=VARCHAR}}

If combined with the data dictionary, the jdbcType configuration can also be automatically generated:

 where $fieldLike{#{PARAM_NAME}}

In this case, if there are multiple parameters, there will be no ambiguity (or a newly defined configuration function $likes{} will be used to eliminate ambiguity), so multiple conditions can be simplified to:

 where $likes{#{PARAM_NAME, PARAM_NAME2, PARAM_NAME3}}

Of course, there are more diggable simplifications that are no longer just in the scope of compatibility, so we will not go further here.

2. DECODE function/CASE... WHEN

The DECODE function in Oracle is very convenient, and the syntax is as follows:

DECODE (condition, value 1, return value 1, value 2, return value 2,... value n, return value n[, default value])

Standard writing of equivalents:

 CASE Condition WHEN Value 1 THEN Return value 1WHEN Value 2 THEN Return value 2...WHEN Value n THEN Return value n[ELSE Default]END

Now let's implement a $decode configuration function:

 public class DecodeSqlConfigFunction extends AbstractSqlConfigFunction{@Overridepublic String getName() {return "decode";}@Overridepublic String eval(String databaseId, String[] args) {if(args.length < 3){Throw.throwException("the decode function requires at least three arguments.");}if("h2".equalsIgnoreCase(databaseId)){//When testing, use h2 instead of oracle, and modify it to oraclereturn in the official program "DECODE("+Tool.STRING.join(args, ",")+")";}else{StringBuffer sb = new StringBuffer();sb.append("CASE ").append(args[0]);int i=2, l = args.length;for(; i < l; i= i+2){sb.append(" WHEN ").append(args[i-1]).append(" THEN ").append(args[i]);}if(i == l){//When ending the loop, the two are equal to indicate that the last parameter is not used sb.append(" ELSE ").append(args[l-1]);}sb.append("END");return sb.toString();}}}

Then use SchemaHandlers to register and modify the configuration in SqlMapper:

 <select id="selectString" resultType="string">select PARAM_NAME, $decode{#{paramName}, '1', 'A', '2', 'B','C'} AS DECODE_TEST from BF_PARAM_ENUM_DEF<if test="null != paramName and '' != paramName">where PARAM_NAME $like{#{paramName, jdbcType=VARCHAR}}</if></select>

The tests are as follows:

(1) In H2 (replace Oracle with H2)

 20161108 06:53:29,747 [main]-[DEBUG] ==> Preparing: select PARAM_NAME, DECODE(?,'1','A','2','B','C') AS DECODE_TEST from BF_PARAM_ENUM_DEF where PARAM_NAME LIKE '%'||?||'%'

(2) In MySQL

 20161108 06:50:55,998 [main]-[DEBUG] ==> Preparing: select PARAM_NAME, CASE ? WHEN '1' THEN 'A' WHEN '2' THEN 'B' ELSE 'C' END AS DECODE_TEST from BF_PARAM_ENUM_DEF where PARAM_NAME LIKE '%'||?||'%'

The above is a detailed introduction to the extension and application of SqlMapper configuration in Mybatis introduced to you by the editor (1). I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!