MyBatis 动态SQL详解

ゝ一世哀愁。 2022-03-25 06:12 178阅读 0赞

** MyBatis的优势**

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

**二、基本参数释义**

1.Mapper.xml之insert

<insert id="save" parameterType="User" keyColumn="id" keyProperty="id" useGeneratedKeys="true" >
        INSERT INTO tb_user (user_name,password,name,age,sex) VALUES (#{userName},#{password},#{name},#{age},#{sex})
    </insert>

**2.返回结果之Map**

<select id="getList" resultType="User" >
        SELECT id ,user_name , password ,age ,name  from tb_user
    </select>

<resultMap id="basicMap" type="User">
    
        <id column="d_id" property="id" ></id>
        <result column="d_username" property="userName" ></result>
        <result column="d_password" property="password" ></result>
        <result column="d_age" property="age" ></result>
        <result column="d_name" property="name" ></result>
    </resultMap>
    <select id="getList1"  resultMap="basicMap" >
        SELECT id as d_id,user_name as d_username, password as  d_password,age as d_age,name as d_name from tb_user
    </select>

**三、动态SQL**

*  **IF**

<select id="getObjectByCondition" parameterType="com.shenzhenair.mybatis.demo_01.QueryObject" resultType="User">
       SELECT  * FROM tb_user
       <where>
         <if test="keyword != null and keyword != '' ">
            AND user_name LIKE concat('%',#{keyword},'%')
         </if>
         <if test="beginAge != null">
             AND age <![CDATA[>=]]> #{beginAge}
         </if>
         <if test="endAge != null">
             AND age <=  #{endAge}
         </if>
       </where>
    </select>

*  **choose (when, otherwise)**

<select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG WHERE state = ‘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>

*  **trim (where, set)**

<select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG 
      WHERE 
      <if test="state != null">
        state = #{state}
      </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>
    </select>
    
    ELECT * FROM BLOG  WHERE 
    <--where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。-->
    <select id="findActiveBlogLike"
         resultType="Blog">
      SELECT * FROM BLOG 
      <where> 
        <if test="state != null">
             state = #{state}
        </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>

<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>

<trim prefix="WHERE" prefixOverrides="AND |OR ">
      ... 
    </trim>
    <trim prefix="SET" suffixOverrides=",">
      ...
    </trim>
    <update id="updateObjectByCondition" parameterType="com.shenzhenair.mybatis.demo_01.QueryObject">
        UPDATE  tb_user
       
        <trim prefix="set" suffixOverrides=",">
            <if test="keyword != null and keyword != ''">
                user_name = #{keyword},
            </if>
            <if test="beginAge != null">
                age = #{beginAge},
            </if>
        </trim>
        where id = #{id}
    </update>

*  ** foreach**

<select id="getListByIds" parameterType="list" resultType="User">
        SELECT  * FROM  tb_user WHERE  id IN
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

*      CommonSQL抽取
        
        <sql id="commonSql" >
            <where>
                <if test="keyword != null and keyword != '' ">
                    AND user_name LIKE concat('%',#{keyword},'%')
                </if>
                <if test="beginAge != null">
                    AND age <![CDATA[>=]]> #{beginAge}
                </if>
                <if test="endAge != null">
                    AND age <=  #{endAge}
                </if>
            </where>
        </sql>
        
        <select id="getCount" parameterType="com.shenzhenair.mybatis.demo_01.QueryObject" resultType="long">
            SELECT  count(*) FROM tb_user
          <include refid="commonSql"  ></include>
        
        </select>
        
        <select id="getPageResult" parameterType="com.shenzhenair.mybatis.demo_01.QueryObject" resultType="User">
            SELECT  * FROM tb_user
            <include refid="commonSql"  ></include>
            limit #{start}, #{row};
        </select>