Ibatis sql书写经验2 模糊查询+动态查询

这几天在学习使用IBATIS突然要使用模糊查询,以及动态多个条件查询,按照自己的想法试了很久,都没解决这个问题.

首先是模糊查 询的问题,开始时我使用如下条件:select * from user where name like '%#value#%'. 可是怎么也不行,好像还报错了.后来在网上找到了解决方法,就是使用$来代替#号.

1>写成: like '%$value$%' 就可以了,<!--  模糊查询不能用#,#是用prepareStatement的?插入参数,$是文本替换  --> ,

2>同时还找到另一个方法,但是那个 方法我试了很久,就是不行,方法为: like '%' || #value# || '%'  , 查询出来的结果居然是全部.后来在网上有人说,这个写法是oracle的写法,

3> 如果是mysql,则应该写成: name like CONCAT('%',#value:VARCHAR#,'%')  ,不过我没试用过,反正有一个方法成功就可以了.

第 一个方法我试用成功,后面的也就没试过,有兴趣的朋友可以试试

第二个大问题就是多条件组合查询,开始时,我也在想这个问题,总不能为每一个 查询都写一个SQL配制吧,这样太........后来参考一些文档,发现,原来IBATIS里提供了动态映射.示例如下:

 <!--
在ibatis中使用安全的拼接语句,动态查询
ibatis比JDBC的优势之一,安全高效
说明文字在注释中
-->


   < select  id ="selectAllProducts"  parameterClass ="Product"  resultMap ="ProductResult" >
    select id,note from Product
        < dynamic  prepend ="WHERE" >
        <!--  isNotNull判断参数是否存在,Integer类型  -->
             < isNotNull  property ="id" >
                 <!--  isGreaterThan判断参数是否大于 compareValue,isGreaterEquals是大于等于  -->
                 < isGreaterThan  prepend =" and "  property ="id"  compareValue ="0" >
                id = #id#
                 </ isGreaterThan >
             </ isNotNull >
             <!--  isNotEmpty判断字串不为空,isEmpty可以判断字串为空  -->
             < isNotEmpty  prepend =" and "  property ="note" >
             <!--  模糊查询不能用#,#在是用prepareStatement的?插入参数,$是文 本替换  -->
            note like '%$note$%'
             </ isNotEmpty >
         </ dynamic >
   </ select >
  
用Map传参数
  
   < select  id ="selectAllProducts"  parameterClass ="java .util.HashMap"  resultMap ="ProductResult" >
    select id,note from Product
        < dynamic  prepend ="WHERE" >
        <!--  isPropertyAvailable判断属性是否有效  -->
           < isPropertyAvailable  property ="id" >
             < isNotNull  property ="id" >
                 <!--  isLessThan判断参数是否小于 compareValue,isLessEquals是小于等于  -->
                 < isLessThan  prepend =" and "  property ="id"  compareValue ="10" >
                id = #id#
                 </ isLessThan >
             </ isNotNull >
           </ isPropertyAvailable >
         </ dynamic >
   </ select >
  <!-- ------------------------------------------------------------------ 几个常用属性 -->

< isPropertyAvailable >  属性是存在
< isNotPropertyAvailable >  属性不存在
< isNull >  属性值是null
< isEmpty >  判断 Collection.size < 1 或String.length()<1
<isEqual >  等于
< isNotEqual >  不等于
< isGreaterThan >  大于
< isGreaterEqual >  大于等于
< isLessThan >  小于
< isLessEqual >  小于等于

 

 

以 下是引用一个文章,大家可以参考一下 http://hi.baidu.com/edmond80/blog/item/44b31afa42aef18b9f51467e.html

iBatis 开发指南告诉我们，当 Person 对象的 name 属性不为 null 时启用 name 查询条件在映射文件 person.xml 中的配置为

<
select
 
id
=
"getPersonsByName"
 
resultClass
=
"com.unmi.Person"
>
      select id as id,name as name,passwd 
as passwd from person
        
<
dynamic
 
prepend
=
"WHERE"
>
            
<
isNotNull
 
prepend
=
"AND"
 
property
=
"name"
>
                  (name like 
#name#)
            
</
isNotNull
>
        
</
dynamic
>
</
select
>

<select id="getPersonsByName" resultClass="com.unmi.Person">           select id as id,name as name,passwd as passwd from person               <dynamic prepend="WHERE">                   <isNotNull prepend="AND" property="name">                       (name like #name#)                   </isNotNull>               </dynamic>      </select>   
再用如下的代码调用

Person person = 
new
 Person();
person.setName(
"unmi"
);
List list = 
sqlMap.queryForList(
"getPersonsByName"
,
 person);

Person person = new Person();      person.setName("unmi");      List list = sqlMap.queryForList("getPersonsByName", person);   
执行效果翻译成 sql 语句就是

select
 * 
from
 person 
where

 
name
 
like

 'unmi'

select * from person where name like 'unmi'
这实际上是一个完全匹配的查询，与用等号写成如下语句是一致的

select
 * 
from
 person 
where

 
name
 = 'unmi'

select * from person where name = 'unmi'
我们之所 以要用 like 谓词，一般都想实现模糊查询，比如说 name 以 'unmi' 开始、结束或包含 'unmi' 的记录，如下

select
 * 
from
 person 
where

 
name
 
like

 'unmi%';
select
 * 
from

 person 
where
 
name
 
like

 '%unmi';
select
 * 
from

 person 
where
 
name
 
like

 '%unmi%';

select * from person where name like 'unmi%'; select * from person where name like '%unmi'; select * from person where name like '%unmi%';
也就是如上的 like 语义在 person.xml中应该怎么表述呢？我曾经是想当然的尝试把
(name like #name#) 写成    (name like '%#name#%')    或 (name like %#name#%) ，都没法通过，分别报错
java .sql.SQLException : Invalid argument in JDBC call: parameter index out of range: 1
和
java .sql.SQLException : Unexpected token: % in statement [     select id......
那么正确的写法 是什么呢？在网上找到一个解答 How do I use LIKE in my queries ，方法有两种
1. 是把上面 (name like '%#name#%') 的 # 换成 $, 也就是 (name like '%$name$%')
2. 是用 || 连接字符串的方式，写成 (name like '%' || #name# || '%')
但却不能写成 (name like '%'||$name$||'%') ，不能又要出错
java .sql.SQLException : Column not found: UNMI in statement [select id......
总结一下，在 iBatis 中用 like 的模糊查询的配置如下（两种方式）

<
select
 
id
=
"getPersonsByName"

 
resultClass
=
"com.unmi.Person"
>
      select id as id,name as name,passwd 
as passwd from person
        
<
dynamic
 
prepend
=
"WHERE"
>
            
<
isNotNull
 
prepend
=
"AND"
 
property
=
"name"
>
                  (name like 
'%$name$%')
                
<!-- (name like '%'||#name#||'%') -->
            
</
isNotNull
>
    
</
dynamic
>
</
select
>

<select id="getPersonsByName" resultClass="com.unmi.Person">       select id as id,name as name,passwd as passwd from person           <dynamic prepend="WHERE">               <isNotNull prepend="AND" property="name">                   (name like '%$name$%')                   <!-- (name like '%'||#name#||'%') -->               </isNotNull>       </dynamic> </select>
不知细心的诸位注意到没有，这同时也是我在组织上面文字时产生的疑问：
1. 写成 (name like '%'||$name$||'%') 为什就不行呢？# 和 $ 有什么区别呢？
2. 还有明明是写成的 unmi，为什么报错的时候又是全大写的 UNMI 呢？
具体的异同我们可能还需从源代码中找，简单的只要知道，$name$ 是字面意义的替换，这种形式要注意 SQL 注入的漏洞；#name# 是带类型的替换。至于unmi被转换成大写，还需再研究研究，对于以上两个疑问必要时还可以发挥一下。也要权衡一下花那个时间值不值。

参 考资料：
      1. How do I use LIKE in my queries