动态SQL

1、概述

在我们生产开发过程中，用户的需要是不断变化的，如果我们还是像之前的思路去编写sql和相关的代码将出现非常多的sql,且很多类似的【也可能只是查询条件不同】，这样的话效率就变得非常的低。我们希望sql写完之后根据需求不同而执行不同的内容。动态sql就可以做到。

2、建实体类studnet、在mapper下写接口方法

在mapper下写StudentMapper接口方法

 在总配置文件下加

<mapper resource="static/StudentMapper.xml"/>

3、在映射文件里面写sql

① if

<select id="getStudentByAge_if" parameterType="int" resultType="Student">
    select * from tbl_student where 1=1
    <if test="age>0">
        and age = #{age}
    </if>
</select>
1=1：为了凑数用的 本身是没有意义
注意：如果if中写的是age>0 则会出现异常  ：没有get 方法的异常。我们得遵从框架的规范 用_paramete 代替age.为了我们看的方便就想写age>0的话则需要在接口方法中将形参 改为带有注解的方式：@Param
 public List<Student> getStudentByAge_if(@Param("age") int age)

② where

<select id="getStudentByName_where" parameterType="Student" resultType="Student">
    select * from tbl_student
    <where>
        <if test="sname!=null &amp;&amp; sname!=''">
             sname like #{sname}
        </if>
       <if test="score != null &amp;&amp; score !=''">
          and score=#{score}
       </if>
    </where>
</select>

③when

<select id="getStudent_choose" parameterType="map" resultType="Student">
    select * from tbl_student
    <choose>
        <when test="sname != null &amp;&amp; sname != ''">
            where sname = #{sname}
        </when>
        <when test="score != null &amp;&amp; score != ''">
            where score = #{score}
        </when>
        <otherwise>
            where 1=1
        </otherwise>
    </choose>
</select>

④foreach

<select id="getStudent_foreach" parameterType="map" resultType="Student">
    select * from tbl_student
    <if test="ages != null">
        <where>
            age in
            <foreach collection="ages" open="(" separator="," close=")" item="val">
                #{val}
            </foreach>
        </where>
    </if>
</select>

⑤trim

<select id="getStudent_trim" parameterType="map" resultType="Student">
    select * from tbl_student
    <trim prefix="where" prefixOverrides="and|or">
        <if test="sname != null &amp;&amp; sname != ''">
            and sname = #{sname}
        </if>
    </trim>
</select>

⑥修改

<select id="updateStudnet" parameterType="Student">
    update tbl_student
    <set>
        <if test="sname != null &amp;&amp; sname !=''">
            sname = #{sname},
        </if>
        <if test="score != null &amp;&amp; score  !=''">
            score  = #{score},
        </if>
        <if test="birth != null &amp;&amp; birth  !=''">
            birth  = #{birth},
        </if>
        <if test="age != null &amp;&amp; age !=''">
            age = #{age},
        </if>
    </set>
        where id = #{id}
</select>