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

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

    maynard

      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!


        • 1. Re: The best way to retrievei part of OneToMany associations - EJB and Seam 2.0.2
          serkan.s.eskici.online.nl

          Just use plain old HQL or EJB-QL.


          Use Criteria Queries for building dynamic queries, for example when you have a search form where one can enter terms on many inputs.


          And the query you have can be written like this:


          from Customer c 
          where c.invoice.date >= 01.01.2010 and c.invoice.date <= 31.01.2010
          




          Furthermore, you should read this about HQL before asking these basic questions:
          http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html



          • 2. Re: The best way to retrievei part of OneToMany associations - EJB and Seam 2.0.2
            kragoth

            I find the best thing to do is ask yourself what are you trying to achieve.


            You say, I want to see all the invoices a customer received in a given quarter.


            If this is true the your sentence already tells you that you should not be using customer.getInvoices.


            customer.getInvoices would be for the statment. I want to see a customer and all of their invoices.


            Your requirement, I want to see all the INVOICES a customer received in a given quarter is the key to your solution.


            Instead of querying the customer table you should be querying the INVOICE table where the customer = c and invoice date is between x and y.


            So, load your customer and leave the invoices collection lazy. Then go query 'seperately' the invoices you are interested in.


            I hope this helps :)