Mybatis之动态SQL标签技术

常用动态SQL标签:
if
where
set
foreach
trim
choose
sql
1.1
if标签:XXX
功能:判断;

<mapper namespace="com.cy.pj.Student">

	<select id="findObjects" resultType="Student">
		select * from student where 1=1
		<if test="id!=0">
		and id=#{id}
		</if>	
		<if test="sname!=null">
		and sname=#{sname}
		</if>
	</select>
	
</mapper>

1.2
where标签:XXX
功能1:去掉头上的and或者or;
功能2:不需要考虑空格问题,会自动补上;

<mapper namespace="com.cy.pj.Student">

	<select id="findObjects" resultType="Student">
		select * from student
		<where>
		<if test="id!=0">
		and id= #{id}
		</if>
		<if test="sname!=null">
		and sname!= #{sname}
		</if>
		</where>
	</select>
	
</mapper>

1.3
set标签:
XXX
功能:只用于update更新语句,去掉最后一个修改的属性不满足条件时之前修改的属性遗留的一个逗号;

<mapper namespace="com.cy.pj.Student">

	<update id="updateObjects">
		update student
		<set>
			<if test="sname != null">sname=#{sname},</if>
			<if test="sage != null">sage=#{sage},</if>
			<if test="snum != null">snum=#{snum},</if>
			<if test="saddr != null">saddr=#{saddr}</if>
		</set>
		where id=#{id}
	</update>

</mapper>

1.4
foreach标签:
元素:collection(要遍历的集合名称),item(遍历出来的元素),open(遍历以什么开头),separator(遍历分隔符),close(遍历以什么结束),index(元素当前迭代的次数)

<mapper namespace="com.cy.pj.Student">

	<select id="getObjects" resultType="Student">
		select * from student
		where id in
		<foreach collection="list" item="item" index="index" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</select>
	
</mapper>

1.5
trim标签:

eg1.
(前缀)prefix属性指定的字符串会会替换掉prefixOverrides属性指定的SQL语句中的字符串;

<select id="dynamicTrimTest" parameterType="Blog"
		resultType="Blog">
		select * from t_blog
		<trim prefix="where" prefixOverrides="and |or">
			<if test="title != null">
				and title = #{title}
			</if>
			<if test="content != null">
				and content = #{content}
			</if>
			<if test="owner != null">
				or owner = #{owner}
			</if>
		</trim>
	</select>

eg2,
(后缀)suffix属性指定的字符串会会替换掉suffixOverrides属性指定的SQL语句中的字符;

<mapper namespace="com.cy.pj.Student">

	<update id="updateObjects" parameterType="com.cy.pj.Student">
        update student
        set
        <trim suffixOverrides="," suffix="where id = #{id}">
            <if test="sage!=null and sage!=''">sage= #{sage},</if>
            <if test="snum!=null and snum!=''">snum= #{snum},</if>
        </trim>
    </update>
	
</mapper>

1.6
choose标签
功能:不希望应用到所有的条件语句，只想从中择其一项;

<mapper namespace="com.cy.pj.Student">

	<select id="getObjects" parameterType="Student">
		select * from student where 1=1
		<choose>
			<when test="id != 0">
				and id = #{id}
			</when>
			<when test="sname != null">
				and sname = #{sname}
			</when>
			<otherwise>
				and sage=#{sage}
			</otherwise>
		</choose>
	</select>
	
</mapper>

1.7
sql标签
功能:提取共性语句;提高效率;缩减工作量;

<mapper namespace="com.cy.pj.sys.dao.SysLogDao">

	<sql id="queryWhereId">
		<where>
			<if test="username!=null and username!=''">
				username like concat("%",#{username},"%")
			</if>
		</where>
	</sql>

	<select id="getRowCount" resultType="Integer">
		select count(*) from sys_Logs
		<include refid="queryWhereId" />
	</select>

	<select id="findPageObjects"
		resultType="com.cy.pj.sys.entity.SysLog">
		select * from sys_Logs
		<include refid="queryWhereId" />
		order by createdTime desc
		limit #{startIndex},#{pageSize}
	</select>

</mapper>