mybatis动态sql语句学习

动态 SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

if
choose (when, otherwise)
trim (where, set)
foreach

if 元素

有条件的执行where语句，if 元素是与(and)的关系。

<select id="selectStudentInfoFive" parameterType="map" resultType="com.springboot02.dao.entity.StudentInfoEntity">
    SELECT * FROM student_t
    WHERE state = 1 
    <if test="id != null">
        AND _id = #{id,jdbcType=VARCHAR}
    </if>
    <if test="name != null">
        AND name = #{name,jdbcType=VARCHAR}
    </if>
</select>

<!--SELECT * FROM student_t WHERE state = 1 AND _id = '1'-->

choose, when, otherwise 元素

类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。choose 是或(or)的关系。

<select id="selectUserInfoTwo" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT * FROM user WHERE status = 1
    <choose>
      <when test="username != null and username != ''">
        AND username = #{username,jdbcType = VARCHAR}
      </when>
      <when test="password != null and password != ''">
       AND password = #{password,jdbcType = VARCHAR}
      </when>
      <otherwise>
        AND power = '1'
      </otherwise>
    </choose>
</select>

<!--SELECT * FROM user WHERE status = 1 AND username = 'nana'-->
<!--SELECT * FROM user WHERE status = 1 AND power = '1'-->

trim, where, set 元素

trim 元素可以定制我们想要的功能，例如set或者是where元素的功能。

where 元素只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的where 元素会将他们去除。

set 元素可以被用于动态包含需要更新的列，去除内容中最后一个“，”。

例子1：trim 元素替换where 元素

<select id="selectUserInfoByPowerTwo" parameterType="java.lang.String" resultMap="BaseResultMap">
  SELECT * FROM user
  <where>
    <if test="power != null and power != ''">
      AND power = #{power,jdbcType = VARCHAR}
    </if>
    <if test="status != null and status != ''">
      AND status = 1
    </if>
  </where>
</select>


<select id="selectUserInfoByPower" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT * FROM user
    <trim prefix=" where" prefixOverrides="AND|OR">
      <if test="power != null and power != ''">
        power = #{power,jdbcType = VARCHAR}
      </if>
      <if test="status != null and status != ''">
        AND status = 1
      </if>
    </trim>
</select>
 <!--select * from user WHERE power = '1'-->
 <!--select * from user WHERE status = 1-->

prefix：前缀　　　　　　

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

例子2：trim 元素替换set 元素

<update id="updateStudentInfoTwo" parameterType="com.springboot02.dao.entity.StudentInfoEntity">
    UPDATE student_t
    <set>
      <if test="name != null and name !=''">
        name=#{name,jdbcType = VARCHAR},
      </if>
      <if test="age != null and age != ''">
        age = #{age,jdbcType = VARCHAR},
      </if>
    </set>
    WHERE _id = #{id,jdbcType = VARCHAR}
</update>
<!--UPDATE student_t SET name='name', age = '12' WHERE _id = 1-->


<update id="updateStudentInfoOne" parameterType="com.springboot02.dao.entity.StudentInfoEntity">
    UPDATE student_t
    <trim prefix="set" suffixOverrides="," suffix="WHERE _id = #{id,jdbcType=VARCHAR}">
      <if test="name != null and name !=''">
        name=#{name,jdbcType = VARCHAR},
      </if>
      <if test="age != null and age != ''">
       age = #{age,jdbcType =VARCHAR},
      </if>
    </trim>
</update>
<!--UPDATE student_t SET name = 'nanana' WHERE _id = 1-->

suffix：后缀　　　　　　

suffixoverride：去掉最后一个，

foreach 元素

foreach 元素主要用于构建in条件，可以在sql中对集合进行迭代。可以在批量删除、添加等操作中使用。

<delete id="deleteStudentInfoOne" parameterType="arraylist">
      DELETE FROM student_t WHERE _id IN
      <foreach collection="array" item="id" index="index" open="(" close=")" separator=",">
          #{id}
      </foreach>
</delete>
<!--DELETE FROM student_t WHERE _id IN (1,2)-->

collection ：collection属性的值有3种：list、array、map，分别对应的参数类型为：List、数组、map集合。

item ：表示迭代元素的别名。

index ：表示当前迭代的次数

open ：前缀

close ：后缀

separator ：分隔符，表示迭代时每个元素之间以什么分隔。

sql 片段

在mybatis中通过使用SQL片段可以提高代码的重用性。

例子1：

<!--创建动态sql-->
<sql id="sql_count">select count(*)</sql>

<!--查找学生表，状态为1的全部学生数量-->
<select id="selectListCount" resultType="java.lang.String">
      <include refid="sql_count"/> FROM student_t where status = 1
</select>

<!--select count(*) FROM student_t where status = 1-->

例子2：

<!--创建动态sql-->
<sql id="sql_student">select * from student_t </sql>
<sql id="sql_where">
        <trim prefix="where" prefixOverrides="AND|OR">
            <if test="id != null">AND _id = #{id}</if>
            <if test="name != null and name.length() > 0">AND name = #{name}</if>
            <if test="age != null and age.length() > 0">AND age = #{age}</if>
        </trim>
</sql>

<!--根据输入条件，查找学生信息-->
<select id="selectStudentInfoThree" parameterType="map" resultMap="BaseResultMap">
     <include refid="sql_student"/>
     <include refid="sql_where"/>
</select>
<!--SELECT * FROM student_t WHERE _id = 1 AND name = 'nana'-->