Mybatis关联查询

作者: 轻轻敲醒沉睡的心灵 | 来源:发表于2020-09-14 14:02 被阅读0次

MyBatis 最佳实践篇 3：关联查询懒加载
关于关联查询的处理
mybatis进阶2——关联查询
Mybatis关联查询
Mybatis | 关联查询
MyBatis关联查询
MyBatis学习：MyBatis中的延迟加载
InvalidDefinitionException: No s
mybatis笔记整理
MyBatis的关联查询

1. mysql中的LEFT JOIN

使用left join是以左表为主，即使右表没有数据，也会出来左表的所有数据
若不用left join，直接 select * from order a, order_detail b where a.id = b.order_id，若表中没有订单详情，则查询结果中没有这个订单
使用left join注意 on 后面的 and 和 where 后面的 and 控制的范围

2. A表多个字段都和B表关联

场景：A表业务表，B表字典表，A中多个字段和字典表关联，查询A表时需要将多个字段替换为B字典表中的值
解决方案：多个LEFT JOIN连续使用，且右表都为同一个表（字典表），只不过关联字段换了

SELECT
    r.*,
    d1.`name` carPrivateFkString,
    d2.`name` cardColorFkString,
    d3.`name` cardTypeFkString,
    d4.`name` userLevelFkString,
    d5.`name` carCardTypeFkString 
FROM
    t_car_registration AS r
    LEFT JOIN (
    SELECT
        d.*,
        t.`name` typeName,
        t.`value` typeValue 
    FROM
        d_dict AS d
        LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id 
    ) AS d1 ON r.car_private_fk = d1.`value` 
    AND d1.typeValue = 'car_private'
    LEFT JOIN (
    SELECT
        d.*,
        t.`name` typeName,
        t.`value` typeValue 
    FROM
        d_dict AS d
        LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id 
    ) AS d2 ON r.card_color_fk = d2.`value` 
    AND d2.typeValue = 'card_color'
    LEFT JOIN (
    SELECT
        d.*,
        t.`name` typeName,
        t.`value` typeValue 
    FROM
        d_dict AS d
        LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id 
    ) AS d3 ON r.card_type_fk = d3.`value` 
    AND d3.typeValue = 'card_type'
    LEFT JOIN (
    SELECT
        d.*,
        t.`name` typeName,
        t.`value` typeValue 
    FROM
        d_dict AS d
        LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id 
    ) AS d4 ON r.user_level_fk = d4.`value` 
    AND d4.typeValue = 'user_level'
    LEFT JOIN (
    SELECT
        d.*,
        t.`name` typeName,
        t.`value` typeValue 
    FROM
        d_dict AS d
        LEFT JOIN d_dict_type AS t ON d.fk_d_dict_type_id = t.pk_id 
    ) AS d5 ON r.car_card_type_fk = d5.`value` 
    AND d5.typeValue = 'car_card_type' 
WHERE
    r.is_delete = 0

3. mybatis中使用结果集ResultMap关联查询

与hibernate 中用one to one和one to many不同，mybatis 中使用association和collection。
association: 一对一关联(has one)
collection:一对多关联(has many)

4. association标签

以员工和部门为例，

4.1 association一步查询

<resultMap id="myMap01" type="com.test.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <result column="d_id" property="did"/>
        <!--通过association级联数据库表-->
        <association property="dept" javaType="com.test.Department">
            <result column="did" property="did"/>
            <result column="dname" property="dname"/>
        </association>
    </resultMap>


<select id="getDeptByIdPlus"   resultMap="myMap01">
        SELECT d.did did,d.dname dname,e.id eid,e.last_name lastname,e.email email,e.gender gender
        FROM t_dept d LEFT JOIN t_employee e
        ON d.id=e.d_id
        WHERE d.id=#{id}
</select>

4.2 association分步查询

<resultMap id="myEmpMap" type="com.test.Employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <result column="did" property="did"/>
        <!-- 使用association进行分步查询
            1、先按照员工id查询员工信息
            2、根据查询员工信息中的d_id值去部门表查出部门信息
            3、部门设置到员工中；
           association定义关联对象的封装规则
           select：表明当前属性是调用select指定的方法查出的结果
           column：指定将哪一列的值传给这个方法(数据库列名不是封装数据的属性名)
        -->
        <association property="dept" select="com.test.dao.EmployeeDeptMapper.getDeptById" column="did">
        </association>
    </resultMap>

<select id="getEmpById"   resultMap="myEmpMap">
        SELECT e.id eid,e.last_name lastname,e.email email,e.gender gender
        FROM t_employee e
        WHERE e.id=#{id}
</select>

<resultMap id="myDeptMap" type="com.test.Department">
        <id column="did" property="did"/>
        <result column="dname" property="dname"/>
 </resultMap>

<!--  Department  getDeptByDid(@param("did")String did);      -->
<!-- 注意此处接收的参数要和association 中 column一样  -->
<select id="getDeptByDid"   resultMap="myDeptMap">
        SELECT d.did did,d.dname dname
        FROM t_dept d
        WHERE d.did=#{did}
</select>

5. 一对多关联查询，B表结果集作为A表的一个List字段

5.1 ResultMap中嵌套Collection直接查询

<resultMap id="BaseResultMap" type="com.test.entity.TemporaryReportCarRegistration">
        <id column="pk_id" property="pkId" />
        <result column="source_id" property="sourceId" />
        <result column="source_time" property="sourceTime" />
        <result column="create_date" property="createDate" />
        <result column="system_code" property="systemCode" />
        <result column="car_no" property="carNo" />
        <result column="entry_door" property="entryDoor" />
        <result column="earliest_entry_time" property="earliestEntryTime" />
        <result column="latest_entry_time" property="latestEntryTime" />
        <result column="visit_matter" property="visitMatter" />
        <result column="interviewee_name" property="intervieweeName" />
        <result column="interviewee_company" property="intervieweeCompany" />
        <result column="interviewee_department" property="intervieweeDepartment" />
        <result column="interviewee_mobile" property="intervieweeMobile" />
        <result column="auditor_history" property="auditorHistory" />
        <result column="car_check_time" property="carCheckTime" />
        <result column="car_check_result" property="carCheckResult" />
        <result column="car_check_result_note" property="carCheckResultNote" />
        <result column="passengers" property="passengers" />
        <result column="report_entry_time" property="reportEntryTime" />
        <result column="status" property="status" />
        <result column="authorizer" property="authorizer" />
        <result column="authorize_time" property="authorizeTime" />
        <result column="reject_reson" property="rejectReson" />
        <result column="is_delete" property="isDelete" />
        <result column="remark" property="remark" />
        <result column="created_time" property="createdTime" />
        <result column="created_by" property="createdBy" />
        <result column="created_org" property="createdOrg" />
        <result column="update_time" property="updateTime" />
        <result column="update_by" property="updateBy" />
        <result column="update_org" property="updateOrg" />
        <collection property="visitors" ofType="com.test.entity.TemporaryRegistrationPersonInfo">
            <result column="visitor_name" property="visitorName" />
            <result column="visitor_card_no" property="visitorCardNo" />
            <result column="visitor_company" property="visitorCompany" />
            <result column="visitor_health_state" property="visitorHealthState" />
            <result column="visitor_mobile" property="visitorMobile" />
            <result column="visitor_portrait" property="visitorPortrait" />
            <result column="visitor_type" property="visitorType" />
            <result column="photo_on" property="photoOn" />
            <result column="photo_down" property="photoDown" />
            <result column="check_time" property="checkTime" />
            <result column="check_result" property="checkResult" />
           <result column="created_time" property="createdTime" />
         </collection>
    </resultMap>

    <select id="listReportCar" resultMap="BaseResultMap">
        select 
            a.*,
            b.visitor_name,
            b.visitor_card_no, 
            b.visitor_company,
            b.visitor_health_state,
            b.visitor_mobile,
            b.visitor_portrait,
            b.visitor_type,
            b.photo_on,
            b.photo_down,
            b.check_time,
            b.check_result
        From
            t_temporary_report_car_registration a 
            left join
            t_temporary_registration_person_info b 
            on 
            a.pk_id = b.temporary_registration_id_fk 
        where 
            a.is_delete = 0 
    </select>

5.2 ResultMap中嵌套Collection分步查询

<resultMap id="BaseResultMap" type="com.test.entity.TemporaryReportCarRegistration">
        <id column="pk_id" property="pkId" />
        <result column="source_id" property="sourceId" />
        <result column="source_time" property="sourceTime" />
        <result column="create_date" property="createDate" />
        <result column="system_code" property="systemCode" />
        <result column="car_no" property="carNo" />
        <result column="entry_door" property="entryDoor" />
        <result column="earliest_entry_time" property="earliestEntryTime" />
        <result column="latest_entry_time" property="latestEntryTime" />
        <result column="visit_matter" property="visitMatter" />
        <result column="interviewee_name" property="intervieweeName" />
        <result column="interviewee_company" property="intervieweeCompany" />
        <result column="interviewee_department" property="intervieweeDepartment" />
        <result column="interviewee_mobile" property="intervieweeMobile" />
        <result column="auditor_history" property="auditorHistory" />
        <result column="car_check_time" property="carCheckTime" />
        <result column="car_check_result" property="carCheckResult" />
        <result column="car_check_result_note" property="carCheckResultNote" />
        <result column="passengers" property="passengers" />
        <result column="report_entry_time" property="reportEntryTime" />
        <result column="status" property="status" />
        <result column="authorizer" property="authorizer" />
        <result column="authorize_time" property="authorizeTime" />
        <result column="reject_reson" property="rejectReson" />
        <result column="is_delete" property="isDelete" />
        <result column="remark" property="remark" />
        <result column="created_time" property="createdTime" />
        <result column="created_by" property="createdBy" />
        <result column="created_org" property="createdOrg" />
        <result column="update_time" property="updateTime" />
        <result column="update_by" property="updateBy" />
        <result column="update_org" property="updateOrg" />
        <collection property="visitors" 
                select="com.test.dao.TemporaryRegistrationPersonInfoDao.getPersonInfoByRegistrationId"
                    column="pk_id">
        </collection>
    </resultMap>

  <select id="listReportCar" resultMap="BaseResultMap">
      select 
         a.*
      From
        t_temporary_report_car_registration a 
      where 
        a.is_delete = 0 
   </select>

** TemporaryRegistrationPersonInfoMapper **

<resultMap id="BaseResultMap1" type="com.test.entity.TemporaryRegistrationPersonInfo">
        <id column="pk_id" property="pkId" />
        <result column="temporary_registration_id_fk" property="temporaryRegistrationIdFk" />
        <result column="visitor_name" property="visitorName" />
        <result column="visitor_card_no" property="visitorCardNo" />
        <result column="visitor_company" property="visitorCompany" />
        <result column="visitor_health_state" property="visitorHealthState" />
        <result column="visitor_mobile" property="visitorMobile" />
        <result column="visitor_portrait" property="visitorPortrait" />
        <result column="visitor_type" property="visitorType" />
        <result column="photo_on" property="photoOn" />
        <result column="photo_down" property="photoDown" />
        <result column="check_time" property="checkTime" />
        <result column="check_result" property="checkResult" />
        <result column="created_time" property="createdTime" />
    </resultMap>

<!-- List<TemporaryRegistrationPersonInfo> getPersonInfoByRegistrationId(String registrationId);  -->
<select id="getPersonInfoByRegistrationId" resultType="BaseResultMap1">
        select * 
        from 
        t_temporary_registration_person_info 
        where temporary_registration_id_fk=#{registrationId}
    </select>

MyBatis 最佳实践篇 3：关联查询懒加载
MyBatis 中联合查询可分为关联查询和关联结果两种方式（具体查看 Mybatis 文档篇 3.5：Mapper...
关于关联查询的处理
mybatis关联查询配置有2种：关联嵌套结果，关联嵌套查询关联的查询过程中如果关联的表可能查到很多数据，应该考虑...
mybatis进阶2——关联查询
关联查询代码参考mybatis-demo测试代码AssociationQueryTest.java 0.关联查询的...
Mybatis关联查询
1. mysql中的LEFT JOIN 使用left join是以左表为主，即使右表没有数据，也会出来左表的所有数...
Mybatis | 关联查询
使用Mybatis进行关联查询，之前发在我的CSDN博客中，现在搬到简书上来。数据库关系图如图是一个博客系统的...
MyBatis关联查询
【目录】1 一对一2 一对多在项目开始之前准备好数据库。数据库database： 1 一对一相关代码：主配置文...
MyBatis学习：MyBatis中的延迟加载
MyBatis中的延迟加载。 1. 什么是延迟加载例子：如果查询订单并且关联查询用户信息。如果先查询订单信息...
InvalidDefinitionException: No s
在使用springboot + mybatis实现多表关联查询时报以下错误:com.fasterxml.jacks...
mybatis笔记整理
mybatis的基本用法及配置: 本文涉及知识点: 1、mybatis入门2、配置版CRUD3、关联查询(1:1&...
MyBatis的关联查询
创建数据库和表两个表的关系： user表的主键id和orders表的外键user_id连接，从user表的角度...