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

2023-07-23 00:22:04 74

查询，结果集为AssociatedInfo：



SELECT
r.requisition_numberASbusiness_code,
r.idASheader_id,
r.document_type_idASreportTypeId,
r.applicant_idasemployeeId
FROM
fec_expense.exp_report_payment_scheduleb,fec_expense.exp_report_headerr
WHERE
b.exp_report_header_id=r.idandr.`status`=1004
and
b.frozen_flag="Y"
andr.applicant_id=#{applicationId}

ANDr.requisition_numberLIKEconcat(
'%',
concat(#{reportNumber,jdbcType=VARCHAR},'%'))


ANDr.document_type_id=#{documentTypeId}

0">
ANDr.document_type_idIN

#{formId}


AND(
b.amount-(SELECT
COALESCE(sum(c.write_off_amount),0)ASwrite_off_amount
FROM
csh_write_offc
WHERE
c.document_header_id=b.exp_report_header_id
ANDc.document_line_id=b.id
AND(c.STATUS='Y'OR(c.STATUS='P'ANDc.operation_type='WRITE_OFF'))
)-(
SELECT
COALESCE(sum(a.amount),0)AScommit_amount
FROM
csh_data_relation_acpa
WHERE
a.report_head_id=b.exp_report_header_id
ANDa.report_line_id=b.id
ANDa.document_type='ACP_REQUISITION'
)>0
)
GROUPBY
r.requisition_number,
b.exp_report_header_id,
r.document_type_id,
b.applicant_id
ORDERBY
r.requisition_number

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








SELECT
	temp.idscheduleLineId,
	""AScshTransactionId,
	temp.amount,
	temp.associated_amountassociatedAmount,
	(
	temp.amount-temp.associated_amount-(
SELECTCOALESCE
	(sum(c.write_off_amount),0)ASwrite_off_amount
FROM
	csh_write_offc
WHERE
	c.document_header_id=temp.exp_report_header_id
	ANDc.document_line_id=temp.id
	ANDc.document_type="PUBLIC_REPORT"
	AND(c.STATUS='Y'OR(c.STATUS='P'ANDc.operation_type='WRITE_OFF'))
	)
	)ASavailableAmount,
	temp.exp_report_header_idexpReportHeaderId,
	0ASscheduleLineNumber,
	temp.company_idcompanyId,
	temp.currency_codecurrency,
	temp.descriptiondescription,
	temp.exchange_rateexchangeRate,
	temp.payment_schedule_dateschedulePaymentDate,
	temp.payment_methodpaymentMethod,
	temp.payment_type,
	temp.prop_flagprop_flag,
	temp.csh_transaction_class_idcshTransactionClassId,
	(SELECTctc.descriptionFROMcsh_transaction_classctcWHEREctc.id=temp.csh_transaction_class_id)AScshTransactionClassName,
	temp.cash_flow_item_idcashFlowItemId,
	temp.payee_categorypayeeCategory,
	temp.payee_idpayeeId,
	temp.account_numberaccountNumber,
	temp.account_nameaccountName,
	temp.bank_codebankCode,
	temp.bank_namebankName,
	temp.bank_codebankLocationCode,
	temp.bank_namebankLocationName,
	""provinceCode,
	""provinceName,
	""cityCode,
	""cityName,
	(selectc.type_codefromcsh_transaction_classcwherec.id=temp.csh_transaction_class_id)cshTransactionTypeCode,
	""contractHeaderId
FROM
	(
SELECT
b.*,
	(
SELECTCOALESCE
	(sum(a.amount),0)ASassociated_amount
FROM
	csh_data_relation_acpa
WHERE
	a.report_head_id=b.exp_report_header_id
	ANDa.report_line_id=b.id
	ANDa.document_type='ACP_REQUISITION'
	)ASassociated_amount
FROM
	fec_expense.exp_report_payment_scheduleb,fec_expense.exp_report_headerr
WHERE
	b.frozen_flag="Y"
	andb.exp_report_header_id=r.id
	ANDb.exp_report_header_id=#{headerId}
	)temp

包含列表的实体类 CashDataPublicReportHeaderDTO：

packagecom.hand.hcf.app.payment.web.dto;

importcom.fasterxml.jackson.databind.annotation.JsonSerialize;
importcom.fasterxml.jackson.databind.ser.std.ToStringSerializer;
importlombok.Data;

importjava.time.ZonedDateTime;
importjava.util.List;

@Data
publicclassCashDataPublicReportHeaderDTO{

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

privateStringreportNumber;//报账单编号

privateStringreportTypeName;//报账单类型
@JsonSerialize(using=ToStringSerializer.class)
privateLongreportTypeId;//报账单类型ID

privateListlineList;//报账单计划付款行

@JsonSerialize(using=ToStringSerializer.class)
privateLongemployeeId;//员工ID
privateStringemployeeName;//员工名称

privateZonedDateTimerequisitionDate;//申请日期
}

总结

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

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

热门推荐

随机推荐