2

I have two entities with ManyToMany-Relation connected via join table:

@Entity
@Table(name = "stations")
public class StationEntity implements Identifyable {

    @Id
    @Column(name = "station_id")
    private Long id;

    @ManyToMany
    @JoinTable(
            name = "stations_zones",
            inverseJoinColumns = @JoinColumn(name = "zone_id"),
            joinColumns = @JoinColumn(name = "station_id"))
    private List<ZoneEntity> zones = new ArrayList<>();
}




@Entity
@Table(name = "zones")
public class ZoneEntity implements Identifyable {

    @Id
    @Column(name = "zone_id")
    private Long id;
 

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "stations_zones",
            joinColumns = @JoinColumn(name = "zone_id"),
            inverseJoinColumns = @JoinColumn(name = "station_id"))
    private List<StationEntity> stations;

}

Now I want to query all zones, which are linked to a station of a given list. For all fetched zones, I need the ids of all linked stations, so I defined entity graph to prevent n+1 problem:

@Repository
public interface ZoneDao extends JpaRepository<ZoneEntity, Long> {

    @EntityGraph(attributePaths = {"stations"})
    List<ZoneEntity> findAllByStations_IdIn(List<Long> stationIds);
}

Resulting sql is this:

select
            zoneentity0_.zone_id as zone_id1_32_0_,
            ...
        from
            zones zoneentity0_ 
        left outer join
            stations_zones stations1_ 
                on zoneentity0_.zone_id=stations1_.zone_id 
        left outer join
            stations stationent2_ 
                on stations1_.station_id=stationent2_.station_id 
        where
            stationent2_.station_id in (?, ..., ?)

Now the problem is the following: Although a zone can have multiple stations assigned (including some that are not included in parameter list!), the resulting ZoneEntities will only have references to the stations from the list, since the result is determined by joining the tables and selecting only the rows, which match to one of the station-ids from the parameter list. When I omit the entityGraph and load the entities by good ol' lazy loading, it will contain references to all stations, includes the ones that are not in the passed parameter list.

Is it possible to prevent this? Can I tell hibernate to use passed query parameters only to restrict the number of root entities, but not use it in the entities graph? If this is not possible, what would be a better solution?

Seb
  • 1,721
  • 1
  • 17
  • 30
  • 1
    `Is it possible to prevent this?` - yes, write a query (`@Query`) with semi-join (`exists`) and optionally `fetch` and do not rely on `spring-data` magic. – Andrey B. Panfilov Sep 01 '22 at 10:55
  • Already thought about that, but way hoping I was just overseeing something obvious in the definition of the entityGraph or something like that. Anyway, after defining custom query as you suggested, it seems to work. – Seb Sep 01 '22 at 14:22
  • 1
    You could configure `@Fetch(SUBSELECT)` for the `stations` and remove the entity graph, but overall, I would suggest writing a semi-join like Andrey suggested. You can write the exists query also with JPA Criteria in a `Specification` though. – Christian Beikov Sep 05 '22 at 06:20

0 Answers0