查詢， 結果集為AssociatedInfo：

<select 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 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 resultType='com.hand.hcf.app.payment.web.dto.CashDataPublicReportLineDTO'> SELECTtemp.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_amountFROMcsh_write_off cWHEREc.document_header_id = temp.exp_report_header_idAND c.document_line_id = temp.idAND 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_amountFROMcsh_data_relation_acp aWHEREa.report_head_id = b.exp_report_header_idAND a.report_line_id = b.idAND a.document_type = ’ACP_REQUISITION’) AS associated_amountFROMfec_expense.exp_report_payment_schedule b ,fec_expense.exp_report_header rWHEREb.frozen_flag = 'Y'and b.exp_report_header_id=r.idAND 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標簽內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！