Marco's Java【Mybatis进阶(一) 动态SQL】

前言

学到这里，大家是不是觉得Mybatis非常强大了，而且上手快，比较简单。如果你觉得Mybatis仅限于此，那就大错特错了，接下来我们要讲解的内容才是大开眼界，，听多了扯淡的介绍，来点正经的概述提提神。

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis采用功能强大的基于 OGNL 的表达式来消除其他元素。
======= 动态SQL语句标签 =========
• if
• where
• trim
• choose (when, otherwise)
• set
• foreach

动态SQL

其实我们的动态SQL和之前学到的JSTL语法一点点类似，而且比较通俗易懂，相信大家应该都能够很快的接受

使用if标签构建动态SQL

在我们没有学习动态Sql之前，大家还记得我们在上个项目的分页查询中是怎么手动的拼接sql吗？我先带大家来回顾一下。

String sql = "select id as id, user_name as userName, password as password, real_name as realName, img as img, is_del as isDel, date_format(create_time,'%Y-%m-%d') as createTime, date_format(del_time,'%Y-%m-%d') as delTime from u_user where";
if(!CheckParamUtil.isBlank(params.get("userName"))) {
	sql += " user_name = '" + params.get("userName") + "' and";
}
if(!CheckParamUtil.isBlank(params.get("realName"))) {
	sql += " real_name like '" + params.get("realName") + "%' and";
}
if(!CheckParamUtil.isBlank(params.get("isDel"))) {
	sql += " is_del = " + params.get("isDel") + "and";
}
if(!CheckParamUtil.isBlank(params.get("startDate"))) {
	sql += " create_time >= '" + params.get("startDate") + "' and";
}
if(!CheckParamUtil.isBlank(params.get("endDate"))) {
	sql += " create_time <= '" + params.get("endDate") + "' and";
}
if(sql.endsWith("and")) {
	int andIndex = sql.lastIndexOf("and");
	sql = sql.substring(0, andIndex);
} else if(sql.endsWith("where")) {
	int whereIndex = sql.lastIndexOf("where");
	sql = sql.substring(0, whereIndex);
}

我这里专门截取了我们销售合同项目分页查询中的一个片段，从上面不难看出当我们没有任何辅助框架的时候，拼接Sql虽然不是一件难事，但说实在的，还是比较麻烦吧？
我依然还记得，我第一次写分页拼接sql的时候出了不少错，比如少打一个标点符号呀，and后面多写了一个空格呀，substring的参数传的不对等等，虽然简单，但真的要非常细心才能完成。

那说了这么多，做了这么多铺垫，当然是为了衬托我们的Mybatis框架啦。同样的我们举一个实用的栗子。
我们平时在做账号密码登录的时候，账号和密码是不能为空的，这个相信大家都知道，我们的校验一般分为两种，一种是在前端校验，再者就是后端校验，为什么必须得校验两次呢？
毕竟前端校验防得了君子，防不了小人，因此，后台的校验也是至关重要的，那么本次我们使用动态Sql来完成一次后端验证码校验啦（这里仅以介绍并测试动态SQL为目的，亲们请忽略掉严谨性啦 ~）。

<select id="query" parameterType="map" resultMap="userResultMap">
	select user_name, password, realName from u_user where
		<if test="userName != null and userName != ''">
			user_name = #{userName} and
		</if>
		<if test="password != null and password != ''">
			password = #{password}
		</if>
</select>

我们先来运行下代码测试一下

public static void main(String[] args) {
		boolean isLogin = UserServiceImpl.doLogin("marco", "123456");
		System.out.println(isLogin);
}
public static boolean doLogin(String userName, String password) {
		UserDao userDao = MybatisUtil.getMapper(UserDao.class);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("userName", userName);
		map.put("password", password);
		User user = userDao.query(map);
		return user == null? false:true;
	}
}

结果为true，发现好像没啥问题

那我在这里把再密码设置为null来试试

boolean isLogin = UserServiceImpl.doLogin("marco", null);

其实大家眼尖应该看的出来我们这个代码的漏洞在哪了，当我们的密码为null，那么<if>里面的代码就不会走
Sql会变成select user_name, password, realName from u_user where user_name = 'marco' and
因此为了避免这种情况产生，我们通常选择在where后面直接跟上 1=1 恒成立语句

<select id="query" parameterType="map" resultMap="userResultMap">
	select user_name, password, realName from u_user where 1=1
		<if test="userName != null and userName != ''">
			and user_name = #{userName}
		</if>
		<if test="password != null and password != ''">
			and password = #{password}
		</if>
</select>

这样写的话就算密码为空，也不会报错，而是查询不到数据啦

使用where标签构建动态SQL

那我们还是接着上面的栗子讲，虽然where 1=1 这种方式真的很好用，但是总觉得很别扭，那接下来我们换种方式来解决这个系统报异常的问题

<select id="query" parameterType="map" resultMap="userResultMap">
	select user_name, password, realName from u_user
	<where>
		<if test="userName != null and userName != ''">
			and user_name = #{userName}
		</if>
		<if test="password != null and password != ''">
			and password = #{password}
		</if>
	</where>
</select>

使用where标签省去了我们加上where 1=1 的麻烦，也不用担心sql需要剪裁的问题啦！

使用trim标签构建动态SQL

解决问题的方式有多种，像上面这种问题，我有一百种解决它的方式！好，再介绍最后一种 (吹完赶紧溜…)
使用trim标签达到剪裁的效果，首先，咱们先点开看我们的trim，会发现里面有4个属性，那我先介绍一下这四个属性的含义

prefix：在当前的SQL前插入的字符串
prefixOverrides：在所在的SQL前需要被剪裁的字符串
suffix：在当前的SQL后插入的字符串
suffixOverrides：在所在的SQL后需要被剪裁的字符串

那我们将上面的栗子再改写一下吧

<select id="query" parameterType="map" resultMap="userResultMap">
	select user_name, password, realName from u_user
	<!-- 在后面跟上的sql语句前加上where，并剪裁掉最后跟上的字符串and -->
		<trim prefix="where" suffixOverrides="and">
			<if test="userName != null and userName != ''">
			 user_name = #{userName} and
			</if>
			<if test="password != null and password != ''">
			 password = #{password} and
			</if>
		</trim>
</select>

使用choose标签构建动态SQL

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<select id="query" parameterType="map" resultMap="userResultMap">
	select user_name, password, realName from u_user from u_user where
		<choose>
			<when test="id != null">
				id = #{id}
			</when>
			<when test="userName != null">
				user_name like concat('%',#{userName},'%')
			</when>
			<otherwise> 1=1 </otherwise>
		</choose>
</select>

当我们传入的值为id的时候，就会按照id查询，当我们传入的值是userName的时候，就会按照userName模糊查询，当以上两种情况都不符合的时候，则全部查询，这点是不是和我们的switch语句的结构很相似呢?

使用set标签构建动态SQL

变相来说set和我们的where标签所扮演的角色都是差不多的，就好比<where>不仅有where语句的功能，还可以去掉多余的and，起到剪裁的作用，那么同理，set不仅有set语句的功能，也可以起到剪裁的作用，只不过，这里剪裁的对象从and变成了,而已

<update id="editUser" parameterType="Map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
	update u_user
	<set>
		<if test="userName  != null and userName != ''">
			user_name = #{userName},
		</if>
		<if test="password  != null and password != ''">
			password = #{password},
		</if>
		<if test="realName  != null and realName != ''">
			real_name = #{realName},
		</if>
	</set>
	where id = #{id}
</update>

使用foreach标签构建动态SQL

接下来的这个标签就比较强大啦，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符，非常的"智能化"。
那我在这里还是先上栗子吧，就拿批量删除来说事儿~

<delete id="delete" parameterType="List">
	delete from u_user where id in 
	<foreach collection="list" item="id" open="(" close=")" separator="," >
		#{id}
	</foreach>
</delete>

这里parameterType我传的是List集合，所以类型是List，我这边取名为list，因此我们需要遍历的容器collection的名称为list，item就是List中的单个元素，是不是和我们JSTL中的items以及var的功能十分相似呢？
foreach标签中还提供了开闭匹配的字符串，以上我们的sql应该组成这个样子
delete from u_user where id in (1,2,3....) 相信聪明的你应该懂了open="(" close=")" separator=","的意思和作用了对吧~
那么本节我们的内容就到此为止啦~