持久层框架MyBatis学习(4)-动态sql

最新推荐文章于 2024-08-21 08:31:53 发布

又又又又

最新推荐文章于 2024-08-21 08:31:53 发布

阅读量206

点赞数 2

分类专栏：笔记文章标签： javaweb mybatis

本文链接：https://blog.csdn.net/weixin_42340305/article/details/101214035

版权

笔记专栏收录该内容

21 篇文章 0 订阅

订阅专栏

一，动态sql

通过mybatis提供的各种标签方法实现动态拼接sql

1.< if > + < where >

多条件查询

<select id="findListByUser" parameterType="user" resultType="user">
	select * from user
	<where>
		<if test="username != null and username != ''">
			and username like '%${username}%'
		</if>
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if>
		<if test="address != null and address != ''">
			and address = #{address}
		</if>
	</where>
</select>

< if >:判断
< where >:去掉第一个and或者or

2.< foreach >

查询多个id

<select id="findListByArray" parameterType="queryVo" resultType="user">
	select * from user where id in
	<foreach collection="ids" open="(" close=")" separator="," item="id">
		#{id}
	</foreach>
</select>

< foreach > ：循环可以循环数据也可以循环集合

3.< sql > 和 < include >

查询多个id

<select id="findListByUser" parameterType="user" resultType="user">
   select * from user 
   <include refid="findUserWhere"></include>
</select>
   
<!--    片段 -->
<sql id="findUserWhere">
    <where>
     <if test="username!=null and username!=''">
       and username like '%${username}%'
     </if>
     
     <if test="sex!=null and sex!=''">
       and sex=#{sex} 
     </if>
     
     <if test="address!=null and address!=''">
      and address=#{address}
     </if>
    </where> 
</sql>

< sql > ：片段
< include >:引用某sql片段

4.< set >

<update id="updateUser" parameterType="user">
	update user 
	<set>
      <if test=" username!=null and username != ''">
       username=#{username},
      </if>
      <if test=" sex!=null and sex != ''">
      sex=#{sex},
      </if>
      <if test="birthday!=null">
      birthday=#{birthday},
      </if>
      <if test="address!=null and address != ''">
      address=#{address},
      </if>
    </set> 
    where id=#{id}
</update>