5.动态SQL语句

一.where 和 if标签

 <select id="getAllUsersBy5" resultType="User" parameterType="User">
	 	select id,userCode,userName,userPassword,address
	 	from smbms_user
	 	<where>
		 	<if test="userName!=null">
		 		and userName like CONCAT('%',#{userName},'%')
		 	</if>
		 	<if test="userRole2.id!=0">
		 		and userRole=#{userRole2.id}
		 	</if>
	 	</where>
	 </select>

二.trim标签

prefix=“where” 添加前缀
prefixOverrides=“and|or” 覆盖前缀(中间为竖线)

	 <select id="getAllUsersBy6" parameterType="User" resultType="User">
	 	select id,userCode,userName,userPassword,address
	 	from smbms_user
	 	<trim prefix="where" prefixOverrides="and|or">
	 		<if test="userName!=null">
	 			and userName like CONCAT('%',#{username},'%')
	 		</if>
	 		<if test="userRole2.id!=0">
	 			and userRole=#{userRole2.id}
	 		</if>
	 	</trim>
	 </select>

三.使用trim代替set标签和where标签

suffix=“where id=#{id}” 添加后缀
suffixOverrides="," 覆盖后缀

	 <update id="updateUser3" parameterType="User">
	 	update smbms_user
	 	<trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">
	 		<if test="userName!=null">
	 			userName=#{userName},
	 		</if>
	 		<if test="userCode!=null">
	 			userCode=#{userCode},
	 		</if>
	 		<if test="userRole2.id!=0">
	 			userRole=#{userRole2.id},
	 		</if>
	 	</trim>
	 </update>

四.声明查询语句的公共部分

	 <sql id="commonselect">
	 	select id,userCode,userName,userPassword,phone,address
	 	from smbms_user
	 </sql>
	 <select id="getAllUsersBy8" parameterType="User" resultType="User">
	 	<!-- 引入外部文件的复用SQL:   命名空间名.id值 -->
	 	<include refid="commonsql.commonselect"></include>
	 	where userName like CONCAT('%',#{userName},'%')
	 </select>

CommonSql.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="commonsql"命名空间的名字 -->
<mapper namespace="commonsql">
	<sql id="commonselect">
		select id,userCode,userName,userPassword,phone,address
		from smbms_user
	</sql>
</mapper

---

五.forEach迭代标签

userDao.java

//参数是数组类型
public List<User> getAllUsersByAry(int[] aryRole);

//参数是集合类型
public List<User> getAllUsersByList(List<Integer> roleList);

UserMapper.xml

<!-- 参数是数组类型 -->
 <select id="getAllUsersByAry" resultType="User">
 	select id,userCode,userName,userPassword,userRole
 	from smbms_user
 	where userRole in
 	<!-- 需要使用迭代标签遍历数组
 		 array:关键字,参数是数组,即遍历的是数组
 	 -->
 	 <foreach collection="array" item="aryRole"
 	 open="(" close=")" separator=",">
 	 #{aryRole}
 	 </foreach>
 </select>
 
 <!-- 参数是集合类型 -->
 <select id="getAllUsersByList" resultType="User">
 	select id,userCode,userName,userPassword,userRole
 	from smbms_user
 	where userRole in
 	<!-- 需要使用迭代标签遍历集合
 		 list:关键字,参数是集合,即遍历的是集合
 	 -->
 	 <foreach collection="list" item="roleList"
 	 open="(" close=")" separator=",">
 	 #{roleList}
 	 </foreach>
 </select>