MySQL動(dòng)態(tài)SQL拼接實(shí)例詳解
目標(biāo)
能夠使用mybatis的標(biāo)簽實(shí)現(xiàn)動(dòng)態(tài)SQL拼接
分析
我們?cè)谇斑叺膶W(xué)習(xí)過程中,使用的SQL語(yǔ)句都非常簡(jiǎn)單。而在實(shí)際業(yè)務(wù)開發(fā)中,我們的SQL語(yǔ)句通常是動(dòng)態(tài)拼接而成的,比如:條件搜索功能的SQL語(yǔ)句。
# 提供了一個(gè)功能:用戶可以在頁(yè)面上根據(jù)username、sex、address進(jìn)行搜索 # 用戶輸入的搜索條件:可以是一個(gè)條件,也可能是兩個(gè)、三個(gè) # 只輸入一個(gè)條件:姓名是"王" SELECT * FROM USER WHERE username LIKE '%王%' # 只輸入一個(gè)條件:性別是“男” SELECT * FROM USER WHERE sex = '男' # 輸入兩個(gè)條件:姓名“王”,性別“男” SELECT * FROM USER WHERE username LIKE '%王%' AND sex = '男' # 輸入三個(gè)條件:姓名“王”,性別“男”,地址“北京” SELECT * FROM USER WHERE username LIKE '%王%' AND sex = '男' AND address LIKE '%北京%';
在Mybatis中,SQL語(yǔ)句是寫在映射配置的XML文件中的。Mybatis提供了一些XML的標(biāo)簽,用來實(shí)現(xiàn)動(dòng)態(tài)SQL的拼接。
? 常用的標(biāo)簽有:
- <if></if>:用來進(jìn)行判斷,相當(dāng)于Java里的if判斷
- <where></where>:通常和if配合,用來代替SQL語(yǔ)句中的where 1=1
- <foreach></foreach>:用來遍歷一個(gè)集合,把集合里的內(nèi)容拼接到SQL語(yǔ)句中。例如拼接:in (value1, value2, ...)
- <sql></sql>:用于定義sql片段,達(dá)到重復(fù)使用的目的
講解
1. 準(zhǔn)備Mybatis環(huán)境
- 創(chuàng)建java項(xiàng)目,導(dǎo)入jar包;準(zhǔn)備JavaBean
- 創(chuàng)建映射器接口UserDao
- 創(chuàng)建映射配置文件UserDao.xml
- 創(chuàng)建全局配置文件SqlMapConfig.xml
- 創(chuàng)建日志配置文件log4j.properties
2. <if>標(biāo)簽:
語(yǔ)法介紹
<if test="判斷條件,使用OGNL表達(dá)式進(jìn)行判斷"> SQL語(yǔ)句內(nèi)容, 如果判斷為true,這里的SQL語(yǔ)句就會(huì)進(jìn)行拼接 </if>
使用示例
根據(jù)用戶的名稱和性別搜索用戶信息。把搜索條件放到User對(duì)象里,傳遞給SQL語(yǔ)句
映射器接口UserDao上加方法
package com.demo.dao; import com.demo.domain.User; import java.util.List; public interface UserDao { /** * 根據(jù)username和sex搜索用戶 * @param user 封裝了搜索條件的User對(duì)象 * @return 搜索的結(jié)果 */ List<User> search1(User user); }
映射文件UserDao.xml里配置statement
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.itheima.dao.UserDao"> <!-- if標(biāo)簽:用于條件判斷 語(yǔ)法:<if test="用OGNL表達(dá)式判斷"> 如果判斷為true,這里的內(nèi)容會(huì)拼接上去 </if> 注意:標(biāo)簽里寫OGNL表達(dá)式,不要再加#{}、${} 常用的OGNL表達(dá)式: 比較:>, <, >=, <=, ==, != 或者 gt, lt, gte, lte, eq, neq 邏輯:&&,||,! 或者 and, or, not 調(diào)用方法:username.length(), list.size() --> <select id="search1" resultType="User"> select * from user where 1=1 <if test="username != null and username.length()>0"> and username like "%"#{username}"%" </if> <if test="sex != null and sex.length()>0"> and sex = #{sex} </if> </select> </mapper>
功能測(cè)試,在測(cè)試類里加測(cè)試方法
package com.demo; import com.demo.dao.UserDao; import com.demo.domain.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class SqlTest { private UserDao userDao; private SqlSession session; private InputStream is; /** * 要求:根據(jù)username和sex搜索用戶 * 搜索條件放到user對(duì)象里 */ @Test public void testSearch(){ User user = new User(); // user.setUsername("王"); // user.setSex("男"); List<User> userList = userDao.search1(user); userList.forEach(System.out::println); } @Before public void init() throws IOException { //1. 讀取全局配置文件 is = Resources.getResourceAsStream("SqlMapConfig.xml"); //2. 得到一個(gè)SqlSession對(duì)象 SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is); session = factory.openSession(); userDao = session.getMapper(UserDao.class); } @After public void destroy() throws IOException { session.close(); is.close(); } }
3. <where>標(biāo)簽
語(yǔ)法介紹
在剛剛的練習(xí)的SQL語(yǔ)句中,我們寫了where 1=1
。如果不寫的話,SQL語(yǔ)句會(huì)出現(xiàn)語(yǔ)法錯(cuò)誤。Mybatis提供了一種代替where 1=1
的技術(shù):<where></where>
標(biāo)簽。
代碼示例
? 把上一章節(jié)的實(shí)現(xiàn)代碼進(jìn)行優(yōu)化,使用<where></where>
標(biāo)簽代替where 1=1
映射器UserDao的search1方法:已有,不用修改
/** * 根據(jù)username和sex搜索用戶 * @param user 封裝了搜索條件的User對(duì)象 * @return 搜索的結(jié)果 */ List<User> search1(User user);
在映射文件UserDao.xml里修改SQL語(yǔ)句
<!-- where標(biāo)簽:讓Mybatis幫我們生成一個(gè)where關(guān)鍵字 Mybatis會(huì)智能判斷: 如果一個(gè)條件都沒有,就不生成where關(guān)鍵字 如果有條件,會(huì)判斷是否有多余的and關(guān)鍵字,把多余的and去掉 注意:建議把所有的where條件都放到where標(biāo)簽里邊 --> <select id="search1" resultType="User"> select * from user <where> <if test="username != null and username.length()>0"> and username like "%"#{username}"%" </if> <if test="sex != null and sex.length()>0"> and sex = #{sex} </if> </where> </select>
在測(cè)試類里進(jìn)行功能測(cè)試:測(cè)試方法不需要修改
@Test public void testSearch(){ User user = new User(); // user.setUsername("王"); // user.setSex("男"); List<User> userList = userDao.search1(user); userList.forEach(System.out::println); }
4. <foreach>標(biāo)簽 語(yǔ)法介紹
? foreach標(biāo)簽,通常用于循環(huán)遍歷一個(gè)集合,把集合的內(nèi)容拼接到SQL語(yǔ)句中。例如,我們要根據(jù)多個(gè)id查詢用戶信息,SQL語(yǔ)句:
select * from user where id = 1 or id = 2 or id = 3; select * from user where id in (1, 2, 3);
? 假如我們傳參了id的集合,那么在映射文件中,如何遍歷集合拼接SQL語(yǔ)句呢?可以使用foreach
標(biāo)簽實(shí)現(xiàn)。
<!-- foreach標(biāo)簽: 屬性: collection:被循環(huán)遍歷的對(duì)象,使用OGNL表達(dá)式獲取,注意不要加#{} open:循環(huán)之前,拼接的SQL語(yǔ)句的開始部分 item:定義變量名,代表被循環(huán)遍歷中每個(gè)元素,生成的變量名 separator:分隔符 close:循環(huán)之后,拼接SQL語(yǔ)句的結(jié)束部分 標(biāo)簽體: 使用#{OGNL}表達(dá)式,獲取到被循環(huán)遍歷對(duì)象中的每個(gè)元素 --> <foreach collection="" open="id in(" item="id" separator="," close=")"> #{id} </foreach>
使用示例
有搜索條件類QueryVO如下:
package com.itheima.domain; public class QueryVO { private Integer[] ids; public Integer[] getIds() { return ids; } public void setIds(Integer[] ids) { this.ids = ids; } }
在映射器UserDao里加方法
/** * QueryVO里有一個(gè)Integer[] ids * 要求:根據(jù)ids查詢對(duì)應(yīng)的用戶列表 */ List<User> search2(QueryVO vo);
在映射文件UserDao.xml里配置statement
<!-- foreach標(biāo)簽:用于循環(huán)遍歷 collection:被循環(huán)的集合/數(shù)組 item:定義一個(gè)變量 separator:定義拼接時(shí)的分隔符 open:拼接字符串時(shí)的開始部分 close:拼接字符串時(shí)的結(jié)束部分 相當(dāng)于 for(Integer id: ids){} select * from user where id in(41, 42, 45) --> <select id="search2" resultType="User"> <!--select * from user where id in(41, 42, 45)--> select * from user where <foreach collection="ids" open="id in(" item="id" separator="," close=")"> #{id} </foreach> </select>
功能測(cè)試
@Test public void testSearch2(){ QueryVO vo = new QueryVO(); vo.setIds(new Integer[]{41,42,43,44,45}); List<User> userList = userDao.search2(vo); userList.forEach(System.out::println); }
5. <sql>標(biāo)簽
在映射文件中,我們發(fā)現(xiàn)有很多SQL片段是重復(fù)的,比如:select * from user
。Mybatis提供了一個(gè)<sql>
標(biāo)簽,把重復(fù)的SQL片段抽取出來,可以重復(fù)使用。
語(yǔ)法介紹
在映射文件中定義SQL片段:
<sql id="唯一標(biāo)識(shí)">sql語(yǔ)句片段</sql>
在映射文件中引用SQL片段:
<include refid="sql片段的id"></include>
使用示例
在查詢用戶的SQL中,需要重復(fù)編寫:select * from user
。把這部分SQL提取成SQL片段以重復(fù)使用
要求:QueryVO里有ids,user對(duì)象。根據(jù)條件進(jìn)行搜索 修改QueryVO,增加成員變量user
package com.itheima.domain; /** * @author liuyp * @date 2021/09/07 */ public class QueryVO { private Integer[] ids; private User user; //get/set方法…… }
在映射器UserDao里加方法
/** * 動(dòng)態(tài)SQL拼接的綜合應(yīng)用:if、where、foreach * 要求:QueryVo里有ids、username、sex值,根據(jù)這些值進(jìn)行搜索 */ List<User> search3(QueryVO vo);
在映射文件UserDao.xml里配置statement
<select id="search3" resultType="User"> <!--select * from user--> <include refid="selUser"/> <where> <if test="ids != null and ids.length > 0"> <foreach collection="ids" open="and id in(" item="id" separator="," close=")"> #{id} </foreach> </if> <!--<if test="user != null"> <if test="user.username != null and user.username.length() > 0"> and username like "%"#{user.username}"%" </if> <if test="user.sex != null and user.sex.length() > 0"> and sex = #{user.sex} </if> </if>--> <include refid="userCondition"/> </where> </select> <!-- sql標(biāo)簽:用于定義一個(gè)sql片段 include標(biāo)簽:什么時(shí)候要引用某個(gè)SQL片段,就使用include標(biāo)簽 注意:引入SQL片段之后,最終的SQL語(yǔ)句必須要完全符合語(yǔ)法 --> <sql id="selUser">select * from user</sql> <sql id="userCondition"> <if test="user != null"> <if test="user.username != null and user.username.length() > 0"> and username like "%"#{user.username}"%" </if> <if test="user.sex != null and user.sex.length() > 0"> and sex = #{user.sex} </if> </if> </sql>
在測(cè)試類里加測(cè)試方法
@Test public void testSearch3(){ QueryVO vo = new QueryVO(); vo.setIds(new Integer[]{41,42,43,44,45}); // User user = new User(); // user.setUsername("王"); // user.setSex("男"); // vo.setUser(user); List<User> userList = userDao.search3(vo); userList.forEach(System.out::println); }
總結(jié)
到此這篇關(guān)于MySQL動(dòng)態(tài)SQL拼接的文章就介紹到這了,更多相關(guān)MySQL動(dòng)態(tài)SQL拼接內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)詳解
這篇文章主要給大家介紹了關(guān)于mysql中find_in_set()函數(shù)用法及自定義增強(qiáng)函數(shù)的相關(guān)資料,在MySQL 數(shù)據(jù)庫(kù)中進(jìn)行復(fù)雜的查詢語(yǔ)句,例如對(duì)多個(gè)字段進(jìn)行篩選和排序,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06MySQL中實(shí)現(xiàn)分頁(yè)操作的實(shí)戰(zhàn)指南
MySQL的分頁(yè)似乎一直是個(gè)問題,下面這篇文章主要給大家介紹了關(guān)于MySQL中實(shí)現(xiàn)分頁(yè)操作的相關(guān)資料,文中通過圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-08-08Dbeaver連接MySQL數(shù)據(jù)庫(kù)及錯(cuò)誤Connection?refusedconnect處理方法
這篇文章主要介紹了dbeaver連接MySQL數(shù)據(jù)庫(kù)及錯(cuò)誤Connection?refusedconnect處理方法,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-08-08MySQL優(yōu)化之表結(jié)構(gòu)優(yōu)化的5大建議(數(shù)據(jù)類型選擇講的很好)
很多人都將 數(shù)據(jù)庫(kù)設(shè)計(jì)范式 作為數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)“圣經(jīng)”,認(rèn)為只要按照這個(gè)范式需求設(shè)計(jì),就能讓設(shè)計(jì)出來的表結(jié)構(gòu)足夠優(yōu)化,既能保證性能優(yōu)異同時(shí)還能滿足擴(kuò)展性要求2014-03-03MySQL數(shù)據(jù)庫(kù)分組查詢group by語(yǔ)句詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)分組查詢group by語(yǔ)句詳解,文中含有詳細(xì)示例代碼說明解析,有需要的朋友可以借鑒參考想=下,希望能夠有所幫助2021-09-09