S
S
Skoleev2021-09-07 19:17:53
Java
Skoleev, 2021-09-07 19:17:53

Why does @Fetch(FetchMode.SUBSELECT) trigger 2 SQL instead of 1?

There is a simple Restaurant entity that has a Vote list (Unnecessary methods and fields not shown)

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

Here is the other side of the relationship
public class Vote extends AbstractBaseEntity {
    @ManyToOne
    @JsonBackReference
    private Restaurant restaurant;
}

When I get data using the findAll() method from Spring Data JPA and convert it through the DTO mapper like this

@Cacheable(value = "restaurantDTOList", key = "-1")
    public List<RestaurantResponseDTO> getAll() {
        List<Restaurant> restaurantList = restaurantRepository.findAll();
        return restaurantList.stream()
                .map(RestaurantMapper::toRestaurantDto)
                .collect(Collectors.toList());
    }
  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();
}

I get these SQL
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 would be extremely grateful if you help me figure out why 2 SQL, and not one

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question