欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mybatis中SqlMapper配置的扩展与应用详细介绍(1)

程序员文章站 2024-03-12 14:01:26
奋斗了好几个晚上调试程序,写了好几篇博客,终于建立起了mybatis配置的扩展机制。虽然扩展机制是重要的,然而如果没有真正实用的扩展功能,那也至少是不那么鼓舞人心的,这篇博...

奋斗了好几个晚上调试程序,写了好几篇博客,终于建立起了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 '%'||?||'%'

以上所述是小编给大家介绍的mybatis中sqlmapper配置的扩展与应用详细介绍(1),希望对大家有所帮助