2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

引言

動(dòng)態(tài)SQL：Dynamic SQL。

本節(jié)我們來通過 MyBatis 的官方文檔進(jìn)行學(xué)習(xí)。

Description（描述）

官方描述如下：

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

● if ● choose (when, otherwise) ● trim (where, set) ● foreach

My View

上面這段話，總結(jié)起來就三點(diǎn)：

• MyBatis 在動(dòng)態(tài)SQL這塊很強(qiáng)大。

• 你需要會(huì) OGNL 表達(dá)式

• if/choose/trim/foreach

OGNL

用法看這里：OGNL指南

另外，支持轉(zhuǎn)義符，所以，看一下轉(zhuǎn)義：

if/where/trim/choose/foreach

• if 表示 判斷

<if test="title != null">AND title like #{title}</if>

• where

我們總會(huì)在if標(biāo)簽里寫AND，那么當(dāng)?shù)谝粋€(gè)不存在時(shí)就會(huì)出問題： sql: ...WHERE AND ...

因此 where 是解決這個(gè)問題的

<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">AND author_name like #{author.name}</if></where>

• trim

有人會(huì)說了，既然寫在前面有問題，那可不可以寫在后面呢？當(dāng)然可以。

來看些 trim 的用法：

<trim prefix="WHERE" prefixOverrides="AND |OR ">... </trim>-----<trim prefix="SET" suffixOverrides=",">... </trim>

總結(jié)一下： prefix:前面加什么 prefixOverrides:前面忽略掉什么 suffix:后綴 suffixOverrides:后面忽略掉什么

• choose

<choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND author_name like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose>

• foreach

遍歷一個(gè)集合。

collection：指定要遍歷的集合： list類型的參數(shù)會(huì)特殊處理封裝在map中，map的key就叫l(wèi)ist item：將當(dāng)前遍歷出的元素賦值給指定的變量 separator:每個(gè)元素之間的分隔符 open：遍歷出所有結(jié)果拼接一個(gè)開始的字符 close:遍歷出所有結(jié)果拼接一個(gè)結(jié)束的字符 index:索引。遍歷list的時(shí)候是index就是索引，item就是當(dāng)前值

WHERE ID in<foreach item="item" index="index" collection="list"open="(" separator="," close=")">#{item}</foreach>

set

<update id="testUpdate">UPDATEuser<set><if test="user.name != null">name = #{user.name},</if><if test="user.age != null and user.age >= 0">age = #{user.age}</if></set><where><if test="user.id != null and user.id > 0">id = #{user.id}</if></where></update>

批量插入

• 方法一：

<insert id="testBatchInsert1">INSERT INTOuser(name, age)VALUES<foreach collection="users" index="i" item="k" separator=",">(<if test="k.name != null">#{k.name}</if>,<if test="k.age != null and k.age >= 0">#{k.age}</if>)</foreach></insert>

• 方法二：

<insert id="testBatchInsert2"><foreach collection="users" index="i" item="k" separator=";">INSERT INTOuser(name, age)VALUES(<if test="k.name != null">#{k.name}</if>,<if test="k.age != null and k.age >= 0">#{k.age}</if>)</foreach></insert>

注意

1、數(shù)據(jù)庫(kù)的url應(yīng)該如下配置：

spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_demo?allowMultiQueries=true

2、此方法返回的影響行數(shù)不正確。

==> Preparing: INSERT INTO user(name, age) VALUES( ? , ? ) ; INSERT INTO user(name, age) VALUES( ? , ? ) ==> Parameters: n3(String), 11(Integer), n4(String), 12(Integer) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@64fc097e] rs num : 1

_parameter and _databaseId

_parameter，參數(shù) _databaseId，當(dāng)前數(shù)據(jù)庫(kù)的Id

bind

關(guān)于數(shù)據(jù)綁定，MyBatis官網(wǎng)提供的例子如下：

<select id="selectBlogsLike" resultType="Blog"><bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />SELECT * FROM BLOGWHERE title LIKE #{pattern} </select>

引用sql

有些重復(fù)的，可以抽取出來

后記

測(cè)試代碼：DynamicSQL-Demo

需要說明的是：為了保持測(cè)試風(fēng)格，有些返回值類型不正確，導(dǎo)致測(cè)試報(bào)錯(cuò)，但這并不影響著一節(jié)的學(xué)習(xí)，因?yàn)槟隳軌蚩吹絪ql和結(jié)果。

轉(zhuǎn)載于:https://my.oschina.net/fengwenyi/blog/1801918

總結(jié)

以上是生活随笔為你收集整理的【MyBatis】学习纪要六：动态SQL的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

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