详解mybatis collection标签一对多的使用

查询， 结果集为AssociatedInfo：

<select id="queryReportAssociatedAcp" resultMap="AssociatedInfo">

    SELECT
    r.requisition_number AS business_code,
    r.id AS header_id,
    r.document_type_id AS reportTypeId,
    r.applicant_id as employeeId
    FROM
    fec_expense.exp_report_payment_schedule b,fec_expense.exp_report_header r
    WHERE
    b.exp_report_header_id=r.id and r.`status`=1004
    and
    b.frozen_flag ="Y"
    and r.applicant_id=#{applicationId}
    <if test="reportNumber!=null and reportNumber != ''">
      AND r.requisition_number LIKE concat(
      '%',
      concat(#{reportNumber,jdbcType=VARCHAR}, '%'))
    </if>
    <if test="documentTypeId !=null and reportNumber != ''">
      AND r.document_type_id = #{documentTypeId}
    </if>
    <if test="formTypes != null and formTypes.size > 0">
      AND r.document_type_id IN
      <foreach collection="formTypes" item="formId" open="(" separator="," close=")">
        #{formId}
      </foreach>
    </if>
    AND (
    b.amount - ( SELECT
    COALESCE(sum( c.write_off_amount ), 0) AS write_off_amount
    FROM
    csh_write_off c
    WHERE
    c.document_header_id = b.exp_report_header_id
    AND c.document_line_id = b.id
    AND ( c.STATUS = 'Y' OR ( c.STATUS = 'P' AND c.operation_type = 'WRITE_OFF' ) )
    ) - (
    SELECT
    COALESCE(sum( a.amount ), 0) AS commit_amount
    FROM
    csh_data_relation_acp a
    WHERE
    a.report_head_id = b.exp_report_header_id
    AND a.report_line_id = b.id
    AND a.document_type = 'ACP_REQUISITION'
    ) > 0
    )
    GROUP BY
    r.requisition_number,
    b.exp_report_header_id,
    r.document_type_id,
    b.applicant_id
    ORDER BY
    r.requisition_number

  </select>

结果集 AssociatedInfo: 使用collection 实现1对多的场景, CashDataPublicReportHeaderDTO实体里包含一个行的集合List<CashDataPublicReportLineDTO> lines：

<resultMap id="AssociatedInfo" type="com.hand.hcf.app.payment.web.dto.CashDataPublicReportHeaderDTO">
    <result column="header_id" property="reportHeadId"/>
    <result column="business_code" property="reportNumber"/>
    <result column="form_name" property="reportTypeName"/>
    <collection property="lineList" column="{headerId=header_id}"
          ofType="ArrayList" select="getPaymentInfo"/>
  </resultMap>
  <select id="getPaymentInfo" resultType="com.hand.hcf.app.payment.web.dto.CashDataPublicReportLineDTO">
    SELECT
	temp.id scheduleLineId,
	"" AS cshTransactionId,
	temp.amount,
	temp.associated_amount associatedAmount,
	(
	temp.amount - temp.associated_amount - (
SELECT COALESCE
	( sum( c.write_off_amount ), 0 ) AS write_off_amount
FROM
	csh_write_off c
WHERE
	c.document_header_id = temp.exp_report_header_id
	AND c.document_line_id = temp.id
	AND c.document_type = "PUBLIC_REPORT"
	AND ( c.STATUS = 'Y' OR ( c.STATUS = 'P' AND c.operation_type = 'WRITE_OFF' ) )
	)
	) AS availableAmount,
	temp.exp_report_header_id expReportHeaderId,
	0 AS scheduleLineNumber,
	temp.company_id companyId,
	temp.currency_code currency,
	temp.description description,
	temp.exchange_rate exchangeRate,
	temp.payment_schedule_date schedulePaymentDate,
	temp.payment_method paymentMethod,
	temp.payment_type,
	temp.prop_flag prop_flag,
	temp.csh_transaction_class_id cshTransactionClassId,
	( SELECT ctc.description FROM csh_transaction_class ctc WHERE ctc.id = temp.csh_transaction_class_id ) AS cshTransactionClassName,
	temp.cash_flow_item_id cashFlowItemId,
	temp.payee_category payeeCategory,
	temp.payee_id payeeId,
	temp.account_number accountNumber,
	temp.account_name accountName,
	temp.bank_code bankCode,
	temp.bank_name bankName,
	temp.bank_code bankLocationCode,
	temp.bank_name bankLocationName,
	"" provinceCode,
	"" provinceName,
	""cityCode,
	"" cityName,
	 (select c.type_code from csh_transaction_class c where c.id=temp.csh_transaction_class_id ) cshTransactionTypeCode,
	""contractHeaderId
FROM
	(
SELECT
  b.*,
	(
SELECT COALESCE
	( sum( a.amount ), 0 ) AS associated_amount
FROM
	csh_data_relation_acp a
WHERE
	a.report_head_id = b.exp_report_header_id
	AND a.report_line_id = b.id
	AND a.document_type = 'ACP_REQUISITION'
	) AS associated_amount
FROM
	fec_expense.exp_report_payment_schedule b ,fec_expense.exp_report_header r
WHERE
	b.frozen_flag = "Y"
	and b.exp_report_header_id=r.id
	AND b.exp_report_header_id = #{headerId}
	) temp

  </select>

包含列表的实体类 CashDataPublicReportHeaderDTO ：

package com.hand.hcf.app.payment.web.dto;

import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;

import java.time.ZonedDateTime;
import java.util.List;

@Data
public class CashDataPublicReportHeaderDTO {

  @JsonSerialize(using = ToStringSerializer.class)
  private Long reportHeadId;//报账单头ID

  private String reportNumber;//报账单编号

  private String reportTypeName;//报账单类型
  @JsonSerialize(using = ToStringSerializer.class)
  private Long reportTypeId;// 报账单类型ID

  private List<CashDataPublicReportLineDTO> lineList;//报账单计划付款行

  @JsonSerialize(using = ToStringSerializer.class)
  private Long employeeId;//员工ID
  private String employeeName;//员工名称

  private ZonedDateTime requisitionDate; // 申请日期
}

总结

到此这篇关于mybatis collection标签一对多的使用的文章就介绍到这了,更多相关mybatis collection标签内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！