案例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,’張學友’,50),(3,’劉德華’,50);

if元素

相當于java中的if判斷，語法：

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

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

當test成立的時候，if體內部的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>

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

當只傳入id的時候，sql如下：

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

當3個參數都傳了，sql如下：

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

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

choose/when/otherwise元素

這個相當于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內部的條件滿足一個，choose內部的sql拼接就會結束。

otherwise屬于可選的，當所有條件都不滿足的時候，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中已經考慮到這種問題了，屬于通用性的問題，mybatis中通過where 元素來解決，當使用where元素的時候，mybatis會將where內部拼接的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 元素。

當傳入id、name的時候，where內部的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元素

現在我們想通過用戶id更新用戶信息，參數為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>

我們來看一下，當所有屬性都傳值了，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元素會對其內部拼接的sql進行處理，會將這部分sql前后的逗號給去掉并在前面加上set。

當傳入id和age的時候，生成的sql：

UPDATE t_user SET age = ? where id = ?

trim元素

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

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

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

1.先對trim內部的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都設置的是逗號，表示trim內部的sql前后的逗號會被去掉，最后會在前面拼接一個prefix指定的set。

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

org.apache.ibatis.scripting.xmltags.TrimSqlNode

實際上where和set的實現是繼承了TrimSqlNode，where對應的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對應的 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來實現的。

foreach元素

相當于java中的循環，可以用來遍歷數組、集合、map等。

語法

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

案例：in多值查詢

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

<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那部分判斷，判斷這個參數不為空，并且size()大于1，表示這個集合不為空，然后會走if元素內部的foreach元素。

比如我們傳遞的idList對應的是[1,2]，最后產生的sql如下：

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

案例：批量插入

傳入UserModel List集合，使用foreach實現批量插入，如下：

<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個元素一般進行配合使用，可以實現代碼重用的效果。

sql元素可以用來定義一段動態sql，語法如下：

<sql id='sql片段id'>各種動態sql</sql>

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

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

注意：refid值的寫法，refid的值為mapper xml的namespace的值.sql的id，如果在同一個mapper中，namespace可以省略，直接寫對應的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表達式在上下文中自定義一個變量，最后在動態sql中可以使用這個變量。

語法

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

案例

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

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

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

對應的測試用例：

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)]

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

#和$

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

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

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

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

下面通過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'

最后運行產生的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=張學友, 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替換。

到此這篇關于Mybatis 動態SQL的幾種實現方法的文章就介紹到這了,更多相關Mybatis 動態SQL內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！