【MyBatis】动态SQL#OGNL常用标签if、where、trim、set、choose、foreach、sql应用实战

笔记大纲

• 动态SQL简述
• 案例应用-非动态SQL查询
• 案例应用-动态SQL查询
• 标签应用

---

一、动态SQL简述

  （1）动态SQL是MyBatis强大特性之一，简化了拼装SQL操作。

  （2）动态SQL元素与使用JSTL(jsp标准标签库)或其他类似基于XML的文本处理器相似。

  （3）动态SQL基于OGNL(对象图导航语言)的表达式来简化操作：

  OGNL（ Object Graph Navigation Language）对象图导航语言，是一种强大的表达式语言，通过它可以非常方便的来操作对象属性。 类似于EL，SpEL等 ,Struts框架使用OGNL作为默认的表达式语言。

标签	说明
if（重点）	用于条件判断，一般用于传入参数进行判空
where（重点）	用于解决SQL语句中where关键字以及条件中第一个and或者or的问题
trim（重点）	用于判断完拼接的SQL语句前后，添加或去掉指定的字符 prefix: 添加前缀、prefixOverrides: 去掉前缀、suffix: 添加后缀、suffixOverrides: 去掉后缀
set（了解）	用于解决修改操作中SQL语句中可能多出逗号的问题
choose(where、otherwise)（了解）	用于分支判断，类似于java中的switch case,只会满足所有分支中的一个
foreach（重点）	用于循环迭代 collection: 迭代的集合、item: 当前从集合中迭代元素、open: 开始字符 close:结束字符、separator: 元素与元素之间的分隔符 index:迭代List(index:当前元素的下标)、迭代Map（index：当前元素的key）
sql	用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用 ①抽取SQL: <sql></sql> ②引用SQL:<include></include>

二、案例应用-非动态SQL查询

1.项目工程结构

测试数据库表

数据库：mybatis_db2,表：t_employee

2.JavaBean-Employee

package com.codinglin.mybatis.beans;
public class Employee {
	private Integer id;//员工号
	private String lastName;//姓名
	private Integer gender;//0表示男，1表示女
	private String post; //职位
	public Employee() {
		super();
	}
	public Employee(Integer id, String lastName, Integer gender, String post) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.gender = gender;
		this.post = post;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	public String getPost() {
		return post;
	}
	public void setPost(String post) {
		this.post = post;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", post=" + post + "]";
	}
}

3.Mapp接口-EmployeeMapperDynamicSQL

package com.codinglin.mybatis.mapper;
import java.util.List;
import com.codinglin.mybatis.beans.Employee;
public interface EmployeeMapperDynamicSQL{
	/*
	 * 根据给定的查询条件获取员工的信息
	 * 查询条件 id、lastName、gender、post
	 */
	List<Employee> getEmpsByCondition(Employee empCondition);
}

4.映射文件-EmployeeMapperDynamicSQL.xml

<?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">
<!-- 映射文件 -->
<!-- 完成绑定namespace、id -->
<mapper namespace="com.codinglin.mybatis.mapper.EmployeeMapperDynamicSQL">
<!-- 返回的是list集合,resultType不是定义的集合，而是定义集合里面的类型 -->
	<select id="getEmpsByCondition" resultType="com.codinglin.mybatis.beans.Employee">
		<!--（1） 测试非动态SQL查询方式 -->
		select eid id,last_name lastName ,gender ,post
	    from 
	    	t_employee
	    where 
	    	 eid = #{id}
	    and last_name =#{lastName}
	    and gender =#{gender}
	    and post =#{post}
	</select>
</mapper>

5.主配置文件-mybatis-conf.xml

<?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">
<!-- Mybatis主配置文件 ，全局配置文件-->
<configuration>
	<!-- 数据库连接环境的配置     default:指定使用哪一个环境配置-->
	<environments default="development">
		<environment id="development">
		<!-- 指定事务控制类 -->
			<transactionManager type="JDBC" />
			<!-- 数据源 连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis_db2?characterEncoding=utf8" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	<!-- 引入SQL映射文件,Mapper映射文件 	-->
	 <mappers>
		<!-- <mapper resource="EmployeeMapperDynamicSQL.xml" /> -->
		<package name="com.codinglin.mybatis.mapper"/>
	</mappers> 
</configuration>

6.测试类-MybatisSQLTest

package com.codinglin.mybatis.test;
import java.io.InputStream;
import java.util.List;
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 com.codinglin.mybatis.beans.Employee;
import com.codinglin.mybatis.mapper.EmployeeMapperDynamicSQL;

public class MybatisSQLTest {
	public static SqlSessionFactory getSqlSessionFactory() throws Exception {
		//定义配置文件
		 String resource ="mybatis-conf.xml";
		 //通过流读取配置文件
		 InputStream inputStream = Resources.getResourceAsStream(resource);
		 //创建sqlSessionFactory对象
		 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		 return sqlSessionFactory;	
	}

	@Test
	public void testDynamicSQL01() throws Exception {
		//获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		//获取sqlSession对象
		SqlSession sqlSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapperDynamicSQL employeeMapperDynamicSQL = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee empCondition = new Employee();	
			empCondition.setId(1);
			/*empCondition.setLastName("林大侠");
			empCondition.setGender(0);
			empCondition.setPost("Java开发");*/
			List<Employee> empsByCondition = employeeMapperDynamicSQL.getEmpsByCondition(empCondition);
			System.out.println("============动态SQL测试================");
			System.out.println(empsByCondition);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭sqlSession资源
			sqlSession.close();
		}
	}
}

7.控制台输出

运行结果01：

查询条件 id、lastName、gender、post全部符合数据库信息，正常输出！

测试：post（职位）属性值不进行赋值操作，为空操作！

  只修改测试类-MybatisSQLTest中的testDynamicSQL01()中的代码，注释setPost()属性，旨在用三个参数进行查询。

运行结果02：

小结：

  根据给定的查询条件获取员工的信息，传入的参数决定员工信息的返回状态，使用非动态SQL查询，只有两种情况，传入的参数都满足数据库中查询条件，查询成功！传入的参数有一个不满足数据库中查询条件，则查询失败，返回空集合！

  如果要想查询成功，则需要修改对应映射文件的传入参数的个数，由此可见，这种非动态SQL查询非常麻烦，可以进行优化。

运行结果03：

三、案例应用-动态SQL查询

1.映射文件-EmployeeMapperDynamicSQL.xml

<?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">
<!-- 映射文件 -->
<!-- 完成绑定namespace、id -->
<mapper namespace="com.codinglin.mybatis.mapper.EmployeeMapperDynamicSQL">
<!-- 返回的是list集合,resultType不是定义的集合，而是定义集合里面的类型 -->
	<select id="getEmpsByCondition" resultType="com.codinglin.mybatis.beans.Employee">
		<!-- 动态SQL -->
		select eid id,last_name lastName ,gender  ,post
	    from 
	    	t_employee
	    where 
	        <if test="id != null"> 
	        	eid = #{id}
	        </if>
	    	<if test="lastName != null &amp;&amp; lastName!='' ">  
	    	  and  last_name=#{lastName}
	    	</if>
	    	<if test="gender != null"> 
	        	 and gender =#{gender}
	        </if>
	        <if test="post != null &amp;&amp; post！=''"> 
	        	and post =#{post}
	        </if> 
	</select>
</mapper>

2.测试类-MybatisSQLTest

3.控制台输出

运行结果01：

测试01：post（职位）属性值不进行赋值操作，为空操作！

与案例应用-非动态SQL查询中的测试场景一致，但结果截然不同，成功查询，这就是动态SQL的优势！

测试02：gender、post属性值不进行赋值操作，为空操作！

成功查询！

测试03：id、lastName、gender、post属性值不进行赋值操作，为空操作！

查询失败！where无查询条件！

四、标签应用

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

（1）改进01-EmployeeMapperDynamicSQL.xml

where后面加上1=1,表示查询所有

（2）使用<where></where>标签

测试01：id、lastName、gender、post属性值不进行赋值操作，为空操作！

查询成功！

测试02：lastName属性值进行赋值操作，其他属性值都为空！

查询成功！注意查询条件后的参数变化。

测试03：lastName、gender属性值进行赋值操作，其他属性值都为空！

---

查询成功！注意查询条件后的参数变化，自动加上了and。

2.trim用于判断完拼接的SQL语句前后，添加或去掉指定的字符

prefix: 添加前缀、prefixOverrides: 去掉前缀、suffix: 添加后缀、suffixOverrides: 去掉后缀

注意：存在查询条件时，and前后位置很重要，这里面需要用trime标签，为空时，需要去掉前缀and。

运行结果：

3.用于解决修改操作中SQL语句中可能多出逗号的问题(了解就行)

<update id="updateEmpByConditionSet">
		update  t_employee  
		<set>
			<if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
				 last_name = #{lastName},
			</if>
			<if test="email!=null and email.trim()!=''">
				 email = #{email} ,
			</if>
			<if test="&quot;m&quot;.equals(gender) or &quot;f&quot;.equals(gender)">
				gender = #{gender} 
			</if>
		</set>
		 where eid =#{id}
	</update>

4.用于分支判断，类似于java中的switch case,只会满足所有分支中的一个(了解就行)

<select id="getEmpsByConditionChoose" resultType="com.codinglin.mybatis.mapper.LEmployee">
		select id ,last_name,gender，post from t_employee
		<where>
			<choose>
				<when test="id!=null">
					eid = #{id}
				</when>
				<when test="lastName!=null">
					last_name = #{lastName}
				</when>
				<when test="gender!=null">
					 gender = 'gender'
				</when>
				<otherwise>
					post='post'
				</otherwise>
			</choose>
		</where>
</select>

5.foreach用于循环迭代

  collection: 迭代的集合、item: 当前从集合中迭代元素、open: 开始字符close:结束字符、separator: 元素与元素之间的分隔符index:迭代List(index:当前元素的下标)、迭代Map（index：当前元素的key）

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

（1）Mapp接口-EmployeeMapperDynamicSQL

/*
	 *查询给定集合id的员工 
	 */
	List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

（2）映射文件-EmployeeMapperDynamicSQL.xml

 <sql id="mySelectSQL">
	 	select eid id,last_name lastName ,gender ,post from t_employee
	  </sql> 

	<select id="getEmpsByConditionForeach" resultType="com.codinglin.mybatis.beans.Employee">
			<include refid="mySelectSQL"></include>
			where eid in
			<foreach collection="ids" item="cur_id" open="(" close=")" separator=",">
			  #{cur_id}
			</foreach>
	</select>

（3）测试类-MybatisSQLTest

@Test
	public void testDynamicSQL02() throws Exception {
		//获取sqlSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		//获取sqlSession对象
		SqlSession sqlSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapperDynamicSQL employeeMapperDynamicSQL = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Integer> list = new ArrayList<Integer>();
			list.add(1);
			list.add(3);
			list.add(5);
		 List<Employee> empsByConditionForeach = employeeMapperDynamicSQL.getEmpsByConditionForeach(list);
			System.out.println("============动态SQL测试================");
			System.out.println(empsByConditionForeach);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//关闭sqlSession资源
			sqlSession.close();
		}

（4）控制台输出

DEBUG 09-12 00:40:21,502 ==>  Preparing: select eid id,last_name lastName ,gender ,post from t_employee where eid in ( ? , ? , ? )   (BaseJdbcLogger.java:145) 
DEBUG 09-12 00:40:21,568 ==> Parameters: 1(Integer), 3(Integer), 5(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 09-12 00:40:21,593 <==      Total: 3  (BaseJdbcLogger.java:145) 
============动态SQL测试================
[
Employee[id=1, lastName=林大侠, gender=0, post=Java开发], 
Employee [id=3, lastName=猪猪侠, gender=0, post=产品运营], 
Employee [id=5, lastName=夜行侠, gender=1, post=PHP开发]
]

---

 ☝上述分享来源个人总结，如果分享对您有帮忙，希望您积极转载；如果您有不同的见解，希望您积极留言，让我们一起探讨，您的鼓励将是我前进道路上一份助力，非常感谢！我会不定时更新相关技术动态，同时我也会不断完善自己，提升技术，希望与君同成长同进步！

☞本人博客：https://coding0110lin.blog.csdn.net/  欢迎转载，一起技术交流吧！