http://zhaohe162.blog.163.com/blog/static/3821679720115275181509/处看到的。
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 >
< 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 >
<![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 >
<![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 >
< 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 >
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 >
(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 >
<![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 >
<![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 >
< 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 >
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 >
< 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 >
< 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 >