Querying an entity with filters on a OneToMany attribute gives a weird result

Question

I use EclipseLink for 9 months and so far no problem. Since I have the need to query an entity with a OneToMany attribute, it's all the contrary. It gives me a strange result. I have simplified my entities until the maximum but the problem remains.

I will explain my need which is ultra simple : I have two entities : Person which has a bidirectional relation with Address. Person has potentially several Addresses but an Address belongs to one and only Person.

In Classes, it gives that :

@Entity
public class Person implements Serializable {

    @Id
    private Long id;

    @OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
    private Set<Address> addresses;

    // Getter and setter
      ...
}


@Entity
public class Address implements Serializable {

    @Id
    private String idAddress;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "idPerson", referencedColumnName = "idPerson")
    private Person person;

    // Getter and setter
    ...
}

I want to query personne with their adresses. All that with some conditions on personne and adresse. My simplified query :

select pers FROM Person pers join pers.addresses address
                 where pers.matricule=:matricule                    
                 and address.date=:dateContract

When I execute it, I retrieve the right person but with all addresses linked (with foreign key) with this person. Even the addresses which don't match with the dateContract condition.

It seems that it's a problem related to the use of filtering on a oneToMany attribute in my query. The problem is solved if i do several requests but it will give low performances as I have several requests like this. I have tried with the oneToMany in eager initialization and with a fetch-join query hint but i have got the same result.

Thank you for having read me :)

PS : I have written the code manually, so a little typo is not impossible

David

score 1 · Answer 1 · answered Dec 06 '11 at 19:33

1

Your query only returns persons. Once you get the persons, you're calling getAddresses(), which lazily loads the addresses of the person - all of them. In short, the query limits the set of returned persons, but since it only returns persons, the addresses are lazy-loaded using another query when accessing the set of addresses.

What you want to do is return the persons with some of their addresses in a single query. To do that, you need to use the fetch keyword:

select distinct pers FROM Person pers 
join fetch pers.addresses address
where pers.matricule = :matricule                    
and address.date = :dateContract

Be very careful, though: this query returns an incorrect view of the person entity. You should make sure not to modify the collection of addresses of the returned persons (although since the addresses association is mapped by the Address.person association and there is no cascade, you should not have problems in this particular case).

answered Dec 06 '11 at 19:33

JB Nizet

678,734
91
1,224
1,255

It works as I said with Hibernate. Then I guess you should select addresses and persons, and associate the persons and the addresses yourself: select a, p from Address a inner join Person p where ... – JB Nizet Dec 06 '11 at 21:05
Hello, Thank you for your response. I have already tried the join fetch and it gives the same result. Besides, The JPA spec does not allow a alias to be given to a fetch join. Eclipselink follows this behavior, so your request would throw an exception. But i agree with you about the fact that the query must be executed in one shot. Unfortunately, EclipseLink splits it in two despite my join-fetch hint... – davidxxx Dec 06 '11 at 21:05
EclipseLink will support join fetch, but will fetch all of the address for the person, as it is require to build a complete person. The issue with building an incomplete Person is that if it is cached, then other queries, or other users that wanted a whole Person will get the partial one. Selecting both the Person and the address would be best, "select p, a from Person p join p.addresses a where ..." – James Dec 07 '11 at 14:04
This kind of solution : multiple objects in clause select is cumbersome since EclipseLink doesn't allow to use "Fetch Join" Hints for this kind of query. Result : It does as many sql queries as I have objects to retrieve in my select clause. I had fastly given up this solution for this reason. Are there a good way to do the job with EclipseLink ? – davidxxx Jan 04 '12 at 13:47

Querying an entity with filters on a OneToMany attribute gives a weird result

1 Answers1