CODEWITHSUNDEEP
javajpaeclipselinkjpql
davidxxx
davidxxx

Reputation: 131456

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

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

Upvotes: 2

Views: 1232

Answers (1)

JB Nizet
JB Nizet

Reputation: 691943

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).

Upvotes: 1

Related Questions