MyBatis动态sql:更方便的拼接sql语句
1、if标签 - 多条件查询,获取用户列表;
<if test="u_sex!=null and u_sex!=''">
u_sex=#{u_sex} and
</if>
<if test="u_username!=null and u_username!=''">
u_username like "%"#{u_username}"%" and
</if>
<if test="u_cid!=null">
u_cid = #{u_cid}
</if>
2、where标签 - 解决if标签拼接字符串AND符号问题;
<where>
<!-- where 标签可以去除开头的AND -->
<if test="u_sex!=null and u_sex!=''">
u_sex=#{u_sex}
</if>
<if test="u_username!=null and u_username!=''">
and u_username like "%"#{u_username}"%"
</if>
<if test="u_cid!=null">
and u_cid = #{u_cid}
</if>
</where>
3、trim标签 - 定制where标签的规则
<trim prefix="where" suffixOverrides="AND|OR">
<if test="u_sex!=null and u_sex!=''">
u_sex=#{u_sex} and
</if>
<if test="u_username!=null and u_username!=''">
u_username like "%"#{u_username}"%" and
</if>
<if test="u_cid!=null">
u_cid = #{u_cid}
</if>
</trim>
4、set标签 - 解决更新数据表时字符串拼接逗号”,”问题;
<!-- //更新用户资料,用户名、用户密码、用户性别,通过id限制
public void updateUser(User u); -->
<update id="updateUser" parameterType="User">
update user
set
<if test="u_username !=null and u_username !=''">
u_username=#{u_username},
</if>
<if test="u_password !=null and u_password !=''">
u_password=#{u_password},
</if>
<if test="u_sex !=null and u_sex !=''">
u_sex=#{u_sex}
</if>
where u_id=#{u_id}
</update>
5、foreach标签 – 如果需要使用IN查询多条相同数据,可以使用foreach遍历;要说明的一点事<collection>中的属性值问题,如果是数组应该是数组,如果是list应该是list,而如果是对象包装类,那应该对应的应该是包装类中对应的属性名.
<!--//通过多个id输出用户列表 通过整数数组
public List<User> selectUserListByIds(Integer[] ids); -->
<select id="selectUserListByIds" parameterType="Integer" resultType="User">
select *
from user
where u_id in
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!--//通过多个id输出用户列表 通过list
public List<User> selectUserListByIdList(Integer[] ids); -->
<select id="selectUserListByIdList" parameterType="Integer" resultType="User">
select *
from user
where u_id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
<!--//通过多个id输出用户列表 通过UserVo
public List<User> selectUserListByUserVo(Integer[] ids); -->
<select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">
<include refid="myselect"/>
where u_id in
<foreach collection="idList" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
6、sql标签 – 可以提取重复sql语句片段;
在Mapper.xml文件中的最开头使用<sql>标签,提取重复利用的SQL语句,id是在引用过程中引用的标识。
例如:
<sql id="myselect">
select * from user
</sql>
在引用过程中,利用<include>标签将内容包含进来,
<select id="selectUserListByUserVo" parameterType="UserVo" resultType="User">
<include refid="myselect"/>
where u_id in
<foreach collection="idList" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>