Built with Seam
You can find the full source code for this website in the Seam package in the directory /examples/wiki. It is licensed under the LGPL.
| Online: | 16 Members of 9438 |
| Forum: Seam Users |
 |
18. Jun 2010, 11:17 America/New_York | Link |
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!
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:
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
I find the best thing to do is ask yourself what are you trying to achieve.
You say,
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.
Your requirement, 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 :)