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


      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

      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!