I have the following DB:
Customer -- oneToMany -- Invoices -- onToMany -- InvoiceLine
I mean - Customer can have large number (thousands and more) of Invoices but Invoice can have up to some tens or hundreds lines. So - I am happy that my Invoice is retrieved together with InvoiceLines - there is no big performance overhead, because lines are few and usually one needs all of them. It is different with Customer and Invoices - there can be use cases when I need only some part of Invoices collections - e.g. - invoices from certain customer in the last quarter. So - how should I make queris?
For Invoice and InvoiceLines it is easy - I simply find Invoice entity and touch InvoiceLines collection to retrieve all the Lined in lazy manner from DB. But what should I do with Customer.
One solution is to make 2 queries - one for Customer (I will get customer instance and this approach assumes that I will never touch customer.GetInvoices) and another for Invoices from the past quarter and I will store them in collection invoices. So - in this scenario I can hope to achieve my goal to display and save changes in customer and her invoices but it is not nice, because I am not employing benefits of EJB association. I guess - each invoice is fetching another customer instance of the same real-world customer and so on. Maybe there can be even some transactio nconflicts when customer and invoices.get(xxx) instances are persisted.
So - I am looking for something like - that I can ask to retrieve me customer and only certain part of her invoices collection. How can I do this?
select customer, invoice from Customer customer, Invoice invoice where customer=invoice.customer and invoice.date>=01.01.2010 and invoice.date<=31.01.2010
returns (as I understand) not one customer instance with the associated past quarter invoices, but say - some hundreds of customer instances and some hundreds of invoice instances.
What is the best practice to achieve this?
I have read a bit about Hibernate criteria API and Hibernate is proud of his smart collections that makes efficient fetching - but can I use this hibernate feature in my JBoss Server 4.2.3 / Seam 2.0.2 and (I guess) Java 1.5. environment - isn't thoses Hibernate criteria API too new for this?
Well - what is your experience about fetching of associated entities?
Thanks in advance!