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:
- Spring Data JPA — JPA Query Methods (reference)
- Spring Data JPA — Repository query keywords (list of supported keywords)
- Baeldung — Spring Data JPA Query Methods
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 theRepositoryand 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
@Querywith JPQL/SQL, QueryDSL, or Specification/Criteria API. - For pagination and sorting options, prefer using
PageableandSortin method parameters instead of writing verboseOrderBymethod names.