mybatis动态sql

最新推荐文章于 2023-05-22 08:15:55 发布

dejavu111

最新推荐文章于 2023-05-22 08:15:55 发布

阅读量125

点赞数

分类专栏： mybatis

本文链接：https://blog.csdn.net/weixin_42983044/article/details/84876686

版权

mybatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

if
动态sql 有条件的包含where子句

<select id="findActiveBlogLike"
	resultType="Blog">
    select * from blog
    where status="ACTIVE"
	<if test="title != null">
		and title like #{title}
	</if>
	<if test="author != null and author.name != null">
		and author_name like #{author.name}
	</if>
</select>

choose类似switch
当有一个满足时就跳出，不判断下面的条件

<select id="findActiveBlogLike"
	resultType="Blog">
    select * from blog
    where status="ACTIVE"
	<choose>
		<when test="title != null">
			and title like #{title}
		</when>
		<when test="author != null and author.name != null">
			and author_name like #{author.name}
		</when>
		<otherwise>
			and featured = 1
		</otherwise>
	</choose>
</select>

where
如果where中没有符合的结果,则执行单条select *，防止出现where后没有条件查询失败的情况

<select id="findActiveBlogLike"
	resultType="Blog">
	 select * from blog
	<where>
		<if test="status != null">
			status = #{status}
		</if>
		<if test="title != null">
			and title like #{title}
		</if>
		<if test="author != null and author.name != null">
			and author_name like #{author.name}
		</if>
	</where>
</select>

set

<update id="updateAuthorIfNecessary">
	update author
	<set>
		<if test="username != null">username=#{username},</if>
		<if test="password != null">password=#{password},</if>
		<if test="email != null">email=#{email},</if>
		<if test="bio != null">bio=#{bio}</if> 
	</set>
	where id = #{id}
</update>

foreach

<select id="selectPostIn" resultType="domain.blog.Post">
	select *
	from post p
	where id in
	<foreach item="item" index="index" collection="list"
		open="(" separator="," close=")"> <!--指定开闭匹配的字符串以及在迭代中间放置分隔符-->
		#{item}
	</foreach>
</select>