ibatis的动态sql

1.介绍

 1 <select id="getUsers"
 2 
 3 parameterClass="user"
 4 
 5 resultMap="get-user-result"> 
 6 
 7 select
 8 
 9 id,
10 
11 name,
12 
13 sex
14 
15 from t_user
16 
17 <dynamic prepend="WHERE">
18 
19    <isNotEmpty prepend="AND" property="name">
20 
21        name like #name#
22 
23    </isNotEmpty>
24 
25    <isNotEmpty prepend="AND" property="address">
26 
27        address like #address#
28 
29    </isNotEmpty>
30 
31 </dynamic>
32 
33 </select>

通过dynamic 节点，可以定义了一个动态的WHERE 子句。此WHERE 子句中将可能包含两个针对name 和address 字段的判断条件。而这两个字段是否加入检索取决于用户所提供的查询条件。

这个节点对应的语义是，如果参数类的”name”属性非空（isNotEmpty，即非空字符串””），则在生成的SQL Where字句中包括判定条件(name like #name#)，其中#name#将以参数类的name属性值填充。

name属性对应的isNotEmpty节点，由于ibatis会自动判定是否需要追加prepend前缀，这里(name like #name#)是WHERE 子句中的第一个条件子句，无需AND 前缀，所以自动省略。

判定节点并非仅限于isNotEmpty，ibatis中提供了丰富的判定定义功能。可以分两类：

一、一元判定

一元判定是针对属性值本身的判定，如属性是否为NULL，是否为空值等。

上面示例中isNotEmpty就是典型的一元判定。

一元判定节点有：

<isPropertyAvailable> 参数类中是否提供了此属性

<isNotPropertyAvailable> 与<isPropertyAvailable>相反

<isNull> 属性值是否为NULL

<isNotNull> 与<isNull>相反

<isEmpty> 如果属性为Collection或者String，其size是否<1，

如果非以上两种类型，则通过

String.valueOf(属性值)

获得其String类型的值后，判断其size是否<1

<isNotEmpty> 与<isEmpty>相反。

二、二元判定

二元判定有两个判定参数，一是属性名，而是判定值，如

<isGreaterThan prepend=”AND” property=”age”

compareValue=”18″>

(age=#age#)

</isGreaterThan>

其中，property=”age”指定了属性名”age”，compareValue=”18”指明

了判定值为”18”。

上面判定节点isGreaterThan 对应的语义是：如果age 属性大于

18(compareValue)，则在SQL中加入(age=#age#)条件。

二元判定节点有：

节点名属性值与compareValues的关系

<isEqual> 相等。

<isNotEqual> 不等。

<isGreaterThan> 大于

<isGreaterEqual> 大于等于

<isLessThan> 小于

<isLessEqual> 小于等于

2.列子

对于一些特殊符号，如大于号>、小于号< 等需要写在<![CDATA[]]中方可有效，否则失效。

1、动态SQL片段

通过SQL片段达到代码复用

 1 <!-- 动态条件分页查询 --> 
 2         <sql id="sql_count"> 
 3                 select count(*) 
 4         </sql> 
 5         <sql id="sql_select"> 
 6                 select * 
 7         </sql> 
 8         <sql id="sql_where"> 
 9                 from icp 
10                 <dynamic prepend="where"> 
11                         <isNotEmpty prepend="and" property="name"> 
12                                 name like '%$name$%' 
13                         </isNotEmpty> 
14                         <isNotEmpty prepend="and" property="path"> 
15                                 path like '%path$%' 
16                         </isNotEmpty> 
17                         <isNotEmpty prepend="and" property="area_id"> 
18                                 area_id = #area_id# 
19                         </isNotEmpty> 
20                         <isNotEmpty prepend="and" property="hided"> 
21                                 hided = #hided# 
22                         </isNotEmpty> 
23                 </dynamic> 
24                 <dynamic prepend=""> 
25                         <isNotNull property="_start"> 
26                                 <isNotNull property="_size"> 
27                                         limit #_start#, #_size# 
28                                 </isNotNull> 
29                         </isNotNull> 
30                 </dynamic> 
31         </sql> 
32         <select id="findByParamsForCount" parameterClass="map" resultClass="int"> 
33                 <include refid="sql_count"/> 
34                 <include refid="sql_where"/> 
35         </select> 
36         <select id="findByParams" parameterClass="map" resultMap="icp.result_base"> 
37                 <include refid="sql_select"/> 
38                 <include refid="sql_where"/> 
39 
40         </select>

2、数字范围查询

所传参数名称是捏造所得，非数据库字段，比如_img_size_ge、_img_size_lt字段

 1 <isNotEmpty prepend="and" property="_img_size_ge"> 
 2                                 <![CDATA[ 
 3                                 img_size >= #_img_size_ge# 
 4                         ]]> 
 5                         </isNotEmpty> 
 6                         <isNotEmpty prepend="and" property="_img_size_lt"> 
 7                                 <![CDATA[ 
 8                                 img_size < #_img_size_lt# 
 9                         ]]> 
10                         </isNotEmpty> 
11 
12

多次使用一个参数也是允许的

 1 <isNotEmpty prepend="and" property="_now"> 
 2                                 <![CDATA[ 
 3                                             execplantime >= #_now# 
 4                                      ]]> 
 5                         </isNotEmpty> 
 6                         <isNotEmpty prepend="and" property="_now"> 
 7                                 <![CDATA[ 
 8                                             closeplantime <= #_now# 
 9                                      ]]> 
10                         </isNotEmpty>

3、时间范围查询

1 <isNotEmpty prepend="" property="_starttime"> 
2                                 <isNotEmpty prepend="and" property="_endtime"> 
3                                         <![CDATA[ 
4                                         createtime >= #_starttime# 
5                                         and createtime < #_endtime# 
6                                  ]]> 
7                                 </isNotEmpty> 
8                         </isNotEmpty>

4、in查询

1 <isNotEmpty prepend="and" property="_in_state"> 
2                                 state in ('$_in_state$') 
3                         </isNotEmpty>

5、like查询

1 <isNotEmpty prepend="and" property="chnameone"> 
2                                 (chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%') 
3                         </isNotEmpty> 
4                         <isNotEmpty prepend="and" property="chnametwo"> 
5                                 chnametwo like '%$chnametwo$%' 
6                         </isNotEmpty>

6、or条件

 1 <isEqual prepend="and" property="_exeable" compareValue="N"> 
 2                                 <![CDATA[ 
 3                                 (t.finished='11'    or t.failure=3) 
 4                         ]]> 
 5                         </isEqual>
 6 
 7 <isEqual prepend="and" property="_exeable" compareValue="Y"> 
 8                                 <![CDATA[ 
 9                                 t.finished in ('10','19') and t.failure<3 
10                         ]]> 
11                         </isEqual>

7、where子查询

 1 <isNotEmpty prepend="" property="exprogramcode"> 
 2                                 <isNotEmpty prepend="" property="isRational"> 
 3                                         <isEqual prepend="and" property="isRational" compareValue="N"> 
 4                                                 code not in 
 5                                                 (select t.contentcode 
 6                                                 from cms_ccm_programcontent t 
 7                                                 where t.contenttype='MZNRLX_MA' 
 8                                                 and t.programcode = #exprogramcode#) 
 9                                         </isEqual> 
10                                 </isNotEmpty> 
11                         </isNotEmpty>
12 
13 <select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_material.result"> 
14                 select * 
15                 from cms_ccm_material 
16                 where code in 
17                 (select t.contentcode 
18                 from cms_ccm_programcontent t 
19                 where t.contenttype = 'MZNRLX_MA' 
20                 and programcode = #value#) 
21                 order by updatetime desc 
22         </select>

9、函数的使用

 1 <!-- 添加 --> 
 2         <insert id="insert" parameterClass="RuleMaster"> 
 3                 insert into rulemaster( 
 4                 name, 
 5                 createtime, 
 6                 updatetime, 
 7                 remark 
 8                 ) values ( 
 9                 #name#, 
10                 now(), 
11                 now(), 
12                 #remark# 
13                 ) 
14                 <selectKey keyProperty="id" resultClass="long"> 
15                         select LAST_INSERT_ID() 
16                 </selectKey> 
17         </insert> 
18         <!-- 更新 --> 
19         <update id="update" parameterClass="RuleMaster"> 
20                 update rulemaster set 
21                 name = #name#, 
22                 updatetime = now(), 
23                 remark = #remark# 
24                 where id = #id# 
25         </update>

10、map结果集

 1 <!-- 动态条件分页查询 --> 
 2         <sql id="sql_count"> 
 3                 select count(a.*) 
 4         </sql> 
 5         <sql id="sql_select"> 
 6                 select a.id                vid, 
 7                 a.img             imgurl, 
 8                 a.img_s         imgfile, 
 9                 b.vfilename vfilename, 
10     b.name            name, 
11                 c.id                sid, 
12                 c.url             url, 
13                 c.filename    filename, 
14                 c.status        status 
15         </sql> 
16         <sql id="sql_where"> 
17                 From secfiles c, juji b, videoinfo a 
18                 where 
19                 a.id = b. videoid 
20                 and b.id = c.segmentid 
21                 and c.status = 0 
22                 order by a.id asc,b.id asc,c.sortnum asc 
23                 <dynamic prepend=""> 
24                         <isNotNull property="_start"> 
25                                 <isNotNull property="_size"> 
26                                         limit #_start#, #_size# 
27                                 </isNotNull> 
28                         </isNotNull> 
29                 </dynamic> 
30         </sql> 
31         <!-- 返回没有下载的记录总数 --> 
32         <select id="getUndownFilesForCount" parameterClass="map" resultClass="int"> 
33                 <include refid="sql_count"/> 
34                 <include refid="sql_where"/> 
35         </select> 
36         <!-- 返回没有下载的记录 --> 
37         <select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap"> 
38                 <include refid="sql_select"/> 
39                 <include refid="sql_where"/> 
40         </select>