JPA: Join Fetch results to NULL on empty many side
hibernate, java, jpa, lazy-loading, spring-data
Solution
A `FETCH JOIN` actually will resolve to an inner join in SQL. This means that any records/entities in the `User` table which have no maps will be removed from the result set. You need the `LEFT` keyword on your `FETCH JOIN` to get all the results, even those without a map.
@Query("SELECT u FROM User u LEFT JOIN FETCH u.maps WHERE u.id = (:id)")
public User findByIdEagerFetch( @Param("id") Long id );
Problem
I have a one to many relationship between User and GameMap. One user can have many maps. User class: ``` // LAZY LOADED @OneToMany(cascade = CascadeType.ALL, mappedBy = "creater") private final List<GameMap> maps = new ArrayList<>(); ``` However, sometimes I need to eager load the maps. To avoid the LazyInitializationException after closing Session, I have two variants of retrieving Users. User Repository: ``` public interface UserRepository extends JpaRepository<User, Long> { Optional<User> findById( Long id ); @Query("SELECT u FROM User u JOIN FETCH u.maps WHERE u.id = (:id)") public User findByIdEagerFetch( @Param("id") Long id ); } ``` Problem: However, the JPQL JOIN FETCH variant to eager load in one go the user and his maps returns a NULL user if there are no maps for this user in the table. Question: How can I rewrite the JPQL statement in order to retrieve the user and optionally(!) all his maps but if there are no maps, than thats okay, but dont return a NULL user.