随笔- 261 文章- 0 评论- 12 阅读- 45万

Mybatis动态SQL

User表结构如下:

一、动态SQL:if标签、where标签

　　需求:使用username和gender来查询数据,如果username为空,则只根据gender来查询数据;如果gender为空,则只根据username来查询数据

　　如果我们不使用动态SQL来书写SQL语句

<select id="queryUserByUsernameAndGender" resultType="com.mybatis.entity.User">
    SELECT * FROM user WHERE username=#{username} AND gender=#{gender}
</select>

　　1、如果username为空,那么SQL语句就会变成这样,这个样子自然是查询不到数据的

1	`SELECT * FROM user WHERE username='null'` `AND gender='female';`

　　2、如果gender为空,那么SQL语句就会变成这样,这个样子也是查询不到数据的

1	`SELECT * FROM user WHERE username='xiaomao'` `AND gender='null';`

　　那么怎么解决上述的问题呢?这个时候就需要使用if标签,加上了if标签的SQL语句如下

<select id="queryUserByUsernameAndGender" resultType="com.mybatis.entity.User">
    SELECT * FROM user WHERE
    // 如果条件成立则执行if内的SQL语句
    <if test="username != null">
        username=#{username}
    </if>
 
    <if test="gender != null">
        AND gender=#{gender}
    </if>
</select>

　　3、这个时候如果gender为空的情况下,SQL语句就变成了这样,看样子已经符合我们的需求了

1	`SELECT * FROM user WHERE username='小毛';`

　　4、但是,如果gender为空的时候,SQL如下,这个语句明显是错误的,因为多了一个AND关键字

1	`SELECT * FROM user WHERE AND gender='female';`

　　5、那么如何处理这个多出来的AND关键字呢?这个时候我们就需要配合使用另外一个标签,where标签

<select id="queryUserByUsernameAndGender" resultType="com.mybatis.entity.User">
    SELECT * FROM user
    <where>
        <if test="username != null">
            username=#{username}
        </if>
 
        <if test="gender != null">
            AND gender=#{gender}
        </if>
    </where>
</select>

　　6、到现在为止我们就可以满足需求了,即

　　　　username和gender都存在的情况下:以两个条件查询

　　　　username存在,gender不存在的情况下,以username作为条件查询

　　　　username不存在,gender存在的情况下,以gender作为条件查询

　　<if>标签:满足该标签的条件之后才能去拼接标签内的SQL

　　<where>标签:如果where标签的返回结果是以 AND 或者 OR 开头,那么它会帮我们剔除多余的AND 和 OR

二、动态SQL:set标签

　　需求:根据id更新用户的username和gender信息

<update id="updateUserById">
    UPDATE user set
    <if test="username != null">
        username=#{username},
    </if>
    <if test="gender != null">
        gender=#{gender}
    </if>
    <where>
        <if test="id != null">
          id = #{id}
        </if>
    </where>
</update>

　　1、如果username为空,那么SQL将会变成如下的样子,这样子的SQL没有任何问题

1	`UPDATE user set gender=? WHERE id = ?`

　　2、如果gender为空的话,那么SQL就会变成如下的样子,很明显这个SQL是有问题的,因为它在username=?后面多了一个逗号

1	`UPDATE user set username=?, WHERE id = ?`

　　3、要解决上面的问题,我们需要使用<set>标签

<update id="updateUserById">
    UPDATE user
    <set>
        <if test="username != null">
            username=#{username},
        </if>
        <if test="gender != null">
            gender=#{gender}
        </if>
    </set>
    <where>
        <if test="id != null">
          id = #{id}
        </if>
    </where>
</update>

　　<set>标签:如果set标签内的SQL返回值以逗号结尾,那么set标签会剔除多余的逗号.

三、动态SQL:choose(when,otherwise)语句

　　有时候,我们不想用到所有的查询条件,只想选择其中的一个,查询条件有一个满足即可,使用 choose 标签可以解决此类问题,类似于 Java 的 switch 语句

<select id="queryUserByOneCondition" resultType="com.mybatis.entity.User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="username != null">
                username = #{username}
            </when>
            <otherwise>
                address=#{address}
            </otherwise>
        </choose>
    </where>
</select>

　　1、如果id满足<when>标签的条件,那么SQL语句如下

1	`SELECT * FROM user where id=?`

　　2、如果id不满足<when>标签的条件,username满足<when>标签的条件,那么SQL语句如下

1	`SELECT * FROM user where username=?`

　　3、如果id和username都不满足<when>标签的条件,那么SQL语句如下

1	`SELECT * FROM user where address=?`

四、动态SQL:SQL片段

　　有时候可能某个 sql 语句我们用的特别多,为了增加代码的重用性,简化代码,我们需要将这些代码抽取出来,然后使用时通过id直接调用.

// 抽取SQL片段,这里有一个id,在别的SQL语句中可以通过这个id引入SQL片段
<sql id="selectBasic">
    SELECT * FROM user
</sql>
 
<select id="queryUserByOneCondition" resultType="com.mybatis.entity.User">
    // 引入SQL片段,相当于是 SELECT * FROM user
    <include refid="selectBasic"></include>
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="username != null">
                username = #{username}
            </when>
            <otherwise>
                address=#{address}
            </otherwise>
        </choose>
    </where>
</select>

五、动态SQL:trim标签

　　trim标记是一个格式化的标记,可以完成set或者是where标记的功能

　　1、替换where的功能

<select id="queryUserByUsernameAndGender" resultType="com.mybatis.entity.User">
    SELECT * FROM user
    <where>
        <if test="username != null">
            username=#{username}
        </if>
 
        <if test="gender != null">
            AND gender=#{gender}
        </if>
    </where>
</select>
 
<select id="queryUserByUsernameAndGender" resultType="com.mybatis.entity.User">
    SELECT * FROM user
    // prefix:为trim返回的结果前面添加一个where
    // prefixOverrides:剔除trim返回的结果后面的 and 或者是 or
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username != null">
            username=#{username}
        </if>
 
        <if test="gender != null">
            AND gender=#{gender}
        </if>
    </trim>
</select>　

　　prefix：为整个的sql语句添加一个前缀　　　　　　

　　prefixoverride：去掉第一个and或者是or

　　2、替换set功能

<update id="updateUserById">
    UPDATE user
    <set>
        <if test="username != null">
            username=#{username},
        </if>
        <if test="gender != null">
            gender=#{gender}
        </if>
    </set>
    <where>
        <if test="id != null">
            id = #{id}
        </if>
    </where>
</update>
 
<update id="updateUserById">
    UPDATE user
    <trim prefix="set" suffixOverrides=",">
        <if test="username != null">
            username=#{username},
        </if>
        <if test="gender != null">
            gender=#{gender}
        </if>
    </trim>
    <trim prefix="where">
        <if test="id != null">
            id = #{id}
        </if>
    </trim>
</update>

　　suffix：后缀　　

　　suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

六、动态SQL:foreach语句　

　　1、UserMapper接口

// 批量添加,使用@Param注解之后在collection中只要写注解对应的value值就可以了,不需要再去判断是数组,List,Map之类的了.
public abstract Boolean addBatch(@Param("Users") List<User> list);
 
// 批量删除
public abstract Boolean deleteBatch(@Param("ids")Integer[] integers);

　　2、UserMapper.xml

// 批量添加
<insert id="addBatch">
    INSERT INTO user VALUES
    <foreach collection="Users" item="user" separator=",">
        (null,#{user.username},#{user.gender},null,null)
    </foreach>
</insert>
 
// 批量删除
<delete id="deleteBatch">
    DELETE FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>