Spring Data如何创建查询返回用户数大于5的Team实体

问题背景 #

给定实体类：

@Entity
public class Team {

    private String title;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "teams")
    @OrderBy("firstName ASC")
    private Set<User> users = new HashSet<>();
}

需要实现Repository查询，返回所有用户数量大于5的Team，期望对应的SQL为：

SELECT t.title, count(*) as total_users FROM team t 
LEFT JOIN team_users tu on t.id=tu.team_id  
GROUP BY t.id HAVING total_users > 5;

以下是几种可行的实现方式：

方法一：使用@Query注解（JPQL方式） #

直接在Repository接口中定义JPQL查询，通过关联查询、分组和条件筛选实现需求：

public interface TeamRepository extends JpaRepository<Team, Long> {

    // 返回完整Team实体
    @Query("SELECT t FROM Team t JOIN t.users u GROUP BY t.id HAVING COUNT(u) > 5")
    List<Team> findTeamsWithUsersCountGreaterThan5();

    // 返回包含标题和用户数的自定义结果（需先创建DTO）
    @Query("SELECT t.title as title, COUNT(u) as totalUsers FROM Team t JOIN t.users u GROUP BY t.id HAVING COUNT(u) > 5")
    List<TeamUserCountDTO> findTeamTitleAndUserCountGreaterThan5();
}

// 对应的DTO类
public class TeamUserCountDTO {
    private String title;
    private Long totalUsers;

    public TeamUserCountDTO(String title, Long totalUsers) {
        this.title = title;
        this.totalUsers = totalUsers;
    }

    // getter和setter方法省略
}

方法二：使用@Query原生SQL查询 #

如果更习惯用原生SQL，指定nativeQuery = true即可：

public interface TeamRepository extends JpaRepository<Team, Long> {

    // 返回完整Team实体
    @Query(value = "SELECT t.* FROM team t LEFT JOIN team_users tu on t.id=tu.team_id GROUP BY t.id HAVING count(*) > 5", nativeQuery = true)
    List<Team> findTeamsWithUsersCountGreaterThan5();

    // 返回标题和用户数的数组结果
    @Query(value = "SELECT t.title, count(*) as total_users FROM team t LEFT JOIN team_users tu on t.id=tu.team_id GROUP BY t.id HAVING count(*) > 5", nativeQuery = true)
    List<Object[]> findTeamTitleAndUserCountGreaterThan5();
}

方法三：使用JpaSpecificationExecutor构建动态查询 #

适合需要动态调整查询条件的场景，通过Specification组装查询逻辑：

// 扩展Repository接口
public interface TeamRepository extends JpaRepository<Team, Long>, JpaSpecificationExecutor<Team> {
}

// 构建查询条件并调用
Specification<Team> spec = (root, query, cb) -> {
    Join<Team, User> userJoin = root.join("users", JoinType.LEFT);
    query.groupBy(root.get("id"));
    return cb.gt(cb.count(userJoin), 5L);
};

List<Team> teams = teamRepository.findAll(spec);

方法四：使用NamedNativeQuery #

在实体类上定义命名原生查询，再在Repository中调用对应方法：

@Entity
@NamedNativeQuery(
    name = "Team.findTeamsWithUsersCountGreaterThan5",
    query = "SELECT t.* FROM team t LEFT JOIN team_users tu on t.id=tu.team_id GROUP BY t.id HAVING count(*) > 5",
    resultClass = Team.class
)
public class Team {
    // 实体原有字段和关联关系
}

// Repository接口中定义匹配方法
public interface TeamRepository extends JpaRepository<Team, Long> {
    List<Team> findTeamsWithUsersCountGreaterThan5();
}

如果需要返回自定义结果，可以配合@SqlResultSetMapping完成结果到DTO的映射。

内容的提问来源于stack exchange，提问作者Kronos Varmentos