奮斗了好幾個(gè)晚上調(diào)試程序，寫了好幾篇博客，終于建立起了Mybatis配置的擴(kuò)展機(jī)制。雖然擴(kuò)展機(jī)制是重要的，然而如果沒(méi)有真正實(shí)用的擴(kuò)展功能，那也至少是不那么鼓舞人心的，這篇博客就來(lái)舉幾個(gè)擴(kuò)展的例子。
這次研讀源碼的起因是Oracle和MySQL數(shù)據(jù)庫(kù)的兼容性，比如在Oracle中使用雙豎線作為連接符，而MySQL中使用CONCAT函數(shù)；比如Oracle中可以使用DECODE函數(shù)，而MySQL中只能使用標(biāo)準(zhǔn)的CASE WHEN；又比如Oracle中可以執(zhí)行DELETE FORM TABLE WHERE FIELD1 IN (SELECT FIELD1 FORM TABLE WHERE FIELD2=?)，但是MySQL中會(huì)拋出異常，等等。
下面就從解決這些兼容性問(wèn)題開(kāi)始，首先需要在配置中添加數(shù)據(jù)庫(kù)標(biāo)識(shí)相關(guān)的配置：
|
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
|
<!-- 自行構(gòu)建Configuration對(duì)象 --> <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配置對(duì)象 --><property name="configuration" ref="mybatisConfig"/><!-- 自動(dòng)掃描SqlMapper配置文件 --><property name="mapperLocations"><array><value>classpath*:**/*.sqlmapper.xml</value></array></property><!-- 數(shù)據(jù)庫(kù)產(chǎn)品標(biāo)識(shí)配置 --><property name="databaseIdProvider"><bean class="org.apache.ibatis.mapping.VendorDatabaseIdProvider"><property name="properties"><props><!-- 意思是如果數(shù)據(jù)庫(kù)產(chǎn)品描述中包含關(guān)鍵字MYSQL，則使用mysql作為Configuration中的databaseId，mybatis原生的實(shí)現(xiàn)關(guān)鍵字區(qū)分大小寫，我沒(méi)有測(cè)試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> |
一、連接符問(wèn)題
1、編寫SQL配置函數(shù)實(shí)現(xiàn)類
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
public class ConcatSqlConfigFunction extends AbstractSqlConfigFunction{//抽象父類中設(shè)定了默認(rèn)的order級(jí)別@Overridepublic String getName() {return "concat";}@Overridepublic 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類的靜態(tài)代碼塊中注冊(cè)，或者在啟動(dòng)初始化類中調(diào)用SchemaHandlers的方法注冊(cè)
|
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 {//注冊(cè)默認(rèn)命名空間的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());//注冊(cè)默認(rèn)命名空間的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());// 注冊(cè)自定義命名空間的處理器registerExtend("db", new DbStatementHandler(), new DbScriptHandler());// 注冊(cè)SqlConfigFunctionregister(new DecodeSqlConfigFunction());register(new ConcatSqlConfigFunction());// 注冊(cè)SqlConfigFunctionFactoryregister(new LikeSqlConfigFunctionFactory());} |
上面代碼除了注冊(cè)ConcatSQLConfigFunction外，還有一些其它的注冊(cè)代碼，這里一并給出，下文將省略。
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
|
@Repositorypublic interface IExampleDao {public String selectString(@Param("paramName")String paramName);} |
5、編寫測(cè)試類
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations={"classpath:spring/applicationContext.xml"})@Componentpublic class ExampleDaoTest {@Resourceprivate IExampleDao dao;@Testpublic void testSelectString(){String a = dao.selectString("顯示");Assert.assertEquals("顯示區(qū)域", a);}} |
6、分別在MySQL和H2中運(yùn)行如下（將mybatis日志級(jí)別調(diào)整為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_NAME20161108 00:12:55,287 [main]-[TRACE] <== Row: 顯示區(qū)域, DISPLAY_AREA顯示區(qū)域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_NAME20161108 00:23:08,411 [main]-[TRACE] <== Row: 顯示區(qū)域, DISPLAY_AREA顯示區(qū)域20161108 00:23:08,411 [main]-[DEBUG] <== Total: 1 |
可以看到，已經(jīng)解決連接符的兼容性問(wèn)題了。
另外，我們也發(fā)現(xiàn)，使用LIKE關(guān)鍵字時(shí)，寫起來(lái)比較麻煩，那我們就給它一組新的SQL配置函數(shù)吧：
|
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{@Overridepublic Collection<ISqlConfigFunction> getSqlConfigFunctions() {return Arrays.asList(getLeftLikeSqlConfigFunction(),getRightLikeSqlConfigFunction(),getLikeSqlConfigFunction());}private ISqlConfigFunction getLeftLikeSqlConfigFunction(){return new AbstractLikeSqlConfigFunction(){@Overridepublic String getName() {return "llike";}@Overrideprotected 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 require one and only one argument.");}return eval(args[0]);}protected abstract String eval(String arg);}} |
這里，定義了一組SQL配置函數(shù)，左相似，右相似以及中間相似匹配，并且SQL配置函數(shù)還可以嵌套。于是，SqlMapper的配置文件簡(jiǎn)化為：
|
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> |
運(yùn)行結(jié)果完全相同。
如果還覺(jué)得麻煩，因?yàn)镻ARAM_NAME和paramName是駝峰式對(duì)應(yīng)，甚至還可以添加一個(gè)fieldLike函數(shù)，并將配置修改為
|
1
|
where $fieldLike{#{PARAM_NAME, jdbcType=VARCHAR}} |
如果再結(jié)合數(shù)據(jù)字典，jdbcType的配置也可自動(dòng)生成：
|
1
|
where $fieldLike{#{PARAM_NAME}} |
這種情形下，如果有多個(gè)參數(shù)，也不會(huì)出現(xiàn)歧義（或者新定義一個(gè)配置函數(shù)$likes{}消除歧義），于是可將多個(gè)條件簡(jiǎn)化成：
|
1
|
where $likes{#{PARAM_NAME, PARAM_NAME2， PARAM_NAME3}} |
當(dāng)然，還有更多可挖掘的簡(jiǎn)化，已經(jīng)不止是兼容性的范疇了，這里就不再進(jìn)一步展開(kāi)了。
二、DECODE函數(shù)/CASE ... WHEN
Oracle中的DECODE函數(shù)非常方便，語(yǔ)法如下：
DECODE(條件,值1,返回值1,值2,返回值2,...值n,返回值n[,缺省值])
等價(jià)的標(biāo)準(zhǔn)寫法：
|
1
2
3
4
5
6
7
|
CASE 條件WHEN 值1 THEN 返回值1WHEN 值2 THEN 返回值2...WHEN 值n THEN 返回值n[ELSE 缺省值]END |
現(xiàn)在我們來(lái)實(shí)現(xiàn)一個(gè)$decode配置函數(shù)：
|
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{@Overridepublic String getName() {return "decode";}@Overridepublic String eval(String databaseId, String[] args) {if(args.length < 3){Throw.throwException("the decode function require at least three arguments.");}if("h2".equalsIgnoreCase(databaseId)){//測(cè)試時(shí)，使用h2代替oracle，正式程序中修改為oraclereturn "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){//結(jié)束循環(huán)時(shí)，兩者相等說(shuō)明最后一個(gè)參數(shù)未使用sb.append(" ELSE ").append(args[l-1]);}sb.append(" END");return sb.toString();}}} |
然后使用SchemaHandlers注冊(cè)，修改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> |
測(cè)試如下：
（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配置的擴(kuò)展與應(yīng)用詳細(xì)介紹(1)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)服務(wù)器之家網(wǎng)站的支持！
原文鏈接：http://www.cnblogs.com/linjisong/p/6041239.html
