如何进行ibatis动态多条件组合查询以及模糊查询(oracle,mysql)

(转载自：)

 

如何进行ibatis动态多条件组合查询以及模糊查询(oracle,mysql) 

这几天在学习使用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 中的配置为 
     
     

       1. 

          <select id="getPersonsByName" resultClass="com.unmi.Person">       
       2. 

                select id as id,name as name,passwd as passwd from person       
       3. 

                  <dynamic prepend="WHERE">       
       4. 

                      <isNotNull prepend="AND" property="name">       
       5. 

                            (name like #name#)       
       6. 

                      </isNotNull>       
       7. 

                  </dynamic>       
       8. 

          </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>   
    再用如下的代码调用 
     
     

       1. 

          Person person = new Person();       
       2. 

          person.setName("unmi");       
       3. 

          List list = sqlMap.queryForList("getPersonsByName", person);     

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

       1. 

          select * from person where name like 'unmi'   

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

       1. 

          select * from person where name = 'unmi'   

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

       1. 

          select * from person where name like 'unmi%';   
       2. 

          select * from person where name like '%unmi';   
       3. 

          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 的模糊查询的配置如下（两种方式） 
     
     

       1. 

          <select id="getPersonsByName" resultClass="com.unmi.Person">  
       2. 

                select id as id,name as name,passwd as passwd from person   
       3. 

                  <dynamic prepend="WHERE">  
       4. 

                      <isNotNull prepend="AND" property="name">  
       5. 

                            (name like '%$name$%')   
       6. 

                          <!-- (name like '%'||#name#||'%') -->  
       7. 

                      </isNotNull>  
       8. 

              </dynamic>  
       9. 

          </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被转换成大写，还需再研究研究，对于以上两个疑问必要时还可以发挥一下。也要权衡一下花那个时间值不值。 



受教了，你已经写的很详细了。 
在ibatis中 # 和 $ 还是有区别，我的经验是 
如果传来的是单一变量要用#value#的形式， 
例如value为一个整数或者一个字段值，而$value$通常是用来代表一个长的字符串的，例如value是 "WHERE id = 4 ORDER BY tid" 这样的情况。 
还有一个情况用到$，例如 WHERE id IN ( $cids$ ) 
cids是“1，2，3 ”