動態sql，顧名思義，就是動態的在xml中拼接sql陳述句，
1、where
<select id="getUserById" resultType="com.guor.bean.User">
SELECT * FROM user
<where>
<if test="position != null">
AND position like #{position}
</if>
<if test="school != null">
AND school like #{school}
</if>
</where>
</select>注：where元素只有至少一個條件存在時，才會有where，而且，如果開頭是and或者or時，where元素會自動將其舍棄，
2、trim
其實where也可以用trim來改寫：
<select id="getUserById" resultType="com.guor.bean.User">
SELECT * FROM user
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="position != null">
AND position like #{position}
</if>
<if test="school != null">
AND school like #{school}
</if>
</trim>
</select>3、set
set元素會動態前置set關鍵字，同時也會洗掉無關的逗號，
<update id="updateUserById">
update user
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="address != null">ddress=#{ddress}</if>
</set>
where id=#{id}
</update>set也可以用trim進行改寫：
<update id="updateUserById">
update user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="address != null">ddress=#{ddress}</if>
</trim>
where id=#{id}
</update>4、if
<select id="getUserById" resultType="com.guor.bean.User">
SELECT * FROM user WHERE 1 = 1
<if test="position != null">
AND position like #{position}
</if>
</select>5、choose、when、otherwish
<select id="getUsersById" resultType="com.guor.bean.User">
SELECT * FROM user WHERE 1 = 1
<choose>
<when test="position != null">
AND position like #{position}
</when>
<when test="department != null and department.name != null">
AND department_name like #{department.name}
</when>
<otherwise>
AND department_name = '公司'
</otherwise>
</choose>
</select>6、foreach
<select id="selectUserById" resultType="com.guor.bean.User">
SELECT id,name,age,sex,address,school FROM user u
WHERE id in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>當使用可迭代物件或者陣列時，index 是當前迭代的次數，item 的值是本次迭代獲取的元素，
當使用 Map 物件（或者 Map.Entry 物件的集合）時，index 是鍵，item 是值，
7、bind
<select id="selectUserByDept" resultType="com.guor.bean.User">
<bind name="dept" value="'%' + _department.getId() + '%'" />
SELECT * FROM user
WHERE dept_id = #{dept}
</select>8、連接多資料庫
<insert id="getCurrentTime">
<if test="_databaseId == 'oracle'">
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
</if>
<if test="_databaseId == 'mysql'">
select now() as Systemtime
</if>
</insert>
往期精彩內容：
Java知識體系總結
Spring框架總結
超詳細的springBoot學習筆記
常見資料結構與演算法整理總結
Java設計模式：23種設計模式全面決議
Java面試題總結（附答案）
Linux知識體系總結
Redis知識體系總結
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/286760.html
標籤：java