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 >

9、函数的使用

<!-- 添加 -->
< 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 >

10、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 >