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>
11、trim
trim是更灵活的去处多余关键字的标签，他可以实践where和set的效果。 where例子的等效trim语句：Xml代码
<!-- 查询学生list，like姓名，=性别 --> <select id="getStudentListWhere" parameterType="StudentEntity" resultMap="studentResultMap"> SELECT * from STUDENT_TBL ST <trim prefix="WHERE" prefixOverrides="AND|OR"> <if test="studentName!=null and studentName!='' "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%') </if> <if test="studentSex!= null and studentSex!= '' "> AND ST.STUDENT_SEX = #{studentSex} </if> </trim> </select>
set例子的等效trim语句：
Xml代码
<!-- 更新学生信息 --> <update id="updateStudent" parameterType="StudentEntity"> UPDATE STUDENT_TBL <trim prefix="SET" suffixOverrides=","> <if test="studentName!=null and studentName!='' "> STUDENT_TBL.STUDENT_NAME = #{studentName}, </if> <if test="studentSex!=null and studentSex!='' "> STUDENT_TBL.STUDENT_SEX = #{studentSex}, </if> <if test="studentBirthday!=null "> STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday}, </if> <if test="classEntity!=null and classEntity.classID!=null and classEntity.classID!='' "> STUDENT_TBL.CLASS_ID = #{classEntity.classID} </if> </trim> WHERE STUDENT_TBL.STUDENT_ID = #{studentID}; </update>
12、choose (when, otherwise)
有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。MyBatis提供了choose 元素，按顺序判断when中的条件出否成立，如果有一个成立，则choose结束。当choose中所有when的条件都不满则时，则执行 otherwise中的sql。类似于Java 的switch 语句，choose为switch，when为case，otherwise则为default。 if是与(and)的关系，而choose是或（or）的关系。 例如下面例子，同样把所有可以限制的条件都写上，方面使用。选择条件顺序，when标签的从上到下的书写顺序：Xml代码
<!-- 查询学生list，like姓名、或=性别、或=生日、或=班级，使用choose --> <select id="getStudentListChooseEntity" parameterType="StudentEntity" resultMap="studentResultMap"> SELECT * from STUDENT_TBL ST <where> <choose> <when test="studentName!=null and studentName!='' "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%') </when> <when test="studentSex!= null and studentSex!= '' "> AND ST.STUDENT_SEX = #{studentSex} </when> <when test="studentBirthday!=null"> AND ST.STUDENT_BIRTHDAY = #{studentBirthday} </when> <when test="classEntity!=null and classEntity.classID !=null and classEntity.classID!='' "> AND ST.CLASS_ID = #{classEntity.classID} </when> <otherwise> </otherwise> </choose> </where> </select>