iBatis动态SQL标签用法

1、动态SQL片段

通过SQL片段达到代码复用

<!-- 动态条件分页查询 --> 
<sql id="sql_count"> 
	select count(*) 
</sql> 
<sql id="sql_select"> 
	select * 
</sql> 
<sql id="sql_where"> 
	from icp 
	<dynamic prepend="where"> 
		<isNotEmpty prepend="and" property="name"> 
			name like '%$name$%' 
		</isNotEmpty> 
		<isNotEmpty prepend="and" property="path"> 
			path like '%path$%' 
		</isNotEmpty> 
		<isNotEmpty prepend="and" property="area_id"> 
			area_id = #area_id# 
		</isNotEmpty> 
		<isNotEmpty prepend="and" property="hided"> 
			hided = #hided# 
		</isNotEmpty> 
	</dynamic> 
	<dynamic prepend=""> 
		<isNotNull property="_start"> 
			<isNotNull property="_size"> 
				limit #_start#, #_size# 
			</isNotNull> 
		</isNotNull> 
	</dynamic> 
</sql> 
<select id="findByParamsForCount" parameterClass="map" resultClass="int"> 
	<include refid="sql_count"/> 
	<include refid="sql_where"/> 
</select> 
<select id="findByParams" parameterClass="map" resultMap="icp.result_base"> 
	<include refid="sql_select"/> 
	<include refid="sql_where"/> 
</select>

2、数字范围查询

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

<isNotEmpty prepend="and" property="_img_size_ge"> 
	<![CDATA[ img_size >= #_img_size_ge# ]]> 
</isNotEmpty> 
<isNotEmpty prepend="and" property="_img_size_lt"> 
	<![CDATA[ img_size < #_img_size_lt# ]]> 
</isNotEmpty>

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

<isNotEmpty prepend="and" property="_now"> 
	<![CDATA[ execplantime >= #_now# ]]> 
</isNotEmpty> 
<isNotEmpty prepend="and" property="_now"> 
	<![CDATA[ closeplantime <= #_now# ]]> 
</isNotEmpty>

3、时间范围查询

<isNotEmpty prepend="" property="_starttime"> 
	<isNotEmpty prepend="and" property="_endtime"> 
		<![CDATA[ createtime >= #_starttime#  
		and createtime < #_endtime# ]]> 
	</isNotEmpty> 
</isNotEmpty>

4、in查询

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

5、like查询

<isNotEmpty prepend="and" property="chnameone"> 
	(chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%') 
</isNotEmpty> 
<isNotEmpty prepend="and" property="chnametwo"> 
	chnametwo like '%$chnametwo$%' 
</isNotEmpty> 

6、or条件

<isEqual prepend="and" property="_exeable" compareValue="N"> 
	<![CDATA[ (t.finished='11' or t.failure=3) ]]> 
</isEqual>

<isEqual prepend="and" property="_exeable" compareValue="Y"> 
	<![CDATA[ t.finished in ('10','19') 

	and t.failure<3 ]]> 
</isEqual>

7、where子查询

<isNotEmpty prepend="" property="exprogramcode"> 
	<isNotEmpty prepend="" property="isRational"> 
		<isEqual prepend="and" property="isRational" compareValue="N"> 
			code not in  
			(select t.contentcode  
			from cms_ccm_programcontent t  
			where t.contenttype='MZNRLX_MA'  
			and t.programcode = #exprogramcode#) 
		</isEqual> 
	</isNotEmpty> 
</isNotEmpty>

<select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_material.result"> 
	select *  
	from cms_ccm_material  
	where code in  
	(select t.contentcode  
	from cms_ccm_programcontent t  
	where t.contenttype = 'MZNRLX_MA'  
	and programcode = #value#)  
	order by updatetime desc 
</select>

8、函数的使用

<!-- 添加 --> 
<insert id="insert" parameterClass="RuleMaster"> 
	insert into rulemaster(  
	name,  
	createtime,  
	updatetime,  
	remark 
	) values ( 
	#name#,  
	now(),  
	now(),  
	#remark# 
	) 
	<selectKey keyProperty="id" resultClass="long"> 
		select LAST_INSERT_ID() 
	</selectKey> 
</insert> 
<!-- 更新 --> 
<update id="update" parameterClass="RuleMaster"> 
	update rulemaster set  
	name = #name#,  
	updatetime = now(),  
	remark = #remark#  
	where id = #id# 
</update>

9、map结果集

<!-- 动态条件分页查询 --> 
<sql id="sql_count"> 
	select count(a.*) 
</sql> 
<sql id="sql_select"> 
	select a.id                vid, 
	a.img             imgurl, 
	a.img_s         imgfile, 
	b.vfilename vfilename, 
	b.name            name, 
	c.id                sid, 
	c.url             url, 
	c.filename    filename, 
	c.status        status 
</sql> 
<sql id="sql_where"> 
	From secfiles c, juji b, videoinfo a 
	where 
	a.id = b. videoid 
	and b.id = c.segmentid 
	and c.status = 0 
	order by a.id asc,b.id asc,c.sortnum asc 
	<dynamic prepend=""> 
		<isNotNull property="_start"> 
			<isNotNull property="_size"> 
				limit #_start#, #_size# 
			</isNotNull> 
		</isNotNull> 
	</dynamic> 
</sql> 
<!-- 返回没有下载的记录总数 --> 
<select id="getUndownFilesForCount" parameterClass="map" resultClass="int"> 
	<include refid="sql_count"/> 
	<include refid="sql_where"/> 
</select> 
<!-- 返回没有下载的记录 --> 
<select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap"> 
	<include refid="sql_select"/> 
	<include refid="sql_where"/> 
</select>