【Mybatis學習筆記】—— 【四】動态SQL

需要更好的閱讀的體驗請移步 👉 小牛肉的個人部落格 👈

文章目錄

- 1. if 判斷 & OGNL 判斷表達式
- 2. where 查詢條件
- 3. trim 自定義字元串截取
- 4. choose 分支選擇
- 5. set 更新
- 6. foreach 周遊集合
- - 批量查詢
  - 批量儲存
- 7. 兩個内置參數 _databaseId / _parameter
- 8. bind 綁定
- 9. sql 抽取可重用的 sql 片段

動态 SQL是MyBatis強大特性之一。極大的簡化我們拼裝 SQL的操作。

動态 SQL 元素和使用 JSTL 或其他類似基于 XML 的文本處理器相似。

MyBatis 采用功能強大的基于

OGNL

的表達式來簡化操作。

if 判斷
choose (when, otherwise) 分支選擇
trim (where, set) 字元串截取
foreach 周遊集合

1. if 判斷 & OGNL 判斷表達式

//攜帶了哪個字段查詢條件就帶上這個字段的值
public List<Employee> getEmpsByConditionIf(Employee employee);

查詢員工，要求：攜帶了哪個字段查詢條件就帶上這個字段的值

<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	 <select id="getEmpsByConditionIf" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee where
		 	<!-- test：判斷表達式（OGNL）
		 	從參數中取值進行判斷
		 	-->
		 	<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> 
		 	<!-- ognl會進行字元串與數字的轉換判斷  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 </select>

如果查詢字段email為空

Employee employee = new Employee(1, "Admin", null, 1);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );

則查詢語句為

select * from tbl_employee where id = ? and last_name = ? and gender = ?

2. where 查詢條件

上述寫法我們可以看見一個問題，如果 id=null，那麼查詢語句就變成

select * from tbl_employee where and last_name = ? and gender = ?

文法出錯，and被強行拼接

解決方法有兩種：

在 selet * from table_name 後面加上 where 1 = 1
，并在所有的條件語句都加上and字首

則如果id = null，那麼查詢語句任然成立

<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	 <select id="getEmpsByConditionIf" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_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> 
		 	<!-- ognl會進行字元串與數字的轉換判斷  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 </select>

select * from tbl_employee where 1 = 1 and last_name = ? and gender = ?

将 if 判斷語句全都寫在 where 标簽中

<select id="getEmpsByConditionIf" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<!-- where -->
	 	<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> 
		 	<!-- ognl會進行字元串與數字的轉換判斷  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	and gender=#{gender}
		 	</if>
	 	</where>
	 </select>

where 會自動剔除多出來的第一個字首 and 或者 or

可以看見，上述的做法依然存在漏洞，

如果我們的寫法是把and放在後面：

<select id="getEmpsByConditionIf" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<where>
		 	<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> 
		 	<!-- ognl會進行字元串與數字的轉換判斷  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	gender=#{gender}
		 	</if>
	 	</where>
	 </select>

如果 gender 為空，則查詢語句為

文法出錯，因為 where 隻能剔除多出來的第一個字首 and 或者 or

是以，在使用where标簽的時候，建議把and寫在語句的前面

3. trim 自定義字元串截取

對于上述把and寫在後面的寫法，我們可以使用

trim

标簽自定義字元串的截取規則

trim 标簽中的屬性：

prefix=""
: 字首：trim标簽體中是整個字元串拼串後的結果。

prefix給拼串後的整個字元串加一個字首
prefixOverrides=""
: 字首覆寫：

去掉整個字元串前面多餘的字元
suffix=""
: 字尾

suffix給拼串後的整個字元串加一個字尾
suffixOverrides=""
字尾覆寫：

去掉整個字元串後面多餘的字元

<select id="getEmpsByConditionTrim" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<trim prefix="where" suffixOverrides="and">
	 		<if test="id!=null">
		 		id=#{id} and
		 	</if>
		 	<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
		 		last_name like #{lastName} and
		 	</if>
		 	<if test="email!=null and email.trim()!=&quot;&quot;">
		 		email=#{email} and
		 	</if> 
		 	<!-- ognl會進行字元串與數字的轉換判斷  "0"==0 -->
		 	<if test="gender==0 or gender==1">
		 	 	gender=#{gender}
		 	</if>
		 </trim>
	 </select>

4. choose 分支選擇

choose (when, otherwise):分支選擇；

等同于帶了 break 的 swtich-case

如果帶了id就用id查，如果帶了lastName就用lastName查 ; 隻會進入其中一個查詢語句

<select id="getEmpsByConditionChoose" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee 
	 	<where>
	 		<!-- 如果帶了id就用id查，如果帶了lastName就用lastName查;隻會進入其中一個 -->
	 		<choose>
	 			<when test="id!=null">
	 				id=#{id}
	 			</when>
	 			<when test="lastName!=null">
	 				last_name like #{lastName}  // 模糊查詢
	 			</when>
	 			<when test="email!=null">
	 				email = #{email}
	 			</when>
	 			<otherwise>
	 				gender = 0 //如果上述條件都不符合，則執行此條語句
	 			</otherwise>
	 		</choose>
	 	</where>
	 </select>

如果 id != null

Employee employee = new Employee(1, "Admin", null, null);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );

則查詢語句為

如果全為 null

Employee employee = new Employee(null, null, null, null);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );

則查詢語句為

5. set 更新

我們之前的更新操作語句是這樣的

<update id="updateEmp">
		update tbl_employee 
		set last_name=#{lastName},email=#{email},gender=#{gender}
		where id=#{id}
	</update>

需要進行全字段更新

比如：已有資料庫資訊 id = 1 , lastName = Jack, email = 123, gender = 1;

我們想要更新它的lastName，則：

Employee employee = new Employee(1, "Admin", 123, 1);
List<Employee> emps = mapper.updateEmp(employee );

set 标簽用來執行更新操作，隻更新需要更新的字段：

<!--public void updateEmp(Employee employee);  -->
	 <update id="updateEmp">
	 	<!-- Set标簽的使用 -->
	 	update tbl_employee 
		<set>
			<if test="lastName!=null">
				last_name=#{lastName},
			</if>
			<if test="email!=null">
				email=#{email},
			</if>
			<if test="gender!=null">
				gender=#{gender}
			</if>
		</set>
		where id=#{id} 
	 </update>

set 标簽會自動剔除多餘的

，

比如：已有資料庫資訊 id = 1 , lastName = Jack, email = 123, gender = 1;

我們想要更新它的lastName，則：

Employee employee = new Employee(1, "Admin", null, null);
List<Employee> emps = mapper.updateEmp(employee );

可以看見 email 和 gender 都為空，即該字段不被更新，保持不變。

6. foreach 周遊集合

foreach 标簽中的屬性：

collection：指定要周遊的集合：

list類型的參數會特殊處理封裝在map中，map的key就叫list
item：将目前周遊出的元素指派給指定的變量
separator:每個元素之間的分隔符
open：周遊出所有結果拼接一個開始的字元
close:周遊出所有結果拼接一個結束的字元
index:索引。周遊list的時候是index就是索引，item就是目前值

周遊map的時候index表示的就是map的key，item就是map的值
#{變量名} 就能取出變量的值也就是目前周遊出的元素

批量查詢

//查詢員工id'在給定集合中的
public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->
	 <select id="getEmpsByConditionForeach" resultType="com.smallbeef.mybatis.bean.Employee">
	 	select * from tbl_employee
	 	<foreach collection="ids" item="item_id" separator=","
	 		open="where id in(" close=")">
	 		#{item_id}
	 	</foreach>
	 </select>

測試：

List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3,4));
for (Employee emp : list) 
	System.out.println(emp);

批量儲存

<!--public void addEmps(@Param("emps")List<Employee> emps);  -->
	 <!--MySQL下批量儲存：可以foreach周遊   mysql支援values(),(),()文法-->
	<insert id="addEmps">
	 	insert into tbl_employee
		values
		<foreach collection="emps" item="emp" separator=",">
			(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	 </insert>

測試：

@Test
	public void testBatchSave() throws IOException{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> emps = new ArrayList<>();
			emps.add(new Employee(null, "smith0x1", "[email protected]", "1",new Department(1)));
			emps.add(new Employee(null, "allen0x1", "[email protected]", "0",new Department(1)));
			mapper.addEmps(emps);
			openSession.commit();
		}finally{
			openSession.close();
		}
	}

7. 兩個内置參數 _databaseId / _parameter

兩個内置參數：

不隻是方法傳遞過來的參數可以被用來判斷，取值

mybatis預設還有兩個内置參數：

_parameter
: 代表整個參數

單個參數：_parameter就是這個參數

多個參數：參數會被封裝為一個map；_parameter就是代表這個map
_databaseId
: 如果配置了databaseIdProvider标簽。

_databaseId就是代表目前資料庫的别名oracle

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
	  <select id="getEmpsTestInnerParameter" resultType="com.smakk.mybatis.bean.Employee">

	  		<if test="_databaseId=='mysql'">
	  			select * from tbl_employee
	  			<if test="_parameter!=null">
	  				where last_name like #{lastName}
	  			</if>
	  		</if>
	  		<if test="_databaseId=='oracle'">
	  			select * from employees
	  			<if test="_parameter!=null">
	  				where last_name like #{_parameter.lastName}
	  			</if>
	  		</if>
	  </select>

8. bind 綁定

bind 元素可以從 OGNL 表達式中建立一個變量并将其綁定到上下文。

比如：

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->
	  <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">
	  		<bind name="_lastName" value="'%'+lastName+'%'"/>
	  		<if test="_databaseId=='mysql'">
	  			select * from tbl_employee
	  			<if test="_parameter!=null">
	  				where last_name like #{lastName}
	  			</if>
	  		</if>
	  </select>

測試：

Employee employee2 = new Employee();
employee2.setLastName("e");
List<Employee> list = mapper.getEmpsTestInnerParameter(employee2);
for (Employee employee : list) 
	System.out.println(employee);

lastName 的值

被 bind 拼接成

%e%

，即由精确查詢—> 模糊查詢

9. sql 抽取可重用的 sql 片段

抽取可重用的sql片段。友善後面引用

sql 抽取：經常将要查詢的列名，或者插入用的列名抽取出來友善引用
include 來引用已經抽取的sql：
include 還可以自定義一些 property ，可在sql标簽内部通過 ${prop}
取出對應值

不能使用這種方式 #{prop}

示例如下：

<sql id="insertColumn">
  		<if test="_databaseId=='oracle'">
  			employee_id,last_name,email,${testColumn}
  		</if>
  		<if test="_databaseId=='mysql'">
  			last_name,email,gender,d_id
  		</if>
  </sql>

<insert id="addEmps" databaseId="oracle">
	 	insert into employees(
	 		<!-- 引用外部定義的sql -->
	 		<include refid="insertColumn">
	 			<property name="testColomn" value="abc"/>
	 		</include>
	 	)
	 	<foreach collection="emps" item="emp" separator="union"
	 		open="select employees_seq.nextval,lastName,email from("
	 		close=")">
	 		select #{emp.lastName} lastName,#{emp.email} email from dual
	 	</foreach>
	 </insert>

【Mybatis學習筆記】—— 【四】動态SQL

文章目錄

1. if 判斷 & OGNL 判斷表達式

2. where 查詢條件

3. trim 自定義字元串截取

4. choose 分支選擇

5. set 更新

6. foreach 周遊集合

批量查詢

批量儲存

7. 兩個内置參數 _databaseId / _parameter

8. bind 綁定

9. sql 抽取可重用的 sql 片段

繼續閱讀

SSM 整合 Spring+Spring MVC+MyBatisSSM 整合

MyEclipse安裝MyBatipse 從dao快速定位mapper的sql語句

Mybatis問答集

【附源碼】計算機畢業設計SSM網絡求職招聘系統

Mybatis源碼閱讀（一）：Mybatis初始化1.1 解析properties、settings前言入口引申結語

mybatis源碼解析（二）初始化SqlSessionFactory對象

Mybatis源碼閱讀（四）：核心接口4.2——Executor（上）

MyBatis源碼解析(一)——MyBatis初始化過程解MyBatis源碼解析(一)——MyBatis初始化過程解1. 準備工作2. MyBatis初始化過程

從你的WEB世界路過之--0.初識0) 前言1) WEB基本概念2) 響應資料3. 結語

Java 實作Gbase資料庫增删改查功能

mybatis——使用代理對象進行資料庫操作

mybatis中#{}和${}和差別，以及防止sql注入。

myeclipse 搭建mybatis架構時報錯（1）Communications link failure 通訊連結故障

MyBatis學習筆記Mybatis學習筆記（maven、Idea版）

用mybatis的generator插件在項目中自動生成dao及entity

從mybatis到mybatis plus(mp)