通過(guò)實(shí)例講解了用mybatis對(duì)一張表進(jìn)行的CRUD操作，但是我們發(fā)現(xiàn)寫(xiě)的 SQL 語(yǔ)句都比較簡(jiǎn)單，如果有比較復(fù)雜的業(yè)務(wù)，我們需要寫(xiě)復(fù)雜的 SQL 語(yǔ)句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引號(hào)，空格等缺失可能都會(huì)導(dǎo)致錯(cuò)誤。 那么怎么去解決這個(gè)問(wèn)題呢？這就是本篇所講的使用 mybatis 動(dòng)態(tài)SQL，通過(guò) if, choose, when, otherwise, trim, where, set, foreach等標(biāo)簽， 1. if 語(yǔ)句 (簡(jiǎn)單的條件判斷)
2. choose (when,otherwize) ,相當(dāng)于java 語(yǔ)言中的 switch ,與 jstl 中的choose 很類似.
3. trim (對(duì)包含的內(nèi)容加上 prefix,或者 suffix 等，前綴，后綴)
4. where (主要是用來(lái)簡(jiǎn)化sql語(yǔ)句中where條件判斷的，能智能的處理 and or ,不必?fù)?dān)心多余導(dǎo)致語(yǔ)法錯(cuò)誤)
5. set (主要用于更新時(shí))
6. foreach (在實(shí)現(xiàn) mybatis in 語(yǔ)句查詢時(shí)特別有用) 可組合成非常靈活的SQL語(yǔ)句，從而在提高 SQL 語(yǔ)句的準(zhǔn)確性的同時(shí)，也大大提高了開(kāi)發(fā)人員的效率。 我們以 User 表為例來(lái)說(shuō)明：  1、動(dòng)態(tài)SQL:if 語(yǔ)句 根據(jù) username 和 sex 來(lái)查詢數(shù)據(jù)。如果username為空，那么將只根據(jù)sex來(lái)查詢；反之只根據(jù)username來(lái)查詢 首先不使用 動(dòng)態(tài)SQL 來(lái)書(shū)寫(xiě) 1 2 3 4 5 6 | <select id="selectUserByUsernameAndSex"
resultType="user" parameterType="com.ys.po.User">
<!-- 這里和普通的sql 查詢語(yǔ)句差不多，對(duì)于只有一個(gè)參數(shù)，后面的 #{id}表示占位符，里面不一定要寫(xiě)id,
寫(xiě)啥都可以，但是不要空著，如果有多個(gè)參數(shù)則必須寫(xiě)pojo類里面的屬性 -->
select * from user where username=#{username} and sex=#{sex}
</select>
|
上面的查詢語(yǔ)句，我們可以發(fā)現(xiàn)，如果 #{username} 為空，那么查詢結(jié)果也是空，如何解決這個(gè)問(wèn)題呢？使用 if 來(lái)判斷 1 2 3 4 5 6 7 8 9 10 | <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
select * from user where
<if test="username != null">
username=#{username}
</if>
<if test="username != null">
and sex=#{sex}
</if>
</select>
|
這樣寫(xiě)我們可以看到，如果 sex 等于 null，那么查詢語(yǔ)句為 select * from user where username=#{username},但是如果usename 為空呢？那么查詢語(yǔ)句為 select * from user where and sex=#{sex}，這是錯(cuò)誤的 SQL 語(yǔ)句，如何解決呢？請(qǐng)看下面的 where 語(yǔ)句 2、動(dòng)態(tài)SQL:if+where 語(yǔ)句1 2 3 4 5 6 7 8 9 10 11 12 | <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
select * from user
<where>
<if test="username != null">
username=#{username}
</if>
<if test="username != null">
and sex=#{sex}
</if>
</where>
</select>
|
這個(gè)“where”標(biāo)簽會(huì)知道如果它包含的標(biāo)簽中有返回值的話，它就插入一個(gè)'where’。此外，如果標(biāo)簽返回的內(nèi)容是以AND 或OR 開(kāi)頭的，則它會(huì)剔除掉。 3、動(dòng)態(tài)SQL:if+set 語(yǔ)句 同理，上面的對(duì)于查詢 SQL 語(yǔ)句包含 where 關(guān)鍵字，如果在進(jìn)行更新操作的時(shí)候，含有 set 關(guān)鍵詞，我們?cè)趺刺幚砟兀?/p> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <!-- 根據(jù) id 更新 user 表的數(shù)據(jù) -->
<update id="updateUserById" parameterType="com.ys.po.User">
update user u
<set>
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex}
</if>
</set>
where id=#{id}
</update>
|
這樣寫(xiě)，如果第一個(gè)條件 username 為空，那么 sql 語(yǔ)句為：update user u set u.sex=? where id=? 如果第一個(gè)條件不為空，那么 sql 語(yǔ)句為：update user u set u.username = ? ,u.sex = ? where id=? 4、動(dòng)態(tài)SQL:choose(when,otherwise) 語(yǔ)句 有時(shí)候，我們不想用到所有的查詢條件，只想選擇其中的一個(gè)，查詢條件有一個(gè)滿足即可，使用 choose 標(biāo)簽可以解決此類問(wèn)題，類似于 Java 的 switch 語(yǔ)句 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
select * from user
<where>
<choose>
<when test="id !='' and id != null">
id=#{id}
</when>
<when test="username !='' and username != null">
and username=#{username}
</when>
<otherwise>
and sex=#{sex}
</otherwise>
</choose>
</where>
</select>
|
也就是說(shuō)，這里我們有三個(gè)條件，id,username,sex，只能選擇一個(gè)作為查詢條件 如果 id 不為空，那么查詢語(yǔ)句為：select * from user where id=? 如果 id 為空，那么看username 是否為空，如果不為空，那么語(yǔ)句為 select * from user where username=?; 如果 username 為空，那么查詢語(yǔ)句為 select * from user where sex=? 5、動(dòng)態(tài)SQL:trim 語(yǔ)句 trim標(biāo)記是一個(gè)格式化的標(biāo)記，可以完成set或者是where標(biāo)記的功能 ①、用 trim 改寫(xiě)上面第二點(diǎn)的 if+where 語(yǔ)句 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
select * from user
<!-- <where>
<if test="username != null">
username=#{username}
</if>
<if test="username != null">
and sex=#{sex}
</if>
</where> -->
<trim prefix="where" prefixOverrides="and | or">
<if test="username != null">
and username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</trim>
</select>
|
prefix：前綴 prefixoverride：去掉第一個(gè)and或者是or ?、凇⒂?trim 改寫(xiě)上面第三點(diǎn)的 if+set 語(yǔ)句 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <!-- 根據(jù) id 更新 user 表的數(shù)據(jù) -->
<update id="updateUserById" parameterType="com.ys.po.User">
update user u
<!-- <set>
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex}
</if>
</set> -->
<trim prefix="set" suffixOverrides=",">
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex},
</if>
</trim>
where id=#{id}
</update>
|
suffix：后綴 suffixoverride：去掉最后一個(gè)逗號(hào)（也可以是其他的標(biāo)記，就像是上面前綴中的and一樣） 6、動(dòng)態(tài)SQL: SQL 片段 有時(shí)候可能某個(gè) sql 語(yǔ)句我們用的特別多，為了增加代碼的重用性，簡(jiǎn)化代碼，我們需要將這些代碼抽取出來(lái)，然后使用時(shí)直接調(diào)用。 比如：假如我們需要經(jīng)常根據(jù)用戶名和性別來(lái)進(jìn)行聯(lián)合查詢，那么我們就把這個(gè)代碼抽取出來(lái)，如下： 1 2 3 4 5 6 7 8 9 | <!-- 定義 sql 片段 -->
<sql id="selectUserByUserNameAndSexSQL">
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="sex != null and sex != ''">
AND sex = #{sex}
</if>
</sql>
|
引用 sql 片段 1 2 3 4 5 6 7 8 | <select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
select * from user
<trim prefix="where" prefixOverrides="and | or">
<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
<include refid="selectUserByUserNameAndSexSQL"></include>
<!-- 在這里還可以引用其他的 sql 片段 -->
</trim>
</select>
|
注意：①、最好基于 單表來(lái)定義 sql 片段，提高片段的可重用性 ?、凇⒃?sql 片段中不要包括 where 7、動(dòng)態(tài)SQL: foreach 語(yǔ)句 需求：我們需要查詢 user 表中 id 分別為1,2,3的用戶 sql語(yǔ)句：select * from user where id=1 or id=2 or id=3 select * from user where id in (1,2,3) ①、建立一個(gè) UserVo 類，里面封裝一個(gè) List<Integer> ids 的屬性 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | package com.ys.vo;
import java.util.List;
public class UserVo {
//封裝多個(gè)用戶的id
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
|
②、我們用 foreach 來(lái)改寫(xiě) select * from user where id=1 or id=2 or id=3 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
select * from user
<where>
<!--
collection:指定輸入對(duì)象中的集合屬性
item:每次遍歷生成的對(duì)象
open:開(kāi)始遍歷時(shí)的拼接字符串
close:結(jié)束時(shí)拼接的字符串
separator:遍歷對(duì)象之間需要拼接的字符串
select * from user where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
|
測(cè)試： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | //根據(jù)id集合查詢user表數(shù)據(jù)
@Test
public void testSelectUserByListId(){
String statement = "com.ys.po.userMapper.selectUserByListId";
UserVo uv = new UserVo();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
uv.setIds(ids);
List<User> listUser = session.selectList(statement, uv);
for(User u : listUser){
System.out.println(u);
}
session.close();
}
|
③、我們用 foreach 來(lái)改寫(xiě) select * from user where id in (1,2,3) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
select * from user
<where>
<!--
collection:指定輸入對(duì)象中的集合屬性
item:每次遍歷生成的對(duì)象
open:開(kāi)始遍歷時(shí)的拼接字符串
close:結(jié)束時(shí)拼接的字符串
separator:遍歷對(duì)象之間需要拼接的字符串
select * from user where 1=1 and id in (1,2,3)
-->
<foreach collection="ids" item="id" open="and id in (" close=") " separator=",">
#{id}
</foreach>
</where>
</select>
|
8、總結(jié) 其實(shí)動(dòng)態(tài) sql 語(yǔ)句的編寫(xiě)往往就是一個(gè)拼接的問(wèn)題，為了保證拼接準(zhǔn)確，我們最好首先要寫(xiě)原生的 sql 語(yǔ)句出來(lái)，然后在通過(guò) mybatis 動(dòng)態(tài)sql 對(duì)照著改，防止出錯(cuò)。
|
