动态SQL

动态SQL是MyBatis强大的特性，在使用JDBC操作数据的时候，如果查询的条件特别多，将SQL语句连成字符串是一件很痛苦的事情，通常我们用if-else进行拼接。但并不能确保忘了空格或在字段的最后省略逗号。MyBatis使用动态SQL语言来改善这种情形。
用于实现动态SQL的元素如下：

1. if：利用if实现简单的条件判断
2. choose(when,otherwise)：相当于Java中的switch语句，通常于where和otherwise搭配。
3. where：简化SQL语句中where的条件判断。
4. set： 解决动态更新语句。
5. trim：可以灵活地去除多余的关键字。
6. foreach：迭代一个集合，通常用于in条件。

使用if+where属性多条件查询

if

前面学习了使用SQL映射文件操作数据库，均为SQL条件都传递了参数的时候，那如果用户不一定完整的输入所有的查询条件也就是没有传递此参数呢，下面我们来看一个例子：

SQL映射文件代码

<select id="getUserListByPojo1" resultType="User">
		select * from smbms_user where UserName like concat('%',#{userName},'%') and userRole=#{userRole} 
</select>

测试类代码

public void getUserListByPojo1(){
			SqlSession session=null;
			String userName=“赵”;
			Integer userRole=3;
			try {
				InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
				SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(is);
				session=sf.openSession();
				UserMapper userMapper=session.getMapper(UserMapper.class);
				List<User> userList=userMapper.getUserListByPojo1(userName,userRole);
				for(User user : userList){
					System.out.println("用户名称:"+user.getUserName());
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally{
				if(session!=null){
					session.close();
				}
			}

从代码中可以看出这是查询条件姓赵并且角色为3的用户。

查询结果
从DEBUG中看到传入了两个参数一个为赵一个为3，查询数据为1条。
那如果用户并没有填入其中的姓名参数呢。也就是String userName=null;的时候呢。
查询结果

这里看到传入了一个null一个3，因此对于类似情况，之前的示例代码映射文件就有漏洞。那么就需要用到动态SQL的if标签了。
修改映射文件SQL

<select id="getUserListByPojo1" resultType="User">
		select * from smbms_user where 
		<if test="userName!=null">
			UserName like concat('%',#{userName},'%') 
		</if>
		<if test="userRole!=null">
			and userRole=#{userRole} 
		</if>
		
	</select>

这个时候如果传递过来的值是空的，那么if条件就不成立就不会执行if标签嵌套的语句了。那么还有一个问题
情况一：如果第一个条件就不成立那不是第一个条件前面多了一个and关键词。执行的语句：select * from smbms_user where and userRole=3
情况二：如果所有条件都不成立那不是多了一个where关键词。执行的语句：select * from smbms_user where
那么就可以看出if只是判断条件是否成立该不该执行下面语句，要解决where、and、or这些关键词问题就需要用where标签了。

where

作用：根据需要自动检测添加where关键字并去除多余的and和or关键字
改造上一个示例，需求相同。
解决方案一：在where后面加一个固定条件where 1=1
映射文件SQL

<select id="getUserListByPojo1" resultType="User">
		select * from smbms_user where 1=1
		<if test="userName!=null">
			UserName like concat('%',#{userName},'%') 
		</if>
		<if test="userRole!=null">
			and userRole=#{userRole} 
		</if>
	</select>

解决方案二：使用where标签
映射文件SQL

select * from smbms_user 
		<where>
			<if test="userName!=null">
				and UserName like concat('%',#{userName},'%') 
			</if>
			<if test="userRole!=null">
				and userRole=#{userRole} 
			</if>
		</where>

那么这个时候就会自动智能的处理and和or了，不必担心多余的关键字导致的语法错误。

使用if+set改造更新操作

作用:去除多余的‘，’关键字。
为什么使用if+set
先看一下我们前面的例子：

<!-- 修改用户 -->
	<update id="updateUser">
		update smbms_user set
		<if test="userCode!=null">
			userCode=#{userCode},
		</if> 
		<if test="userName!=null">
			userName=#{userName},
		</if> 
		<if test="userPassword!=null">
			userPassword=#{userPassword}
		</if>
		where id=20
	</update>

在更新数据时，最后一个更新是不需要加’，'的，那如果用户并没有给最后一个条件的值时，那是不是就多了一个逗号。很简单，用set标签。
修改后映射文件SQL

<!-- 修改用户 -->
	<update id="updateUser">
		update smbms_user 
		<set>
			<if test="userCode!=null">
				userCode=#{userCode},
			</if> 
			<if test="userName!=null">
				userName=#{userName},
			</if> 
			<if test="userPassword!=null">
				userPassword=#{userPassword}
			</if>
		</set>
		where id=#{id}
	</update>

set元素主要用于更新操作，会自动检测是否加’，’，再配合if元素就可以动态的更新需要修改的字段，而不需要修改的字段，则可以不被更新。

使用if+trim替代if+where或if+set

前面学习了where标签用来去除多余的and和or，也学习了set去除多余的’，’。现在我们学习一个trim标签用来替换where或set标签更简洁的编写代码。
属性

• prefix：去除前缀
• prefixOverrides：添加前缀
• suffix：添加后缀
• suffixOverrides：去除后缀
  替代where标签

<select id="getUserListByPojo1" resultType="User">
		select * from smbms_user 
		<trim prefix="where" prefixOverrides="and|or">
			<if test="userName!=null">
				and UserName like concat('%',#{userName},'%') 
			</if>
			<if test="userRole!=null">
				and userRole=#{userRole} 
			</if>
		</trim>	
</select>

这里prefix="where"自动检测是否添加where关键字，prefixOverrides="and|or"自动检测去除多余的and或者or。
替代set标签

<!-- 修改用户 -->
	<update id="updateUser">
		update smbms_user 
		<trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">
			<if test="userCode!=null">
				userCode=#{userCode},
			</if> 
			<if test="userName!=null">
				userName=#{userName},
			</if> 
			<if test="userPassword!=null">
				userPassword=#{userPassword}
			</if>
		</trim>
	</update>

解析
prefix="set"自动检测是否添加set关键字，
suffix="where id=#{id}“自动添加后缀where id=#{id}
suffixOverrides=”,"自动检测去除多余的‘,’

使用foreach完成复杂查询

在前面我们用if、where、trim元素来处理一下简单的查询操作。那么对于一些SQL语句中含有in条件，比如要按照角色为1和3两个条件，普通SQL写法：select * from smbms_user where userRole in(1,3)，那in里面的条件传过来的就是一个集合了，这时候我们就要用foreach标签来实现SQL条件的迭代。
foreach基本属性
item：表示集合中每一个元素进行迭代时的别名。
index：指定一个名称，用于表示在迭代过程中，每次迭代的位置。
open：表示该语句以什么开始（既然是 in 条件语句所以必然以(开始）
separator：每次迭代之间以什么符号作为分割符（既然是 in 条件语句，所以必然是以 ， 作为分隔符。）
close：表示该语句以什么结尾。（既然是 in 必然是以）结尾）。
collection：表示集合的类型。最关键最容易出错的属性，需要格外注意，主要有三种情况。
（1） 若入参为单参数且参数类型为List的时候，collection属性为list
（2）若入参为单参数且参数类型为数组的时候，collection属性为array
（3）若传入的参数为多参数，就需要把它封装成Map进行处理。

MyBatis入参为数组类型的foreach迭代

//根据角色列表查询用户列表（Array）
List<User> ForeachArray(Integer[] rids);

//测试类传入的参数
//角色列表
		Integer[] rids=new Integer[]{1,3};

<!-- 根据角色列表查询用户列表（Array） -->
	<select id="ForeachArray" resultType="User">
		select * from smbms_user
		where userRole in
		<foreach collection="array" item="rid" open="(" close=")" separator=",">
			#{rid}
		</foreach>
	</select>

解析
collection=“array"表示集合为数组类型
item=“rid"表示集合迭代后的别名
open=”(” 表示以"(“开始
close=”)" 表示以”)“结尾
separator=","表示以,隔开
执行完这语句完整的应为：select * from smbms_user where userRole in(1,3)。

MyBatis入参为List类型的foreach迭代

以list集合入参和以数组入参区别就是需要改collection为list

<!-- 根据角色列表查询用户列表（List） -->
	<select id="ForeachList" resultType="User">
		select * from smbms_user
		where userRole in
		<foreach collection="list" item="rid" open="(" close=")" separator=",">
			#{rid}
		</foreach>
	</select>

MyBatis入参为Map类型的foreach迭代

以Map入参就需要注意了，以Map类型入参取的集合是Map集合中的某一个key，那么我们collection属性就需要填入需要迭代集合在Map中的key值。
例：

//根据角色列表和性别查询用户列表（Map）
List<User> foreachMap(Map<String, Object> map);

		//角色列表
		List<Integer> rids=new ArrayList<Integer>();
		rids.add(1);
		rids.add(3);
		//性别
		Integer gender=1;
		Map<String,Object> map=new HashMap<String, Object>();
		map.put("rids", rids);
		map.put("gender",gender);


<!-- 根据角色列表和性别查询用户列表（Map） -->
	<select id="foreachMap" resultType="User">
		select * from smbms_user
		where userRole in
		<foreach collection="rids" item="rid" open="(" close=")" separator=",">
			#{rid}
		</foreach>
		and gender=#{gender}
	</select>

解析
这里collection属性填的是Map中集合的key rids。

choose（when，otherwise）

对于某些查询需要，虽然有多个查询条件，但是我们不想应用所有的条件，只想选择其中一个条件去查询，这时候可以用choose标签。

select * from smbms_user 
		<where>
			<choose>
				<when test="userRole!=null and userRole!=0">
					and userRole=#{userRole} 
				</when>
				<when test="userName!=null and userName!=''">
					and userName like concat('%',#{userName},'%') 
				</when>
				<otherwise>and 1=1</otherwise>
			</choose>
		</where>

when元素相当于java中的switch，如果满足条件执行内容，不满足继续往下面找，如果满足了就不再执行下面的语句。如果都不满足将执行otherwise的语句。

MyBatis实现分页

我们先回顾一下SQL语句怎么查询分页：
SELECT * FROM 表名 WHERE 条件 LIMIT 偏移量, 需要显示的总记录数;

使用普通的方法实现：

//分页
	@Select("select * from smbms_user limit #{from},#{pageSize}")
	List<User> getUserListByPage(
			@Param("from")Integer from,
			@Param("pageSize")Integer pageSize
			);

@Test
	public void testPage(){
		/**
		 * 分页：
		 * 1.查询总记录数：计算分页信息
		 * 		1.1、pageNum合理性
		 * 		1.2、pages计算（整除|不整除）
		 * 		1.3、有没有上一页，有没有下一页
		 * 2.查询数据:分页数据limit(from,pageSize)
		 */
		Integer pageNum=4;
		Integer pageSize=5;
		Integer from=(pageNum-1)*pageSize;
		SqlSession session=null;
		try{
			session=MyBatisUtil.getSession();
			UserMapper userMapper=session.getMapper(UserMapper.class);
			List<User> userlist=userMapper.getUserListByPage(from,pageSize);
			if(userlist!=null){
				for(User user : userlist){
					System.out.println("用户名称:"+user.getUserName());
					System.out.println("角色名称："+user.getUserRoleName());
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSession(session);
		}
}

使用分页助手实现

1.导入jar包
jsqlparser-0.9.5.jar
pagehelper-5.1.8.jar
2.配置分页助手的拦截器
mybatis-config.xml

<!-- 分页助手 -->
	<plugins>
		<plugin interceptor="com.github.pagehelper.PageInterceptor">
			<property name="reasonable" value="true"/>
		</plugin>
	</plugins>

需要按照顺序在typeAliases标签的下面
3.开始使用

//查询用户列表并分页
List<User> getUserList();

<!-- 查询用户列表并分页 -->
	<select id="getUserList" resultType="User">
		select <include refid="uc"/> from smbms_user
	</select>


@Test
	public void testGetUserList(){
		//查询用户列表并分页
		SqlSession session=null;
		try{
			session=MyBatisUtil.getSession();
			UserMapper userMapper=session.getMapper(UserMapper.class);
			PageHelper.startPage(10, 5);//第几页，拿几条，如果没有那么多页默认拿最后一页的数据
			List<User> userlist=userMapper.getUserList();
			PageInfo<User> pi=new PageInfo<User>(userlist);//利用分页助手查询的分页的一些信息
			System.out.println("总页数："+pi.getPages());
			System.out.println("总记录数："+pi.getTotal());
			System.out.println("有没有上一页："+pi.isHasPreviousPage());
			System.out.println("有没有下一页："+pi.isHasNextPage());
			System.out.println("页码："+pi.getPageNum());
			if(userlist!=null){
				for(User user : userlist){
					System.out.println("用户名称:"+user.getUserName());
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSession(session);
		}
	}

其中测试类中关键的是PageHelper.startPage(10, 5);//第几页，拿几条，如果没有那么多页默认拿最后一页的数据
和PageInfo pi=new PageInfo(userlist);//利用分页助手查询的分页的一些信息。

总结