JPA:在空的多方面将 Fetch 结果加入 NULL

2022-01-18 00:00:00 lazy-loading java Hibernate jpa spring-data

我在 User 和 GameMap 之间有一对多的关系.一个用户可以拥有多张地图.

I have a one to many relationship between User and GameMap. One user can have many maps.

用户类别:

// LAZY LOADED
@OneToMany(cascade = CascadeType.ALL, mappedBy = "creater")
private final List<GameMap> maps = new ArrayList<>();

但是，有时我需要预先加载地图.为了避免在关闭 Session 后出现 LazyInitializationException，我有两种检索用户的变体.

However, sometimes I need to eager load the maps. To avoid the LazyInitializationException after closing Session, I have two variants of retrieving Users.

用户存储库:

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

问题:
但是，JPQL JOIN FETCH 变体可以一次性立即加载用户，并且他的地图返回 NULL 用户如果表中没有此用户的地图.

问题:
我如何重写 JPQL 语句以检索用户和可选(！)他的所有地图，但如果没有地图，那没关系，但不要返回 NULL 用户.

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.

推荐答案

FETCH JOIN 实际上会解析为 SQL 中的 inner 连接.这意味着 User 表中没有映射的任何记录/实体都将从结果集中删除.您需要 FETCH JOIN 上的 LEFT 关键字来获得所有结果，即使是没有地图的结果.

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

相关文章