Retrieving student course count data in Spring Boot using JPA query

Courses
1

I’m trying to get a list of students with their course counts using Spring Boot and JPA. Here’s what I want:

Student | Courses
--------|--------
John    | 5
Jenny   | 8

I need this as JSON:

[
  {
    "student_name": "John",
    "course_count": 5
  },
  {
    "student_name": "Jenny",
    "course_count": 8
  }
]

I made a query in my repo:

@Query(value = "SELECT COUNT(*), s.name FROM students s JOIN courses c ON c.student_id = s.id GROUP BY s.name", nativeQuery = true)
List<LearnerStats> getCourseCountPerStudent();

My DTO:

@Data
@NoArgsConstructor
public class LearnerStats {
    Integer courseCount;
    String learnerName;
}

Service and controller are set up, but when I test in Postman, I get an empty array. Any ideas how to fix this?

2

hey there, i think ur query might be off. try switching the order of COUNT(*) and s.name in ur SELECT statement. also, make sure the column names in ur DTO match the query output exactly. if that doesnt work, double check ur database has data and the table/column names are correct. good luck!

3

I’ve encountered similar issues before. Your approach is on the right track, but there are a few tweaks that might help. First, ensure your DTO field names match the query result exactly. Try this query instead:

@Query(value = "SELECT s.name as learnerName, COUNT(*) as courseCount FROM students s JOIN courses c ON c.student_id = s.id GROUP BY s.name", nativeQuery = true)
List<LearnerStats> getCourseCountPerStudent();

This aligns the query output with your DTO structure. Also, verify that your database contains the expected data and that the table relationships are set up correctly. If you’re still getting an empty array, consider adding some logging or debugging statements in your service layer to pinpoint where the data might be getting lost.

4

Hey Ethan85! That’s an interesting challenge you’ve got there. I’m curious about a few things:

Have you tried running the SQL query directly in your database to see if it returns the expected results? Sometimes it helps to isolate whether the issue is with the query itself or how Spring is handling it.

Also, I wonder if you’ve considered using Spring Data JPA’s projection feature instead of a native query? It might simplify things a bit. Something like:

interface LearnerStats {
    String getLearnerName();
    Integer getCourseCount();
}

@Repository
interface StudentRepository extends JpaRepository<Student, Long> {
    @Query("SELECT s.name as learnerName, COUNT(c) as courseCount FROM Student s LEFT JOIN s.courses c GROUP BY s.name")
    List<LearnerStats> getCourseCountPerStudent();
}

This approach could potentially be more type-safe and easier to maintain. What do you think about trying this out?

By the way, how are you planning to use this data in your application? Are you building some kind of dashboard or report for students?