Mybatis多表关联查询与动态SQL（下）-阿里云开发者社区

Mybatis多表关联查询与动态SQL（上）：https://developer.aliyun.com/article/1509367

2.0、MySQL环境与前置要求

数据与SQL环境如下：

2.1、什么是动态SQL

MyBatis的动态SQL是基于OGNL的表达式的。它对SQL语句进行灵活的操作，通过表达式判断来实现对SQL的灵活拼接、组装。

mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。

主要通过以下标签：if，where，choose（when，otherwise），trim，set，foreach。

2.2、if条件判断

根据 name和 sex 来查询数据。如果name为空，那么将只根据sex来查询；反之只根据name来查询

首先不使用动态SQL 来书写

接口：

    /**
     * 根据学生姓名和性别获得学生集合
     */
    List<Student> selectStudentsByNameAndSex(@Param("name") String name,@Param("sex") String sex);
映射：

    <select id="selectStudentsByNameAndSex" resultType="student">
        SELECT id,name,sex from student where name=#{name} and sex=#{sex};
    </select>

测试：

/**

Method: selectStudentsByNameAndSex
*/

@Test

public void testSelectStudentsByNameAndSex() throws Exception {

List students=dao.selectStudentsByNameAndSex(“rose”,null);

System.out.println(students);

Assert.assertNotNull(students);

}

上面的查询语句，我们发现如果 #{sex} 为空，那么查询结果也是空，如何解决这个问题呢？使用 if 来判断

<select id="selectStudentsByNameAndSex" resultType="student">

    SELECT id,name,sex from student where 1=1
    <!--如果test为真会输出中间的内容-->
    <if test="name!=null and name!=''">
        and name=#{name}
    </if>

    <if test="sex!=null and sex!=''">
        and sex=#{sex}
    </if>

</select>



<!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 -->
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
 SELECT ST.STUDENT_ID,
 ST.STUDENT_NAME,
 ST.STUDENT_SEX,
 ST.STUDENT_BIRTHDAY,
 ST.STUDENT_PHOTO,
 ST.CLASS_ID,
 ST.PLACE_ID
 FROM STUDENT_TBL ST
 WHERE
 <if test="studentName !=null ">
 ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
 </if>
 <if test="studentSex != null and studentSex != '' ">
 AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
 </if>
 <if test="studentBirthday != null ">
 AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
 </if>
 <if test="classId != null and classId!= '' ">
 AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
 </if>
 <if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
 AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
 </if>
 <if test="placeId != null and placeId != '' ">
 AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
 </if>
 <if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
 AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
 </if>
 <if test="studentId != null and studentId != '' ">
 AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
 </if>
</select> 
 虽然1=1这种方法结合if可以解决我们的需求，但是1=1明显是冗余的，通过where可以解决。

2.3、where条件

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

修改后的映射：

SELECT id,name,sex from student

<!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
<where>
    <if test="name!=null and name!=''">
        <!--2、如果where后以and或or开始则会删除and或or-->
        and name like concat(concat('%',#{name}),'%');
    </if>

    <if test="sex!=null and sex!=''">
        and sex=#{sex}
    </if>
</where>

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

where标记的作用类似于动态sql中的set标记，他的作用主要是用来简化sql语句中where条件判断的书写的，如下所示：

　　　　select * from user

　　　　　　id=#{id}

　　　　　　and name=#{name}

　　　　　　and gender = #{gender}

where 标记会自动将其后第一个条件的and或者是or给忽略掉

2.4、if+set设置值

当update语句中没有使用if标签时，如果有一个参数为null，都会导致错误。

当在update语句中使用if标签时，如果前面的if没有执行，则或导致逗号多余错误。使用set标签可以将动态的配置SET 关键字，和剔除追加到条件末尾的任何不相关的逗号。如果set包含的内容为空的话则会出错。

使用if+set标签修改后，如果某项为null则不进行更新，而是保持数据库原值。

如果通过if判断表面可以解决问题，如下所示：

update student set

<if test="name!=null and name.lenght()>0">
    name=#{name} ,
</if>

<if test="sex!=null and sex.lenght()>0">
    sex=#{sex}
</if>

where id=#{id}

这样做也会有问题，就是当sex为空时的sql就变成了 update student set name=#{name} , where id=#{id}，这明显是错误的。

同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，我们怎么处理呢？

接口：

/**
 * 更新学生
 */
int updateStudent(Student entity);

映射：

update student name=#{name}设置标签

    <if test="sex!=null and sex.length()>0">
        sex=#{sex}
    </if>
</set>
where id=#{id}

注意：某些情况下逗号必须添加，如下所示：

update student name=#{name} ,

    <if test="sex!=null and sex.length()>0">
        sex=#{sex} ,
    </if>
</set>
where id=#{id}

这样写，如果第一个条件 name 为空，那么 sql 语句为：update student set sex=? where id=?

如果第一个条件不为空，那么 sql 语句为：update student u set name= ? , sex = ? where id=?

set主要解决了自动添加标签与处理逗号的问题，另外这种更新方法比较以前的全部更新方式在开发中性能更高。

2.5、choose(when,otherwise) 开关

如果不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。

假定这里需要优先根据编号搜索，没有时选择name，最后考虑sex：

接口：

/**
 * 根据学生编号、姓名和性别获得学生集合
 */
List<Student> selectStudentsByNameAndSex(@Param("id") int id, @Param("name") String name,@Param("sex") String sex);

映射：

SELECT id,name,sex from student
<where>
<choose>
    <when test="id>0">
        id=#{id}
    </when>
    <when test="name!=null and name!=''">
        name=#{name}
    </when>
    <otherwise>
        sex=#{sex}
    </otherwise>
</choose>
</where>

也就是说，这里我们有三个条件，id,name,sex，只能选择一个作为查询条件

如果 id 不为空，那么查询语句为：select * from student where id=?

如果 id 为空，那么看name是否为空，如果不为空，那么语句为 select * from student where name=?;

如果name为空，那么查询语句为 select * from student where sex=?

2.6、trim裁剪

trim标记是一个格式化的标记，可以完成set或者是where标记的功能

①、用 trim 改写上面第二点的 if+where 语句

if+where的办法：

  <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、如果两个if只要有一个有输出就会在sql中添加 where-->
        <where>
            <if test="name!=null and name!=''">
                <!--2、如果where后以and或or开始则会删除and或or-->
                and name like concat(concat('%',#{name}),'%');
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>

    </select>

trim的办法：

 <select id="selectStudentsByNameAndSex" resultType="student">

        SELECT id,name,sex from student

        <!--1、prefix表示将前置where，prefixOverrides将删除打头内容-->
        <trim prefix="where" prefixOverrides="and | or">
            <if test="name!=null and name!=''">
                and name like concat(concat('%',#{name}),'%')
            </if>

            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>

    </select>

prefix：将加上前缀

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

②、用 trim 改写上面第三点的 if+set 语句

if+set的方法：

  <update id="updateStudent" parameterType="student">
        update student
        <!--自动添加set-->
        <set>
            <!--智能处理逗号问题-->
            <if test="name!=null and name.length()>0">
                name=#{name}
            </if>

            <if test="sex!=null and sex.length()>0">
                sex=#{sex}
            </if>
        </set>
        where id=#{id}
    </update>

trim的方法：

update student

   <trim prefix="set" suffixOverrides=",">
       <if test="name!=null and name.length()>0">
           name=#{name},
       </if>

       <if test="sex!=null and sex.length()>0">
           sex=#{sex},
       </if>
   </trim>

where id=#{id}

suffix：后缀

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

可以自定义添加前后缀，与之对应的属性是prefix和suffix。同时通过prefixOverrides和suffixOverrides分别来覆盖首尾部的内容，即忽略不必要的前后缀。就是说它可以充当where标签，也可以充当set标签啦~

Mybatis多表关联查询与动态SQL（下）

2.0、MySQL环境与前置要求

热门文章

最新文章

相关课程

相关电子书

相关实验场景