Mybatis之动态SQL

朴灿烈づ我的快乐病毒、 2022-04-25 02:12 365阅读 0赞

Mybatis 动态SQL的使用：

## 简介 ##

*  动态SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作
 *  动态SQL元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
 *  MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
    
     *  If
     *  choose (when, otherwise)
     *  trim (where, set)
     *  foreach

OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的表达式语言，通过它可以非常方便的来操作对象属性。 类似于我们的EL，SpEL等

<table> 
 <thead> 
 <tr> 
 <th>功能</th> 
 <th>参数</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>访问对象属性</td> 
 <td>person.name</td> 
 </tr> 
 <tr> 
 <td>调用方法</td> 
 <td>person.getName()</td> 
 </tr> 
 <tr> 
 <td>person.getName()</td> 
 <td>@java.lang.Math@PI @java.util.UUID@randomUUID()</td> 
 </tr> 
 <tr> 
 <td>调用构造方法</td> 
 <td>new com.atguigu.bean.Person(‘admin’).name</td> 
 </tr> 
 <tr> 
 <td>运算符</td> 
 <td>+,-*,/,%</td> 
 </tr> 
 <tr> 
 <td>逻辑运算符</td> 
 <td>in,not in,&gt;,&gt;=,&lt;,&lt;=,==,!=</td> 
 </tr> 
 </tbody> 
</table>

**注意：xml中特殊符号如”,>,<等这些都需要使用转义字符**

## 项目结构 ##

## 数据准备 ##

![Amx8Z4.png][]

![AmxZIs.png][]

## 项目结构 ##

![Amxyod.png][]

### Department ###

### Employee ###

### EmployeeMapperDynamicSQL ###

### EmployeeMapperDynamicSQL.xml ###

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 </pre></td> 
 <td><pre>&lt;?xml version="1.0" encoding="UTF-8" ?&gt; &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt; &lt;mapper namespace="com.hph.mybatis.dao.EmployeeMapperDynamicSQL"&gt; &lt;!-- public List&lt;Employee&gt; getEmpsByConditionIfWhere(Employee Condition); --&gt; &lt;select id="getEmpsByConditionIfWhere" resultType="com.hph.mybatis.beans.Employee"&gt; select id, last_name, email, gender from tbl_employee &lt;!-- where 1=1 --&gt; &lt;where&gt; &lt;!-- 在SQL语句中提供WHERE关键字， 并且要解决第一个出现的and 或者是 or的问题 --&gt; &lt;if test="id!=null"&gt; and id = #{id } &lt;/if&gt; &lt;if test="lastName!=null&amp;amp;&amp;amp;lastName!=&amp;quot;&amp;quot;"&gt; and last_name = #{lastName} &lt;/if&gt; &lt;if test="email!=null and email.trim()!=''"&gt; and email = #{email} &lt;/if&gt; &lt;if test="gender==0 or gender==1"&gt; and gender = #{gender} &lt;/if&gt; &lt;/where&gt; &lt;/select&gt; &lt;select id="getEmpsByConditionTrim" resultType="com.hph.mybatis.beans.Employee"&gt; select id, last_name, email, gender from tbl_employee &lt;!-- prefix: 添加一个前缀 pprefixOverrides:覆盖/去掉一个前缀 ssuffxi:添加一个后缀 suffixOverrides:覆盖/去掉一个后缀 --&gt; &lt;trim prefix="where" suffixOverrides="and|or"&gt; &lt;if test="id!=null"&gt; id = #{id } and &lt;/if&gt; &lt;if test="lastName!=null&amp;amp;&amp;amp;lastName!=&amp;quot;&amp;quot;"&gt; last_name = #{lastName} and &lt;/if&gt; &lt;if test="email!=null and email.trim()!=''"&gt; email = #{email} or &lt;/if&gt; &lt;if test="gender==0 or gender==1"&gt; gender = #{gender} &lt;/if&gt; &lt;/trim&gt; &lt;/select&gt; &lt;!--public void updateEmpByConitionSet(Employee condition);--&gt; &lt;update id="updateEmpByConitionSet"&gt; update tbl_employee &lt;set&gt; &lt;if test="lastName!=null"&gt; last_name = #{lastName}, &lt;/if&gt; &lt;if test="email!=null"&gt; email = #{email}, &lt;/if&gt; &lt;if test="gender==0 or gender==1"&gt; gender= #{gender}, &lt;/if&gt; &lt;/set&gt; where id = #{id} &lt;/update&gt; &lt;!-- public void updateEmpByConitionSet(Employee condition);--&gt; &lt;select id="getEmpsByConditionChoose" resultType="com.hph.mybatis.beans.Employee"&gt; select id,last_name,email,gender from tbl_employee where &lt;choose&gt; &lt;when test="id!=null"&gt; id=#{id} &lt;/when&gt; &lt;when test="lastName!=null"&gt; last_name=#{lastName} &lt;/when&gt; &lt;when test="email!=null"&gt; email = #{email} &lt;/when&gt; &lt;otherwise&gt; gender = 0 &lt;/otherwise&gt; &lt;/choose&gt; &lt;/select&gt; &lt;!-- public List&lt;Employee&gt; getEmpsByIds(@Param("ids")List&lt;Integer&gt; ids);--&gt; &lt;select id="getEmpsByIds" resultType="com.hph.mybatis.beans.Employee"&gt; &lt;!-- foreach: collection:指定要迭代的几乎额 item:当前集合中迭代出的元素 open:指定一个开始字符 close:指定一个结束字符 separtor:元素与元素之间的分隔符 --&gt; select id,last_name,email,gender from tbl_employee where id in &lt;foreach collection="ids" item="currId" open="(" close=")" separator=","&gt; #{currId} &lt;/foreach&gt; &lt;/select&gt; &lt;!-- public void addEmps(@Param("emps") List&lt;Employee&gt; emps) 添加:insert into tbl_employee(x,x,x) values(?,?,?),(?,?&lt;?),(?,?,?) 删除:delete from tbl_employee where id in (?,?,?) 修改:update tbl_employee set last_name = #{lastName }...where id = #{id}} update tbl_employee set last_name = #{lastName }...where id = #{id}} update tbl_employee set last_name = #{lastName }...where id = #{id}} 默认情况下,JDBCb允许将多条分号SQL通过;平日你改成一个字符串 ;--&gt; &lt;insert id="addEmps"&gt; insert into tbl_employee(last_name, email,gender ) values &lt;foreach collection="emps" item="emp" separator=","&gt; (#{emp.lastName},#{emp.email},#{emp.gender}) &lt;/foreach&gt; &lt;/insert&gt; &lt;/mapper&gt; </pre></td> 
 </tr> 
 </tbody> 
</table>

### TestMybatisDynamicSQL ###

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 </pre></td> 
 <td><pre>package com.hph.mybatis.test; import com.hph.mybatis.beans.Employee; import com.hph.mybatis.dao.EmployeeMapperDynamicSQL; 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.Test; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class TestMybatisDynamicSQL {
 public SqlSessionFactory getSqlSessionFactory() throws Exception {
 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); return sqlSessionFactory; } @Test public void testIf() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); Employee condition = new Employee(); /* condition.setId(5); condition.setLastName("清风_笑丶");*/ //condition.setEmail("qfx@sina.com"); //condition.setGender(1); List&lt;Employee&gt; emps = mapper.getEmpsByConditionIfWhere(condition); System.out.println(emps); } finally {
 session.close(); } } @Test public void testTrim() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); Employee condition = new Employee(); condition.setId(5); condition.setLastName("清风_笑丶"); condition.setEmail("qfx@gmail.com"); //condition.setGender(1); List&lt;Employee&gt; emps = mapper.getEmpsByConditionTrim(condition); System.out.println(emps); } finally {
 session.close(); } } @Test public void testSet() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(true); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); Employee condition = new Employee(); condition.setId(5); condition.setLastName("清风_笑丶testSet"); condition.setEmail("qf_x@qq.com"); // condition.setGender(1); mapper.updateEmpByConitionSet(condition); } finally {
 session.close(); } } @Test public void testChoose() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(true); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); Employee condition = new Employee(); condition.setId(1); condition.setLastName("清风_笑丶testChoose"); condition.setEmail("qf_xtestChoose@gmail.com"); //condition.setGender(1); List&lt;Employee&gt; emps = mapper.getEmpsByConditionChoose(condition); System.out.println(emps); } finally {
 session.close(); } } @Test public void testForeach() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(true); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); Employee condition = new Employee(); List&lt;Integer&gt; ids = new ArrayList&lt;Integer&gt;(); ids.add(5); ids.add(6); ids.add(7); List&lt;Employee&gt; emps = mapper.getEmpsByIds(ids); System.out.println(emps); } finally {
 session.close(); } } @Test public void testBatch() throws Exception {
 SqlSessionFactory ssf = getSqlSessionFactory(); SqlSession session = ssf.openSession(true); try {
 EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class); List&lt;Employee&gt; emps = new ArrayList&lt;Employee&gt;(); emps.add(new Employee(null, "清风笑_testBatch1", "qfx_testBatch1@sina.com", 1)); emps.add(new Employee(null, "清风笑_testBatch2", "qfx_testBatch2@sina.com", 0)); emps.add(new Employee(null, "清风笑_testBatch3", "qfx_testBatch3@sina.com", 1)); mapper.addEmps(emps); } finally {
 session.close(); } } } </pre></td> 
 </tr> 
 </tbody> 
</table>

## if where ##

If用于完成简单的判断.

Where用于解决SQL语句中where关键字以及条件中第一个and或者or的问题

![Anokv9.png][]

![AnotVP.png][]

## trim ##

![AnocV0.png][]

## set ##

set 主要是用于解决修改操作中SQL语句中可能多出逗号的问题.

![AnTKZq.png][]

![AnTQoV.png][]

## choose(when、otherwise) ##

choose 主要是用于分支判断，类似于java中的switch case,只会满足所有分支中的一个

![An7b9O.png][]

## foreach ##

foreach 主要用户循环迭代

collection: 要迭代的集合

item: 当前从集合中迭代出的元素

open: 开始字符

close:结束字符

separator: 元素与元素之间的分隔符

index:

迭代的是List集合: index表示的当前元素的下标

迭代的Map集合: index表示的当前元素的key

![AnHorj.png][]

![AnbnLd.png][]

## sql ##

sql 标签是用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用.

抽取SQL

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 2 3 </pre></td> 
 <td><pre>&lt;sql id="selectSQL"&gt; 		select id , last_name, email ,gender from tbl_employee &lt;/sql&gt; </pre></td> 
 </tr> 
 </tbody> 
</table>

引用SQL:

<table> 
 <tbody> 
 <tr> 
 <td><pre>1 </pre></td> 
 <td><pre>&lt;include refid="selectSQL"&gt;&lt;/include&gt; </pre></td> 
 </tr> 
 </tbody> 
</table>

[Amx8Z4.png]: /images/20220210/9d9bfb58a7064bfc92dc74d1ced9c092.png
[AmxZIs.png]: /images/20220210/c28a7cc9074f4d45bcb75aae176beaae.png
[Amxyod.png]: /images/20220210/a7af9bf088134bd2b17f5e2127eee652.png
[Anokv9.png]: /images/20220210/7ff777a7503646289091ea27e2de462b.png
[AnotVP.png]: /images/20220210/14de76d4a5b14a958cc486b961609461.png
[AnocV0.png]: /images/20220210/9343bd452e444524a1c2135172085582.png
[AnTKZq.png]: /images/20220210/c8733f4ffe5a4072bd599f06f60deff5.png
[AnTQoV.png]: /images/20220210/103f874e89c74df885b1b25c237dfb8b.png
[An7b9O.png]: /images/20220210/9d48adcdf62c49d1950d88a0800e8bbf.png
[AnHorj.png]: /images/20220210/7ae10e46bb5249cfbf4377777245e3a7.png
[AnbnLd.png]: /images/20220210/25852a64550b49a9ad09cf45b5f21a5b.png