奋斗了好几个晚上调试程序,写了好几篇博客,终于建立起了Mybatis配置的扩展机制。虽然扩展机制是重要的,然而如果没有真正实用的扩展功能,那也至少是不那么鼓舞人心的,这篇博客就来举几个扩展的例子。
这次研读源码的起因是Oracle和MySQL数据库的兼容性,比如在Oracle中使用双竖线作为连接符,而MySQL中使用CONCAT函数;比如Oracle中可以使用DECODE函数,而MySQL中只能使用标准的CASE WHEN;又比如Oracle中可以执行DELETE FORM TABLE WHERE FIELD1 IN (SELECT FIELD1 FORM TABLE WHERE FIELD2=?),但是MySQL中会抛出异常,等等。
下面就从解决这些兼容性问题开始,首先需要在配置中添加数据库标识相关的配置:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
<!-- 自行构建Configuration对象 -->
<bean id= "mybatisConfig" class = "org.dysd.dao.mybatis.schema.SchemaConfiguration" />
<bean id= "sqlSessionFactory" p:dataSource-ref= "dataSource"
class = "org.dysd.dao.mybatis.schema.SchemaSqlSessionFactoryBean" >
<!-- 注入mybatis配置对象 -->
<property name= "configuration" ref= "mybatisConfig" />
<!-- 自动扫描SqlMapper配置文件 -->
<property name= "mapperLocations" >
<array>
<value>classpath*:**/*.sqlmapper.xml</value>
</array>
</property>
<!-- 数据库产品标识配置 -->
<property name= "databaseIdProvider" >
<bean class = "org.apache.ibatis.mapping.VendorDatabaseIdProvider" >
<property name= "properties" >
<props>
<!-- 意思是如果数据库产品描述中包含关键字MYSQL,则使用mysql作为Configuration中的databaseId,mybatis原生的实现关键字区分大小写,我没有测试Oracle和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、编写SQL配置函数实现类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{ //抽象父类中设定了默认的order级别
@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、在SchemaHandlers类的静态代码块中注册,或者在启动初始化类中调用SchemaHandlers的方法注册
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
static {
//注册默认命名空间的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());
//注册默认命名空间的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());
// 注册自定义命名空间的处理器
registerExtend( "db" , new DbStatementHandler(), new DbScriptHandler());
// 注册SqlConfigFunction
register( new DecodeSqlConfigFunction());
register( new ConcatSqlConfigFunction());
// 注册SqlConfigFunctionFactory
register( new LikeSqlConfigFunctionFactory());
}
|
上面代码除了注册ConcatSQLConfigFunction外,还有一些其它的注册代码,这里一并给出,下文将省略。
3、修改SqlMapper配置
1
2
3
4
5
6
7
|
<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、编写dao接口类
1
2
3
4
|
@Repository
public interface IExampleDao {
public String selectString( @Param ( "paramName" )String paramName);
}
|
5、编写测试类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@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( "显示" );
Assert.assertEquals( "显示区域" , a);
}
}
|
6、分别在MySQL和H2中运行如下(将mybatis日志级别调整为TRACE)
(1)MySQL
1
2
3
4
5
|
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: 显示(String)
20161108 00 : 12 : 55 , 287 [main]-[TRACE] <== Columns: PARAM_NAME, CODE_NAME
20161108 00 : 12 : 55 , 287 [main]-[TRACE] <== Row: 显示区域, DISPLAY_AREA显示区域
20161108 00 : 12 : 55 , 289 [main]-[DEBUG] <== Total: 1
|
(2)H2
1
2
3
4
5
|
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: 显示(String)
20161108 00 : 23 : 08 , 411 [main]-[TRACE] <== Columns: PARAM_NAME, CODE_NAME
20161108 00 : 23 : 08 , 411 [main]-[TRACE] <== Row: 显示区域, DISPLAY_AREA显示区域
20161108 00 : 23 : 08 , 411 [main]-[DEBUG] <== Total: 1
|
可以看到,已经解决连接符的兼容性问题了。
另外,我们也发现,使用LIKE关键字时,写起来比较麻烦,那我们就给它一组新的SQL配置函数吧:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
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);
}
}
|
这里,定义了一组SQL配置函数,左相似,右相似以及中间相似匹配,并且SQL配置函数还可以嵌套。于是,SqlMapper的配置文件简化为:
1
2
3
4
5
6
7
|
<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>
|
运行结果完全相同。
如果还觉得麻烦,因为PARAM_NAME和paramName是驼峰式对应,甚至还可以添加一个fieldLike函数,并将配置修改为
1
|
where $fieldLike{#{PARAM_NAME, jdbcType=VARCHAR}}
|
如果再结合数据字典,jdbcType的配置也可自动生成:
1
|
where $fieldLike{#{PARAM_NAME}}
|
这种情形下,如果有多个参数,也不会出现歧义(或者新定义一个配置函数$likes{}消除歧义),于是可将多个条件简化成:
1
|
where $likes{#{PARAM_NAME, PARAM_NAME2, PARAM_NAME3}}
|
当然,还有更多可挖掘的简化,已经不止是兼容性的范畴了,这里就不再进一步展开了。
二、DECODE函数/CASE ... WHEN
Oracle中的DECODE函数非常方便,语法如下:
DECODE(条件,值1,返回值1,值2,返回值2,...值n,返回值n[,缺省值])
等价的标准写法:
1
2
3
4
5
6
7
|
CASE 条件
WHEN 值 1 THEN 返回值 1
WHEN 值 2 THEN 返回值 2
...
WHEN 值n THEN 返回值n
[ELSE 缺省值]
END
|
现在我们来实现一个$decode配置函数:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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)){ //测试时,使用h2代替oracle,正式程序中修改为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){ //结束循环时,两者相等说明最后一个参数未使用
sb.append( " ELSE " ).append(args[l- 1 ]);
}
sb.append( " END" );
return sb.toString();
}
}
}
|
然后使用SchemaHandlers注册,修改SqlMapper中配置:
1
2
3
4
5
6
7
|
<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>
|
测试如下:
(1)H2中(以H2代替Oracle)
1
|
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)MySQL中
1
|
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 '%' ||?|| '%'
|
以上所述是小编给大家介绍的Mybatis中SqlMapper配置的扩展与应用详细介绍(1),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!
原文链接:http://www.cnblogs.com/linjisong/p/6041239.html