mybatis配置文件动态sql简化开发

在mybatis跟实体类的映射sql文件中，有时候好多重复的sql语句，那么我们怎么简化少些这些语句？如下中：

<?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">

<!-- usermapper.xml对用user表的操作 -->
<!-- namespace：用于隔离sql语句 -->
<!-- namesapce是类路径名 -->
<mapper namespace="com.hpe.mapper.UserMapper">

	<!-- 当条件表达式条件为真，才会拼接上if中的sql -->
	<select id="findUserByUser" parameterType="User" resultType="User">
		select* from user
		<where><!-- 自动去掉第一个and -->
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>
		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
		</where>
	</select>

	<select id="findUserByUser1" parameterType="User" resultType="User">
		select* from user
		<where><!-- 自动去掉第一个and -->
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>

		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
			<!-- <if test="ids!=null and ids.size()>0"> and id in (<foreach collection="ids" 
				item="item" separator=","> #{item} </foreach> ) </if> -->
			<if test="ids!=null and ids.size()>0">
				and (
				<foreach collection="ids" item="item" separator="or">
					id=#{item}
				</foreach>
				)
			</if>
		</where>

	</select>

	<select id="findUserCountByUser" parameterType="User"
		resultType="int">
		select count(*) from user
		<where><!-- where ognl标签自动去掉第一个and -->
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>
		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
		</where>
	</select>
</mapper>

可见每个sql标签中，都有一段共同得sql语句

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

这样一来重复的代码量很大，那么我们把这段公共代码提取出来，放在<sql>标签中,并给个id，以便在引用来减少代码量

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

		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
</sql>

那么，整体代码就可以写成这样：

<?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">

<!-- usermapper.xml对用user表的操作 -->
<!-- namespace：用于隔离sql语句 -->
<!-- namesapce是类路径名 -->
<mapper namespace="com.hpe.mapper.UserMapper">
	
		<sql id="query_user">
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>

		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
	</sql>
	
	<!-- 当条件表达式条件为真，才会拼接上if中的sql -->
	<select id="findUserByUser" parameterType="User" resultType="User">
		select* from user
		<where><!-- 自动去掉第一个and -->
			<include refid="query_user"></include>
		</where>
	</select>

	<select id="findUserByUser1" parameterType="User" resultType="User">
		select* from user
		<where><!-- 自动去掉第一个and -->
			<include refid="query_user"></include>

			<!-- <if test="ids!=null and ids.size()>0"> and id in (<foreach collection="ids" 
				item="item" separator=","> #{item} </foreach> ) </if> -->

			<if test="ids!=null and ids.size()>0">
				and (
				<foreach collection="ids" item="item" separator="or">
					id=#{item}
				</foreach>
				)
			</if>
		</where>

	</select>

	<select id="findUserCountByUser" parameterType="User"
		resultType="int">
		select count(*) from user
		<where><!-- 自动去掉第一个and -->
			<include refid="query_user"></include>
		</where>
	</select>
</mapper>