- 相關(guān)推薦
詳解MyBatis動態(tài)SQL標(biāo)簽用法
本文通過實例代碼給大家介紹了MyBatis動態(tài)SQL標(biāo)簽用法，具有參考借鑒價值，需要的朋友可以參考一下。想了解更多相關(guān)信息請持續(xù)關(guān)注我們應(yīng)屆畢業(yè)生考試網(wǎng)!
1、動態(tài)SQL片段
通過SQL片段達(dá)到代碼復(fù)用
<!-- 動態(tài)條件分頁查詢 -->
<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、數(shù)字范圍查詢
所傳參數(shù)名稱是捏造所得，非數(shù)據(jù)庫字段，比如_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>
多次使用一個參數(shù)也是允許的
<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、函數(shù)的使用
<!-- 添加 -->
< 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結(jié)果集
<!-- 動態(tài)條件分頁查詢 -->
<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>
<!-- 返回沒有下載的記錄總數(shù) -->
<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是更靈活的去處多余關(guān)鍵字的標(biāo)簽，他可以實踐where和set的效果。
where例子的等效trim語句：
Xml代碼
<!-- 查詢學(xué)生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代碼
<!-- 更新學(xué)生信息 -->
<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)
有時候我們并不想應(yīng)用所有的條件，而只是想從多個選項中選擇一個。MyBatis提供了choose 元素，按順序判斷when中的條件出否成立，如果有一個成立，則choose結(jié)束。當(dāng)choose中所有when的條件都不滿則時，則執(zhí)行 otherwise中的sql。類似于Java 的switch 語句，choose為switch，when為case，otherwise則為default。
if是與(and)的關(guān)系，而choose是或（or）的關(guān)系。
例如下面例子，同樣把所有可以限制的條件都寫上，方面使用。選擇條件順序，when標(biāo)簽的從上到下的書寫順序：
Xml代碼
<!-- 查詢學(xué)生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>
【詳解MyBatis動態(tài)SQL標(biāo)簽用法】相關(guān)文章：
Spring+MyBatis數(shù)據(jù)讀寫分離的實例詳解10-09
java list的用法詳解08-24
C語言for語句用法詳解10-30
C語言指針用法詳解08-21
英語代詞的幾種用法詳解07-12
將來進(jìn)行時用法知識詳解06-12
英語中know的幾種用法詳解10-02
cut英語短語用法詳解08-24
雅思閱讀：動詞不定式用法詳解10-14