MyBatis動態(tài)SQL、模糊查詢與結(jié)果映射操作過程
前言
在我們編寫的sql語句的內(nèi)容并不是固定的,會通過一些條件判斷拼接成最終符合要求的sql語句。
本篇所講的動態(tài)SQL,是mybatis通過標(biāo)簽元素的形式, 如if, choose, when, otherwise, trim, where, set, foreach等標(biāo)簽完成對sql的拼接功能,使用起來也非常靈活方便,大大提高了開發(fā)人員的工作效率!
一、MyBatis動態(tài)SQL
1.動態(tài)SQL是什么
動態(tài)SQL是一種根據(jù)不同條件動態(tài)生成SQL語句的技術(shù)。在MyBatis中,我們可以使用動態(tài)SQL來根據(jù)不同的查詢條件生成不同的SQL語句,從而實現(xiàn)更靈活的查詢。
2.動態(tài)SQL的作用
1. 條件查詢:通過動態(tài)SQL,我們可以根據(jù)用戶輸入的條件動態(tài)生成查詢語句,從而實現(xiàn)靈活的條件查詢。例如,根據(jù)用戶選擇的不同篩選條件,我們可以動態(tài)地拼接SQL語句,實現(xiàn)根據(jù)不同條件查詢不同的結(jié)果。
2. 動態(tài)排序:有時候我們需要根據(jù)用戶的選擇對查詢結(jié)果進行排序。通過動態(tài)SQL,我們可以根據(jù)用戶選擇的排序字段和排序方式動態(tài)生成排序語句,從而實現(xiàn)按照不同的規(guī)則對查詢結(jié)果進行排序。
3. 動態(tài)更新:有時候我們需要根據(jù)不同的條件更新數(shù)據(jù)庫中的數(shù)據(jù)。通過動態(tài)SQL,我們可以根據(jù)不同的條件動態(tài)生成更新語句,從而實現(xiàn)根據(jù)不同的條件更新不同的數(shù)據(jù)。
4. 動態(tài)插入:有時候我們需要根據(jù)不同的條件插入數(shù)據(jù)到數(shù)據(jù)庫中。通過動態(tài)SQL,我們可以根據(jù)不同的條件動態(tài)生成插入語句,從而實現(xiàn)根據(jù)不同的條件插入不同的數(shù)據(jù)。
3.常用動態(tài)SQL元素
下面我們將以用戶表為例舉例說明:
1. where + if 元素
先來個簡單的需求,根據(jù) username 和 sex 來查詢user用戶,首先看下普通的sql:
<select id="selectUserByNameAndSex" resultType="com.ctb.model.User" parameterType="com.kzy.entity.User"> select * from user where username=#{username} and sex=#{sex} </select>
這種方式如果其中一個參數(shù)為空,可能就會導(dǎo)致最終查詢不到數(shù)據(jù)。
我們現(xiàn)在想實現(xiàn)如果username為空,將只根據(jù)sex來查詢;如果sex為空,我們將只根據(jù)username來查詢。那么可以使用 where + if 標(biāo)簽進行判斷,sql如下:
<select id="selectUserByNameOrSex" resultType="com.ctb.model.User" parameterType="com.ctb.model.User"> select * from user <where> <if test="username != null"> username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </where> </select>
if 元素:即根據(jù)條件判斷是否顯示其里面的內(nèi)容。 where 元素:自行判斷若下面的子元素有內(nèi)容,則此處會添加一個'where',如果下面的子元素?zé)o內(nèi)容,即條件判斷都為空,則此處不添加'where'。此外,如果where標(biāo)簽內(nèi)容以'and' 或 'or'開頭的話,會將and/or自動剔除(否則 'where and/or' 連在一起會報語法錯誤)。
2. set + if 元素
不僅查詢操作可能會用到動態(tài)sql,有時一些更新操作也會需要根據(jù)前端傳來的參數(shù)進行判斷,拼接符合條件的sql語句,如下:
<update id="updateUserById" parameterType="com.ctb.model.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>
若第一個條件 username 為空,那么 sql 語句為:update user u set u.sex=? where id=?
若第一個條件不為空,那么 sql 語句為:update user u set u.username = ? ,u.sex = ? where id=?
多個條件以此類推,最后一個if標(biāo)簽里的內(nèi)容結(jié)尾不用加逗號,是為了防止出現(xiàn) '... , where ...' 語法錯誤。
3. choose + when + otherwise 元素
有時候,我們不想使用所有的條件,而只是想從多個條件中選擇一個使用。針對這種情況,MyBatis 提供了 choose 元素,它有點像 Java 中的 switch 語句。
<select id="selectUserByChoose" resultType="com.ctb.model.User" parameterType="com.ctb.model.User"> select * from user <where> <choose> <when "id != null and test=id !='' "> id=#{id} </when> <when test="username != null and username !='' "> and username=#{username} </when> <otherwise> and sex=#{sex} </otherwise> </choose> </where> </select>
業(yè)務(wù)還是查詢user,三個條件分別為 id,username,sex,但是策略變?yōu)橹贿x擇一個作為查詢條件:傳入 “id” ,對應(yīng)的sql語句就是 select * from user where id=?;
傳入 “username” ,對應(yīng)的sql語句就是 select * from user where username=?;
如果前兩者都沒有傳入,那么默認選擇<otherwise>標(biāo)簽里的內(nèi)容,對應(yīng)的查詢語句為 select * from user where sex=?;
4. 自定義 trim 元素
如果 where 或者 set 元素與你期望的不太一樣,你也可以通過自定義 trim 元素來定制 where 或 set 的功能,非常實用!
<1>. 自定義 trim 元素改寫上面的 where + if 語句
<select id="selectUserByNameOrSex" resultType="com.ctb.model.User" parameterType="com.ctb.model.User"> select * from user <!-- <where> <if test="username != null"> username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </where> --> <!-- 改寫后的效果 --> <trim prefix="where" prefixOverrides="and | or"> <if test="username != null"> username=#{username} </if> <if test="sex != null"> and sex=#{sex} </if> </trim> </select>
prefix:插入 prefix 屬性中指定的內(nèi)容,即前綴。
prefixoverride:前綴覆蓋,去掉第一個and 或者or,即 prefixoverride 屬性中的內(nèi)容,此處為了防止拼接sql時出現(xiàn)" where and "這種情況。
<2>. 自定義 trim 元素改寫上面的 set + if 語句
<update id="updateUserById" parameterType="com.ctb.model.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> --> <!-- 修改后的內(nèi)容 --> <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>
suffixoverride:后綴覆蓋,去掉最后一個逗號,即 suffixoverride 屬性中的內(nèi)容,此處為了防止拼接sql時出現(xiàn)尾部多個逗號這種情況。
5. foreach 元素
foreach 元素的非常強大,它允許你指定一個集合,聲明可以在元素體內(nèi)使用的集合項(item)和索引(index)變量,它也允許你指定開頭與結(jié)尾的字符串以及集合項迭代之間的分隔符,不會有不必要的語法錯誤。
你可以將任何可迭代對象(如 List、Set 等)、Map 對象或者數(shù)組對象作為集合參數(shù)傳遞給 foreach 。當(dāng)使用可迭代對象或者數(shù)組時,index 是當(dāng)前迭代的序號,item 的值是本次迭代獲取到的元素。當(dāng)使用 Map 對象(或者 Map.Entry 對象的集合)時,index 是鍵,item 是值。
例如:現(xiàn)在我們有個簡單的sql語句:select * from user where id in (1,2,3);
現(xiàn)在我們來對它進行改寫 :
<select id="selectUserByListId" parameterType="java.util.List" resultType="com.ctb.model.User"> select * from user where id in <!-- collection:指定輸入對象中的集合屬性,可以是array數(shù)組,也可是list集合 item:每次遍歷生成的對象 open:開始遍歷時的拼接字符串 close:結(jié)束時拼接的字符串 separator:遍歷對象之間需要拼接的字符串 select * from user where id in (1,2,3) --> <foreach collection="list" item="id" open="(" close=") " separator=","> #{id} </foreach> </select>
foreach 元素內(nèi)各個屬性對應(yīng)的含義:
collection:指定輸入對象中的集合屬性,可以是array數(shù)組,也可是list集合
item:每次遍歷生成的對象
open:開始遍歷時的拼接字符串
close:結(jié)束時拼接的字符串
separator:遍歷對象之間需要拼接的字符串
6.SQL片段重用
該作用于當(dāng)我們經(jīng)常需要使用某表的某些字段時,我們可以它分裝起來,便于直接引用。
<sql id="userColumns"> id, name, sex,birthday </sql> <select id="selectUsers" resultMap="BaseResultMap"> SELECT <include refid="userColumns" /> FROM t_user </select> <select id="selectUsersWithPrice" resultMap="BaseResultMap"> SELECT <include refid="userColumns" />, info FROM t_user </select>
在這個示例中,通過<sql>標(biāo)簽定義了一個名為bookColumns的SQL片段,包含了書籍表的列名。然后,在不同的查詢語句中通過<include>標(biāo)簽引用了該SQL片段,實現(xiàn)了列名的重用。
二、模糊查詢的三種SQL方式、#和$的區(qū)別 三種模糊查詢的方法是:
1.使用#{字段名}
<select id="like1" resultType="com.ctb.model.Book" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from t_mvc_book where bname like #{bname} </select>
測試結(jié)果
2.使用${字段名}
<select id="like2" resultType="com.ctb.model.Book" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from t_mvc_book where bname like ${bname} </select>
測試結(jié)果:
3.使用concat{'%',#{字段名},'%'}
<select id="like3" resultType="com.ctb.model.Book" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from t_mvc_book where bname like concat('%',#{bname},'%') </select>
測試結(jié)果:
#與$的區(qū)別
1.$ 符號(sql拼接符號)
- $符號占位符是簡單的字符串替換,不進行預(yù)編譯和參數(shù)類型處理,也不會進行轉(zhuǎn)義。
- $符號占位符直接將參數(shù)的值替換到SQL語句中,可以用于動態(tài)拼接SQL語句的部分內(nèi)容。
- $符號占位符存在SQL注入的風(fēng)險,因為參數(shù)值直接替換到SQL語句中,可能導(dǎo)致惡意注入攻擊。
- 沒有 '引號'
2. # 符號(占位符)
- #符號占位符是預(yù)編譯的占位符,會對參數(shù)進行類型處理和安全處理。
- #符號占位符將參數(shù)值作為預(yù)編譯參數(shù)傳遞給數(shù)據(jù)庫,可以防止SQL注入攻擊。
- #符號占位符可以用于動態(tài)生成SQL語句的條件部分,例如WHERE子句、ORDER BY子句等。
- 有引號
注:
1) mybatis中使用OGNL表達式傳遞參數(shù)
2) 優(yōu)先使用#{...}
3) ${...}方式存在SQL注入風(fēng)險
#{...}
更加安全可靠,適用于大多數(shù)情況下。而 ${...}
則更加靈活,但需要注意潛在的安全風(fēng)險。在使用 ${...}
時,要確保參數(shù)值的來源可信,避免直接將用戶輸入的數(shù)據(jù)作為參數(shù)值傳遞進去。
四、MyBatis結(jié)果映射
Mybatis中結(jié)果集的處理分為兩種:
resultMap:適合使用返回值是自定義實體類的情況
resultType:適合使用返回值的數(shù)據(jù)類型是非自定義的,即jdk的提供的類型
映射場景
①返回單表的對應(yīng)的實體類,僅有一個查詢結(jié)果,可以用resultType/resultMap。
②返回單表的對應(yīng)的實體類,有多個查詢結(jié)果,可以用resultType/resultMap。
③返回多表對應(yīng)結(jié)果,僅有一個查詢結(jié)果,通常用resultType也可以用resultMap。
④返回多表對應(yīng)結(jié)果,有多個查詢結(jié)果,通常用resultType也可以用resultMap。
⑤返回單個列段,僅有一個查詢結(jié)果,就用resultType。
⑥返回單個列段,有多個查詢結(jié)果,就用resultType。
resultType進行結(jié)果映射
首先,我們需要定義一個User類,用于存儲查詢結(jié)果:
public class User { private int id; private String name; private int age; private String gender; private String email; // getter和setter方法省略 }
然后,在MyBatis的mapper文件中,我們可以使用resultType來映射查詢結(jié)果到User對象上。具體配置如下:
<select id="getUserById" resultType="com.ctb.model.User"> SELECT id, name, age, gender, email FROM user WHERE id = #{id} </select>
在上面的配置中,我們使用了resultType屬性來指定查詢結(jié)果映射到的Java對象的全限定名。這樣,當(dāng)執(zhí)行查詢語句時,MyBatis會自動將查詢結(jié)果映射到指定的Java對象上。
resultMap進行結(jié)果映射
首先,我們需要定義一個Order類,用于存儲查詢結(jié)果:
public class Order { private int id; private int userId; private int productId; private double price; private int quantity; // getter和setter方法省略 }
然后,在MyBatis的mapper文件中,我們可以使用resultMap來映射查詢結(jié)果到Order對象上。具體配置如下:
<resultMap id="OrderResultMap" type="com.example.Order"> <id property="id" column="id" /> <result property="userId" column="user_id" /> <result property="productId" column="product_id" /> <result property="price" column="price" /> <result property="quantity" column="quantity" /> </resultMap> <select id="getOrderById" resultMap="OrderResultMap"> SELECT id, user_id, product_id, price, quantity FROM order WHERE id = #{id} </select>
在上面的配置中,我們定義了一個名為"OrderResultMap"的resultMap,它的type屬性指定了要映射的Java對象的全限定名。接下來,我們?yōu)槊總€字段指定了對應(yīng)的屬性名和數(shù)據(jù)庫表中的列名。最后,在查詢語句中引用這個resultMap即可將查詢結(jié)果映射到Order對象上。
需要注意的是,如果查詢結(jié)果中的某個字段在Java對象中沒有對應(yīng)的屬性,那么該字段將被映射為null。此外,如果查詢結(jié)果中的某個字段在Java對象中有多個對應(yīng)的屬性,那么該字段的值將被映射為一個列表或數(shù)組。
總結(jié)
resultType
用于指定查詢結(jié)果的類型。它可以是Java的基本數(shù)據(jù)類型(如int、String等),也可以是自定義的Java對象。當(dāng)查詢結(jié)果只有一列時,可以使用 resultType
來指定該列的類型。例如,如果查詢結(jié)果只有一個整數(shù)列,可以將 resultType
設(shè)置為 int
。
resultMap
用于定義復(fù)雜的查詢結(jié)果映射關(guān)系。當(dāng)查詢結(jié)果包含多個列,并且需要將這些列映射到一個Java對象中時,可以使用 resultMap
來定義映射規(guī)則。 resultMap
可以指定每個列與Java對象的屬性之間的映射關(guān)系,以及一些其他的映射規(guī)則,如類型轉(zhuǎn)換、關(guān)聯(lián)關(guān)系等。
resultType
適用于簡單的查詢結(jié)果映射,而 resultMap
適用于復(fù)雜的查詢結(jié)果映射。
MyBatis動態(tài)SQL、模糊查詢與結(jié)果映射到這就結(jié)束了。
到此這篇關(guān)于MyBatis動態(tài)SQL、模糊查詢與結(jié)果映射的文章就介紹到這了,更多相關(guān)MyBatis動態(tài)SQL結(jié)果映射內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL關(guān)閉過程詳解和安全關(guān)閉MySQL的方法
這篇文章主要介紹了MySQL關(guān)閉過程詳解和安全關(guān)閉MySQL的方法,在了解了關(guān)閉過程后,出現(xiàn)故障能迅速定位,本文還給出了安全關(guān)閉MySQL的建議及方法,需要的朋友可以參考下2014-08-08MySQL默認值(DEFAULT)和非空約束(NOT NULL)的實現(xiàn)
本文主要介紹了MySQL默認值(DEFAULT)和非空約束(NOT NULL)的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05mysql報錯RSA?private?key?file?not?found的解決方法
當(dāng)MySQL報錯RSA?private?key?file?not?found時,可能是由于MySQL的RSA私鑰文件丟失或者損壞導(dǎo)致的,此時可以重新生成RSA私鑰文件,以解決這個問題2023-06-06最全50個Mysql數(shù)據(jù)庫查詢練習(xí)題
這篇文章主要介紹了最全50個數(shù)據(jù)庫查詢練習(xí)題,Mysql數(shù)據(jù)庫版本,全部都驗證過2020-12-12