什麼是動態SQL
MyBatis的一個強大特性之一通常是它的動態SQL能力。如果你有使用JDBC或其他相似框架的經驗,你就明白條件串聯SQL字串在一起是多麼地痛苦,確保不能忘了空格或者在列表的最後的省略逗號,動態SQL可以徹底處理這種痛苦。
通常使用動態SQL不可能是獨立的一部分,MyBatis當然使用一種強大的動態SQL語言來改進這種情形,這種語言可以被用在任意對映的SQL語句中。
動態SQL元素和使用JSTL或其它相似的基於XML的文字處理器相似,在MyBatis之前的版本中,有很多元素需要了解,MyBatis3大大地提升了它們,現在用不到原先一半的元素就能工作了,MyBatis採用功能強大的基於OGNL的表示式來消除其他元素。
OK,介紹就到這兒,下面來進入動態SQL的學習吧。
if
在動態SQL中所做的最通用的事情就是包含部分where子句的條件,比如:
<select id="selectInCondition" parameterType="student" resultType="student"> select * from student where studentId > #{studentId} <if test="studentName != null"> and studentName = #{studentName}; </if> </select>
具體實現不寫了,那麼如果我這麼呼叫:
List<Student> list = StudentOperator.getInstance().selectInCondition(0, "Jack", 0, null);
查詢的就是studentId>0且studentName="Jack"的所有學生資訊,如果換一種呼叫方式:
List<Student> list = StudentOperator.getInstance().selectInCondition(0, null, 0, null);
那麼查詢的就是studentId>0的所有學生資訊。
多個where子句也是一樣的,比如:
<select id="selectInCondition" parameterType="student" resultType="student"> <![CDATA[ select * from student where studentId > #{studentId} ]]> <if test="studentName != null and studentName != 'Jack' "> and studentName = #{studentName} </if> <if test="studentAge != 0"> and studentAge = #{studentAge}; </if> </select>
注意一下,能用"<![CDATA[ ... ]]>"儘量還是用,不過只包動態SQL外的內容。
另外,test裡面可以判斷字串、整型、浮點型,大膽地寫判斷條件吧。如果屬性是複合型別,則可以使用A.B的方式去獲取複合型別中的屬性來進行比較。
choose、when、otherwise
有時候我們不想應用所有的應用條件,相反我們想選擇很多情況下的一種。和Java中的switch...case...類似,MyBasit提供choose元素。
上面的例子是兩種if判斷都可能存在,接下來使用choose、when、other做一些修改:
<select id="selectInCondition" parameterType="student" resultType="student"> <![CDATA[ select * from student where studentId > #{studentId} ]]> <choose> <when test="studentName != null"> and studentName = #{studentName}; </when> <when test="studentAge != 0"> and studentAge = #{studentAge}; </when> <otherwise> or 1 = 1; </otherwise> </choose> </select>
兩個when只能滿足一個,都不滿足則走other。還是注意一下這裡的"<![CDATA[ ... ]]>",不可以包圍整個語句。
trim、where、set
第一個例子已經示例了if的用法,但是這種用法有個缺陷----動態SQL外必須有where子句。
什麼意思,因為很多時候我們需要where後面的子句都動態生成,而不是事先有一個where,這樣就有問題,比如說:
<select id="selectInCondition" parameterType="student" resultType="student"> <![CDATA[ select * from student where ]]> <if test="studentName != null and studentName != 'Jack' "> and studentName = #{studentName} </if> <if test="studentAge != 0"> and studentAge = #{studentAge}; </if> </select>
如果所有條件都不匹配,那麼生成的SQL語句將是:
select * from student where
這將導致查詢失敗。即使只滿足一個查詢條件還是有問題,比如滿足studentName那個吧,生成的SQL語句將是:
select * from student where and studentName = #{studentName};
這個查詢也會失敗。
解決辦法也有,一個討巧的辦法是用where 1 = 1的方式,即:
<select id="selectInCondition" parameterType="student" resultType="student"> <![CDATA[ select * from student where 1 = 1 ]]> <if test="studentName != null and studentName != 'Jack' "> and studentName = #{studentName} </if> <if test="studentAge != 0"> and studentAge = #{studentAge}; </if> </select>
因為"1 = 1"永遠滿足,所以相當於給where加了一層true而已,此時動態SQL生成什麼where判斷條件就是什麼。
另外一個解決辦法是利用MyBatis中的一個簡單處理方式,這在90%情況下都會有用而且。而在不能使用的地方,可以以自定義方式處理。加上一個簡單的改變,所有的事情都會順利進行:
<select id="selectInCondition" parameterType="student" resultType="student"> <![CDATA[ select * from student ]]> <where> <if test="studentName != null and studentName != 'Jack' "> and studentName = #{studentName} </if> <if test="studentAge != 0"> and studentAge = #{studentAge}; </if> </where> </select>
where元素知道如果由被包含的標記返回任意內容,就僅僅插入where。而且,如果以"and"或"or"開頭的內容,那麼就會跳過where不插入。
如果where元素沒有做出你想要的,那麼可以使用trim元素來自定義。比如,和where元素相等的trim元素是:
<trim prefix="WHERE" prefixOverrides="AND |OR "> … </trim>
即:
<select id="selectInCondition" parameterType="student" resultType="student"> select * from student <trim prefix="WHERE" prefixOverrides="AND |OR "> <if test="studentName != null and studentName != 'Jack' "> and studentName = #{studentName} </if> <if test="studentAge != 0"> and studentAge = #{studentAge}; </if> </trim> </select>
特別要注意,prefixOverrides中的空白也是很重要的。
最後一個小內容,和動態更新語句相似的解決方案是set。set元素可以被用於動態包含更新的列,而不包含不需要更新的。比如:
<update id="updateStudentAgeById" parameterType="Student"> <!--update student set studentAge = #{studentAge} where studentId = #{studentId}; --> <![CDATA[ update student ]]> <set> <if test="studentAge != 0">studentAge = #{studentAge}</if> </set> where studentId = #{studentId} </update>
可以對比一下,註釋掉的是原update語句,沒有註釋的是加入動態SQL之後的語句。
這裡,set元素會動態前置set關鍵字,而且也會消除任意無關的逗號。如果你對和這裡對等的trim元素好奇,它看起來是這樣的:
<trim prefix="SET" prefixOverrides=","> … </trim>
這種時候我們附加一個字尾,同時也附加一個字首。
foreach
另外一個動態SQL通用的必要操作時迭代一個集合,通常是構建在in條件中的。比如(上面的例子都是我在自己電腦上跑通過的例子,這個例子就直接複製MyBatis官方文件上的內容了):
<select id="selectPostIn" resultType="domain.blog.Post"> <![CDATA[ SELECT * FROM POST P WHERE ID in ]]> <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
foreach是非常強大的,它允許你指定一個集合,宣告集合項和索引變數,它們可以用在元素體內。他也允許你指定開放和關閉字串,在迭代之間放置分隔符。這個元素是很智慧的,它不會偶然地附加多餘的分隔符。