詳解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>
