亲宝软件园·资讯

展开

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

love_Watermelon 人气:0

查询, 结果集为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; // 申请日期
}

总结

加载全部内容

相关教程
猜你喜欢
用户评论