详解MyBatis动态SQL标签用法

本文通过实例代码给大家介绍了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="lt_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[

(shed='11' or ure=3)

]]>

</isEqual>

<isEqual prepend="and" property="_exeable" compareValue="Y">

<![CDATA[

shed in ('10','19') and ure<3

]]>

</isEqual>

　　7、where子查询

<isNotEmpty prepend="" property="exprogramcode">

<isNotEmpty prepend="" property="isRational">

<isEqual prepend="and" property="isRational" compareValue="N">

code not in

(select entcode

from cms_ccm_programcontent t

where enttype='MZNRLX_MA'

and ramcode = #exprogramcode#)

</isEqual>

</isNotEmpty>

</isNotEmpty>

<select id="findByProgramcode" parameterClass="string" resultMap="cms_ccm_lt">

select *

from cms_ccm_material

where code in

(select entcode

from cms_ccm_programcontent t

where enttype = 'MZNRLX_MA'

and programcode = #value#)

order by updatetime desc

</select>

　　9、函数的使用

<!-- 添加 -->

< id="" parameterClass="RuleMaster">

into rulemaster(

name,

createtime,

updatetime,

remark

) values (

#name#,

now(),

now(),

#remark#

)

<selectKey keyProperty="id" resultClass="long">

select LAST_INSERT_ID()

</selectKey>

</>

<!-- 更新 -->

<id="update" parameterClass="RuleMaster">

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 vid,

imgurl,

_s imgfile,

ename vfilename,

name,

sid,

url,

name filename,

us status

</sql>

<sql id="sql_where">

From secfiles c, juji b, videoinfo a

where

= b. videoid

and = entid

and us = 0

order by asc, asc,num 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="Map">

<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!='' ">

ENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')

</if>

<if test="studentSex!= null and studentSex!= '' ">

AND ENT_SEX = #{studentSex}

</if>

</trim>

</select>

set例子的等效trim语句：

Xml代码

<!-- 更新学生信息 -->

<id="updateStudent" parameterType="StudentEntity">

UPDATE STUDENT_TBL

<trim prefix="SET" suffixOverrides=",">

<if test="studentName!=null and studentName!='' ">

STUDENT_ENT_NAME = #{studentName},

</if>

<if test="studentSex!=null and studentSex!='' ">

STUDENT_ENT_SEX = #{studentSex},

</if>

<if test="studentBirthday!=null ">

STUDENT_ENT_BIRTHDAY = #{studentBirthday},

</if>

<if test="classEntity!=null and sID!=null and sID!='' ">

STUDENT_S_ID = #{sID}

</if>

</trim>

WHERE STUDENT_ENT_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!='' ">

ENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')

</when>

<when test="studentSex!= null and studentSex!= '' ">

AND ENT_SEX = #{studentSex}

</when>

<when test="studentBirthday!=null">

AND ENT_BIRTHDAY = #{studentBirthday}

</when>

<when test="classEntity!=null and sID !=null and sID!='' ">

AND S_ID = #{sID}

</when>

<otherwise>

</otherwise>

</choose>

</where>

</select>