mybatis resultMap之collection嵌套查询

一直有使用mybatis，这几天有遇到一个需求就是查询一个流程信息里面带的有附件名集合，

public class BusinessReimbursementVO {
    private int id;
    private int workFlowId;
    private String reimbursementCode;
    private int applyUserId;
    private String applyUser;
    private String applyDate;
    private String department;
    private String useage;
    private double amount;
    private String status;
    private List<String> attachments;
}

本想在Java中先把流程数据全部查询出来再for循环查询每一个附件列表，这样感觉有点太low，而且代码感觉也不好维护，就想看看使用resultmap实现嵌套查询，以前使用过ibatis有用过，就像看看mybatis因该也有差不多一样的东西，在网上找了好久，都说使用 resultMap 的collection实现，可是找了半天也没有一个帖子把 collection的column说清楚，代码测试也没有查询到。皇天不负有心人还是让我找到了一个帖子怎么讲的。并且写完代码一跑结果是对的，万分开心。下面是贴的代码

流程数据查询接口：

@Mapper
public interface BusinessReimbursementMapper extends BaseMapper<BusinessReimbursement> {


    /**
     * 查询
     * @param page
     * @param param
     * @return
     */
    IPage<BusinessReimbursementVO> queryBusinessReimbursement(Page<BusinessReimbursementVO> page, @Param("param") ReimbursementQueryParam param);

}

xml：

<resultMap id="businessResult" type="BusinessReimbursementVO">
        <id property="id" column="id"></id>
        <result property="workFlowId" column="work_flow_id"></result>
        <result property="reimbursementCode" column="reimbursement_code"></result>
        <result property="applyUserId" column="apply_user_id"></result>
        <result property="applyUser" column="user_name"></result>
        <result property="applyDate" column="apply_date"></result>
        <result property="department" column="department"></result>
        <result property="useage" column="useage"></result>
        <result property="amount" column="amount"></result>
        <result property="status" column="status"></result>
        <collection property="attachments"
                    select="com.desire.application.modules.workflow.mapper.WorkFlowAttachmentMapper.queryWorkFlowAttachments"
                    javaType="ArrayList"
                    ofType="String"
                    column="{instanceId=id,workFlowId=work_flow_id}"></collection>
    </resultMap>


    <select id="queryBusinessReimbursement" resultMap="businessResult">
        SELECT
            w.id,
            w.work_flow_id,
            w.reimbursement_code,
            w.apply_user_id,
            w.apply_date,
            w.department,
            w.useage,
            w.amount,
            w.status,
            u.name user_name
        FROM
            business_reimbursement_work_flow w,
            user u
        WHERE
            w.apply_user_id = u.id AND
            u.id = #{param.applyUserId} AND
            w.apply_user_id = #{param.applyUserId}
            <if test="param.applyDate != null and param.applyDate != ''">
                AND w.apply_date = param.applyDate
            </if>
        order by w.apply_date desc
    </select>

附件查询的接口：

public interface WorkFlowAttachmentMapper extends BaseMapper<WorkFlowAttachment> {

    /**
     * 查询Id
     * @param instanceId
     *          流程实例id
     * @param workFlowId
     *          流程类型id
     * @return
     */
    List<String> queryWorkFlowAttachments(@Param("instanceId") int instanceId, @Param("workFlowId") int workFlowId);

}

xml：

<select id="queryWorkFlowAttachments" resultType="String">
        select
            attachment
        from
            work_flow_instance_attachment
        where
            flow_instance_id = #{instanceId} and
            flow_id = #{workFlowId}
        order by attachment
    </select>

关键在于 collection元素的column属性上，column="{instanceId=id,workFlowId=work_flow_id}"

instanceId和workflowId分别是传给嵌套查询的键，他们的值则是传给嵌套查询的值的字段

键名必须跟嵌套查询的参数名一致

代码运行结果

[BusinessReimbursementVO(id=1, workFlowId=2, reimbursementCode=YWBX-2022-001, applyUserId=1, applyUser=浩哥, applyDate=2022-08-30, department=技术部, useage=出去看技术, amount=3254.0, status=-1, attachments=[ewsgfwe.png, szfsaf.jpg, xjsdfjsdfop.pdf])]

 

第一次写博客，语言组织能力有限，请看到的读者多多包涵。

以后自己在开发中遇到什么问题，解决之后都会在博客中写出来，就当是个记录和总结，本想弄个文档自己记录一下，后来想想我自己也经常再网上查找一些东西，万一自己遇到的问题以及解决办法能够帮助到更多的人也是挺好的，自己也可以方便的到自己的博客上找之前解决办法