MyBatis動態SQL

動態SQL
MyBatis的動態SQL，解決了SQL字串拼接的痛苦。

1.if

<select id="findActiveBlogWithTitleLike"parameterType="Blog" resultType="Blog">SELECT * FROM BLOGWHERE state = 'ACTIVE'<if test="title != null">AND title like #{title}</if></select>

這條一句會提供一個可選的文本尋找功能。如果沒有傳遞title，那麼所有啟用的部落格都會被返回。
如果傳遞了title，那麼就會尋找相近的title。

2.choose，when，otherwise

<select id="findActiveBlogLike"parameterType="BLOG" resultType="BLOG">SELECT * FROM BLOGWHERE<choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND title like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></select>

註：如果上述條件都沒有匹配，則會變成SELECT * FROM BLOG WHERE
如果僅有第二個匹配，則會變成SELECT * FROM BLOG WHERE AND title LIKE somelike
顯然這樣會查詢失敗。要解決這個問題，mybatis提供瞭解決方法。

<select id="findActiveBlogLike"parameterType="BLOG" resultType="BLOG">SELECT * FROM BLOGWHERE<trim prefix="WHERE" prefixOverrides="AND |OR "><choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND title like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></trim></select>

overrides屬性採用管道文本分隔字元來覆蓋，這裡的空白是重要的。它的結果就是移除在InnerText中overrides中指定的內容。

3.set

<update id="updateAuthorIfNecessary"parameterType="Author">update Author<set><if test="username != null">username=#{username},</if><if test="password != null">password=#{password},</if><if test="email != null">email=#{email}</if></set>where id=#{id}</update>

同上的問題，最佳化後：

<update id="updateAuthorIfNecessary"parameterType="Author">update Author<trim prefix="where" prefixOverrides=","> <set><if test="username != null">username=#{username},</if><if test="password != null">password=#{password},</if><if test="email != null">email=#{email}</if></set>where id=#{id}</trim></update>