Mybatis动态sql

来源：互联网发布：好吃的零食知乎编辑：程序博客网时间：2024/06/02 02:13

动态SQL
MyBatis的动态SQL，解决了SQL字符串拼接的痛苦。

1.if

<select id="findActiveBlogWithTitleLike"parameterType="Blog" resultType="Blog">SELECT * FROM BLOGWHERE state = 'ACTIVE'<if test="title != null">AND title like #{title}</if></select>

<span style="font-family: Arial; font-size: 14px; line-height: 26px;">这条语句会提供一个可选的文本查找功能。如果没有传递title，那么所有激活的博客都会被返回。</span><br style="font-family: Arial; font-size: 14px; line-height: 26px;" /><span style="font-family: Arial; font-size: 14px; line-height: 26px;">如果传递了title，那么就会查找相近的title。</span><br style="font-family: Arial; font-size: 14px; line-height: 26px;" />

<span style="font-family: Arial; font-size: 14px; line-height: 26px;">.choose，when，otherwise</span><br style="font-family: Arial; font-size: 14px; line-height: 26px;" /><pre name="code" class="html" style="white-space: pre-wrap; word-wrap: break-word; font-size: 14px; line-height: 26px; background-color: rgb(255, 255, 255);">

<select id="findActiveBlogLike"parameterType="BLOG" resultType="BLOG">SELECT * FROM BLOGWHERE<trim prefix="WHERE" prefixOverrides="AND |OR "><choose><when test="title != null">AND title like #{title}</when><when test="author != null and author.name != null">AND title like #{author.name}</when><otherwise>AND featured = 1</otherwise></choose></trim></select>

3.set

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

优化后：

<update id="updateAuthorIfNecessary"parameterType="Author">update Author<trim prefix="where" prefixOverrides=","> <set><if test="username != null">username=#{username},</if><if test="password != null">password=#{password},</if><if test="email != null">email=#{email}</if></set>where id=#{id}</trim></update>

0 0