I have an APVendor class, which has a collection of APInvoice classes and a unique Name; each APInvoice has a collection of APPayment classes. Each APPayment object references exactly one BankAccount class, a ClearedDate, an Amount, and a CheckNumber property.
With a bank statement that has a check record, let's say my Bank of Foo checking account, check 1111, for the amount of $1000.00, I want to see if the payment exists in my persisted layer, and mark the APPayment object's ClearedDate property.
I can do this by querying the vendor, then querying the vendor's invoices, then looking for that check number written against the BankAccount, but I'm sure there's a much more efficient way to write this as one Criteria query.
Can someone help me with how that should look? Here's how I would write the query in SQL to fetch the object graph I would need to work with:
select p.*, i.*, v.*
from appayments p
join bankaccounts a on p.bankaccountid = a.bankaccountid
join apinvoices i on i.invoiceid = p.invoiceid
join apvendors v on v.vendorid = i.vendorid
where a.bankaccountid = ????
and p.checknumber = ????
The HQL query would be very similar to the SQL query:
select payment from APPayment payment
inner join payment.BankAccount bankAccount
left join fetch payment.Invoice invoice
left join fetch invoice.Vendor vendor
where bankAccount.id = :accountId
and payment.checkNumber = :checkNumber
The Criteria query is left as an exercise, but I don't see the poin tin using Criteria for such a static query.