Mybatis中SqlMapper配置的扩展与应用(1)
奋斗了好几个晚上调试程序,写了好几篇博客,终于建立起了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中会抛出异常,等等。下面就从解决这些兼容性问题开始,首先需要在配置中添加数据库标识相关的配置:
<!-- 自行构建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配置函数实现类
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的方法注册
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配置
<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接口类
@Repository public interface IExampleDao { public String selectString(@Param("paramName")String paramName); }
5、编写测试类
@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
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
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配置函数吧:
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的配置文件简化为:
<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函数,并将配置修改为
where $fieldLike{#{PARAM_NAME, jdbcType=VARCHAR}}
如果再结合数据字典,jdbcType的配置也可自动生成:
where $fieldLike{#{PARAM_NAME}}
这种情形下,如果有多个参数,也不会出现歧义(或者新定义一个配置函数$likes{}消除歧义),于是可将多个条件简化成:
where $likes{#{PARAM_NAME, PARAM_NAME2, PARAM_NAME3}}
当然,还有更多可挖掘的简化,已经不止是兼容性的范畴了,这里就不再进一步展开了。
二、DECODE函数/CASE ... WHEN
Oracle中的DECODE函数非常方便,语法如下:
DECODE(条件,值1,返回值1,值2,返回值2,...值n,返回值n[,缺省值])
等价的标准写法:
CASE 条件 WHEN 值1 THEN 返回值1 WHEN 值2 THEN 返回值2 ... WHEN 值n THEN 返回值n [ELSE 缺省值] END
现在我们来实现一个$decode配置函数:
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中配置:
<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)
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中
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 '%'||?||'%'
郴江幸自绕郴山,为谁流下潇湘去?
欲将心事付瑶琴,知音少,弦断有谁听?
倩何人,唤取红巾翠袖,揾英雄泪!
零落成泥碾作尘,只有香如故!