CODEWITHSUNDEEP
jpajoinfetchone-to-many
user2466586
user2466586

Reputation: 3

JPA fetching too many entries

I have a Customer Entity with a OneToMany relationship to an Invoice Entity.

In plain old sql i can do "select customer_name,customer_age,[some other fields] from customer, invoice where ... [put some filtering here]", which gets me exactly one record with the fields i need.

In JPA i use "select c from Customer c join c.invoiceCollection where ... [same filtering as above]"

This works, but i get the Customer entity with all its associated invoices. This is nonsense, because i pull a huge amount of (invoices) data from the database, which i do not need. I need only my customer data with exactly one invoice, as specified in the where clause.

To make things worse, i have to loop over the Customer.invoiceCollection in order to find the one specific invoice needed. This costs me even more time plus it exposes my "where" clause to the middle-tier.

Question: is there a JPA select syntax which fetches exactly one record from a one-to-many relationship, as defined in the where clause?

Things tried so far: a) lazy loading. This does not work, throws an exception whenever i try to access Customer.invoiceCollection. Even if it worked, i'd get a Collection with some 1000 entries, which i do not need.

b) changed my jpa statement to "select c,i from Customer c join c.invoiceCollection i where ...". This returns me an array of objects, which i have to manually map to a Customer / Invoice entity. It works, but it makes the ORM philosophy obsolete. If i perform all the mapping from relational database records/fields to java objects manually in my code, why do i need JPA?

Upvotes: 0

Views: 1530

Answers (3)

robert_difalco
robert_difalco

Reputation: 4914

This is one of the most infuriating things about JPA. For example, you NEED the OneToMany side if you want Customer to cascade delete Invoices. In most cases you'd like to tell Invoices to delete itself when a Customer is deleted so that Customer does not necessarily need to know about Invoice.

My suggestion for you is that you keep the OneToMany there but get the Lazy Loading working. In your code, do not access "Customer#getInvoices" directly (unless you really need all of them).

This will allow you to do queries on customers that join to invoices without loading them.

I'm guess the exception you are getting just has to do with transaction boundaries which can be easily fixed.

For a lot of these relationships I often add the OneToMany as a private instance variable but I don't create the #getter method. That way I can use it in queries, setup cascade delete, etc. but I don't provide a way to accidentally load thousands of invoices from a customer.

Oh, and for those queries where you need exactly one invoice with its associated customer you should just do the JPA query on the Invoice and then call #getCustomer on that invoice object. That will be eagerly fetched for you.

Upvotes: 1

axtavt
axtavt

Reputation: 242706

You should not use one-to-many relationship in this case.

One-to-many relationships are suitable for situations when objects at "many" side are logical parts of object at "one" side (e.g. relationship from Invoice to InvoiceLine).

In you case you need a unidirectional many-to-one relationship from Invoice to Customer, so that you can query it as follows:

select i from Invoice i where ...

Then you can use customer field of Invoice to access Customer or filter by its properties.

Upvotes: 0

Csaba
Csaba

Reputation: 949

If you need exactly one invoice with the related customer, why don't you create a query simply based on Invoice?

select i from Invoice i where [same filtering..]

Upvotes: 0

Related Questions