JavaEE_08_Mybatis动态SQL

《Mybatis动态SQL》

目录

• if（熟练）

• choose（了解）

• set（掌握）

• trim（熟练）

• foreach（掌握）

一、if

1. com.hpr.mapper.TeacherMapper

...
public interface TeacherMapper {
    ...
    List<Teacher> testIf(Teacher teacher);
}

2. mappers/TeacherMapper.xml

<!--
    if标签：条件满足则拼接该段sql。
    where标签：防止内部条件全不满足多出where关键字，或部分满足时过滤第一个and/or关键字。
-->
<select id="testIf" resultMap="BaseResultMap">
    select * from teacher
    <where>
        <if test="teacherName!=null and teacherName.trim != ''">
            teacher_name like "%${teacherName}%"
        </if>
        <if test="phoneNumber!=null and phoneNumber.trim != ''">
            and phone_number like "%${phoneNumber}%"
        </if>
        <if test="info!=null and info.trim != ''">
            and info like "%${info}%"
        </if>
    </where>
</select>

二、choose

1. com.hpr.mapper.TeacherMapper

...
public interface TeacherMapper {
    ...
    List<Teacher> testChoose(Teacher teacher);
}

2. mappers/TeacherMapper.xml

<!--
    choose...when...otherwise标签：类似switch...case...default语句，只能进入其中一条分支。
-->
<select id="testChoose" resultMap="BaseResultMap">
    select * from teacher where
    <choose>
        <when test="teacherName!=null and teacherName.trim != ''">
            teacher_name like "%${teacherName}%"
        </when>
        <when test="phoneNumber!=null and phoneNumber.trim != ''">
            phone_number like "%${teacherName}%"
        </when>
        <when test="info!=null and info.trim != ''">
            info like "%${info}%"
        </when>
        <otherwise>
            age>20
        </otherwise>
    </choose>
</select>

三、set

1. com.hpr.mapper.TeacherMapper

...
public interface TeacherMapper {
    ...
    int testSet(Teacher teacher);
}

2. mappers/TeacherMapper.xml

<!--
    set标签：用于update语句中部分字段修改，过滤最后一个“,”
-->
<update id="testSet">
    update teacher
    <set>
        <if test="teacherName!=null and teacherName.trim != ''">
            teacher_name=#{teacherName},
        </if>
        <if test="phoneNumber!=null and phoneNumber.trim != ''">
            phone_number=#${teacherName},
        </if>
        <if test="info!=null and info.trim != ''">
            info=#{info},
        </if>
        <if test="age!=0">
            age=#{age}
        </if>
    </set>
    where teacher_id=#{teacherId}
</update>

四、trim

1. com.hpr.mapper.TeacherMapper

...
public interface TeacherMapper {
    ...
    List<Teacher> testTrim(Teacher teacher);
}

2. mappers/TeacherMapper.xml

<!--
    trim标签：可代替where标签跟set标签。
        prefix：前缀；
        prefixOverrides：内部语句前缀消除；
        suffix：后缀；
        suffixOverrides：内部语句后缀消除。
 -->
<select id="testTrim" resultMap="BaseResultMap">
    select * from teacher
    <trim prefix="where" prefixOverrides="and|or">
        <if test="teacherName!=null and teacherName.trim != ''">
            teacher_name like "%${teacherName}%"
        </if>
        <if test="phoneNumber!=null and phoneNumber.trim != ''">
            and phone_number like "%${teacherName}%"
        </if>
        <if test="info!=null and info.trim != ''">
            and info like "%${info}%"
        </if>
    </trim>
</select>

五、foreach

1. com.hpr.mapper.TeacherMapper

...
public interface TeacherMapper {
    ...
    List<Teacher> testForeach(List<Integer> idList);
}

2. mappers/TeacherMapper.xml

<!--
    foreach标签：用于遍历集合，
    如in查询：select * from teacher where teacher_id in (101,102,103)
-->
<select id="testForeach" resultMap="BaseResultMap">
    select * from teacher where teacher_id in
    <foreach collection="idList" item="tid" open="(" close=")" separator=",">
        #{tid}
    </foreach>
</select>

总结

重点

1. 动态sql的作用；
2. 动态sql常用标签。

难点

1. 动态sql标签的使用。