當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

动态SQL1

發(fā)布時(shí)間：2025/3/20 数据库 11 豆豆

生活随笔收集整理的這篇文章主要介紹了动态SQL1 小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

If標(biāo)簽

動(dòng)態(tài)SQL可以說(shuō)是MyBatis最強(qiáng)大之處了，這塊的應(yīng)用主要有四個(gè)方面if,choose,trim和foreach，接下來(lái)先說(shuō)說(shuō)if。

顧名思義，if是用來(lái)判斷條件的，現(xiàn)在假設(shè)我們有個(gè)需求，原先我們查詢員工是靠id查詢的，現(xiàn)在提出新的要求，要求用戶輸入攜帶了哪個(gè)字段查詢條件就帶上這個(gè)字段的值，

List<Employee>?getEmpsByConditionIf(Employee?e);??

那么這個(gè)sql語(yǔ)句該怎么寫(xiě)呢？

<select?id="EmployeeMapperDynamicTest"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee?where?id?=?#{id}?and?last_name?like?#{lastName}??

????and?email?=?#{email}?and?gender=#{gender}??

</select>?

這種寫(xiě)法顯然是有問(wèn)題的，如果傳入的Employee中l(wèi)astName為空，這個(gè)select語(yǔ)句就查不出我們想要的結(jié)果，還有很多類(lèi)似的情況，此時(shí)，就要使用到if，<if test=""></if>，test屬性中采用的是OGNL表達(dá)式，與EL表達(dá)式和你像，大家可以去官網(wǎng)查找具體的內(nèi)容[http://commons.apache.org/proper/commons-ognl/download_ognl.cgi]

在書(shū)寫(xiě)test屬性中的OGNL時(shí)要注意遇到特殊符號(hào)應(yīng)該寫(xiě)轉(zhuǎn)移字符，比如&要寫(xiě)成&;

具體的對(duì)應(yīng)關(guān)系可以查找W3C的手冊(cè):

[https://www.w3cschool.cn/htmltags/ref-entities.html]

<select?id="getEmpsByConditionIf"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee??

????<if?test="id!=null">??

????????id?=?#{id}??

????</if>??

????<if?test="lastName!=null?and?lastName!=""">??

????????and?last_name?like?#{lastName}??

????</if>??

????<if?test="email!=null?and?email.trim()!=""">??

????????and?email?=?#{email}??

????</if>??

????<if?test="gender==0?or?gender==1">??

????????gender=#{gender}??

????</if>??

</select>

注意兩點(diǎn)1.email判斷時(shí)用到了email.trim() 2.gender原本是字符串，可此處將它當(dāng)做是是數(shù)字,OGNL會(huì)進(jìn)行字符串與數(shù)字的轉(zhuǎn)化判斷.

????做一下測(cè)試：

public?static?SqlSessionFactory?getSqlSessionFactory()?throws?IOException?{??

????String?resource?=?"mybatis-conf.xml";??

????InputStream?inputStream?=?Resources.getResourceAsStream(resource);??

????return?new?SqlSessionFactoryBuilder().build(inputStream);??

}??

@Test??

public?void?testGetEmpsByConditionIf()?throws?IOException?{??

????SqlSessionFactory?sqlSessionFactory?=?getSqlSessionFactory();??

????SqlSession?openSession?=?sqlSessionFactory.openSession();??

????try?{??

????????EmployeeMapperDynamic?mapper?=?openSession.getMapper(EmployeeMapperDynamic.class);??

????????Employee?e?=?new?Employee();??

????????e.setId(5);??

????????e.setLastName("%青%");??

????????List<Employee>?employeeList?=?mapper.getEmpsByConditionIf(e);??

????????for?(Employee?employee:employeeList){??

????????????System.out.println(employee);??

????????}??

????}finally?{??

????????openSession.close();??

????}??

}??

結(jié)果：Employee{id=5, lastName='吳青寧', gender='1', email='wq', department=null}

接著，再試試創(chuàng)建的Employee對(duì)象，只給lastName一個(gè)屬性賦值的情況

@Test??

public?void?testGetEmpsByConditionIf()?throws?IOException?{??

????SqlSessionFactory?sqlSessionFactory?=?getSqlSessionFactory();??

????SqlSession?openSession?=?sqlSessionFactory.openSession();??

????try?{??

????????EmployeeMapperDynamic?mapper?=?openSession.getMapper(EmployeeMapperDynamic.class);??

????????Employee?e?=?new?Employee();??

????????e.setLastName("%青%");??

????????List<Employee>?employeeList?=?mapper.getEmpsByConditionIf(e);??

????????for?(Employee?employee:employeeList){??

????????????System.out.println(employee);??

????????}??

????}finally?{??

????????openSession.close();??

????}??

}??

結(jié)果：

????報(bào)錯(cuò)！！！

????分析一下，為什么？如果傳入的Employee對(duì)象中沒(méi)有id值，但是有l(wèi)astName時(shí)，SQL語(yǔ)句會(huì)變成下面這個(gè)樣子：

????select * from tb_employee and last_name like #{lastName}

顯然是個(gè)有問(wèn)題的查詢語(yǔ)句，我們?cè)撊绾谓鉀Q這個(gè)問(wèn)題？

????我們可以將<select>查詢中添加1=1，在之后的每個(gè)屬性前都加上and XXXXXXX，就像下面這樣：

<select?id="getEmpsByConditionIf"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee?where?1=1??

????<if?test="id!=null">??

????????and?id?=?#{id}??

????</if>??

????<if?test="lastName!=null?and?lastName!=""">??

????????and?last_name?like?#{lastName}??

????</if>??

????<if?test="email!=null?and?email.trim()!=""">??

????????and?email?=?#{email}??

????</if>??

????<if?test="gender==0?or?gender==1">??

????????gender=#{gender}??

????</if>??

</select>??

這顯然沒(méi)有問(wèn)題，另外一種方法，MyBatis的<where>標(biāo)簽

where標(biāo)簽

????使用方法很簡(jiǎn)單，將之前的<if>標(biāo)簽都包含在<where></where>之中即可

<select?id="getEmpsByConditionIf"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee??

????<where>??

????????<if?test="id!=null">??

????????????and?id?=?#{id}??

????????</if>??

????????<if?test="lastName!=null?and?lastName!=""">??

????????????and?last_name?like?#{lastName}??

????????</if>??

????????<if?test="email!=null?and?email.trim()!=""">??

????????????and?email?=?#{email}??

????????</if>??

????????<if?test="gender==0?or?gender==1">??

????????????gender=#{gender}??

????????</if>??

????</where>??

</select>?

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ?

可以發(fā)現(xiàn)<where>能將多余的and或者or去掉，當(dāng)然where也有不好使的時(shí)候，有些人習(xí)慣將and放在后面，如下:

<select?id="getEmpsByConditionIf"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee??

????<where>??

????????<if?test="id!=null">??

????????????and?id?=?#{id}?and??

????????</if>??

????????<if?test="lastName!=null?and?lastName!=""">??

????????????last_name?like?#{lastName}?and??

????????</if>??

????????<if?test="email!=null?and?email.trim()!=""">??

????????????email?=?#{email}?and??

????????</if>??

????????<if?test="gender==0?or?gender==1">??

????????????gender=#{gender}??

????????</if>??

????</where>??

</select>??

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ? and

SQL語(yǔ)句明顯出錯(cuò)，可見(jiàn)<where>只能去掉頭部的and或者or

trim標(biāo)簽

????如果我就想把a(bǔ)nd 放在后面，要怎么辦？這是我們?cè)撚?lt;trim>標(biāo)簽了，<trim>可以自定義截取標(biāo)簽

????<trim>標(biāo)簽包含4個(gè)屬性:

前綴：prefix

前綴覆蓋：prefixOverrides

后綴：suffix

后綴覆蓋：suffixOverrides

trim標(biāo)簽體中是整個(gè)字符串拼串后的結(jié)果，prefix給拼串后的整個(gè)字符串加一個(gè)前綴，比如我們可以不在SQL語(yǔ)句中寫(xiě)where而選擇在<trim>中寫(xiě)：<trim prefix="where">……

????前綴覆蓋指的是去掉拼串前面多余的字符

后綴和后綴覆蓋同理，那么我們?cè)诤竺嫣砑觓nd就有可能出現(xiàn)后面多出and的情況，因此我們只要指定后綴覆蓋的屬性值為and，就可以處理上述情況：

<select?id="getEmpsByConditionTrim"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee??

????<trim?prefix="where"?suffixOverrides="and">??

????????<if?test="id!=null">??

?????????????id?=?#{id}?and??

????????</if>??

????????<if?test="lastName!=null?and?lastName!=""">??

?????????????last_name?like?#{lastName}?and??

????????</if>??

????????<if?test="email!=null?and?email.trim()!=""">??

?????????????email?=?#{email}?and??

????????</if>??

????????<if?test="gender==0?or?gender==1">??

?????????????gender=#{gender}??

????????</if>??

????</trim>??

</select>??

DEBUG [main] - ==> Preparing: select * from tb_employee where last_name like ?

????雖然看似功能強(qiáng)大的<trim>其實(shí)并不常用，大家作為了解即可

choose標(biāo)簽

????這是一個(gè)分支選擇標(biāo)簽，類(lèi)似于Java中的switch-case，之前我們?cè)?lt;if>標(biāo)簽中是逐一判斷哪個(gè)條件是合法存在的，然后使用該標(biāo)簽，接下來(lái)我們換一種要求，已知傳入的Employee對(duì)象只會(huì)有一種屬性是存在值的，那么我們就可以使用choose，當(dāng)然也可以用if

????List<Employee> getEmpsByConditionChoose(Employee e);

<select?id="getEmpsByConditionChoose"?resultType="com.figsprite.bean.Employee">??

????select?*?from?tb_employee??

????<where>??

????????<choose>??

????????????<when?test="id!=null">??

????????????????id=#{id}??

????????????</when>??

????????????<when?test="lastName!=null?and?lastName!=""">??

????????????????last_name?like?#{lastName}??

????????????</when>??

????????????<when?test="email!=null?and?email.trim()!=""">??

????????????????email?=?#{email}??

????????????</when>??

????????????<otherwise>??

????????????????1=1??

????????????</otherwise>??

????????</choose>??

????</where>??

</select>??

(其中test屬性中的""要改成")

????<otherwise>標(biāo)簽，就是當(dāng)前面所有的when條件都沒(méi)中的時(shí)候，拼接上這個(gè)標(biāo)簽里的語(yǔ)句

@Test??

public?void?testGetEmpsByConditionIf()?throws?IOException?{??

????SqlSessionFactory?sqlSessionFactory?=?getSqlSessionFactory();??

????SqlSession?openSession?=?sqlSessionFactory.openSession();??

????try?{??

????????EmployeeMapperDynamic?mapper?=?openSession.getMapper(EmployeeMapperDynamic.class);??

????????Employee?e?=?new?Employee();??

????????e.setLastName("%青%");??

????????List<Employee>?employeeList?=?mapper.getEmpsByConditionChoose(e);??

????????for?(Employee?employee:employeeList){??

????????????System.out.println(employee);??

????????}??

????}finally?{??

????????openSession.close();??

????}??

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ?

如果傳入的對(duì)象同時(shí)包含id和lastName的話會(huì)查誰(shuí)？顯然是只會(huì)用id作為條件

轉(zhuǎn)載于:https://www.cnblogs.com/figsprite/p/10745050.html

總結(jié)

以上是生活随笔為你收集整理的动态SQL1的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

动态

上一篇： js 空语句
下一篇：一个故事讲清楚BIO NIO 异步