MyBatis动态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> 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>