CODEWITHSUNDEEP
javaspringjpa
ZZZ
ZZZ

Reputation: 945

JPA query always returns all rows rather than rows that match criteria

I have a JPA query written like this:

public interface MyDataRepository extends CrudRepository<MyData, String> {
    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where c.date = :date")
    List<MyData> getMyDataOfDate(@NotNull LocalDate date);


    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where c.name = :name")
    List<MyData> getMyDataOfName(@NotNull String name);

    @Query("select md " +
            "from MyData md " +
            "join fetch md.child c " +
            "where md.type = :type")
    List<MyData> getMyDataOfType(@NotNull String type);
}

Class MyData and Child are defined as:

class MyData {
    String id;
    String type;

    @ManyToOne
    @JoinColumn(name = "CHILD_ID", referencedColumnName = "ID", nullable = false, updatable = false)
    Child child;        
}


class Child {
    String id;
    String name;
    LocalDate date;
}

The problem is that whenever I call the getMyDataOfDate method or getMyDataOfName method, they always return ALL rows rather than the rows that matches the where condition, as if the where clause never exists.

However, the getMyDataOfType method works fine. The difference of this method is that the where condition is on a property of md, not c.

What did I do wrong?

Upvotes: 0

Views: 753

Answers (1)

Chris
Chris

Reputation: 21145

JPA does not allow filtering on join fetches. Reasons being is that when you specify a join fetch, you are telling JPA to fetch the parent and all its children defined by that relationship in the managed entities it returns. If filtering were allowed, the list of children, the relationship in the parent, might not reflect what is actually in the database. Take the case of Parent with many children

"Select parents from Parent p fetch join p.children c where c.firstName = 'Bob'"

For such a query, when you get a list of parents and calling getChildren on them, do you expect to see all their children or a list that only contains children named Bob? If the later (which is the only way to do so), how should JPA handle changes to a parents children list, and know what to do with the not-fetched children?

This is why JPA doesn't allow filtering over fetch joins, and they restrict it across all relationships to be consistent. If you want the parents who have children with the firstName of 'Bob', it would look like:

"Select parents from Parent p join p.children c where c.firstName = 'Bob'"

Every parent returned will be a complete representation of its state in the database based on its mappings; so accessing parent.getChildren will return the current state of its children list and not something affected by the way it was fetched.

Upvotes: 2

Related Questions