查詢, 結果集為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}AND r.requisition_number LIKE concat( '%', concat(#{reportNumber,jdbcType=VARCHAR}, '%'))AND r.document_type_id = #{documentTypeId} AND r.document_type_id IN#{formId}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
結果集 AssociatedInfo: 使用collection 實現1對多的場景, CashDataPublicReportHeaderDTO實體裡包含一個行的集合List
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
包含列表的實體類 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 ListlineList;//報賬單計劃付款行 @JsonSerialize(using = ToStringSerializer.class) private Long employeeId;//員工ID private String employeeName;//員工名稱 private ZonedDateTime requisitionDate; // 申請日期 }
[e36605 ] 詳解mybatis collection標籤一對多的使用已經有230次圍觀