Как получить записи таблицы, у которых где больше двадцати студентов?

# Получение записей таблицы, у которых больше двадцати студентов

Это типичная задача на SQL агрегацию и фильтрацию по GROUP BY. Расскажу о разных подходах: от чистого SQL до JPA/Hibernate в Java.

1. Основной SQL запрос

-- Предположим таблицы:
-- groups (id, name, class_year)
-- students (id, group_id, name, email)

-- Запрос: получить группы, у которых больше 20 студентов
SELECT 
    g.id,
    g.name,
    g.class_year,
    COUNT(s.id) as student_count
FROM groups g
LEFT JOIN students s ON g.id = s.group_id
GROUP BY g.id, g.name, g.class_year
HAVING COUNT(s.id) > 20
ORDER BY student_count DESC;

Объяснение:

• GROUP BY группирует строки по группам
• COUNT(s.id) считает количество студентов в каждой группе
• HAVING COUNT(s.id) > 20 фильтрует группы с количеством > 20
• LEFT JOIN обеспечивает, что группы без студентов тоже учитываются (count = 0)

2. Различие WHERE vs HAVING

-- ПЛОХО: WHERE не работает с агрегатными функциями
SELECT g.id, g.name, COUNT(s.id) as student_count
FROM groups g
JOIN students s ON g.id = s.group_id
WHERE COUNT(s.id) > 20  -- ошибка SQL!
GROUP BY g.id, g.name;

-- ХОРОШО: используем HAVING для фильтрации агрегатов
SELECT g.id, g.name, COUNT(s.id) as student_count
FROM groups g
LEFT JOIN students s ON g.id = s.group_id
GROUP BY g.id, g.name
HAVING COUNT(s.id) > 20;

3. Java реализация с JDBC

import java.sql.*;
import java.util.*;

public class GroupRepository {
    
    public List<GroupWithStudentCount> getGroupsWithMoreThanTwentyStudents(
            Connection connection) throws SQLException {
        
        String sql = """
            SELECT g.id, g.name, g.class_year, COUNT(s.id) as student_count
            FROM groups g
            LEFT JOIN students s ON g.id = s.group_id
            GROUP BY g.id, g.name, g.class_year
            HAVING COUNT(s.id) > 20
            ORDER BY student_count DESC
        """;
        
        List<GroupWithStudentCount> result = new ArrayList<>();
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                GroupWithStudentCount group = new GroupWithStudentCount(
                    rs.getLong("id"),
                    rs.getString("name"),
                    rs.getInt("class_year"),
                    rs.getInt("student_count")
                );
                result.add(group);
            }
        }
        
        return result;
    }
}

record GroupWithStudentCount(
    Long id,
    String name,
    Integer classYear,
    Integer studentCount
) {}

4. JPA/Hibernate реализация

4.1 С использованием native query

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface GroupRepository extends CrudRepository<Group, Long> {
    
    @Query(nativeQuery = true, value = """
        SELECT g.id, g.name, g.class_year, COUNT(s.id) as student_count
        FROM groups g
        LEFT JOIN students s ON g.id = s.group_id
        GROUP BY g.id, g.name, g.class_year
        HAVING COUNT(s.id) > 20
        ORDER BY student_count DESC
    """)
    List<GroupWithStudentCountDTO> findGroupsWithMoreThanTwentyStudents();
}

public interface GroupWithStudentCountDTO {
    Long getId();
    String getName();
    Integer getClassYear();
    Integer getStudentCount();
}

4.2 С использованием JPQL

import org.springframework.data.jpa.repository.Query;

public interface GroupRepository extends CrudRepository<Group, Long> {
    
    // JPQL запрос с явным JOIN и GROUP BY
    @Query("""
        SELECT new com.example.GroupWithStudentCountDTO(
            g.id, g.name, g.classYear, COUNT(s.id)
        )
        FROM Group g
        LEFT JOIN g.students s
        GROUP BY g.id, g.name, g.classYear
        HAVING COUNT(s.id) > 20
        ORDER BY COUNT(s.id) DESC
    """)
    List<GroupWithStudentCountDTO> findGroupsWithMoreThanTwentyStudents();
}

4.3 С использованием Criteria API

import javax.persistence.criteria.*;
import org.springframework.stereotype.Repository;

@Repository
public class GroupRepositoryCustom {
    
    @Autowired
    private EntityManager entityManager;
    
    public List<GroupWithStudentCountDTO> findGroupsWithMoreThanTwentyStudents() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<GroupWithStudentCountDTO> query = cb.createQuery(GroupWithStudentCountDTO.class);
        
        Root<Group> groupRoot = query.from(Group.class);
        Join<Group, Student> studentJoin = groupRoot.join("students", JoinType.LEFT);
        
        // Проекция на DTO
        query.select(cb.construct(
            GroupWithStudentCountDTO.class,
            groupRoot.get("id"),
            groupRoot.get("name"),
            groupRoot.get("classYear"),
            cb.count(studentJoin.get("id")).alias("studentCount")
        ));
        
        // GROUP BY
        query.groupBy(
            groupRoot.get("id"),
            groupRoot.get("name"),
            groupRoot.get("classYear")
        );
        
        // HAVING
        query.having(cb.gt(cb.count(studentJoin.get("id")), 20L));
        
        // ORDER BY
        query.orderBy(cb.desc(cb.count(studentJoin.get("id"))));
        
        return entityManager.createQuery(query).getResultList();
    }
}

public class GroupWithStudentCountDTO {
    private Long id;
    private String name;
    private Integer classYear;
    private Integer studentCount;
    
    public GroupWithStudentCountDTO(
            Long id, String name, Integer classYear, Long studentCount) {
        this.id = id;
        this.name = name;
        this.classYear = classYear;
        this.studentCount = studentCount.intValue();
    }
    
    // getters
}

5. Сущности JPA

import javax.persistence.*;
import java.util.List;

@Entity
@Table(name = "groups")
public class Group {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "name", nullable = false)
    private String name;
    
    @Column(name = "class_year")
    private Integer classYear;
    
    @OneToMany(mappedBy = "group", fetch = FetchType.LAZY)
    private List<Student> students;
    
    // getters, setters, constructors
}

@Entity
@Table(name = "students")
public class Student {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "name", nullable = false)
    private String name;
    
    @Column(name = "email")
    private String email;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "group_id")
    private Group group;
    
    // getters, setters, constructors
}

6. Spring Data JPA с Query DSL

import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;
import javax.persistence.EntityManager;

public class GroupRepositoryQueryDsl {
    
    private final JPAQueryFactory queryFactory;
    
    public GroupRepositoryQueryDsl(EntityManager entityManager) {
        this.queryFactory = new JPAQueryFactory(entityManager);
    }
    
    public List<GroupWithStudentCountDTO> findGroupsWithMoreThanTwentyStudents() {
        QGroup group = QGroup.group;
        QStudent student = QStudent.student;
        
        return queryFactory
            .select(Projections.constructor(
                GroupWithStudentCountDTO.class,
                group.id,
                group.name,
                group.classYear,
                student.count().as("studentCount")
            ))
            .from(group)
            .leftJoin(group.students, student)
            .groupBy(group.id, group.name, group.classYear)
            .having(student.count().gt(20L))
            .orderBy(student.count().desc())
            .fetch();
    }
}

7. Stream API (для данных в памяти)

import java.util.*;
import java.util.stream.Collectors;

public class GroupFilteringWithStreams {
    
    public List<GroupWithStudentCount> findGroupsWithMoreThanTwentyStudents(
            List<Group> allGroups) {
        
        return allGroups.stream()
            .map(group -> new GroupWithStudentCount(
                group.getId(),
                group.getName(),
                group.getClassYear(),
                group.getStudents().size()
            ))
            .filter(groupDto -> groupDto.studentCount() > 20)
            .sorted(Comparator.comparingInt(GroupWithStudentCount::studentCount).reversed())
            .collect(Collectors.toList());
    }
}

record GroupWithStudentCount(
    Long id,
    String name,
    Integer classYear,
    Integer studentCount
) {}

8. Пример REST контроллера

import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/api/v1/groups")
public class GroupController {
    
    @Autowired
    private GroupRepository groupRepository;
    
    @GetMapping("/with-large-student-count")
    public List<GroupWithStudentCountDTO> getGroupsWithMoreThanTwentyStudents() {
        // Запрос к БД
        return groupRepository.findGroupsWithMoreThanTwentyStudents();
    }
    
    // Response:
    // [
    //   {
    //     "id": 1,
    //     "name": "Group A",
    //     "classYear": 2024,
    //     "studentCount": 35
    //   },
    //   {
    //     "id": 2,
    //     "name": "Group B",
    //     "classYear": 2024,
    //     "studentCount": 28
    //   }
    // ]
}

9. Тестирование запроса

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
public class GroupRepositoryTest {
    
    @Autowired
    private GroupRepository groupRepository;
    
    @Test
    public void testFindGroupsWithMoreThanTwentyStudents() {
        // Arrange
        Group group = new Group();
        group.setName("Large Group");
        groupRepository.save(group);
        
        // Создаем 25 студентов
        for (int i = 0; i < 25; i++) {
            Student student = new Student();
            student.setName("Student " + i);
            student.setGroup(group);
            // сохраняем студента
        }
        
        // Act
        List<GroupWithStudentCountDTO> result = 
            groupRepository.findGroupsWithMoreThanTwentyStudents();
        
        // Assert
        assertFalse(result.isEmpty());
        assertTrue(result.stream()
            .anyMatch(g -> g.getStudentCount() > 20));
    }
}

Рекомендации по выбору подхода

1. Native Query — когда нужна сложная SQL, специфичная для СУБД
2. JPQL — стандартный подход, портируемый
3. Criteria API — когда запросы динамические
4. Query DSL — когда нужна типобезопасность
5. Stream API — только для фильтрации в памяти (после загрузки из БД)

Для этой задачи рекомендую JPQL с native query как оптимальный вариант.