動態SQL的處理

學習視頻：3001 動態SQL中的元素_嗶哩嗶哩_bilibili

1.1為什么學

1.2動態SQL中的元素

?????????????條件查詢操作

? ? ? ? ? ? ? ?if?元素

? ? ? ? choose、when、otherwise元素

? ? ? ? where、trim元素

? ? ? ? 更新操作

? ? ? ? set元素使用場景

????????復雜查詢操作

? ? ? ?foreach?元素中的屬性

?編輯

??????????迭代數組

????????迭代List

????????迭代Map

???? ? ?1.3案例：學生信息查詢系統

總結：

1.1為什么學

?????????在實際項目的開發中，開發人員在使用JDBC或其他持久層框架進行開發時，經常需要根據不同的條件拼接SQL語句，拼接SQL語句時還要確保不能遺漏必要的空格、標點符號等，這種編程方式給開發人員帶來了非常大的不便，而MyBatis提供的SQL語句動態組裝功能，恰能很好地解決這一問題。

1.2動態SQL中的元素

????????? 使用動態SQL的好處

?????????動態SQL是MyBatis的強大特性之一，MyBatis采用了功能強大的基于OGNL（Object Graph Navigation Language）的表達式來完成動態SQL。在MyBatis的映射文件中，開發人員可通過動態SQL元素靈活組裝SQL語句，這在很大程度上避免了單一SQL語句的反復堆砌，提高了SQL語句的復用性。???????????????

?????????動態SQL常用元素

?????????????條件查詢操作

????????????????<if>元素

???????????在MyBatis中，<if>元素是最常用的判斷元素，它類似于Java中的if語句，主要用于實現某些簡單的條件判斷。在實際應用中，我們可能會通過某個條件查詢某個數據。例如，要查找某個客戶的信息，可以通過姓名或者年齡來查找客戶，也可以不填寫年齡直接通過姓名來查找客戶，還可以都不填寫而查詢出所有客戶，此時姓名和年齡就是非必須條件。類似于這種情況，在MyBatis中就可以通過<if>元素來實現。

? ? ? ? ? ? ? ? 數據庫準備

????????在名稱為mybatis的數據庫中，創建一個t_customer表，并插入幾條測試數據。

USE mybatis;
CREATE TABLE t_customer (id int(32) PRIMARY KEY AUTO_INCREMENT,username varchar(50),jobs varchar(50),phone varchar(16));
INSERT INTO t_customer VALUES ('1', 'joy', 'teacher', '13733333333');
INSERT INTO t_customer VALUES ('2', 'jack', 'teacher', '13522222222');
INSERT INTO t_customer VALUES ('3', 'tom', 'worker', '15111111111');

????????????????POJO類準備

????????創建持久化類Customer，在類中聲明id、username、jobs和phone屬性，及屬性對應的getter/setter方法。

public class Customer {private Integer id;   private String username; // 主鍵ID、客戶名稱private String jobs;  private String phone;      // 職業、電話// 省略getter/setter方法@Overridepublic String toString() {return "Customer [id=" + id + ", username=" + username + ", jobs=" + jobs + ", phone=" + phone + "]"; }
}

????????????????創建映射文件

????????創建映射文件CustomerMapper.xml，在映射文件中，根據客戶姓名和年齡組合條件查詢客戶信息，使用<if>元素編寫該組合條件的動態SQL。

<!– 該xml文件中只列出了if元素的動態SQL-->
<if test="username !=null and username !=‘’“>and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=‘’“>and jobs= #{jobs}
</if>

? ? ? ? ? ??修改核心配置文件

????????在配置文件mybatis-config.xml中，引入CustomerMapper.xml映射文件，將CustomerMapper.xml映射文件加載到程序中。

<mapper 
resource="com/it/mapper/CustomerMapper.xml">
</mapper>

????????創建獲取SqlSession對象的工具類

public class MybatisUtils {private static SqlSessionFactory sqlSessionFactory = null;// 初始化SqlSessionFactory對象static {try {// 使用MyBatis提供的Resources類加載MyBatis的配置文件Reader reader = Resources.getResourceAsReader("mybatis-config.xml");// 構建SqlSessionFactory工廠sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);} catch (Exception e) {e.printStackTrace();}}// 獲取SqlSession對象的靜態方法public static SqlSession getSession() {return sqlSessionFactory.openSession();}
}

????????編寫測試類

????????在測試類MyBatisTest中，編寫測試方法findCustomerByNameAndJobsTest()，該方法用于根據客戶姓名和職業組合條件查詢客戶信息列表。

public class MyBatisTest { @Testpublic void findCustomerByNameAndJobsTest(){SqlSession session = 	MyBatisUtils.getSession();Customer customer = new Customer();customer.setUsername(“jack");  customer.setJobs("teacher");List<Customer> customers = session.selectList("com.itheima.mapper"+ ".CustomerMapper.findCustomerByNameAndJobs",customer);for (Customer customer2 : customers) { System.out.println(customer2); }session.close();}
}

MyBatisTest.java

package com.it.test;import com.it.pojo.Customer;
import com.it.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class MyBatisTest {@Testpublic void findCustomerByUsernameAndJobsTest(){SqlSession sqlSession= MyBatisUtils.getSession();Customer customer=new Customer();customer.setUsername("jack");customer.setJobs("teacher");//執行sqlList<Customer> customers=sqlSession.selectList("com.it.mapper.CustomerMapper.findCustomerByUsernameAndJobs",customer);for (Customer c:customers){System.out.println(c);}//釋放資源sqlSession.close();}}

CustomerMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--mapper為映射的根節點，用來管理DAO接口namespace指定DAO接口的完整類名，表示mapper配置文件管理哪個DAO接口(包.接口名)mybatis會依據這個接口動態創建一個實現類去實現這個接口，而這個實現類是一個Mapper對象-->
<mapper namespace="com.it.mapper.CustomerMapper"><!--id = "接口中的方法名"parameterType = "接口中傳入方法的參數類型"resultType = "返回實體類對象：包.類名"  處理結果集 自動封裝注意:sql語句后不要出現";"號查詢：select標簽增加：insert標簽修改：update標簽刪除：delete標簽--><select id="findCustomerByUsernameAndJobs" parameterType="com.it.pojo.Customer"  resultType="com.it.pojo.Customer">SELECT * FROM t_customer where 1=1<if test="username!=null and username!=''">and  username like concat('%',#{username},'%')</if><if test="jobs!=null and jobs!=''">and jobs=#{jobs}</if></select>
</mapper>

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd" >
<!--配置mybatis環境-->
<configuration><!--配置連接使用的相關參數default為默認使用的環境：development 測試環境product     生產環境--><properties resource="jdbc.properties"></properties><environments default="development"><!--測試環境--><environment id="development"><!--事務管理類型：指定事務管理的方式 JDBC--><transactionManager type="JDBC"/><!--數據庫連接相關配置，動態獲取config.properties文件里的內容--><!--數據源類型：POOLED 表示支持JDBC數據源連接池UNPOOLED 表示不支持數據源連接池JNDI 表示支持外部數據源連接池--><dataSource type="POOLED"><!--此處使用的是MySQL數據庫，使用Oracle數據庫時需要修改，仔細檢查各項參數是否正確，里面配置了時區、編碼方式、SSL，用以防止中文查詢亂碼，導致查詢結果為null及SSL警告等問題--><property name="driver" value="${jdbc.driver}"/><property name="url"value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><mappers><!--下面編寫mapper映射文件↓↓↓↓↓ 參考格式：<mapper resource="dao/UserMapper.xml"/> --><mapper resource="com/it/mapper/CustomerMapper.xml"></mapper></mappers></configuration>

????????<choose>、<when>、<otherwise>元素

? ? ? ? ?<choose><when>otherwise>使用場景

????????在使用<if>元素時，只要test屬性中的表達式為true，就會執行元素中的條件語句，但是在實際應用中，有時只需要從多個選項中選擇一個去執行。 ? ? ? ?

????????例如下面的場景：“當客戶名稱不為空，則只根據客戶名稱進行客戶篩選；當客戶名稱為空，而客戶職業不為空，則只根據客戶職業進行客戶篩選。當客戶名稱和客戶職業都為空，則要求查詢出所有電話不為空的客戶信息。” ? ? ? ?

????????針對上面情況，使用<if>元素進行處理是不合適的。MyBatis提供了<choose>、<when>、<otherwise>元素進行處理，這三個元素往往組合在一起使用，作用相當于Java語言中的if…else if…else。

 <select id="findCustomerByUsernameOrJobs"  parameterType="com.it.pojo.Customer" resultType="com.it.pojo.Customer">SELECT * FROM t_customer where 1=1<choose><when test="username!=null and username!=''">and  username like concat('%',#{username},'%')</when><when test="jobs!=null and jobs!=''">and jobs=#{jobs}</when><otherwise>and phone is not null</otherwise></choose></select>

@Testpublic void findCustomerByUsernameOrJobsTest(){SqlSession sqlSession= MyBatisUtils.getSession();Customer customer=new Customer();customer.setUsername("jack");customer.setJobs("teacher");//執行sqlList<Customer> customers=sqlSession.selectList("com.it.mapper.CustomerMapper.findCustomerByUsernameOrJobs",customer);for (Customer c:customers){System.out.println(c);}//釋放資源sqlSession.close();}

????????<where>、<trim>元素

????????<where><trim>使用場景

?????????在映射文件中，編寫的SQL后面加入了“where 1=1”的條件的話，既保證了where后面的條件成立，又避免了where后面第一個詞是and或者or之類的關鍵字。 ? ? ? ? 例如下面這條Mybatis拼接出的SQL語句是不正確的。

select * from t_customer where and username likeconcat('%',?, '%') and jobs = #{jobs}

? ? ? ? 上述SQL語句中，where后直接跟的是and，這在運行時會報SQL語法錯誤，針對這種情況，可以使用MyBatis提供的<where>元素和<trim>元素進行處理。

 <select id="findCustomerByUsernameAndJobs"  parameterType="com.it.pojo.Customer" resultType="com.it.pojo.Customer">select * from t_customer<where><if test="username !=null and username !=''">and username like concat('%',#{username}, '%')</if><if test="jobs !=null and jobs !=''">and jobs= #{jobs}</if></where></select>

如果2個if都不滿足,where會被忽視

????????上述代碼配置中，<where>元素會自動判斷由組合條件拼裝的SQL語句，只有<where>元素內的某一個或多個條件成立時，才會在拼接SQL中加入where關鍵字，否則將不會添加；即使where之后的內容有多余的“AND”或“OR”，<where>元素也會自動將他們去除。

????????<trim>元素

????????????????? <trim>元素用于刪除多余的關鍵字，它可以直接實現<where>元素的功能。<trim>元素包含4個屬性。?

<select id="findCustomerByUsernameAndJobs"  parameterType="com.it.pojo.Customer" resultType="com.it.pojo.Customer">select * from t_customer<trim prefix="where" prefixOverrides="and"><if test="username !=null and username !=''">and username like concat('%',#{username}, '%')</if><if test="jobs !=null and jobs !=''">and jobs= #{jobs}</if></trim></select>

? ? ? ? 更新操作

????????<set>元素使用場景

????????????????在Hibernate框架中，如果想要更新某一個對象，就需要發送所有的字段給持久化對象，然而在實際應用中，大多數情況下都是更新某一個或幾個字段。如果更新的每一條數據都要將其所有的屬性都更新一遍，那么執行效率是非常差的。為了解決更新數據的效率問題，MyBatis提供了<set>元素。<set>元素主要用于更新操作，它可以在動態SQL語句前輸出一個SET關鍵字，并將SQL語句中最后一個多余的逗號去除。<set>元素與<if>元素結合可以只更新需要更新的字段。

? ? ? ? 映射文件

<update id="updateCustomerBySet" parameterType="com.it.pojo.Customer">update t_customer<set><if test="username !=null and username !=''">username=#{username},</if><if test="jobs !=null and jobs !=''">  jobs=#{jobs},</if><if test="phone !=null and phone !=''">phone=#{phone},</if></set> where id=#{id}</update>

? ? ? ? 測試

 @Testpublic void updateCustomerBySetTest(){SqlSession session =MyBatisUtils.getSession();Customer customer=new Customer();customer.setId(3);customer.setPhome("12345678912");int rows=  session.update("com.it.mapper.CustomerMapper.updateCustomerBySet",customer);if(rows>0){System.out.println("修改了"+rows+"條數據");}else{System.out.println("沒有修改數據");}session.commit();session.close();}

????????<set>元素字段非空

????????在映射文件中使用<set>元素和<if>元素組合進行update語句動態SQL組裝時，如果<set>元素內包含的內容都為空，則會出現SQL語法錯誤。因此，在使用<set>元素進行字段信息更新時，要確保傳入的更新字段不能都為空。

????????使用<trim>元素更新?

????????除了使用<set>元素外，還可以通過<trim>元素來實現更新操作。其中， <trim>元素的prefix屬性指定要添加的<trim>元素所包含內容的前綴為set，suffixOverrides屬性指定去除的<trim>元素所包含內容的后綴為逗號。

<update id="updateCustomerBySet" parameterType="com.it.pojo.Customer">update t_customer<trim prefix="set" suffixOverrides=","><if test="username !=null and username !=''">username=#{username},</if><if test="jobs !=null and jobs !=''">  jobs=#{jobs},</if><if test="phone !=null and phone !=''">phone=#{phone},</if></trim> where id=#{id}</update>

????????復雜查詢操作

????????<foreach>元素中的屬性

????????<collection>屬性的取值

????????在遍歷參數時，<collection>屬性的值是必須指定的。不同情況下，該屬性的取值也是不一樣的，主要有以下三種情況：List類型、數組類型、Map類型。

? ? ? ? List->list

? ? ? ? 數組類型->array

? ? ? ? Map->Map

??????????<foreach>元素迭代數組

????????<foreach>實現入參為數組類型的遍歷

????????例如，要從數據表t_customer中查詢出id為1、2、3的客戶信息，就可以利用數組作為參數，存儲id的屬性值1、2、3，并通過<foreach>元素迭代數組完成客戶信息的批量查詢操作。

<select id="findByArray"resultType="com.it.pojo.Customer">select * from t_customer where id in<foreach item="id" index="index" collection="array"open="(" separator="," close=")">	#{id}</foreach></select>

@Testpublic void SelectByArrayTest(){SqlSession session =MyBatisUtils.getSession();//準備查詢的條件Integer[] ids={2,3};List<Customer>  customers=  session.selectList("com.it.mapper.CustomerMapper.findByArray",ids);for(Customer customer:customers){System.out.println(customer);}session.close();}

????????<foreach>元素迭代List

 <select id="findByList"resultType="com.it.pojo.Customer">select * from t_customer where id in<foreach item="id" index="index" collection="list"open="(" separator="," close=")">	#{id}</foreach></select>

@Testpublic void SelectByListTest(){SqlSession session =MyBatisUtils.getSession();//準備查詢的條件List<Integer> a=new ArrayList<Integer>();a.add(1);a.add(2);List<Customer>  customers=  session.selectList("com.it.mapper.CustomerMapper.findByList",a);for(Customer customer:customers){System.out.println(customer);}session.close();}

????????<foreach>元素迭代Map

<select id="findByMap" parameterType="java.util.Map"resultType="com.it.pojo.Customer">select * from t_customer where jobs=#{jobs} and id in<foreach item="roleMap" index="index" collection="id" open="(" 	separator="," close=")"> #{roleMap}</foreach></select>

 @Testpublic void findByMapTest() {SqlSession session = MyBatisUtils.getSession();List<Integer> ids=new ArrayList<Integer>();ids.add(1); ids.add(2); ids.add(3);Map<String,Object> conditionMap = new HashMap<String, Object>();conditionMap.put("id",ids); conditionMap.put("jobs","teacher");List<Customer> customers = session.selectList("com.it.mapper"+ ".CustomerMapper.findByMap", conditionMap);for (Customer customer : customers) { System.out.println(customer);}session.close();}

???? ? ?1.3案例：學生信息查詢系統

多條件查詢

?????????當用戶輸入的學生姓名不為空，則只根據學生姓名進行學生信息的查詢； ? ? ? ?

? ? ? ? ?當用戶輸入的學生姓名為空，而學生專業不為空，則只根據學生專業進行學生的查詢；

  <select id="findStudentByNameAndMajor"resultType="com.it.pojo.Student">select * from dm_student where 1=1<choose><when test="name!=null and name!=''">and name like concat('%',#{name},'%')</when><when test="major!=null and major!=''">and major=#{major}</when><otherwise>and sno is not null</otherwise></choose></select>

 @Testpublic void findStudentByNameAndMajorTest(){SqlSession session= MyBatisUtils.getSession();Student student=new Student();student.setName("張三");student.setMajor("英語");List<Student> students = session.selectList("com.it.mapper.StudentMapper.findStudentByNameAndMajor",student);for (Student student2 : students){System.out.println(student2);}session.close();}

單條件查詢

? 查詢出所有id值小于5的學生的信息；

<select id="findByList" parameterType="java.util.List"resultType="com.it.pojo.Student">select * from dm_student where id in<foreach item="id" index="index" collection="list"open="(" separator="," close=")">#{id}</foreach></select>

  @Testpublic void findByListTest() {SqlSession session = MyBatisUtils.getSession();List<Integer> ids=new ArrayList<Integer>();for(int i =1;i<5;i++){ids.add(i);}List<Student> students = session.selectList("com.it.mapper.StudentMapper.findByList", ids);for (Student student : students) { System.out.println(student);}session.close();}