JPA: Join Fetch results to NULL on empty many side

Question

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.

Use "left join"

Andriy Slobodyanyk
– Andriy Slobodyanyk

2017-05-09 20:45:09 +00:00
Commented May 9, 2017 at 20:45 — Andriy Slobodyanyk
– Andriy Slobodyanyk, Commented May 9, 2017 at 20:45

Tim Biegeleisen · Accepted Answer · 2019-07-16 06:21:39Z

34

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 );

edited Jul 16, 2019 at 6:21

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered May 9, 2017 at 20:46

Andriy Slobodyanyk

2,08517 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tequilacat Over a year ago

In my case it did not work as I expected. I have one to many relationship, had exactly same problem. I use identical JPQL, and if there's no related entities on "many" end the list of requested entities (like 'user' in example above) is returned orrectly. But if a requested entity refers several related entities (e.g. user having 2 maps in the example) it's repeated several times, once for each related entity. Both in SQL result rows and in java objects. I put 'distinct' to query and it fixed the problem but I wonder if I do it correctly and no problems will arise later.

deviant Over a year ago

@tequilacat distinct is specifically for such cases

Collectives™ on Stack Overflow

JPA: Join Fetch results to NULL on empty many side

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related