The extension of sqlmapper configuration in MyBatis and its application in detail (1) _java

Struggled for several nights debugging procedures, wrote several blog, and finally set up a mybatis configuration extension mechanism. While it is important to extend the mechanism, it is at least less inspiring if there are no truly useful extensions, and this blog will give you a few examples of extensions.

The source of this study is oracle and MySQL database compatibility, such as the use of double vertical bar in Oracle as a connector, and MySQL using the concat function, such as Oracle can use the Decode function, and MySQL only use the standard case When; for example, in Oracle, you can execute the Delete FORM table where FIELD1 in (SELECT FIELD1 FORM table where field2=?), but it throws an exception in MySQL, and so on.

Let's begin by addressing these compatibility issues by adding a database identity-related configuration to the configuration:

<!--construct Configuration objects--> <bean id= "Mybatisconfig" class= " Org.dysd.dao.mybatis.schema.SchemaConfiguration "/> <bean id=" sqlsessionfactory "p:datasource-ref=" DataSource "class=" Org.dysd.dao.mybatis.schema.SchemaSqlSessionFactoryBean > <!--injection MyBatis Configuration Object--> < Property name= "Configuration" ref= "Mybatisconfig"/> <!--auto Scan Sqlmapper profile--> <property name= " Mapperlocations "> <array> <value>classpath*:* */*.sqlmapper.xml</value> </array> </ property> <!--database Product identification configuration--> <property name= "Databaseidprovider" > <bean class= " Org.apache.ibatis.mapping.VendorDatabaseIdProvider "> <property name=" Properties "> <props> <!-- It means that if the database product description contains the keyword MySQL, then use MySQL as the Databaseid,mybatis native implementation keyword in configuration to match the case, 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> </property> </bean>

First, the connector problem

1. Write SQL configuration Function implementation class

The public class Concatsqlconfigfunction extends abstractsqlconfigfunction{//The default order level is set in the abstract parent class
@Override
Public String GetName () {return
"concat";
}
@Override public
String eval (string databaseId, string[] args) {
if (Args.length < 2) {
Throw.throwexception ("The concat function require at least two arguments.");
}
if ("MySQL". Equalsignorecase (DatabaseId)) {return
"CONCAT (" +tool.string.join (args, ",") + ")";
} else{return
Tool.STRING.join (args, "| |");}}

2, register in static code block of Schemahandlers class, or call Schemahandlers method registration in Startup initialization class

static {//Statementhandler Register of Default namespaces ("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 ());
Registers the Scripthandler register for the default namespace ("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 ());
Registers a custom namespace with the processor Registerextend ("DB", New Dbstatementhandler (), New Dbscripthandler ());
Registered Sqlconfigfunction Register (new Decodesqlconfigfunction ());
Register (new Concatsqlconfigfunction ()); // Registered Sqlconfigfunctionfactory Register (new Likesqlconfigfunctionfactory ()); }

The above code in addition to register concatsqlconfigfunction, there are some other registration code, here together, the following will be omitted.

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 $CO ncat{'% ', #{paramname, jdbctype=varchar}, '% '}
</if>
</select>

4. Write DAO interface class

@Repository Public
interface Iexampledao {public
string selectstring (@Param ("ParamName") string paramname) ;
}

5. Write Test class

@RunWith (Springjunit4classrunner.class)
@ContextConfiguration (locations={
"classpath:spring/ Applicationcontext.xml " 
})
@Component public
class Exampledaotest {
@Resource
private Iexampledao DAO;
@Test public
void Testselectstring () {
String a = dao.selectstring ("display");
Assert.assertequals ("Show Area", a);
}
}

6, respectively in MySQL and H2 run as follows (the MyBatis log level is adjusted 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_name
20161108 00:12:55,287 [Main]-[trace] <== Row: Display area, Display_area display area
20161108 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_name
20161108 00:23:08,411 [main]-[trace] <== Row: Display area, Display_area display area
20161108 00:23:08,411 [Main]-[debug] <== total:1

As you can see, the compatibility issue has been resolved for the connector.

In addition, we also found that when you use the LIKE keyword, it is more cumbersome to write, so let's give it a new set of SQL configuration functions:

public class Likesqlconfigfunctionfactory implements isqlconfigfunctionfactory{@Override public collection< Isqlconfigfunction> getsqlconfigfunctions () {return arrays.aslist (getleftlikesqlconfigfunction),
Getrightlikesqlconfigfunction (), getlikesqlconfigfunction ());
Private Isqlconfigfunction getleftlikesqlconfigfunction () {return new abstractlikesqlconfigfunction () {@Override public string GetName () {return "Llike";} @Override protected String eval (string arg) {return "like $concat {'% '," +arg+ "
}";
}
};
Private Isqlconfigfunction getrightlikesqlconfigfunction () {return new abstractlikesqlconfigfunction () {@Override public string GetName () {return "Rlike";} @Override protected String eval (string arg) {return "like $concat {" +arg+ ", '%
'}";
}
}; Private Isqlconfigfunction getlikesqlconfigfunction () {Return to New Abstractlikesqlconfigfunction () {@Override public String GetName () {return "like";} @Override protected String eval (string arg) {return] like $concat {'% ', ' +arg+ ', '%'}";
}
}; Private abstract class Abstractlikesqlconfigfunction extends abstractsqlconfigfunction{@Override public String eval (  String databaseId, string[] args {if (args.length!= 1) {throw.throwexception ("The like function require one and only one
argument. ");}
Return eval (args[0]);
Protected abstract String eval (string arg); }
}

Here, a set of SQL configuration functions is defined, left similar, right similar, and middle similarity matching, and SQL configuration functions can be nested. As a result, the Sqlmapper configuration file 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 results of the run are exactly the same.

If you still feel trouble, because Param_name and paramname are hump-style counterparts, you can even add a fieldlike function and change the configuration to

Where $fieldLike {#{param_name, Jdbctype=varchar}}

If the data dictionary is combined, the Jdbctype configuration can also be automatically generated:

where $fieldLike {#{param_name}}

In this case, if there are multiple arguments, there is no ambiguity (or a new definition of a configuration function $likes{} eliminates ambiguity), so multiple conditions can be simplified to:

Where $likes {#{param_name, param_name2, Param_name3}}

There are, of course, more streamlined simplifications, which are more than just compatibility, and no further expansion.

Second, decode function/case ... When

The Decode function in Oracle is very handy 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]

Equivalence of standard wording:

Case condition when
value 1 THEN return value 1 when
value 2 THEN return value 2
...
When value n THEN return value n
[ELSE default] End

Now we're going to implement a $decode configuration function:

public class Decodesqlconfigfunction extends abstractsqlconfigfunction{
@Override public
String GetName () { Return
"decode";
}
@Override public
String eval (string databaseId, string[] args) {
if (Args.length < 3) {
Throw.throwexception ("The Decode function require at least three arguments.");
}
if ("H2". Equalsignorecase (DatabaseId)) {//When testing, use H2 instead of Oracle, the official program modifies the Oracle return
"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) {//end loop, both equality indicates that the last parameter did not use
sb.append ("ELSE"). Append (Args[l-1]);
Sb.append ("End");
return sb.tostring ();}}

Then use schemahandlers registration to 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 test is as follows:

(1) H2 (with H2 instead of Oracle)

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 ' if ' 2 ' THEN ' B ' ELSE ' C ' end as Decode_test from Bf_param_enum_def where param_name like '% ' | |?| | ' %'

The above is a small set to introduce the expansion and application of Sqlmapper configuration in MyBatis (1), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!