MyBatis 動態SQL全面詳解
前言
前面mysql都是通過靜態sql進行查詢的，但是如果業務復雜的時候，我們會遇到引號問題，或者多一個空格，這就使得sql代碼編寫錯誤瞭，所以為瞭解決這個問題，我們有瞭動態sql。
Mybatis框架的動態SQL技術是一種根據特定條件動態拼裝SQL語句的功能，它存在的意義是為瞭解決拼接SQL語句字符串時的痛點問題。具體是通過標簽來實現的。
動態sql
1.先看一下模塊目錄結構
在類路徑的resources下的mapper包下創建sql.xml文件（共性抽取）
2.物理建模和邏輯建模
這裡省略物理建模步驟，要求數據庫的表與pojo類要對應。
package pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
private Integer empId;
private String empName;
private Double empSalary;
}
3. 引入依賴
把之前的log4j復制到類路徑resouces下，另外我們引入依賴後的pom.xml如下：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>day03-mybatis02-dynamic</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>provided</scope>
</dependency>
<!-- Mybatis核心 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- junit測試 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- MySQL驅動 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.3</version>
<scope>runtime</scope>
</dependency>
<!-- log4j日志 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
4.全局配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--駝峰映射-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--類型別名映射-->
<typeAliases>
<package name="pojo"/>
</typeAliases>
<!--環境配置-->
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="username" value="root"/>
<property name="password" value="888888"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/>
<property name="driver" value="com.mysql.jdbc.Driver"/>
</dataSource>
</environment>
</environments>
<!--路徑映射-->
<mappers>
<mapper resource="mapper/sql.xml"/>
<package name="mapper"/>
</mappers>
</configuration>
註意： 這裡有駝峰映射，別名映射，路徑映射和路徑映射。和以前的不同的是，我們這裡做瞭sql語句的共性抽取，所以得加一個sql的路徑映射 <mapper resource="mapper/sql.xml"/>。
5.sql共性抽取文件
在類路徑resources下的包mapper下創建一個sql.xml（因為我們sql是要寫在映射文件中，自己本身也是映射文件,所以需要寫在mapper下）。到要用的時候，在映射路徑文件中需要用到這個sql語句的地方加入 <include refid="mapper.sql.mySelectSql"></include>。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.sql">
<sql id="mySelectSql">
select emp_id,emp_name,emp_salary from t_emp
</sql>
</mapper>
共性抽取文件也可以不配置，這時候直接在映射文件中把要執行的語句重新編寫就行瞭。
6.mapper接口
一共有七個方法
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.Employee;
import java.util.List;
public interface EmployeeMapper {
//根據員工的empId查詢大於該empId的所有員工，如果empId為null，則查詢全體員工
List<Employee> selectEmployeeListByEmpId(Integer empId);
/**
* 查詢大於傳入的empId並且工資大於傳入的empSalary的員工集合,如果傳入的empId為null，則不考慮empId條件
* 傳入的empSalary為null則不考慮empSalary的條件
*/
List<Employee> selectEmployeeListByEmpIdAndEmpSalary(@Param("empId") Integer empId, @Param("empSalary") Double empSalary);
/**
* 根據empId更新員工信息,如果某個值為null，則不更新這個字段
*/
void updateEmployee(Employee employee);
/**
* 根據emp_id查詢員工信息，如果0<emp_id<6,那麼就查詢所有大於該emp_id的員工，如果emp_id是大於6，那麼就查詢所有小於該emp_id的員工
* 如果是其它情況，則查詢所有員工信息
*/
List<Employee> selectEmployeeList(Integer empId);
/**
* 添加員工信息
*/
void insertEmployee(Employee employee);
/**
* 批量添加員工集合
*/
void insertEmployeeList(@Param("employeeList") List<Employee> employeeList);
/**
* 根據員工的id集合查詢員工集
*/
List<Employee> selectEmployeeListByEmpIdList(List<Integer> idList);
}
if
目標：根據員工的empId查詢大於該empId的所有員工，如果empId為null，則查詢全體員工。
Dao接口的方法為：
List<Employee> selectEmployeeListByEmpId(Integer empId);
靜態sql:
<select id="selectEmployeeListByEmpId" resultType="Employee">
<include refid="mapper.sql.mySelectSql"></include> where emp_id>#{empId}
</select>
動態sql:
<select id="selectEmployeeListByEmpId" resultType="Employee">
<include refid="mapper.sql.mySelectSql"></include>
<if test="empId != null">
where emp_id>#{empId}
</if>
</select>
<include refid="mapper.sql.mySelectSql"></include>表示引用抽取出的sql片段，也可以直接寫sql語句。如果是靜態sql，當id為null時，查詢出來的是空，動態sql則可以查出全部。if標簽裡面有test屬性名，作為判斷語句。
where
目標：
- 查詢大於傳入的empId並且工資大於傳入的empSalary的員工集合
- 如果傳入的empId為null，則不考慮empId條件
- 傳入的empSalary為null則不考慮empSalary的條件
Dao接口方法：
List<Employee> selectEmployeeListByEmpIdAndEmpSalary(@Param("empId") Integer empId, @Param("empSalary") Double empSalary);
用if標簽的動態sql:
<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
<include refid="mapper.sql.mySelectSql"></include> where
<if test="empId != null">
emp_id>#{empId}
</if>
<if test="empSalary != null">
and emp_salary>#{empSalary}
</if>
這裡可以看到，如果empSalary為空，那麼sql語句為select * from t_emp where emp_id >#{empId},但是如果empId為空，那麼sql語句為select * from t_emp where and emp_salary>#{empSalary}，很明顯這個是錯的，if標簽在這裡就不適用瞭。所以我們用where標簽，或者trim標簽。
where和if的動態sql:
<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
<include refid="mapper.sql.mySelectSql"></include>
<where>
<if test="empId != null">
emp_id>#{empId}
</if>
<if test="empSalary != null">
and emp_salary>#{empSalary}
</if>
</where>
</select>
where標簽的作用:
- 在第一個條件之前自動添加WHERE關鍵字
- 自動去掉第一個條件前的連接符(AND、OR等等)
trim
trim是修建的意思，其實就是去頭去尾，這裡還是根據上面那個方法
trim的動態sql
<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
<include refid="mapper.sql.mySelectSql"></include>
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="empId != null">
emp_id>#{empId}
</if>
<if test="empSalary != null">
AND emp_salary>#{empSalary}
</if>
</trim>
</select>
trim標簽：
- prefix：指定要動態添加的前綴
- suffix屬性：指定要動態添加的後綴
- prefixOverrides：指定要動態去掉的前綴，使用“|”分隔有可能的多個值
- suffixOverrides屬性：指定要動態去掉的後綴，使用“|”分隔有可能的多個值
set
目標：根據empId更新員工信息,如果某個值為null，則不更新這個字段
Dao接口方法：
void updateEmployee(Employee employee);
我們先用上面的trim標簽來解決一下這個問題，
trim的動態sql：
<update id="updateEmployee" >
<trim prefix="set" prefixOverrides=",">
<if test="empName!=null">
emp_name=#{empName}
</if>
<if test="empSalary!=null">
, emp_salary=#{empSalary}
</if>
</trim>
where emp_id=#{empId}
</update>
set的動態sql
<update id="updateEmployee" >
update t_emp
<set >
<if test="empName!=null">
emp_name=#{empName}
</if>
<if test="empSalary!=null">
, emp_salary=#{empSalary}
</if>
</set>
可以看出
set標簽的作用:
- 自動在要修改的第一個字段之前添加SET關鍵字
- 去掉要修改的第一個字段前的連接符(,)
choose、when、otherwise
目標：
- 根據emp_id查詢員工信息，如果0<emp_id<6,那麼就查詢所有大於該emp_id的員工
- 如果emp_id是大於6，那麼就查詢所有小於該emp_id的員工
- 如果是其它情況，則查詢所有員工信息
Dao接口方法：
List<Employee> selectEmployeeList(Integer empId);
動態sql
<select id="selectEmployeeList" resultType="employee">
<include refid="mapper.sql.mySelectSql"></include> where
<choose>
<!--<是<號的轉義字符-->
<when test="empId>0 and empId<6">
emp_id>#{empId}
</when>
<when test="empId>6">
emp_id<#{empId}
</when>
<otherwise>
1==1
</otherwise>
</choose>
</select>
choose、when、otherwise
相當於if ... else if... else if ... else
- 如果某一個when的條件成立，則不會繼續判斷後續的when
- 如果所有的when都不成立，則會拼接otherwise標簽中的內容
foreach
目標1：批量添加員工信息
Dao接口方法：
void insertEmployeeList(@Param("employeeList") List employeeList);
1.動態sql
<insert id="insertEmployeeList">
insert into t_emp(emp_name,emp_salary)values
<!--collection標簽可以寫list,collection,
或者自己自己定義參數名@Param("employeeList") List<Employee> employeeList-->
<foreach collection="employeeList" separator="," item="emp">
(#{emp.empName},#{emp.empSalary})
</foreach>
</insert>
目標2：根據多個id查詢多個員工信息
Dao接口
List selectEmployeeListByEmpIdList(List idList);
2.動態sql
<select id="selectEmployeeListByEmpIdList" resultType="employee">
<include refid="mapper.sql.mySelectSql"></include>
<foreach collection="collection" item="id" separator="," open="where emp_id in (" close=")">
#{id}
</foreach>
</select>
批量查詢:foreach標簽
- collection屬性: 表示要遍歷的對象，如果要遍歷的參數使用@Param註解取名瞭就使用該名字，如果沒有取名List，或者collection。
- item屬性: 表示遍歷出來的元素，我們到時候要拼接SQL語句就得使用這個元素: 如果遍歷出來的元素是POJO對象， 那麼我們就通過 #{遍歷出來的元素.POJO的屬性} 獲取數據;如果遍歷出來的元素是簡單類型的數據,那麼我們就使用 #{遍歷出來的元素} 獲取這個簡單類型數據
- separator屬性: 遍歷出來的元素之間的分隔符
- open屬性: 在遍歷出來的第一個元素之前添加前綴
- close屬性: 在遍歷出來的最後一個元素之後添加後綴
測試程序
import mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import pojo.Employee;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class Test {
private EmployeeMapper employeeMapper;
private InputStream is;
private SqlSession sqlSession;
@Before
public void init() throws Exception{
//目標:獲取EmployeeMapper接口的代理對象，並且使用該對象調用selectEmployee(1)方法,然後返回Employee對象
//1. 將全局配置文件轉成字節輸入流
is = Resources.getResourceAsStream("mybatisConfig.xml");
//2. 創建SqlSessionFactoryBuilder對象
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
//3. 使用構建者模式創建SqlSessionFactory對象
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
//4. 使用工廠模式創建一個SqlSession對象
sqlSession = sqlSessionFactory.openSession();
//5. 使用動態代理模式，創建EmployeeMapper接口的代理對象
employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
}
@After
public void after() throws Exception{
//提交事務!!!
sqlSession.commit();
//7. 關閉資源
is.close();
sqlSession.close();
}
@org.junit.Test
public void testSelectEmployeeListByEmpId(){
System.out.println(employeeMapper.selectEmployeeListByEmpId(null));
}
@org.junit.Test
public void testSelectEmployeeListByEmpIdAndEmpSalary(){
System.out.println(employeeMapper.selectEmployeeListByEmpIdAndEmpSalary(2, 300d));
}
@org.junit.Test
public void testUpdateEmployee(){
Employee employee = new Employee(3,"celia", 9000d);
employeeMapper.updateEmployee(employee);
}
@org.junit.Test
public void testSelectEmployeeList(){
System.out.println(employeeMapper.selectEmployeeList(7));
}
@org.junit.Test
public void testInsertEmployee(){
employeeMapper.insertEmployee(new Employee(null,"tom",300d));
}
@org.junit.Test
public void testInsertEmployeeList(){
List<Employee> employeeList = new ArrayList<>();
for (int i = 11; i <=20 ; i++) {
employeeList.add(new Employee(null,"aobama"+i,2000d));
}
employeeMapper.insertEmployeeList(employeeList);
}
@org.junit.Test
public void testSelectEmployeeListByEmpIdList(){
List<Integer> idList = new ArrayList<>();
idList.add(23);
idList.add(33);
idList.add(32);
idList.add(21);
idList.add(22);
System.out.println(employeeMapper.selectEmployeeListByEmpIdList(idList));
}
}
到此這篇關於MyBatis 動態SQL全面詳解的文章就介紹到這瞭,更多相關MyBatis 動態SQL內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！