記一次mybatis復(fù)雜動(dòng)態(tài)sql拼接優(yōu)化方案
前言
今天的內(nèi)容是關(guān)于昨天優(yōu)化的mybatis動(dòng)態(tài)sql的一次簡單總結(jié)，簡單來說就是我通過trim實(shí)現(xiàn)了不確定參數(shù)union all的可變查詢，讓之前的動(dòng)態(tài)sql邏輯更加簡潔，內(nèi)容當(dāng)然算不上高大上，只能算是給可能遇到問題的小伙伴探個(gè)路，下面我們就來展開看下吧。
背景
最近開發(fā)的一個(gè)功能要用到用戶中心的一個(gè)接口，原有接口無法滿足我的需求，所以我需要自己擴(kuò)展一個(gè)新的接口，這個(gè)接口的需要實(shí)現(xiàn)的功能也很簡單，就是根據(jù)崗位id、用戶id或者用戶組id獲取一批用戶信息。
由于接口涉及到多個(gè)表的組合查詢，包括用戶信息表、崗位用戶映射信息表、用戶用戶組映射信息表等，而且參數(shù)是可以為空的（至少有一個(gè)參數(shù)不為空，否則也不會(huì)調(diào)用接口），所以在實(shí)現(xiàn)的時(shí)候我就有考慮到多個(gè)查詢通過union all來拼接。
但是由于參數(shù)可能為空，所以union all是通過動(dòng)態(tài)拼接的，最開始我是通過if判斷進(jìn)行拼接的，剛開始接口一直都沒有問題，但是昨天測試同學(xué)在測試的時(shí)候，發(fā)現(xiàn)如果單傳用戶組id的話，接口會(huì)報(bào)錯(cuò)，然后我就開始對這個(gè)接口的sql進(jìn)行了優(yōu)化，剛開始我是這么寫的：
<select?id="listUsersInfoIds"?resultType="io.github.syske.user.UserInfo">
????????select?ui.id,
????????ui.userId,
????????ui.name,
????????ui.active
????????from?(
????????<if?test="userIds?!=?null?and?userIds.size?>?0">
????????????select
????????????u.id,
????????????u.user_id?as?userId,
????????????u.name,
????????????u.active
????????????from?user?u
????????????where?u.id?in
????????????<foreach?collection="userIds"?item="userId"?open="("?close=")"?separator=",">
????????????????#{userId,?jdbcType=BIGINT}
????????????foreach>
????????????and?u.active?=?true
????????if>
????????<if?test="postIds?!=?null?and?postIds.size?>?0">
????????????<if?test="userIds?!=?null?and?and?userIds.size?>?0">
????????????????union?all
????????????if>
????????????select
????????????u.id,
????????????u.user_id?as?userId,
????????????u.name,
????????????u.active
????????????from?post_user_mapping?m,
????????????user?u
????????????where?m.post_id?in
????????????<foreach?collection="postIds"?item="postId"?open="("?close=")"?separator=",">
????????????????#{postId,?jdbcType=BIGINT}
????????????foreach>
????????????and?m.user_id=u.id
????????????and?u.active?=?true
????????if>
????????<if?test="groupIds?!=?null?and?groupIds.size?>?0">
????????????<if?test="(postIds?!=?null?and?postIds.size?>?0)?or?(userIds?!=?null?and?userIds.size?>?0)">
????????????????union?all
????????????if>
????????????select
????????????u.id,
????????????u.user_id?as?userId,
????????????u.name,
????????????u.active
????????????from?group_user_mapping?m,
????????????user?u
????????????where?m.group_id?in
????????????<foreach?collection="groupIds"?item="groupId"?open="("?close=")"?separator=",">
????????????????#{groupId,?jdbcType=BIGINT}
????????????foreach>
????????????and?m.user_id=u.id
????????????and?u.active?=?true
????????if>
????????)?ui?group?by?ui.id
????select>
但是上面的寫法在只傳groupIds的時(shí)候會(huì)報(bào)錯(cuò)，準(zhǔn)確來說是groupIds這里拼接union all的語句會(huì)報(bào)錯(cuò)，應(yīng)該是不支持or這種復(fù)雜語句的，之后我把這里的if條件語句改成這樣：
?<if?test="(postIds?!=?null?and?postIds.size?>?0)?and?(userIds?==?null?or?userIds.size?==?0)">
?????union?all
if>
<if?test="(postIds?==?null?or?postIds.size?==?0)?and?(userIds?!=?null?and?userIds.size?>?0)">
????union?all
if>
也就是分別判斷postIds和userIds是不是有一個(gè)一個(gè)不為空，如果是則拼接union all，當(dāng)然最后我測試了下發(fā)現(xiàn)確實(shí)解決了，但是我覺得這種方式不夠優(yōu)雅，而且不夠靈活，特別是如果我后面還需要加入union all語句的時(shí)候，那就要再多判斷一個(gè)字段，越往后需要判斷的字段就越多，然后我再網(wǎng)上找了一圈并沒有找到解決方法，最后我打算看下mybatis的文檔，幸運(yùn)的是我還真找到了自己想要的答案。
解決方案
今天的解決方案是基于trim標(biāo)簽實(shí)現(xiàn)的，所以下面我們先來看下trim的一些知識(shí)點(diǎn)。
trim標(biāo)簽
在我們大多數(shù)的需求場景下，mybatis提供的動(dòng)態(tài)語句語法已經(jīng)可以勝任了，比如if、where、choose、when、otherwise、foreach，再復(fù)雜一點(diǎn)的還有set，但是像我現(xiàn)在的需求他們都沒辦法完美解決（畢竟用if太過繁瑣），于是我發(fā)現(xiàn)了一個(gè)靈活性更高的標(biāo)簽——trim。
簡單探索
trim標(biāo)簽的作用就是幫助我們生成更復(fù)雜的sql，關(guān)于它的具體作用官方文檔并沒有給出明確說明，但是根據(jù)它的幾個(gè)參數(shù)以及示例，我們可以看出它的用法。我們先看下trim標(biāo)簽的幾個(gè)屬性：
suffixOverrides：要替換的后綴（被替換內(nèi)容）suffix：替換的后綴（替換內(nèi)容）prefixOverrides：要替換的前綴（被替換內(nèi)容）prefix：替換的前綴（替換內(nèi)容）
但看這四個(gè)屬性確實(shí)可能有點(diǎn)迷，下面我們通過幾個(gè)實(shí)例來說明下trim的用法。
前置用法
先看第一個(gè)，也是官方給出的示例——通過trim來實(shí)現(xiàn)where標(biāo)簽，用where標(biāo)簽我們通常是這么寫的：
<select?id="findActiveBlogLike"
?????resultType="Blog">
??SELECT?*?FROM?BLOG
??<where>
????<if?test="state?!=?null">
?????????state?=?#{state}
????if>
????<if?test="title?!=?null">
????????AND?title?like?#{title}
????if>
????<if?test="author?!=?null?and?author.name?!=?null">
????????OR?author_name?like?#{author.name}
????if>
??where>
select>
用trim實(shí)現(xiàn)的話，可以這樣寫：
<select?id="findActiveBlogLike"
?????resultType="Blog">
??SELECT?*?FROM?BLOG
?<trim?prefix="where"?prefixOverrides="AND?|?OR">
????<if?test="state?!=?null">
?????????state?=?#{state}
????if>
????<if?test="title?!=?null">
????????AND?title?like?#{title}
????if>
????<if?test="author?!=?null?and?author.name?!=?null">
????????OR?author_name?like?#{author.name}
????if>
??trim>
select>
這里trim標(biāo)簽的意思就是把trim標(biāo)簽中第一個(gè)AND或者OR替換為where，也就是說如果第一個(gè)條件為空，第二個(gè)條件中的AND會(huì)被替換成where，如果前兩個(gè)條件都為空，第三個(gè)條件中的OR會(huì)被替換為where。
后置用法
上面我們演示了前置替換的用法，下面我們來看下后置用法，后置用法是通過trim來實(shí)現(xiàn)set標(biāo)簽（話說我之前好像也用的不多，孤陋寡聞了），通常情況下的set是這么用的：
<update?id="updateAuthorIfNecessary">
??update?Author
????<set>
??????<if?test="username?!=?null">username=#{username},if>
??????<if?test="password?!=?null">password=#{password},if>
??????<if?test="email?!=?null">email=#{email},if>
??????<if?test="bio?!=?null">bio=#{bio}if>
????set>
??where?id=#{id}
update>
set標(biāo)簽的作用就是當(dāng)如上語句中，第四個(gè)更新語句為空的時(shí)候，會(huì)將set標(biāo)簽內(nèi)末尾的,移除掉，并在標(biāo)簽內(nèi)語句開始處加上set關(guān)鍵字。用trim標(biāo)簽的話，可以這么寫：
<update?id="updateAuthorIfNecessary">
??update?Author
????<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="bio?!=?null">bio=#{bio}if>
????trim>
??where?id=#{id}
update>
好了，關(guān)于trim我們就演示這么多，下面我們做一個(gè)簡單總結(jié)：
prefix：表示前置要插入的內(nèi)容（這樣看，前面說的替換有點(diǎn)不太合理），比如where、set，它可以單獨(dú)使用suffix：表示后置插入的內(nèi)容（同prefix）prefixOverrides：表示前置要移除的內(nèi)容（中文翻譯前置覆寫）suffixOverrides：表示后置要移除的內(nèi)容（同prefixOverrides）
也就是說trim本質(zhì)上就是通過這四個(gè)屬性，實(shí)現(xiàn)在語句前后加上或者移除相關(guān)內(nèi)容，來實(shí)現(xiàn)復(fù)雜的動(dòng)態(tài)sql，在實(shí)現(xiàn)方面也很簡單，但是靈活度更多。
解決我的問題
最后讓我們再回到我前面說的優(yōu)化，我的這個(gè)sql如果用trim實(shí)現(xiàn)的話，可以這樣寫：
????<select?id="listUsersInfoIds"?resultType="net.coolcollege.user.facade.model.user.UserInfo">
????????select?ui.id,
????????ui.userId,
????????ui.name,
????????ui.active
????????from?(
????????<trim?suffixOverrides="union?all">
????????????<trim?suffix="union?all">
????????????????<if?test="userIds?!=?null?and?userIds.size?>?0">
????????????????????select
????????????????????u.id,
????????????????????u.user_id?as?userId,
????????????????????u.name,
????????????????????u.active
????????????????????from?user?u
????????????????????where?u.id?in
????????????????????<foreach?collection="userIds"?item="userId"?open="("?close=")"?separator=",">
????????????????????????#{userId,?jdbcType=BIGINT}
????????????????????foreach>
????????????????????and?u.active?=?true
????????????????if>
????????????trim>
????????????<trim?suffix="union?all">
????????????????<if?test="postIds?!=?null?and?postIds.size?>?0">
????????????????????select
????????????????????u.id,
????????????????????u.user_id?as?userId,
????????????????????u.name,
????????????????????u.active
????????????????????from?post_user_mapping?m,
????????????????????user?u
????????????????????where?m.post_id?in
????????????????????<foreach?collection="postIds"?item="postId"?open="("?close=")"?separator=",">
????????????????????????#{postId,?jdbcType=BIGINT}
????????????????????foreach>
????????????????????and?m.user_id=u.id
????????????????????and?u.active?=?true
????????????????if>
????????????trim>
????????????<if?test="groupIds?!=?null?and?groupIds.size?>?0">
????????????????select
????????????????u.id,
????????????????u.user_id?as?userId,
????????????????u.name,
????????????????u.active
????????????????from?group_user_mapping?m,
????????????????user?u
????????????????where?m.group_id?in
????????????????<foreach?collection="groupIds"?item="groupId"?open="("?close=")"?separator=",">
????????????????????#{groupId,?jdbcType=BIGINT}
????????????????foreach>
????????????????and?m.user_id=u.id
????????????????and?u.active?=?true
????????????if>
????????trim>
????????)?ui?group?by?ui.id
????select>
首先我通過一個(gè)大的trim包裝所有子查詢（之前通過union all連接），條件是移除最后的union all，然后再用一個(gè)trim標(biāo)簽包裝除最后一個(gè)子查詢之外的其他子查詢，條件是在語句末尾加上union all，這樣前面需要通過復(fù)雜if判斷的語句就直接省略了，而且好處也很明顯：
后續(xù)不論我增加多少個(gè)子查詢，我只需要給子查詢加上trim標(biāo)簽即可（條件都一樣），而不需要關(guān)心其他子查詢是否為空，這樣整個(gè)sql不僅更簡潔，而且擴(kuò)展性也很強(qiáng)，后期不論我增加多少個(gè)子查詢，只需要給子查詢加上trim標(biāo)簽即可，而不需要處理其他復(fù)雜判斷。
結(jié)語
mybatis算是一個(gè)比較流行的ORM框架，應(yīng)該說是國內(nèi)最主流的數(shù)據(jù)庫交互框架了，但是從我自身使用的情況來說，大多數(shù)復(fù)雜場景我好像只想到了if、choose、when、where、foreach等，甚至連set都沒用過，這樣不僅導(dǎo)致寫出的動(dòng)態(tài)sql邏輯復(fù)雜，不夠簡潔，不利于后期維護(hù)，而且很容易出錯(cuò)。
總之，我是覺得學(xué)習(xí)東西，我們不應(yīng)該僅僅停留在夠用和滿足需求的程度，而應(yīng)該養(yǎng)成多看官方文檔、多探索的習(xí)慣，選擇更適合、更優(yōu)的解決方案，這樣才不至于成為井底之蛙。好了，今天的內(nèi)容就到這里吧！
- END -