Mybatis多表关联查询与动态SQL(下)

简介: 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标签啦~


相关文章
|
1天前
|
SQL 存储 程序员
SQL查询的一些基本知识和学习指导
【6月更文挑战第17天】SQL查询核心包括基础选择、连接(JOIN)、子查询、聚合函数与GROUP BY、模糊匹配(LIKE)、分页与排序。JOIN操作连接多表,GROUP BY配合聚合函数做统计,LIKE用于模糊搜索。理解存储过程、触发器及自动增长列等进阶概念,通过实践提升SQL技能。
22 2
|
2天前
|
SQL 人工智能 Java
mybatis-plus配置sql拦截器实现完整sql打印
_shigen_ 博主分享了如何在MyBatis-Plus中打印完整SQL,包括更新和查询操作。默认日志打印的SQL用?代替参数,但通过自定义`SqlInterceptor`可以显示详细信息。代码示例展示了拦截器如何替换?以显示实际参数,并计算执行时间。配置中添加拦截器以启用此功能。文章提到了分页查询时的限制,以及对AI在编程辅助方面的思考。
31 5
mybatis-plus配置sql拦截器实现完整sql打印
|
5天前
|
SQL Java 数据库连接
MyBatis SQL 批量更新(代码➕案例)
MyBatis SQL 批量更新(代码➕案例)
11 0
|
5天前
|
SQL
SQL查询
SQL查询
11 0
|
5天前
|
SQL XML Java
MyBatis动态SQL------------------choose用法
MyBatis动态SQL------------------choose用法
14 1
|
7天前
|
SQL 存储 安全
SQL入门与进阶:数据库查询与管理的实用指南
一、引言 在数字化时代,数据库已经成为各行各业存储、管理和分析数据的关键基础设施
|
8天前
|
SQL 安全 数据库
SQL实践指南:从基础到进阶的数据库查询与管理
一、引言 在数据驱动的时代,数据库已成为各行各业不可或缺的一部分
|
8天前
|
SQL 安全 数据库
掌握SQL:数据库操作与查询的精髓
一、引言 在数据驱动的时代,数据库是任何组织或项目不可或缺的一部分
|
8天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
101 1
|
1月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
156 1