動態SQL語句主要元素使用動態SQL語句實現多條件查詢使用動態SQL語句實現更新使用<foreach>元素實現復雜查詢使用<bind>元素實現SQL語句拼接第3章

動態SQL語句2024/1/29回顧與作業點評2MyBatis基本要素核心對象SqlSessionFactoryBuilderSqlSessionFactorySqlSession通過SqlSession實例直接運行映射的SQL語句基于mapper接口的方式執行SQL語句系統核心配置文件propertiestypeAliasesenvironmentsmappersSQL映射文件核心對象最佳生命周期最佳作用域SqlSessionFactoryBuilder方法體內方法體內(局部變量)SqlSessionFactory從應用服務啟動開始一直到應用服務停止—application整個應用內SqlSession一次請求的有效期一次請求的有效期內回顧作業點評點評作業的提交情況和共性問題學習目標/Target3

了解動態SQL語句中的主要元素及其說明掌握動態SQL語句中主要元素的使用方法

熟練掌握動態SQL語句的運用章節概述/Summary4在實際項目的開發中，開發人員在使用JDBC或其他持久層框架進行開發時，經常需要根據不同的條件拼接SQL語句，拼接SQL語句時還要確保不能遺漏必要的空格、標點符號等，這種編程方式給開發人員帶來了非常大的不便，而MyBatis提供的SQL語句動態組裝功能，恰能很好的解決這一問題。本章將對MyBatis框架的動態SQL進行詳細講解。

目錄/CONTENTS動態SQL語句主要元素使用<foreach>元素實現復雜查詢使用動態SQL語句實現更新使用動態SQL語句實現多條件查詢5使用<bind>元素實現SQL語句拼接14325動態SQL語句

主要元素01第3章動態SQL語句動態SQL有什么作用？動態SQL中的元素7

開發人員在使用JDBC或其他類似的框架進行數據庫開發時，通常都要根據需求去手動拼裝SQL，這是一個非常麻煩且痛苦的工作，而MyBatis提供的對SQL語句動態組裝的功能，恰能很好的解決這一麻煩工作。STEP01

動態SQL是MyBatis的強大特性之一，MyBatis采用了功能強大的基于OGNL（ObjectGraphNavigationLanguage）的表達式來完成動態SQL。在MyBatis的映射文件中，開發人員可通過動態SQL元素靈活組裝SQL語句，這在很大程度上避免了單一SQL語句的反復堆砌，提高了SQL語句的復用性。STEP03使用動態SQL的好處動態SQL中的元素8動態SQL中的元素9

動態SQL是MyBatis的強大特性之一，MyBatis3采用了功能強大的基于OGNL的表達式來完成動態SQL。動態SQL主要元素如下表所示：SQL元素說明<if>判斷語句，用于單條件分支判斷<choose>(<when>、<otherwise>)相當于Java中的switch...case...default語句，用于多條件分支判斷<where>簡化SQL語句中where的條件判斷。<trim>可以靈活地去除多余的關鍵字。<set>解決動態更新語句。<foreach>循環語句，常用于in語句等列舉條件中<bind>從OGNL表達式中創建一個變量，并將其綁定到上下文，常用于模糊查詢的sql中動態SQL10基于OGNL表達式完成多條件查詢等邏輯實現用于實現動態SQL的元素主要有iftrimwheresetchoose（when、otherwise）foreach使用動態SQL語句實現多條件查詢02第3章動態SQL語句STEP01

在MyBatis中，<if>元素是最常用的判斷元素，它類似于Java中的if語句，主要用于實現某些簡單的條件判斷。在實際應用中，我們可能會通過某個條件查詢某個數據。例如，要查找某個客戶的信息，可以通過姓名或者年齡來查找客戶，也可以不填寫年齡直接通過姓名來查找客戶，還可以都不填寫而查詢出所有客戶，此時姓名和年齡就是非必須條件。類似于這種情況，在MyBatis中就可以通過<if>元素來實現。STEP03<if>元素的應用<if>元素12<if>元素13

在MyBatis中，<if>元素是最常用的判斷語句，它類似于Java中的if語句，主要用于實現某些簡單的條件選擇。其基本使用示例如下：select*fromtb_userwhere1=1<iftest="username!=nullandusername!=''"> andusernamelikeconcat('%',#{username},'%')</if><iftest="jobs!=nullandjobs!=''"> andjobs=#{jobs}</if>使用<if>元素對username和jobs進行非空判斷，并動態組裝SQL<if>元素14需求說明改造查詢用戶信息列表的演示示例，增加查詢條件用戶角色（根據角色id查詢）用戶名稱（模糊查詢）演示示例：改造用戶表的查詢操作-多條件查詢<if>元素15當傳入用戶角色參數為空的時候，檢索結果為空？正確結果所有用戶角色下的用戶數據原因如何處理if(判斷參數)：實現簡單的條件判斷問題分析select*fromtb_useru,tb_roler whereu.userRole=r.idandu.userNamelikeCONCAT(‘%’,‘’,‘%’) andu.userRole=null;演示示例

：改造用戶表的查詢操作-if<where>元素16當只傳入參數：用戶角色，而不傳入參數：用戶名稱的時候，控制臺報SQL異常錯誤？正確結果指定用戶角色下的所有用戶數據原因如何處理where問題分析select*fromtb_userwhereanduserRole=?<where>元素17where簡化SQL語句中where條件判斷智能處理and和or演示示例：改造用戶表的查詢操作-if+where改造訂單表查詢(if)18需求說明改造訂單表的查詢功能，使用動態SQL完善此功能查詢條件商品名稱(模糊查詢)供應商id是否付款查詢結果列列表訂單id、訂單編碼、商品名稱、供應商id、供應商名稱、訂單金額、是否付款、創建時間修改SQL語句–使用if完成時間：15分鐘練習指導改造供應商表查詢(if+where)19需求說明改造供應商表的查詢功能，使用動態SQL完善此功能查詢條件供應商編碼(模糊查詢)供應商名稱(模糊查詢)查詢結果列列表供應商id、供應商編碼、供應商名稱、聯系人、聯系電話、傳真、創建時間修改SQL語句–使用if+where組合完成時間：15分鐘練習指導共性問題集中講解20常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解

<trim>元素用于刪除多余的關鍵字，它可以直接實現<where>元素的功能。<trim>元素包含4個屬性。

<trim>元素

21屬性說明prefix

指定給SQL語句增加的前綴prefixOverrides

指定SQL語句中要去掉的前綴字符串suffix

指定給SQL語句增加的后綴suffixOverrides

指定SQL語句中要去掉的后綴字符串<trim>元素22trim屬性prefixsuffixprefixOverridessuffixOverrides更靈活地去除多余關鍵字替代where和set<selectid="getUserList5"resultType="User"> select*fromtb_user

<trimprefix="where"prefixOverrides="and|or"> <iftest="userName!=nullanduserName!=''"> anduserNamelikeCONCAT('%',#{userName},'%') </if> <iftest="userRole!=null"> anduserRole=#{userRole} </if> </trim></select>

上述配置代碼中，<trim>元素的作用是去除一些多余的前綴字符串，它的prefix屬性代表的是語句的前綴（where），而prefixOverrides屬性代表的是需要去除的前綴字符串（SQL中的“AND”或“OR”）。

<where>、<trim>元素23

在前兩個小節的案例中，映射文件中編寫的SQL后面都加入了“where1=1”的條件，那么到底為什么要這么寫呢？如果將where后“1=1”的條件去掉，那么MyBatis所拼接出來的SQL將會如下所示：select*fromtb_userwhereandusernamelikeconcat('%',?,'%')

可以看出上面SQL語句明顯存在SQL語法錯誤，而加入了條件“1=1”后，既保證了where后面的條件成立，又避免了where后面第一個詞是and或者or之類的關鍵詞。不過“where1=1”這種寫法對于初學者來將不容易理解，并且也不夠雅觀。<where>、<trim>元素24select*fromt_customerwhere1=1<choose><whentest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</when><whentest="jobs!=nullandjobs!=''">andjobs=#{jobs}</when><otherwise>andphoneisnotnull</otherwise></choose>

針對上述情況中“where1=1”，在MyBatis的SQL中就可以使用<where>或<trim>元素進行動態處理。<where>會自動判斷SQL語句，只有<where>內的條件成立時，才會在拼接SQL中加入where關鍵字，否則將不會添加；還會去除多余的“AND”或“OR”。<where>元素處理<trim>元素處理<trim>的作用是去除特殊的字符串，它的prefix屬性代表語句的前綴，prefixOverrides屬性代表需要去除的哪些特殊字符串，功能和<where>基本是等效的。動態SQL處理“where1=1”select*fromtb_user<where><iftest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</if><iftest="jobs!=nullandjobs!=''">andjobs=#{jobs}</if></where>select*fromtb_user<trimprefix="where"prefixOverrides="and"><iftest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</if><iftest="jobs!=nullandjobs!=''">andjobs=#{jobs}</if></trim>STEP01

在使用<if>元素時，只要test屬性中的表達式為true，就會執行元素中的條件語句，但是在實際應用中，有時只需要從多個選項中選擇一個去執行。

例如下面的場景：“當客戶名稱不為空，則只根據客戶名稱進行客戶篩選；當客戶名稱為空，而客戶職業不為空，則只根據客戶職業進行客戶篩選。當客戶名稱和客戶職業都為空，則要求查詢出所有電話不為空的客戶信息。”

針對上面情況，使用<if>元素進行處理是不合適的。MyBatis提供了<choose>、<when>、<otherwise>元素進行處理，這三個元素往往組合在一起使用，作用相當于Java語言中的if…elseif…else。STEP03<choose><when>otherwise>使用場景<choose>、<when>、<otherwise>元素25<choose>、<when>、<otherwise>元素26“當客戶名稱不為空，則只根據客戶名稱進行客戶篩選；

當客戶名稱為空，而客戶職業不為空，則只根據客戶職業進行客戶篩選。

當客戶名稱和客戶職業都為空，則要求查詢出所有電話不為空的客戶信息。”假設如下場景：

這種情況下，使用<if>元素進行處理是非常不合適的。如果使用的是Java語言，這種情況顯然更適合使用switch…case…default語句來處理，而在SQL中就可以使用<choose>、<when>、<otherwise>元素組合進行處理。其基本使用示例如代碼所示：select*fromtb_userwhere1=1<choose><whentest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</when><whentest="jobs!=nullandjobs!=''">andjobs=#{jobs}</when><otherwise>andphoneisnotnull</otherwise></choose>使用<choose>及其子元素依次對條件進行非空判斷，并動態組裝SQLchoose(when、otherwise)27choose(when、otherwise)相當于Java中switch語句當when有條件滿足的時候，就跳出choose演示示例：改造用戶表的查詢操作-choose<choose> <whentest="條件1">…</when> <whentest="條件2">…</when> <whentest="條件3">…</when> … <otherwise>…</otherwise></choose> 改造供應商列表查詢-choose28需求說明實現按條件查詢供應商表，查詢條件如下供應商編碼（模糊查詢）供應商名稱（模糊查詢）供應商聯系人（模糊查詢）創建時間在本年內（時間范圍）查詢結果列顯示：供應商id、供應商編碼、供應商名稱、供應商聯系人、創建時間choose(when、otherwise)完成時間：15分鐘練習指導共性問題集中講解29常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解使用動態SQL語句實現更新03第3章動態SQL語句<set>元素使用場景

在Hibernate框架中，如果想要更新某一個對象，就需要發送所有的字段給持久化對象，然而在實際應用中，大多數情況下都是更新某一個或幾個字段。如果更新的每一條數據都要將其所有的屬性都更新一遍，那么執行效率是非常差的。為了解決更新數據的效率問題，MyBatis提供了<set>元素。<set>元素主要用于更新操作，它可以在動態SQL語句前輸出一個SET關鍵字，并將SQL語句中最后一個多余的逗號去除。<set>元素與<if>元素結合可以只更新需要更新的字段。

更新操作31<set>元素32select*fromt_customerwhere1=1<choose><whentest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</when><whentest="jobs!=nullandjobs!=''">andjobs=#{jobs}</when><otherwise>andphoneisnotnull</otherwise></choose>

在Hibernate中，想要更新某個對象，就需要發送所有的字段給持久化對象，這種想更新的每一條數據都要將其所有的屬性都更新一遍的方法，其執行效率是非常差的。為此，在MyBatis中可以使用動態SQL中的<set>元素進行處理：<updateid="update"parameterType=“User">updatetb_user<set><iftest="username!=nullandusername!=''">username=#{username},</if><iftest="jobs!=nullandjobs!=''">jobs=#{jobs},</if></set>whereid=#{id}</update>使用<set>和<if>元素對username和jobs進行更新判斷，并動態組裝SQL。這樣就只需要傳入想要更新的字段即可<set>元素字段非空

在映射文件中使用<set>元素和<if>元素組合進行update語句動態SQL組裝時，如果<set>元素內包含的內容都為空，則會出現SQL語法錯誤。因此，在使用<set>元素進行字段信息更新時，要確保傳入的更新字段不能都為空。更新操作33set34更新用戶表數據時，若某個參數為null時，會導致更新錯誤正確結果若某個參數為null，則不需要更新，保持數據庫原值原因SQL語句如何處理ifset演示示例：改造用戶表的修改操作-if+set指導注意技能訓練—改造供應商表修改操作(if+set)35需求說明改造供應商表的修改功能，使用動態SQL完善此功能修改SQL語句–使用if+set組合完成時間：15分鐘練習指導共性問題集中講解36常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解使用<trim>元素更新

除了使用<set>元素外，還可以通過<trim>元素來實現更新操作。其中，<trim>元素的prefix屬性指定要添加的<trim>元素所包含內容的前綴為set，suffixOverrides屬性指定去除的<trim>元素所包含內容的后綴為逗號。更新操作37trim38if+trim使用if+trim替代if+set進行更新用戶表數據，效果一樣演示示例：改造用戶表的修改操作-if+trim<updateid="modify"parameterType="User">updatetb_user<trimprefix="set"suffixOverrides=","suffix="whereid=#{id}"> <iftest="userCode!=null">userCode=#{userCode},</if> <iftest="userName!=null">userCode=#{userName},</if> <iftest="userPassword!=null">userPassword=#{userPassword},</if></trim></update>示例改造供應商表修改操作(if+trim)39需求說明改造供應商表的修改功能，使用動態SQL完善此功能修改SQL語句–使用if+trim組合完成時間：15分鐘練習指導共性問題集中講解40常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解使用<foreach>元素實現復雜查詢04第3章動態SQL語句<foreach>元素42select*fromt_customerwhere1=1<choose><whentest="username!=nullandusername!=''">andusernamelikeconcat('%',#{username},'%')</when><whentest="jobs!=nullandjobs!=''">andjobs=#{jobs}</when><otherwise>andphoneisnotnull</otherwise></choose>

在一個客戶表中有1000條數據，現在需要將id值小于100的客戶信息全部查詢出來，這要怎么做呢？假設如下需求：一條一條的查詢1在Java中用for循環查詢2那如果要查詢1000條數據呢,豈不是很累?考慮過N條查詢語句時的查詢效率了嗎？<foreach>元素43foreach迭代一個集合，通常用于in條件屬性itemindexcollection:必須指定listarraymap-keyopenseparatorclose<foreach>元素的屬性

44屬性說明item

表示集合中每一個元素進行迭代時的別名。該屬性為必選。index在List和數組中，index是元素的序號，在Map中，index是元素的key。該屬性可選。open

表示foreach語句代碼的開始符號，一般和close=“)”合用。常用在in條件語句中。該屬性可選。separator

表示元素之間的分隔符，例如，在條件語句中，separator=“,”會自動在元素中間用“,”隔開，避免手動輸入逗號導致SQL錯誤，錯誤示例如in(1,2,)。該屬性可選。close表示foreach語句代碼的關閉符號，一般和open="("合用。常用在in條件語句中。該屬性可選。collection用于指定遍歷參數的類型。注意，該屬性必須指定，不同情況下，該屬性的值是不一樣的。

<foreach>元素45需求說明：指定用戶角色(1-n個)，獲取這些用戶角色下的用戶列表信息查詢SQL語句含有in條件使用foreach實現參數：用戶角色列表參數類型：數組演示示例：獲取指定用戶角色下用戶列表-foreach_array分析select*fromtb_user whereuserRolein(參數1,參數2,參數3…);<foreach>元素46

針對上述需求，理想的解決方法就是使用MyBatis中動態SQL的<foreach>元素進行處理。其基本使用示例如下所示：<selectid="findUserByIds"parameterType="List"resultType=“User">select*fromtb_userwhereidin<foreachitem="id"index="index"collection="list"open="("separator=","close=")">#{id}</foreach></select><foreach>元素47

關于上述示例中<foreach>元素中使用的幾種屬性的描述具體如下：<foreach>主要屬性item：配置的是循環中當前的元素。collection：配置的list是傳遞過來的參數類型（首字母小寫），它可以是一個array、list（或collection）、Map集合的鍵、POJO包裝類中數組或集合類型的屬性名等。index：配置的是當前元素在集合的位置下標。separator：配置的是各個元素的間隔符。open和close：配置的是以什么符號將這些集合元素包裝起來。在遍歷參數時，<collection>屬性的值是必須指定的。不同情況下，該屬性的取值也是不一樣的，主要有以下三種情況。List類型

若入參為單參數且參數類型是一個List，collection屬性值為list。數組類型若入參為單參數且參數類型是一個數組，collection屬性值為array。Map類型

若傳入參數為多參數，就需要把參數封裝為一個Map進行處理，collection屬性值為Map。<collection>屬性的取值48<foreach>元素49

在使用<foreach>時最關鍵也是最容易出錯的就是collection屬性，該屬性是必須指定的，而且在不同情況下，該屬性的值是不一樣的。主要有以下3種情況：如果傳入的是單參數且參數類型是一個數組或者List的時候，collection屬性值分別為array和list（或collection）。1如果傳入的參數是多個的時候，就需要把它們封裝成一個Map了，當然單參數也可以封裝成Map集合，這時候collection屬性值就為Map的鍵。2如果傳入的參數是POJO包裝類的時候，collection屬性值就為該包裝類中需要進行遍歷的數組或集合的屬性名。3<foreach>元素50需求說明：改造上一個演示示例，使用foreach實現，參數類型改為List演示示例：獲取指定用戶角色下用戶列表-foreach_list獲取指定供應商列表下的訂單列表51需求說明指定供應商列表(1-n個)，獲取這些供應商下的訂單列表信息要求使用foreach實現，參數類型為數組完成之后，把參數類型改為List實現此功能完成時間：20分鐘練習共性問題集中講解52常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解<foreach>元素53需求說明：在上一個演示示例中，增加一個參數：gender，要求查詢出指定性別和用戶角色列表下的用戶列表多參數封裝成Map單參數也可封裝成Map嗎？演示示例：獲取多參數下用戶列表-foreach_map分析問題思考獲取多參數下的訂單列表54需求說明：根據訂單編碼（模糊查詢），指定供應商列表(1-n個)，獲取相應的訂單列表信息多參數封裝成Map完成時間：30分鐘練習指導共性問題集中講解55常見問題及解決辦法代碼規范問題調試技巧共性問題集中講解使用<bin