Spring Data JPA原生查询场景下如何解决N+1查询问题？

@EntityGraph只对JPQL或Spring Data自动生成的查询生效，原生查询不支持这个注解，所以得换思路处理，下面是几种可行方案：

方案一：改用JPQL查询（优先推荐） #

把原生查询换成JPQL的LEFT JOIN FETCH，结合分页逻辑，这样就能一次性加载关联的User，避免N+1：

@Repository
public interface ThreadRepository extends JpaRepository<Thread, Long> {

    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"user"})
    Page<Thread> findAllByOrderByCreatedDateDesc(Pageable pageable);

    @Query(value = "SELECT DISTINCT t FROM Thread t LEFT JOIN FETCH t.user ORDER BY t.createdDate DESC",
            countQuery = "SELECT COUNT(DISTINCT t.id) FROM Thread t")
    Page<Thread> findAllCustomMethod(Pageable pageable);
}

这里加DISTINCT是因为LEFT JOIN FETCH会返回重复的Thread实例（同一个User对应多个Thread时），分页时必须去重才能保证页码正确，countQuery也要用COUNT(DISTINCT t.id)来匹配实际数据量。

方案二：用@SqlResultSetMapping映射原生查询结果 #

如果必须保留原生查询，可以通过定义结果集映射，让JPA直接把查询返回的Thread和User字段映射到关联实体，避免懒加载触发额外查询：

1. 在Thread实体上定义结果集映射：

@Entity
@SqlResultSetMapping(
    name = "ThreadWithUserMapping",
    entities = {
        @EntityResult(entityClass = Thread.class, fields = {
            @FieldResult(name = "id", column = "T_ID"),
            @FieldResult(name = "title", column = "T_TITLE"),
            // 补充Thread的其他字段，对应原生查询里的别名
        }),
        @EntityResult(entityClass = User.class, fields = {
            @FieldResult(name = "id", column = "U_ID"),
            @FieldResult(name = "username", column = "U_USERNAME"),
            // 补充User的其他字段，对应原生查询里的别名
        })
    }
)
public class Thread {
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "USER")
    private User user;
    // 其他实体字段...
}

2. 修改原生查询，给字段加别名并引用映射：

@Repository
public interface ThreadRepository extends JpaRepository<Thread, Long> {

    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"user"})
    Page<Thread> findAllByOrderByCreatedDateDesc(Pageable pageable);

    @Query(value = "SELECT T.id AS T_ID, T.title AS T_TITLE, " +
                   "U.id AS U_ID, U.username AS U_USERNAME " +
                   "FROM THREAD T LEFT JOIN FRM_USER U ON U.id = T.USER " +
                   "ORDER BY T.created_date DESC",
            countQuery = "SELECT COUNT(T.ID) FROM THREAD T",
            nativeQuery = true,
            resultSetMapping = "ThreadWithUserMapping")
    Page<Thread> findAllCustomMethod(Pageable pageable);
}

这样JPA会直接把查询结果组装成带User关联的Thread实体，不会触发懒加载的N+1查询。

方案三：手动处理结果集（复杂场景适用） #

如果上面两种方法都不满足需求，可以自定义Repository实现，手动执行查询并组装关联关系：

// 主Repository接口
@Repository
public interface ThreadRepository extends JpaRepository<Thread, Long>, ThreadRepositoryCustom {

    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"user"})
    Page<Thread> findAllByOrderByCreatedDateDesc(Pageable pageable);
}

// 自定义接口
public interface ThreadRepositoryCustom {
    Page<Thread> findAllCustomMethod(Pageable pageable);
}

// 实现类
public class ThreadRepositoryCustomImpl implements ThreadRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<Thread> findAllCustomMethod(Pageable pageable) {
        // 分页查询数据
        String dataSql = "SELECT T.*, U.* FROM THREAD T LEFT JOIN FRM_USER U ON U.id = T.USER ORDER BY T.created_date DESC";
        Query dataQuery = entityManager.createNativeQuery(dataSql);
        dataQuery.setFirstResult((int) pageable.getOffset());
        dataQuery.setMaxResults(pageable.getPageSize());

        List<Object[]> resultList = dataQuery.getResultList();
        List<Thread> threads = new ArrayList<>();
        Map<Long, User> userCache = new HashMap<>();

        for (Object[] row : resultList) {
            // 手动映射Thread字段（根据你的数据库字段顺序调整索引）
            Thread thread = new Thread();
            thread.setId((Long) row[0]);
            thread.setTitle((String) row[1]);
            // 其他Thread字段映射...

            // 处理User关联，用缓存避免重复创建
            Long userId = (Long) row[3];
            User user;
            if (userCache.containsKey(userId)) {
                user = userCache.get(userId);
            } else {
                user = new User();
                user.setId(userId);
                user.setUsername((String) row[4]);
                // 其他User字段映射...
                userCache.put(userId, user);
            }
            thread.setUser(user);
            threads.add(thread);
        }

        // 查询总条数
        String countSql = "SELECT COUNT(T.ID) FROM THREAD T";
        Query countQuery = entityManager.createNativeQuery(countSql);
        long total = ((Number) countQuery.getSingleResult()).longValue();

        return new PageImpl<>(threads, pageable, total);
    }
}

这种方式需要手动处理字段映射，但完全可控，适合原生查询逻辑特别复杂的场景。

内容的提问来源于stack exchange，提问作者Mehdi Rahimi