【Java】MyBatis应用(七)动态SQL

正文索引 [隐藏]

MyBatis动态SQL

使用动态SQL可简化代码的开发,减少工作量,程序可以自动根据业务参数来决定SQL的组成。

  • if标签
  • <select id="findByAccount" parameterType="com.mybatistest.entity.Account" resultType="com.mybatistest.entity.Account">
    select * from t_accout where
    <if test="id != 0">id = #{id}</if>
    <if test="username !=null">and username = #{username}</if>
    <if test="password !=null">and password = #{password}</if>
    <if test="age != 0">and age = #{age}</if>
    </select>

    if标签可以自动根据表达式的结果来决定是否将对应的语句添加到SQL中,如果条件不成功,则不添加,如果条件成立,则添加。

  • where标签
  • <select id="findByAccount" parameterType="com.mybatistest.entity.Account" resultType="com.mybatistest.entity.Account">
    select * from t_accout
    <where>
    <if test="id != 0">id = #{id}</if>
    <if test="username !=null">and username = #{username}</if>
    <if test="password !=null">and password = #{password}</if>
    <if test="age != 0">and age = #{age}</if>
    </where>
    </select>

    where标签可以自动判断是否要删除语句块中的and关键字,如果检测到where直接跟and拼接,则自动删除and,通常清情况下,if和where结合起来使用

  • choose、when标签
  • <select id="findByAccount" parameterType="com.mybatistest.entity.Account" resultType="com.mybatistest.entity.Account">
    select * from t_accout
    <where>
    <choose>
    <when test="id != 0">
    id = #{id}
    </when>
    <when test="username != null">
    username = #{username}
    </when>
    <when test="password !=null">
    password = #{password}
    </when>
    <when test="age != 0">
    age = #{age}
    </when>
    </choose>
    </where>
    </select>
  • trim标签
  • trim标签中的prefix和suffix属性会被用于生成实际的SQL语句,会和标签内部的语句进行拼接,如果语句前后出现了prefixOverrides或者suffixOverrides属性中指定值,Mybatis框架会自动将其删除

    <select id="findByAccount" parameterType="com.mybatistest.entity.Account" resultType="com.mybatistest.entity.Account">
    select * from t_accout
    <trim prefix="where" prefixOverrides="and">
    <if test="id != 0">id = #{id}</if>
    <if test="username !=null">and username = #{username}</if>
    <if test="password !=null">and password = #{password}</if>
    <if test="age != 0">and age = #{age}</if>
    </trim>
    </select>

  • set标签
  • set标签用于update操作,会自动根据参数选择生成SQL语句

    <update id="update" parameterType="com.mybatistest.entity.Account">
    update t_accout
    <set>
    <if test="username != null">
    username = #{username},
    </if>
    <if test="password != null">
    password = #{password},
    </if>
    <if test="age != 0">
    age = #{age}
    </if>
    </set>
    where id = #{id}
    </update>

  • foreach标签
  • foreach标签可以迭代生成一系列的值,这个标签主要用于SQL的in语句

    <select id="findByIds" parameterType="com.mybatistest.entity.Account" resultType="com.mybatistest.entity.Account">
    SELECT * FROM t_accout
    <where>
    <foreach collection="ids" open="id in(" close=")" item="id" separator=",">
    #{id}
    </foreach>
    </where>
    </select>
blank