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 >