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>