Skip to main content

JPA Exercises with Query Methods

Brief Introduction and References

In this guide, you will find 50 exercises demonstrating various features of Query Methods with Spring Data JPA (derived method names) using JpaRepository.
For the official reference on creating queries from method names and the list of keywords supported by Spring Data JPA, check the official Spring Data JPA documentation.

Recommended resources:

Domain Model Specification​

Below are the entities used as reference across the exercises. You can copy them directly into your Spring Boot project and generate accessors using Lombok.

Relational Entities Specification​

Below are the Java entity definitions (JPA annotations) used across the exercises.

package com.example.demo.model;

import jakarta.persistence.*;
import java.sql.Timestamp;
import java.util.*;

@Entity
@Table(name = "departments")
public class Department {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    // getters / setters
}

@Entity
@Table(name = "instructors")
public class Instructor {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private Timestamp hireDate;
    private boolean active;

    @ManyToOne
    private Department department;
    // getters / setters
}

@Entity
@Table(name = "courses")
public class Course {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String code; // e.g. CS101
    private String title;
    private int credits;
    private Timestamp startDate;
    private boolean active;

    @Enumerated(EnumType.STRING)
    private Level level; // BEGINNER / INTERMEDIATE / ADVANCED

    @ManyToOne
    private Department department;

    @ManyToMany(mappedBy = "courses")
    private Set<Student> students = new HashSet<>();
    // getters / setters
}

@Entity
@Table(name = "students")
public class Student {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String username;
    private String email;
    private String firstName;
    private String lastName;
    private Integer age;
    private Double gpa;
    private boolean active;
    private Timestamp registrationDate;

    private String city;
    private String state;

    @ManyToMany
    @JoinTable(name = "student_course",
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id"))
    private Set<Course> courses = new HashSet<>();

    @ManyToOne
    private Instructor advisor;

    @OneToMany(mappedBy = "student")
    private List<Enrollment> enrollments = new ArrayList<>();
    // getters / setters
}

@Entity
@Table(name = "enrollments")
public class Enrollment {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    private Student student;

    @ManyToOne
    private Course course;

    @Enumerated(EnumType.STRING)
    private Semester semester; // SPRING, FALL, etc.

    private Double grade;

    @Enumerated(EnumType.STRING)
    private EnrollmentStatus status; // ENROLLED, DROPPED, COMPLETED
    // getters / setters
}

public enum Level {
    BEGINNER, INTERMEDIATE, ADVANCED
}

public enum Semester {
    SPRING, SUMMER, FALL, WINTER
}

public enum EnrollmentStatus {
    ENROLLED, DROPPED, COMPLETED
}

Note: The entities above illustrate the basic fields and relational mappings.

Query Method Exercises Using JpaRepository​

Below are practical exercises to practice creating query methods using JpaRepository. Each exercise includes:

  • A heading with the query method name.
  • A description of the requirement.
  • A <details> block with the expected solution: the method signature in the Repository and an example of usage (service or test).

findByUsername​

Requirement: Find all students whose username matches a given string.

Expected solution
public interface StudentRepository extends JpaRepository<Student, Long> {
    List<Student> findByUsername(String username);
}

Usage example:

@Autowired
private StudentRepository repo;

public List<Student> findStudentsByUsername(String username) {
    return repo.findByUsername(username);
}

findByEmailIgnoreCase​

Requirement: Search for students by email, ignoring case.

Expected solution
List<Student> findByEmailIgnoreCase(String email);

Usage:

List<Student> s = repo.findByEmailIgnoreCase("ALICE@EXAMPLE.COM");
// Finds records even if the email has different casing.

findByFirstNameAndLastName​

Requirement: Search for students by firstName and lastName.

Expected solution
List<Student> findByFirstNameAndLastName(String firstName, String lastName);

Usage:

repo.findByFirstNameAndLastName("Alice", "Lopez");

findByAgeGreaterThan​

Requirement: Find students with age greater than a value.

Expected solution
List<Student> findByAgeGreaterThan(Integer age);

Usage:

repo.findByAgeGreaterThan(21);

findByAgeGreaterThanEqual​

Requirement: Find students with age greater than or equal to a value.

Expected solution
List<Student> findByAgeGreaterThanEqual(Integer age);

findByGpaBetween​

Requirement: Find students with gpa within a given range (inclusive).

Expected solution
List<Student> findByGpaBetween(Double min, Double max);

Usage:

repo.findByGpaBetween(3.0, 4.0);

findByActiveTrue​

Requirement: Find active students (boolean field active = true).

Expected solution
List<Student> findByActiveTrue();

Usage:

repo.findByActiveTrue();

findByActiveFalse​

Requirement: Find inactive students (boolean field active = false).

Expected solution
List<Student> findByActiveFalse();

findByRegistrationDateAfter​

Requirement: Find students who registered after a given date.

Expected solution
List<Student> findByRegistrationDateAfter(Timestamp date);

Usage:

repo.findByRegistrationDateAfter(Timestamp.valueOf(LocalDateTime.of(2024, 1, 1, 0, 0)));

findByRegistrationDateBetween​

Requirement: Find students registered between two dates.

Expected solution
List<Student> findByRegistrationDateBetween(Timestamp from, Timestamp to);

Usage:

repo.findByRegistrationDateBetween(Timestamp.valueOf(LocalDateTime.of(2024,1,1,0,0)), Timestamp.valueOf(LocalDateTime.of(2024,12,31,23,59)));

findByCoursesCode​

Requirement: Find students enrolled in a course with a given code (implicit join with courses collection).

Expected solution
List<Student> findByCoursesCode(String code);

Usage:

repo.findByCoursesCode("CS101");

findByCoursesTitleContainingIgnoreCase​

Requirement: Search for students who have courses whose title contains a substring (case-insensitive).

Expected solution
List<Student> findByCoursesTitleContainingIgnoreCase(String titlePart);

Usage:

repo.findByCoursesTitleContainingIgnoreCase("data");

findDistinctByCoursesCode​

Requirement: Find distinct students (without duplicates) enrolled in course code.

Expected solution
List<Student> findDistinctByCoursesCode(String code);

Usage:

repo.findDistinctByCoursesCode("CS101");

findByAdvisorLastName​

Requirement: Find students whose advisor (ManyToOne Instructor) has a given last name.

Expected solution
List<Student> findByAdvisorLastName(String lastName);

Usage:

repo.findByAdvisorLastName("Gonzalez");

findByAdvisorId​

Requirement: Find students by their advisor ID.

Expected solution
List<Student> findByAdvisorId(Long instructorId);

Usage:

repo.findByAdvisorId(42L);

findByAdvisorIsNull​

Requirement: Find students who do not have an assigned advisor.

Expected solution
List<Student> findByAdvisorIsNull();

Usage:

repo.findByAdvisorIsNull();

existsByEmail​

Requirement: Check if a student exists with a given email.

Expected solution
boolean existsByEmail(String email);

Usage:

boolean exists = repo.existsByEmail("alice@example.com");

countByActiveTrue​

Requirement: Count how many students are active.

Expected solution
long countByActiveTrue();

Usage:

long activeStudents = repo.countByActiveTrue();

deleteByUsername​

Requirement: Delete students by username (reserved repository method: delete...).

Expected solution
void deleteByUsername(String username);

Usage:

repo.deleteByUsername("old_user");

findTop5ByOrderByGpaDesc​

Requirement: Return the top 5 students with the highest GPA (Top / OrderBy).

Expected solution
List<Student> findTop5ByOrderByGpaDesc();

Usage:

List<Student> top5 = repo.findTop5ByOrderByGpaDesc();

findFirstByOrderByRegistrationDateAsc​

Requirement: Find the first student (oldest) ordered by registration date.

Expected solution
Optional<Student> findFirstByOrderByRegistrationDateAsc();

Usage:

Optional<Student> first = repo.findFirstByOrderByRegistrationDateAsc();

findByFirstNameStartingWith​

Requirement: Search for students whose firstName starts with a given prefix.

Expected solution
List<Student> findByFirstNameStartingWith(String prefix);

Usage:

repo.findByFirstNameStartingWith("Al");

findByLastNameEndingWith​

Requirement: Search for students whose lastName ends with a given suffix.

Expected solution
List<Student> findByLastNameEndingWith(String suffix);

Usage:

repo.findByLastNameEndingWith("ez");

findByFirstNameContaining​

Requirement: Search for students whose firstName contains a substring.

Expected solution
List<Student> findByFirstNameContaining(String fragment);

Usage:

repo.findByFirstNameContaining("li");

findByEmailLike​

Requirement: Search for students by email using LIKE (use % for wildcards).

Expected solution
List<Student> findByEmailLike(String pattern);

Usage:

repo.findByEmailLike("%example.com");

findByGpaIsNull​

Requirement: Find students whose gpa is NULL.

Expected solution
List<Student> findByGpaIsNull();

findByGpaIsNotNull​

Requirement: Find students whose gpa is not NULL.

Expected solution
List<Student> findByGpaIsNotNull();

findByUsernameIn​

Requirement: Find students whose username is in a given collection (IN).

Expected solution
List<Student> findByUsernameIn(Collection<String> usernames);

Usage:

repo.findByUsernameIn(List.of("a","b","c"));

findByUsernameNotIn​

Requirement: Find students whose username is NOT in a given collection (NOT IN).

Expected solution
List<Student> findByUsernameNotIn(Collection<String> usernames);

findByUsernameNot​

Requirement: Find students whose username is NOT the provided one (NOT).

Expected solution
List<Student> findByUsernameNot(String username);

findByDepartmentName (CourseRepository)​

Requirement: In CourseRepository: search for courses by associated department name.

Expected solution
public interface CourseRepository extends JpaRepository<Course, Long> {
    List<Course> findByDepartmentName(String deptName);
}

Usage:

courseRepo.findByDepartmentName("Computer Science");

findByCreditsLessThan (CourseRepository)​

Requirement: Find courses with fewer than N credits.

Expected solution
List<Course> findByCreditsLessThan(int credits);

Usage:

courseRepo.findByCreditsLessThan(4);

findByLevelIn (CourseRepository)​

Requirement: Find courses whose level is within a collection of levels.

Expected solution
List<Course> findByLevelIn(Collection<Level> levels);

Usage:

courseRepo.findByLevelIn(List.of(Level.BEGINNER, Level.INTERMEDIATE));

findByEnrollmentsGradeGreaterThan​

Requirement: Find students who have an enrollment (enrollments) with a grade greater than X.

Expected solution
List<Student> findByEnrollmentsGradeGreaterThan(Double grade);

Usage:

repo.findByEnrollmentsGradeGreaterThan(85.0);

findByEnrollmentsStatus​

Requirement: Find students according to the status of any of their enrollments.

Expected solution
List<Student> findByEnrollmentsStatus(EnrollmentStatus status);

Usage:

repo.findByEnrollmentsStatus(EnrollmentStatus.COMPLETED);

findByEnrollmentsSemesterAndCourseCode​

Requirement: Find students enrolled in a specific course during a concrete semester.

Expected solution
List<Student> findByEnrollmentsSemesterAndEnrollmentsCourseCode(Semester semester, String courseCode);

Usage:

repo.findByEnrollmentsSemesterAndEnrollmentsCourseCode(Semester.FALL, "CS101");

findByCoursesLevelAndCreditsGreaterThan​

Requirement: Find students who have at least one course with a given level and more than N credits.

Expected solution
List<Student> findByCoursesLevelAndCoursesCreditsGreaterThan(Level level, int credits);

Usage:

repo.findByCoursesLevelAndCoursesCreditsGreaterThan(Level.ADVANCED, 3);

findByActiveTrue (paginated)​

Requirement: Pagination: return active students using Pageable.

Expected solution
Page<Student> findByActiveTrue(Pageable pageable);

Usage:

Page<Student> page = repo.findByActiveTrue(PageRequest.of(0, 20, Sort.by("gpa").descending()));

findByCoursesStartDateBefore​

Requirement: Find students who have courses whose startDate is before a given date.

Expected solution
List<Student> findByCoursesStartDateBefore(Timestamp date);

Usage:

repo.findByCoursesStartDateBefore(Timestamp.valueOf(LocalDateTime.now()));

findByFirstNameIgnoreCaseAndLastNameIgnoreCase​

Requirement: Search comparing firstName and lastName ignoring case.

Expected solution
List<Student> findByFirstNameIgnoreCaseAndLastNameIgnoreCase(String first, String last);

findDistinctByFirstNameAndLastName​

Requirement: Return distinct results when filtering by first and last name.

Expected solution
List<Student> findDistinctByFirstNameAndLastName(String first, String last);

findByEmailEndingWith​

Requirement: Search for students whose email ends with a given string.

Expected solution
List<Student> findByEmailEndingWith(String suffix);
// Ej: findByEmailEndingWith("@gmail.com")

findByFirstNameOrderByLastNameAsc​

Requirement: Sort: return students with a given firstName ordered by lastName ascending.

Expected solution
List<Student> findByFirstNameOrderByLastNameAsc(String firstName);

findByLastNameOrderByFirstNameDesc​

Requirement: Sort by firstName descending.

Expected solution
List<Student> findByLastNameOrderByFirstNameDesc(String lastName);

findByCoursesCodeOrderByCreditsDesc​

Requirement: Search students by course code and sort by credits (descending).

Expected solution
List<Student> findByCoursesCodeOrderByCoursesCreditsDesc(String courseCode);

Usage:

repo.findByCoursesCodeOrderByCoursesCreditsDesc("CS101");

streamByActiveTrue​

Requirement: Return a Stream of active students (useful for processing large result sets).

Expected solution
Stream<Student> streamByActiveTrue();

Uso (recordar cerrar el stream si es necesario):

try (Stream<Student> s = repo.streamByActiveTrue()) {
    s.forEach(...);
}

findTopByOrderByGpaAsc​

Requirement: Return the student with lowest GPA.

Expected solution
Optional<Student> findTopByOrderByGpaAsc();

Usage:

Optional<Student> worst = repo.findTopByOrderByGpaAsc();

findByRegistrationDateYear (ejemplo con Between)​

Requirement: Search students registered in a given year using date range Between.

Expected solution
List<Student> findByRegistrationDateBetween(Timestamp startOfYear, Timestamp endOfYear);
// Ejemplo de llamada:
// repo.findByRegistrationDateBetween(Timestamp.valueOf(LocalDateTime.of(2024,1,1,0,0)), Timestamp.valueOf(LocalDateTime.of(2024,12,31,23,59)));

findByFirstNameNot​

Requirement: Find students whose firstName is not the given one.

Expected solution
List<Student> findByFirstNameNot(String name);

findByEmailContainingIgnoreCaseAndActiveTrue​

Requirement: Complex combination: email contains X (ignoring case) and active = true.

Expected solution
List<Student> findByEmailContainingIgnoreCaseAndActiveTrue(String fragment);

Usage:

repo.findByEmailContainingIgnoreCaseAndActiveTrue("example");

findDistinctByCoursesDepartmentName​

Requirement: Return distinct students enrolled in courses of a department with a given name.

Expected solution
List<Student> findDistinctByCoursesDepartmentName(String deptName);

Usage:

repo.findDistinctByCoursesDepartmentName("Mathematics");

findByEnrollmentsCourseCodeAndEnrollmentsStatus​

Requirement: Find students by course code and enrollment status.

Expected solution
List<Student> findByEnrollmentsCourseCodeAndEnrollmentsStatus(String courseCode, EnrollmentStatus status);

Usage:

repo.findByEnrollmentsCourseCodeAndEnrollmentsStatus("CS101", EnrollmentStatus.ENROLLED);

existsByUsername​

Requirement: Check existence by username.

Expected solution
boolean existsByUsername(String username);

Usage:

boolean e = repo.existsByUsername("kevin");

deleteAllByActiveFalse​

Requirement: Delete all inactive students (reserved repository method: deleteAllBy...).

Expected solution
void deleteAllByActiveFalse();

Usage:

repo.deleteAllByActiveFalse();

findByFirstNameOrLastName​

Requirement: Search students whose firstName OR lastName matches parameters.

Expected solution
List<Student> findByFirstNameOrLastName(String first, String last);

Usage:

repo.findByFirstNameOrLastName("Carlos","Gomez");

findByFirstNameNotContaining​

Requirement: Find students whose firstName does NOT contain a substring.

Expected solution
List<Student> findByFirstNameNotContaining(String fragment);

Usage:

repo.findByFirstNameNotContaining("test");

Final Notes​

  • Many keywords supported by Spring Data JPA (such as And, Or, Between, LessThan, GreaterThan, Like, OrderBy, Distinct, Top, First, IgnoreCase, IsNull, IsNotNull, etc.) allow expressing powerful conditions without writing explicit JPQL.
  • When derived expressions become overly complex (multiple joins, subqueries, dynamic criteria), consider using @Query with JPQL/SQL, QueryDSL, or Specification/Criteria API.
  • For pagination and sorting options, prefer using Pageable and Sort in method parameters instead of writing verbose OrderBy method names.