案例sql腳本

DROP DATABASE IF EXISTS `javacode2018`;CREATE DATABASE `javacode2018`;USE `javacode2018`;DROP TABLE IF EXISTS t_user;CREATE TABLE t_user( id int AUTO_INCREMENT PRIMARY KEY COMMENT ’用戶id’, name VARCHAR(32) NOT NULL DEFAULT ’’ COMMENT ’用戶名’, age SMALLINT NOT NULL DEFAULT 1 COMMENT ’年齡’) COMMENT ’用戶表’;INSERT INTO t_user VALUES (1,’路人甲Java’,30),(2,’張學(xué)友’,50),(3,’劉德華’,50);

if元素

相當(dāng)于java中的if判斷，語法：

<if test='判斷條件'>需要追加的sql</if>

test的值為一個判斷表達式，寫法上采用OGNL表達式的方式，OGNL在struts2中用的比較多，本文暫時對ognl不做詳細介紹，有興趣的可以去查一下相關(guān)資料。

當(dāng)test成立的時候，if體內(nèi)部的sql會被拼接上。

如：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user WHERE 1 = 1 <if test='id!=null'> AND id = #{id} </if> <if test='name!=null and name.toString()!=’’'> AND name = #{name} </if> <if test='age!=null'> AND age = #{age} </if></select>

上面查詢用戶列表，參數(shù)為一個map，當(dāng)map中id不為空的時候，將其作為條件查詢，如果name不為空，將name也作為條件，如果age不為空，將age也作為條件進行查詢

當(dāng)只傳入id的時候，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

當(dāng)3個參數(shù)都傳了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? AND name = ? AND age = ?

上面這種寫法相對于java代碼看起來是不是清爽了很多，也更方便維護，大家注意一下sql中有個WHERE 1=1，如果沒有這個，上面單通過if元素就不好實現(xiàn)了，mybatis也有解決方案，稍后會說明。

choose/when/otherwise元素

這個相當(dāng)于java中的if…else if…else，語法：

<choose> <when test='條件1'> 滿足條件1追加的sql </when> <when test='條件2'> 滿足條件2追加的sql </when> <when test='條件n'> 滿足條件n追加的sql </when> <otherwise> 都不滿足追加的sql </otherwise></choose>

choose內(nèi)部的條件滿足一個，choose內(nèi)部的sql拼接就會結(jié)束。

otherwise屬于可選的，當(dāng)所有條件都不滿足的時候，otherwise將起效。

如：

傳入id、name、age作為條件，按順序進行判斷，如果id不為空，將id作為條件，忽略其他條件，如果id為空，會判斷name是否為空，name不為空將name作為條件，如果name為空，再看看age是否為空，如果age不為空，將age作為條件。

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user WHERE 1 = 1 <choose> <when test='id!=null'> AND id = #{id} </when> <when test='name!=null and name.toString()!=’’'> AND name = #{name} </when> <when test='age!=null'> AND age = #{age} </when> </choose></select>

如果id、name、age都傳了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

如果值傳遞了name、age，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND name = ?

name判斷在age前面，所以name條件先匹配上了。

where元素

上面2個案例的sql中都有where 1=1這部分代碼，雖然可以解決問題，但是看起來不美觀，如果將where 1=1中1=1這部分干掉，上面的兩個案例都會出問題，where后面會多一個AND符號，mybatis中已經(jīng)考慮到這種問題了，屬于通用性的問題，mybatis中通過where 元素來解決，當(dāng)使用where元素的時候，mybatis會將where內(nèi)部拼接的sql進行處理，會將這部分sql前面的AND 或者 OR給去掉，并在前面追加一個where，我們使用where元素來對上面的案例1進行改造，如下：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <where> <if test='id!=null'> AND id = #{id} </if> <if test='name!=null and name.toString()!=’’'> AND name = #{name} </if> <if test='age!=null'> AND age = #{age} </if> </where></select>

where 1=1被替換成了where 元素。

當(dāng)傳入id、name的時候，where內(nèi)部的sql會變成這樣：

AND id = ? AND name = ?

mybatis會對上面這個sql進行處理，將前面的AND給去掉，并在前面追加一個where，變成了下面這樣

where id = ? AND name = ?

案例2也用where改造一下，變成了下面這樣：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <where> <choose> <when test='id!=null'>AND id = #{id} </when> <when test='name!=null and name.toString()!=’’'>AND name = #{name} </when> <when test='age!=null'>AND age = #{age} </when> </choose> </where></select>

這下看起來是不是舒服很多了。

set元素

現(xiàn)在我們想通過用戶id更新用戶信息，參數(shù)為UserModel對象，對象中的屬性如果不為空，就進行更新，我們可以這么寫：

<update parameterType='com.javacode2018.chat05.demo8.model.UserModel'> UPDATE t_user SET <if test='name!=null'> name = #{name}, </if> <if test='age!=null'> age = #{age}, </if> <where> <if test='id!=null'> AND id = #{id} </if> </where></update>

我們來看一下，當(dāng)所有屬性都傳值了，sql變成了下面這樣：

UPDATE t_user SET name = ?, age = ?, where id = ?

上面這個sql是有問題的，where前面多了一個逗號，得想辦法將這個逗號去掉，這個逗號屬于最后一個需要更新的字段后面的逗號，屬于多余的，mybatis中提供了set元素來解決這個問題，將上面的代碼改成下面這樣：

<update parameterType='com.javacode2018.chat05.demo8.model.UserModel'> UPDATE t_user <set> <if test='name!=null'> name = #{name}, </if> <if test='age!=null'> age = #{age}, </if> </set> <where> <if test='id!=null'> AND id = #{id} </if> </where></update>

我們將sql中的set去掉了，加了個set元素，set元素會對其內(nèi)部拼接的sql進行處理，會將這部分sql前后的逗號給去掉并在前面加上set。

當(dāng)傳入id和age的時候，生成的sql：

UPDATE t_user SET age = ? where id = ?

trim元素

這個元素的功能比較強大，先看一下他的語法：

<trim prefix='' prefixOverrides='' suffix='' suffixOverrides=''></trim>

trim元素內(nèi)部可以包含各種動態(tài)sql，如where、chose、sql等各種元素，使用trim包含的元素，mybatis處理過程：

1.先對trim內(nèi)部的sql進行拼接，比如這部分sql叫做sql1

2.將sql1字符串前面的部分中包含trim的prefixOverrides指定的部分給去掉，得到sql2

3.將sql2字符串后面的部分中包含trim的suffixOverrides指定的部分給去掉，得到sql3

4.在sql3前面追加trim中prefix指定的值，得到sql4

5.在sql4后面追加trim中suffix指定的值，得到最終需要拼接的sql5

了解了這個過程之后，說明可以通過trim來代替where和set，我們使用trim來改造一下案例1，如下：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <trim prefix='where' prefixOverrides='and|or'> <if test='id!=null'> AND id = #{id} </if> <if test='name!=null and name.toString()!=’’'> AND name = #{name} </if> <if test='age!=null'> AND age = #{age} </if> </trim></select>

注意上面的prefixOverrides的值的寫法，如果有多個需要覆蓋的之間用|進行分割，suffixOverrides寫法和prefixOverrides的寫法類似。

我們在用trim來改造一下上面的update中的，如下：

<update parameterType='com.javacode2018.chat05.demo8.model.UserModel'> UPDATE t_user <trim prefix='SET' prefixOverrides=',' suffixOverrides=','> <if test='name!=null'> name = #{name}, </if> <if test='age!=null'> age = #{age}, </if> </trim> <where> <if test='id!=null'> AND id = #{id} </if> </where></update>

上面的prefixOverrides和suffixOverrides都設(shè)置的是逗號，表示trim內(nèi)部的sql前后的逗號會被去掉，最后會在前面拼接一個prefix指定的set。

大家有興趣的可以去看一下trim的java實現(xiàn)，代碼下面這個類中：

org.apache.ibatis.scripting.xmltags.TrimSqlNode

實際上where和set的實現(xiàn)是繼承了TrimSqlNode，where對應(yīng)的java代碼：

public class WhereSqlNode extends TrimSqlNode { private static List<String> prefixList = Arrays.asList('AND ','OR ','ANDn', 'ORn', 'ANDr', 'ORr', 'ANDt', 'ORt'); public WhereSqlNode(Configuration configuration, SqlNode contents) { super(configuration, contents, 'WHERE', prefixList, null, null); }}

set對應(yīng)的 java代碼：

public class SetSqlNode extends TrimSqlNode { private static final List<String> COMMA = Collections.singletonList(','); public SetSqlNode(Configuration configuration,SqlNode contents) { super(configuration, contents, 'SET', COMMA, null, COMMA); }}

最后都是依靠TrimSqlNode來實現(xiàn)的。

foreach元素

相當(dāng)于java中的循環(huán)，可以用來遍歷數(shù)組、集合、map等。

語法

<foreach collection='需要遍歷的集合' item='集合中當(dāng)前元素' index='' open='' separator='每次遍歷的分隔符' close=''>動態(tài)sql部分</foreach> collection：可以是一個List、Set、Map或者數(shù)組 item：集合中的當(dāng)前元素的引用 index：用來訪問當(dāng)前元素在集合中的位置 separator：各個元素之間的分隔符 open和close用來配置最后用什么前綴和后綴將foreach內(nèi)部所有拼接的sql給包裝起來。

案例：in多值查詢

我們對案例1做個改造，map中支持放入用戶的id列表（ArrayList），對應(yīng)的key為idList，然后支持多個用戶id查詢，此時我們需要用in來查詢，實現(xiàn)如下：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <where> <if test='id!=null'> AND id = #{id} </if> <if test='name!=null and name.toString()!=’’'> AND name = #{name} </if> <if test='age!=null'> AND age = #{age} </if> <if test='idList!=null and idList.size()>=1'> <foreach collection='idList' item='id' open='AND id in (' separator=',' close=')'>#{id} </foreach> </if> </where></select>

大家看一下上面idList那部分判斷，判斷這個參數(shù)不為空，并且size()大于1，表示這個集合不為空，然后會走if元素內(nèi)部的foreach元素。

比如我們傳遞的idList對應(yīng)的是[1,2]，最后產(chǎn)生的sql如下：

SELECT id,name,age FROM t_user WHERE id in ( ? , ? )

案例：批量插入

傳入UserModel List集合，使用foreach實現(xiàn)批量插入，如下：

<insert parameterType='list'> INSERT INTO t_user (id,name,age) VALUES <foreach collection='collection' separator=',' item='item'> (#{item.id}, #{item.name}, #{item.age}) </foreach></insert>

測試用例

com.javacode2018.chat05.demo8.Demo8Test#insertBatch@Testpublic void insertBatch() throws IOException { try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) { UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<UserModel> userModelList = new ArrayList<>(); for (int i = 0; i < 3; i++) { userModelList.add(UserModel.builder().id(10 + i).name('mybatis-' + i).age(20 + i).build()); } int count = mapper.insertBatch(userModelList); log.info('{}', count); }}

傳入了3個用戶信息，運行輸出

39:52.241 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Preparing: INSERT INTO t_user (id,name,age) VALUES (?, ?, ?) , (?, ?, ?) , (?, ?, ?) 39:52.317 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Parameters: 10(Integer), mybatis-0(String), 20(Integer), 11(Integer), mybatis-1(String), 21(Integer), 12(Integer), mybatis-2(String), 22(Integer)39:52.327 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - <== Updates: 339:52.327 [main] INFO c.j.chat05.demo8.Demo8Test - 3

sql/include元素

這兩2個元素一般進行配合使用，可以實現(xiàn)代碼重用的效果。

sql元素可以用來定義一段動態(tài)sql，語法如下：

<sql id='sql片段id'>各種動態(tài)sql</sql>

其他地方需要使用的時候需要通過include關(guān)鍵字進行引入：

<include refid='需要引入的sql片段的id'/>

注意：refid值的寫法，refid的值為mapper xml的namespace的值.sql的id，如果在同一個mapper中，namespace可以省略，直接寫對應(yīng)的sql的id就可以了，如：

<include refid='findSql'/><include refid='com.javacode2018.chat05.demo8.mapper.UserMapper.findSql'/>

來個案例

下面定義2個查詢，他們的查詢條件一樣，最后將條件抽出來用sql元素定義了一個片段，然后進行共用。

<sql id='findSql'> <where> <if test='id!=null'> AND id = #{id} </if> <if test='name!=null and name.toString()!=’’'> AND name = #{name} </if> <if test='age!=null'> AND age = #{age} </if> <if test='idList!=null and idList.size()>=1'> <foreach collection='idList' item='id' open='AND id in (' separator=',' close=')'>#{id} </foreach> </if> </where></sql><select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <include refid='com.javacode2018.chat05.demo8.mapper.UserMapper.findSql' /></select><select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT count(*) FROM t_user <include refid='findSql' /></select>

bind元素

bind元素允許我們通過ognl表達式在上下文中自定義一個變量，最后在動態(tài)sql中可以使用這個變量。

語法

<bind name='變量名稱' value='ognl表達式'>

案例

對sql、include中的案例進行擴展，添加一個按照用戶名模糊查詢，用戶名在map中對應(yīng)的key為likeName，主要修改上面sql片段部分，在sql中加入下面部分：

<if test='likeName!=null and likeName.trim()!=’’'> <bind name='nameLike' value='’%’+likeName.trim()+’%’' /> AND name like #{nameLike}</if>

先判斷傳入的參數(shù)likeName是否不為空字符串，然后使用bind元素創(chuàng)建了一個變量nameLike，值為’%’+likeName.trim()+’%’。

對應(yīng)的測試用例：

com.javacode2018.chat05.demo8.Demo8Test#getModelList@Testpublic void getModelList() throws IOException { try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) { UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map<String, Object> paramMap = new HashMap<>(); paramMap.put('likeName','java'); List<UserModel> userModelList = mapper.getList1(paramMap); log.info('{}', userModelList); }}

運行輸出：

06:25.633 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Preparing: SELECT id,name,age FROM t_user WHERE name like ? 06:25.671 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: %java%(String)06:25.690 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <== Total: 106:25.691 [main] INFO c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31)]

注意輸出中的第二部分，參數(shù)的值為%java%。

#和$

#和KaTeX parse error: Expected ’EOF’, got ’#’ at position 15: 一般都是結(jié)合變量來使用，如：#̲{}、{}這種來進行使用。

#{}:為參數(shù)占位符？，即sql預(yù)編譯，相當(dāng)于使用jdbc中的PreparedStatement中的sql占位符，可以防止sql注入

${}:為字符串替換， 即字符串拼接，不能訪問sql注入。

#{}的用法上面已經(jīng)有很多案例了，此處我們來一個${}的案例。

下面通過orderSql變量傳入任意的排序sql，如下：

<select resultType='com.javacode2018.chat05.demo8.model.UserModel' parameterType='map'> SELECT id,name,age FROM t_user <if test='orderSql'> ${orderSql} </if></select>

傳入值：

orderSql = 'order by id asc,age desc'

最后運行產(chǎn)生的sql如下：

20:32.138 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Preparing: SELECT id,name,age FROM t_user order by id asc,age desc 20:32.173 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: 20:32.196 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <== Total: 620:32.197 [main] INFO c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31), UserModel(id=2, name=張學(xué)友, age=50), UserModel(id=3, name=劉德華, age=50), UserModel(id=10, name=mybatis-0, age=20), UserModel(id=11, name=mybatis-1, age=21), UserModel(id=12, name=mybatis-2, age=22)]

mybatis會對$包含部分進行sql替換。

到此這篇關(guān)于Mybatis 動態(tài)SQL的幾種實現(xiàn)方法的文章就介紹到這了,更多相關(guān)Mybatis 動態(tài)SQL內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！