I have a simple Restaurant Entity that has a List of Votes ( some fields and methods are omitted for brevity ).

public class Restaurant extends AbstractNamedEntity {
    @OneToMany(mappedBy = "restaurant", cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    @Fetch(FetchMode.SUBSELECT)
    private List<Vote> votes;
}

Here is another side of the relationship

 public class Vote extends AbstractBaseEntity {
    @ManyToOne
    @JsonBackReference
    private Restaurant restaurant;
}

When I try to fetch data using Spring data JPA findAll() method and then convert it to DTOs

  public static RestaurantResponseDTO toRestaurantDto(Restaurant restaurant) {

        return new RestaurantResponseDTO(restaurant.getId(), restaurant.getName(),
                restaurant.getAddress(), getRestaurantVoteCount(restaurant));
    }
    public static long getRestaurantVoteCount(Restaurant restaurant) {
    var votes = restaurant.getVotes();
    if (votes == null) return 0;
    return  votes.stream().filter(vote -> vote.getVoteDate().equals(LocalDate.now())).count();
}

these are the SQLs I have:

Hibernate: 
select
    restaurant0_.id as id1_1_,
    restaurant0_.name as name2_1_,
    restaurant0_.address as address3_1_ 
from
    restaurant restaurant0_

Hibernate: 
select
    votes0_.restaurant_id as restaura3_4_1_,
    votes0_.id as id1_4_1_,
    votes0_.id as id1_4_0_,
    votes0_.restaurant_id as restaura3_4_0_,
    votes0_.user_id as user_id4_4_0_,
    votes0_.vote_date as vote_dat2_4_0_ 
from
    vote votes0_ 
where
    votes0_.restaurant_id in (
        select
            restaurant0_.id 
        from
            restaurant restaurant0_
    )

I thought that Subselect needs only 1, why do I have 2?

CodePudding user response：

Actually, what you see is expected hibernate behavior. See this section of the hibernate documentation.

Hibernate is going to avoid the N 1 query issue by generating a single SQL statement to initialize all votes collections for all Restaurant entities that were previously fetched. Instead of using passing all entity identifiers, Hibernate simply reruns the previous query that fetched the Restaurant entities.

P.S. As for the FetchMode.JOIN option, please note that as it's mentioned here:

The reason why we are not using a JPQL query to fetch multiple Department entities is because the FetchMode.JOIN strategy would be overridden by the query fetching directive.

To fetch multiple relationships with a JPQL query, the JOIN FETCH directive must be used instead.

So, I guess the most flexible way for your case would be to write own JPQL query and use JOIN FETCH directive where it's needed.