动态SQL

动态SQL

开发人员在使用JDBC或其他类似的框架进行数据库开发时，通常都要根据需求去手动拼装SQL，这是一个非常麻烦且痛苦的工作，而MyBatis提供了对SQL语句动态组装的功能，恰好解决了这一麻烦工作。

动态SQL中的元素

动态SQL是MyBatis的强大特性之一，MyBatis 3采用了强大的基于OGNL的表达式来完成动态SQL，它消除了之前版本中需要了解的大多数元素，使用不到原来一半的元素就能完成所需工作。

元素	说明
<if>	判断语句，用于单条件分支判断
<choose>(<when>、<otherwise>)	相当于Java中的swith…case…default语句，用于多条件分支判断
<where>、<trim>、<set>	辅助元素，用于处理一些SQL拼接、特殊字符问题
<foreach>	循环语句，常用于in语句等列举条件中
<bind>	从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询的sql中

<if>元素

1）在Eclipse中，创建一个名为chapter08的Web项目，编写工具类MybatisUtils

package com.ex.utils;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
public class MybatisUtils {
	private static SqlSessionFactory sqlSessionFactory=null;
	static{
		try{
			Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public static SqlSession getSession(){
		return sqlSessionFactory.openSession();
	}
}

2）编写映射关系

	 <select id="findCustomerByNameAndJobs"
	 	parameterType="com.ex.po.Customer"
	 	resultType="com.ex.po.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>

使用if元素的test属性分别对username和jobs进行了非空判断，如果传入的查询条件不为空就进行动态SQL组装。
3）编写测试类

package com.ex.test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.ex.po.*;
import com.ex.utils.*;
public class MybatisTest {
	@Test
	public void findCustomerByNameAndJobsTest(){
		SqlSession sqlSession=MybatisUtils.getSession();
		Customer customer=new Customer();
		customer.setUsername("jack");
		customer.setJobs("teacher");
		List<Customer> listCustomers=sqlSession.selectList("com.ex.mapper"
				+ "CustomerMapper.findCustomerByNameAndJobs", customer);
		for(Customer oneCustomer:listCustomers){
			System.out.println(oneCustomer);
		}
		sqlSession.close();
	}
}

传入一个空Customer对象

可以看到sql将数据库中所有的数据查出

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

1）在映射文件中，添加映射

	 	<select id="findCustomerByNameOrJobs"
	 		parameterType="com.ex.po.Customer"
	 		resultType="com.ex.po.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>

注：choose只会选择一个分支，顺序判断分支条件，当满足条件时，就执行并跳出判断。（与switch…case…default有区别）
2）在测试类MybatisTest中，编写测试方法

	public void findCustomerByNameOrJobsTest(){
		SqlSession sqlSession=MybatisUtils.getSession();
		Customer customer=new Customer();
		customer.setUsername("jack");
		customer.setJobs("teacher");
		List<Customer> listCustomers=sqlSession.selectList("com.ex.mapper"
				+ ".CustomerMapper.findCustomerByNameOrJobs", customer);
		for(Customer oneCustomer:listCustomers){
			System.out.println(oneCustomer);
		}
		sqlSession.close();
	}

传入一个空对象

<where>、<trim>元素

在前面案例中，映射文件中编写的SQL后面都加入了"where 1=1"的条件，这是因为如果不添加会出现如下情况

select * from t_customer where and username like concat('%',?,'%')

where后直接跟and显然会报错，为了避免这一错误，我们添加1=1这一条件，能使拼接的sql成立。
针对这种问题，MyBatis提供了<where>元素来处理这些的问题。
例如上面的代码就可以写成

<select id="findCustomerByNameAndJobs"
	 	parameterType="com.ex.po.Customer"
	 	resultType="com.ex.po.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>

上述代码中使用<where>元素对where 1=1进行了替换，<where>元素会自动判断组合条件下拼接的SQL语句，只有<where>内的条件成立时，才会拼接SQL中加入where关键字，否则将不会添加；即使where之后的内容有多余的"AND"或"OR"，<where>也会自动将它们取出。
除了使用<where>元素外，还可以通过<trim>元素来定制需要的功能

<select id="findCustomerByNameAndJobs"
	 	parameterType="com.ex.po.Customer"
	 	resultType="com.ex.po.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>

<trim>元素对where 1=1条件进行了替换，<trim>元素的作用是去除一些特殊的字符串，它的prefix属性代表的是语句的前缀(这里使用where来连接sql)，而prefixOverrides属性代表的是需要去除的特殊字符串（原sql的前缀），这里去除的是and，上面写法与<where>等效。

<set>元素

在Hibernate中，如果想要更新一个对象，就需要发送所有的字段给持久化对象，然而实际应用中，大多数情况都是更新某一个或某几个字段。如果更新的每一个数据都有将其所有的属性都更新一遍，那么其执行效率是非常差的。
MyBatis中提供了<set>元素来完成这一工作。元素用于更新操作，主要作用是在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中最后一个多余的逗号去除。

	 	<update id="updateCustomer"
	 		parameterType="com.ex.po.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>

	@Test
	public void updateCustomer(){
		SqlSession sqlSession = MybatisUtils.getSession();
		Customer customer=new Customer();
		customer.setId(3);
		customer.setPhone("13314415515");
		int rows = sqlSession.update("com.ex.mapper"
				+ ".CustomerMapper.updateCustomer", customer);
		if(rows>0){
			System.out.println("你成功修改了"+rows+"条数据!");
		}else{
			System.out.println("执行修改操作失败!");
		}
		sqlSession.commit();
		sqlSession.close();
	}

在映射文件中使用<set>和<if>组合进行update语句动态SQL组装时，如果<set>元素内包含的内容都为空，则会出现SQL语法错误。所以在使用时需要注意。

<foreach>元素

Mybatis提供了一种用于数组和集合循环遍历的元素<foreach>
元素通常在构建IN条件语句时使用

	 	<select id="findCustomerByIds"
	 		parameterType="List"
	 		resultType="com.ex.po.Customer">
	 			select * from t_customer where id in
	 			<foreach item="id" index="index" collection="list"
	 				open="(" separator="," close=")">
	 					#{id}
	 			</foreach>
	 	</select>

item：配置的是循环中当前的元素
index：配置的是当前元素在集合的位置下标
collection：配置的list是传递过来的参数类型（首字母小写），它可以是一个array、list（或collection）、Map集合的键、POJO包装类中数组或集合类型的属性名等。
open和close：配置的是以什么符合将这些集合元素包装起来
separator：配置的是各个元素的间隔符
当使用可迭代对象或数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。
当使用字典时，index是键，item是值。

	@Test
	public void findCustomerByIdsTest(){
		SqlSession sqlSession=MybatisUtils.getSession();
		List<Integer> ids=new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		List<Customer> customers=sqlSession.selectList("com.ex.mapper"
				+ ".CustomerMapper.findCustomerByIds", ids);
		for(Customer customer:customers){
			System.out.println(customer);
		}
		sqlSession.close();
	}

<bind>元素

MyBatis的<bind>元素可以通过OGNL表达式来创建一个上下文变量

	 	<select id="findCustomerByName"
	 		parameterType="com.ex.po.Customer"
	 		resultType="com.ex.po.Customer">
	 			<bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'"/>
	 			select * from t_customer
	 			where
	 			username like #{pattern_username}
	 	</select>

	@Test
	public void findCustomerByNameTest(){
		SqlSession sqlSession=MybatisUtils.getSession();
		Customer customer=new Customer();
		customer.setUsername("j");
		List<Customer> listCustomers=sqlSession.selectList("com.ex.mapper"
				+ ".CustomerMapper.findCustomerByName", customer);
		for(Customer oneCustomer:listCustomers){
			System.out.println(oneCustomer);
		}
		sqlSession.close();
	}