The extension and application of SqlMapper configuration in Mybatis are described in detail in of 1

After several nights of debugging and several blog posts, I finally set up the extension mechanism for the Mybatis configuration. While the extension mechanism is important, it is at least less encouraging if there is no really useful extension capability. This blog will give you a few examples of extensions.

The reason for reading the source code this time is the compatibility between Oracle and MySQL databases. For example, Oracle USES double vertical lines as connectors, while MySQL USES CONCAT functions. For example, Oracle can use DECODE function, while MySQL can only use standard CASE WHEN. For example, DELETE FORM TABLE WHERE FIELD1 IN (SELECT FIELD1 FORM TABLE WHERE FIELD2=?) , but MySQL throws exceptions, and so on.

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

<!--  Building on its own Configuration object  --> 
<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 A configuration object  -->
<property name="configuration" ref="mybatisConfig"/>
<!--  Automatic scanning SqlMapper The configuration file  -->
<property name="mapperLocations">
<array>
<value>classpath*:**/*.sqlmapper.xml</value>
</array>
</property>
<!--  Database product identity configuration  -->
<property name="databaseIdProvider">
<bean class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
<property name="properties">
<props>
<!--  This means if the database product description contains keywords MYSQL , use the mysql As a Configuration In the databaseId . mybatis Native implementation keyword 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>
</property>
</bean>

1. Conjunction problem

1. Write the SQL configuration function implementation class

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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 the static code block of the SchemaHandlers class, or call the SchemaHandlers method in the startup initialization class

static {
// Register the default namespace StatementHandler
register("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 ScriptHandler
register("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 the handler for the custom namespace 
registerExtend("db", new DbStatementHandler(), new DbScriptHandler());
//  registered SqlConfigFunction
register(new DecodeSqlConfigFunction());
register(new ConcatSqlConfigFunction());
//  registered SqlConfigFunctionFactory
register(new LikeSqlConfigFunctionFactory());
}

In addition to registering ConcatSQLConfigFunction, there are some other registration codes in the above code, which are given here and 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>

Write the dao interface class

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

5. Write test classes

@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(" According to ");
Assert.assertEquals(" Display area ", a);
}
}

6. Run in MySQL and H2 respectively as follows (adjust the log level of mybatis 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:  According to (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:  According to (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 concatenation compatibility issue has been resolved.

In addition, we also found that when using the LIKE keyword, it is difficult 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 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 similar matches, and SQL configuration functions can also be nested. Thus, the configuration file for 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 results are exactly the same.

If that's still a problem, because PARAM_NAME and paramName are hump counterparts, you can even add an fieldLike function and change the configuration to

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

0

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

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

1

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

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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

There are, of course, many more mining simplifications, not just for compatibility, but for the next step.

2. DECODE function /CASE... WHEN

The DECODE function in Oracle is very convenient. The syntax is as follows:

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

Equivalent standard writing method:

CASE  conditions 
WHEN  value 1 THEN  The return value 1
WHEN  value 2 THEN  The return value 2
...
WHEN  value n THEN  The return value n
[ELSE  The default value ]
END

Now let's implement a $decode configuration function:

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

4

Then register with SchemaHandlers and modify the configuration in SqlMapper:

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

5

The tests are as follows:

(1) H2 (substitute H2 for Oracle)

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

6

(2) in MySQL

public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{// The default is set in the abstract parent class order level 
@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, "||");
}
}
}

7