2 Replies Latest reply on Jun 22, 2010 12:57 AM by Tim Evers

    The best way to retrievei part of OneToMany associations - EJB and Seam 2.0.2

    Maynard Witten Newbie

      Hi,


      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?
      The query:




      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!