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 >
ibatis的动态sql
标签：
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did119368