查询，结果集为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_amountFROM csh_write_off cWHERE 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, ""contractHeaderIdFROM (SELECT b.*, (SELECT COALESCE ( sum( a.amount ), 0 ) AS associated_amountFROM csh_data_relation_acp aWHERE a.report_head_id = b.exp_report_header_id AND a.report_line_id = b.id AND a.document_type = 'ACP_REQUISITION' ) AS associated_amountFROM fec_expense.exp_report_payment_schedule b ,fec_expense.exp_report_header rWHERE 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; @Datapublic 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标签内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！