MyBatis：动态SQL

动态SQL

通过MyBatis 提供的各种标签方法实现动态拼接 SQL 。

if

<select id="findUserByUsername" parameterType="org.haiwen.entity.User" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<if test="username != null and username != ''">
			and username = #{username}
		</if>
	</where>
</select>

如果没有传入“username”，那么发送 sql ：select * from user;

反之传入“username”，那么发送sql：select * from user WHERE username = ?;

若语句的开头为“AND”或“OR”，where元素也会将它们去除。

choose,when,otherwise

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

<select id="findUser" parameterType="org.haiwen.entity.User" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<choose>
			<when test="id != null and id != ''">
				and id = #{id}
			</when>
			<when test="username != null and username != ''">
				and username = #{username}
			</when>
			<otherwise>
				and password is not null
			</otherwise>
		</choose>
	</where>
</select>

当所有条件都不符合时，拼接otherwise子句。

如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

trim,where

<select id="findUser2" parameterType="org.haiwen.entity.User" resultType="org.haiwen.entity.User">
	select * from user
	<trim prefix="where" prefixOverrides="and |or ">
		<choose>
			<when test="id != null and id != ''">
				and id = #{id}
			</when>
			<when test="username != null and username != ''">
				and username = #{username}
			</when>
			<otherwise>
				and password is not null
			</otherwise>
		</choose>
	</trim>
</select>

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。

set

<update id="updateUser" parameterType="org.haiwen.entity.User">
	update user
	<set>
		<if test="username != null and username != ''">username = #{username},</if>
		<if test="password != null and password != ''">password = #{password},</if>
	</set>
	where id = #{id}
</update>

set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。

trim,set

<update id="updateUser2" parameterType="org.haiwen.entity.User">
	update user
	<trim prefix="set" suffixOverrides=",">
		<if test="username != null and username != ''">username = #{username},</if>
		<if test="password != null and password != ''">password = #{password},</if>
	</trim>
	where id = #{id}
</update>

foreach

传递pojo包装类，包装类中是list

<select id="findUserByIds" parameterType="org.haiwen.pojo.QueryUserVo" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<if test="ids != null and ids.size>0">
			<foreach collection="ids" item="id" open=" and id in(" close=")" separator="," >
    			#{id}
    		</foreach>
		</if>
	</where>
</select>

public List<User> findUserByIds(QueryUserVo queryUserVo) throws Exception;

@Test
public void testFindUserByIds() throws Exception {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	List<Integer> ids = new ArrayList<>();
	ids.add(1);
	ids.add(2);
	QueryUserVo queryUserVo = new QueryUserVo();
	queryUserVo.setIds(ids);
	List<User> list = userMapper.findUserByIds(queryUserVo);
	for (User user : list) {
		System.out.println(user);
	}
	sqlSession.close();
}

传递单个list，list中是pojo

<select id="findUserByList" parameterType="java.util.List" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<!-- 传递List，List中是pojo -->
		<if test="list != null">
			<foreach collection="list" item="item" open="and id in(" close=")" separator="," >
				#{item.id}
			</foreach>
		</if>
	</where>
</select>

public List<User> findUserByList(List userList) throws Exception;

@Test
public void testFindUserByList() throws Exception {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	User user1 = new User();
	user1.setId(1);
	User user2 = new User();
	user2.setId(2);
	List<User> userList = new ArrayList<>();
	userList.add(user1);
	userList.add(user2);
	List<User> list = userMapper.findUserByList(userList);
	for (User user : list) {
		System.out.println(user);
	}
	sqlSession.close();
}

传递单个数组，数组中是pojo

<select id="findUserByArray" parameterType="Object[]" resultType="org.haiwen.entity.User">
	select * from user 
	<where>
		<!-- 传递数组，数组中是pojo -->
		<if test="array != null">
			<foreach collection="array" item="item" index="index" open="and id in(" close=")" separator="," >
			    #{item.id} 
			</foreach>
		</if>
	</where>
</select>

public List<User> findUserByArray(Object[] userArray) throws Exception;

@Test
public void testFindUserByArray() throws Exception {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	User user1 = new User();
	user1.setId(1);
	User user2 = new User();
	user2.setId(2);
	Object[] userArray = new Object[2];
	userArray[0] = user1;
	userArray[1] = user2;
	List<User> list = userMapper.findUserByArray(userArray);
	for (User user : list) {
		System.out.println(user);
	}
	sqlSession.close();
}

传递单个数组，数组中是string

<select id="findUserByIds2" parameterType="Object[]" resultType="org.haiwen.entity.User">
	select * from user 
	<where>
		<!-- 传递数组，数组中是string -->
		<if test="array != null">
			<foreach collection="array" item="item" index="index" open="and id in(" close=")" separator="," >
			    #{item} 
			</foreach>
		</if>
	</where>
</select>

public List<User> findUserByIds2(Object[] idArray) throws Exception;

@Test
public void testFindUserByIds2() throws Exception {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	Object[] idArray = new Object[2];
	idArray[0] = "1";
	idArray[1] = "2";
	List<User> list = userMapper.findUserByIds2(idArray);
	for (User user : list) {
		System.out.println(user);
	}
	sqlSession.close();
}

bind

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如：

<select id="findUserByVO" resultType="org.haiwen.entity.User">
	<bind name="username" value="'%' + userCustom.getUsername() + '%'" />
	select * from user where username like #{username}
</select>

public List<User> findUserByVO(QueryUserVo queryUserVo) throws Exception;

@Test
public void testFindUserByVO() throws Exception {
	SqlSession sqlSession = sqlSessionFactory.openSession();
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	UserCustom userCustom = new UserCustom();
	userCustom.setUsername("马");
	QueryUserVo queryUserVo = new QueryUserVo();
	queryUserVo.setUserCustom(userCustom);
	List<User> list = userMapper.findUserByVO(queryUserVo);
	for (User user : list) {
		System.out.println(user);
	}
	sqlSession.close();
}

多数据库支持

一个配置了“_databaseId”变量的 databaseIdProvider 可用于动态代码中，这样就可以根据不同的数据库厂商构建特定的语句。比如下面的例子：

<insert id="insert">
	<selectKey keyProperty="id" resultType="int" order="BEFORE">
		<if test="_databaseId == 'oracle'">
			select seq_users.nextval from dual
		</if>
		<if test="_databaseId == 'db2'">
			select nextval for seq_users from sysibm.sysdummy1
		</if>
	</selectKey>
	insert into users values (#{id}, #{name})
</insert>

sql片段

sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的，如下：

<select id="selectUserList" parameterType="org.haiwen.entity.User" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<if test="id !=null and id != ''">
			and id = #{id}
		</if>
		<if test="username != null and username != ''">
			and username like '%${username}%'
		</if>
	</where>
</select>

将where条件抽取出来：

<sql id="query_user_where">
	<if test="id !=null and id != ''">
			and id = #{id}
	</if>
	<if test="username != null and username != ''">
		and username like '%${username}%'
	</if>
</sql>

使用include引用：

<select id="selectUserList" parameterType="org.haiwen.entity.User" resultType="org.haiwen.entity.User">
	select * from user
	<where>
		<include refid="query_user_where" />
	</where>
</select>

注意：如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，如下： <include refid="namespace.sql片段” />