Mybatis-3(常用标签组合)

一.标签概览

• 1.查询,where+if组合

<!-- where 自动判断第一个条件前是否需要加 and -->
select
    a.id
 from t_user a
<where>
	<if test="email != null and email != ''">
		and a.email like CONCAT('%', #{email}, '%')
	</if>
	<if test="sex != null ">
		and a.sex = #{sex}
	</if>
</where>

• 2.更新,update+set组合

   <!-- set 会自动去掉最后一个条件中的',' -->	
   update t_user
<set>
	<if test="userName != null">
		userName = #{userName,jdbcType=VARCHAR},
	</if>
	<if test="realName != null">
		realName = #{realName,jdbcType=VARCHAR},
	</if>
</set>
where id = #{id,jdbcType=INTEGER} 

• 3.插入,insert+trem组合

	<!-- trim 会自动在左边加上'(',右边加上')',并且去处最后一个','-->
insert into t_user
	<trim prefix="(" suffix=")" suffixOverrides="," >
		<if test="id != null">
			id,
		</if>
		<if test="userName != null">
			userName,
		</if>
	</trim>
	<!-- trim 会自动在左边加上'values (',右边加上')',并且去处最后一个','-->
	<trim prefix="values (" suffix=")" suffixOverrides=",">
		<if test="id != null">
			#{id,jdbcType=INTEGER},
		</if>
		<if test="userName != null">
			#{userName,jdbcType=VARCHAR},
		</if>
	</trim>

• 4.forEach标签

  查询,参数为arry或者list时

<!-- 查询, 参数为数组时 -->
select
	a.id
	from t_user a
	where a.userName in
	<foreach collection="array" open="(" close=")" item="userName" separator=",">
		#{userName}
	</foreach> 
<!-- 查询, 参数为List时 -->
select
	a.id
	from t_user a
	where a.userName in
	<foreach collection="list" open="(" close=")" item="userName" separator=",">
		#{userName}
	</foreach> 

批量插入1

insert into t_user (userName,position_id)
	values
	<foreach collection="list" separator="," item="user">
		(
		#{user.userName,jdbcType=VARCHAR},
		#{user.position.id,jdbcType=INTEGER}
		)
	</foreach>

批量插入2

使用BATCH类型的excutor

示例参考跳转: