SpringTutorials
    Back to Master Spring Data JPA
    Topic 3

    Query Methods

    Create custom queries using method naming conventions, @Query annotation, and derived query methods

    Derived Query Methods

    Spring Data JPA can automatically generate queries based on method names. The framework parses the method name and creates the appropriate query. This is called query derivation.

    Subject Keywords

    find...By, read...By, query...By, count...By, get...By, exists...By, delete...By

    Predicate Keywords

    And, Or, Is, Equals, Between, LessThan, GreaterThan, Like, In, OrderBy

    UserRepository.java - Derived Query Methods
    publicinterfaceUserRepositoryextendsJpaRepository<User,Long>{// Find by single propertyList<User>findByEmail(String email);List<User>findByLastName(String lastName);Optional<User>findByUsername(String username);// Find by multiple propertiesList<User>findByFirstNameAndLastName(String firstName,String lastName);List<User>findByFirstNameOrLastName(String firstName,String lastName);// Comparison operatorsList<User>findByAgeGreaterThan(int age);List<User>findByAgeLessThanEqual(int age);List<User>findByAgeBetween(int startAge,int endAge);// String matchingList<User>findByEmailContaining(String keyword);List<User>findByEmailStartingWith(String prefix);List<User>findByEmailEndingWith(String suffix);List<User>findByFirstNameIgnoreCase(String firstName);// Null checksList<User>findByMiddleNameIsNull();List<User>findByMiddleNameIsNotNull();// Boolean checksList<User>findByActiveTrue();List<User>findByActiveFalse();// Collection operationsList<User>findByAgeIn(Collection<Integer> ages);List<User>findByAgeNotIn(Collection<Integer> ages);// OrderingList<User>findByLastNameOrderByFirstNameAsc(String lastName);List<User>findByActiveOrderByCreatedAtDesc(boolean active);// Limiting resultsUserfindFirstByOrderByCreatedAtDesc();List<User>findTop5ByActiveOrderByCreatedAtDesc(boolean active);// Count and existslongcountByActive(boolean active);booleanexistsByEmail(String email);// DeletevoiddeleteByEmail(String email);longdeleteByActiveFalse();}

    @Query Annotation

    For more complex queries that can't be expressed through method naming, use the @Query annotation with JPQL (Java Persistence Query Language) or native SQL.

    Custom Queries with @Query
    publicinterfaceUserRepositoryextendsJpaRepository<User,Long>{// JPQL query (uses entity names and properties)@Query("SELECT u FROM User u WHERE u.email = ?1")UserfindByEmailAddress(String email);// Named parameters (recommended)@Query("SELECT u FROM User u WHERE u.firstName = :firstName AND u.lastName = :lastName")List<User>findByFullName(@Param("firstName")String firstName,@Param("lastName")String lastName);// JPQL with LIKE@Query("SELECT u FROM User u WHERE u.email LIKE %:keyword%")List<User>searchByEmail(@Param("keyword")String keyword);// JPQL JOIN query@Query("SELECT u FROM User u JOIN u.roles r WHERE r.name = :roleName")List<User>findByRoleName(@Param("roleName")String roleName);// JPQL with ORDER BY@Query("SELECT u FROM User u WHERE u.active = true ORDER BY u.createdAt DESC")List<User>findActiveUsersOrderByRecent();// Native SQL query (uses table/column names)@Query(value ="SELECT * FROM users WHERE status = ?1", nativeQuery =true)List<User>findByStatusNative(String status);// Native query with named parameters@Query(value ="SELECT * FROM users WHERE email LIKE :pattern", nativeQuery =true)List<User>searchEmailNative(@Param("pattern")String pattern);// Projection - return specific fields@Query("SELECT u.firstName, u.lastName FROM User u WHERE u.active = true")List<Object[]>findActiveUserNames();// Update query@Modifying@Transactional@Query("UPDATE User u SET u.active = false WHERE u.lastLogin < :date")intdeactivateInactiveUsers(@Param("date")LocalDateTime date);// Delete query@Modifying@Transactional@Query("DELETE FROM User u WHERE u.email = :email")voiddeleteByEmailCustom(@Param("email")String email);}

    Important!

    For @Modifying queries (UPDATE, DELETE), you must also add @Transactional annotation. The method returns the number of affected rows.

    Pagination and Sorting

    Spring Data JPA provides built-in support for pagination and sorting using Pageable and Sort objects.

    Repository with Pagination
    publicinterfaceUserRepositoryextendsJpaRepository<User,Long>{// Pageable returns Page<User> with metadataPage<User>findByActive(boolean active,Pageable pageable);// Slice is lighter - doesn't count totalSlice<User>findByLastName(String lastName,Pageable pageable);// List with pagination (no metadata)List<User>findByFirstName(String firstName,Pageable pageable);// With Sort parameterList<User>findByActive(boolean active,Sort sort);// Combined with @Query@Query("SELECT u FROM User u WHERE u.department = :dept")Page<User>findByDepartment(@Param("dept")String department,Pageable pageable);}
    Using Pagination in Service
    @ServicepublicclassUserService{@AutowiredprivateUserRepository userRepository;publicPage<User>getActiveUsers(int page,int size){// Create Pageable object (page is zero-indexed)Pageable pageable =PageRequest.of(page, size);return userRepository.findByActive(true, pageable);}publicPage<User>getActiveUsersSorted(int page,int size){// With sortingPageable pageable =PageRequest.of(page, size,Sort.by("createdAt").descending());return userRepository.findByActive(true, pageable);}publicPage<User>getUsersWithMultipleSorts(int page,int size){// Multiple sort criteriaSort sort =Sort.by(Sort.Order.desc("active"),Sort.Order.asc("lastName"),Sort.Order.asc("firstName"));Pageable pageable =PageRequest.of(page, size, sort);return userRepository.findAll(pageable);}publicList<User>getUsersSortedOnly(){// Just sorting, no paginationSort sort =Sort.by(Sort.Direction.ASC,"lastName");return userRepository.findByActive(true, sort);}}// Using Page objectPage<User> page = userService.getActiveUsers(0,10);List<User> users = page.getContent();// The actual dataint totalPages = page.getTotalPages();// Total number of pageslong totalElements = page.getTotalElements();// Total number of recordsint currentPage = page.getNumber();// Current page number (0-indexed)int pageSize = page.getSize();// Page sizeboolean hasNext = page.hasNext();// Has more pages?boolean hasPrevious = page.hasPrevious();// Has previous pages?

    Query Keywords Reference

    Complete reference of supported keywords for derived query methods:

    KeywordSampleJPQL Equivalent
    AndfindByFirstNameAndLastName... WHERE x.firstName = ?1 AND x.lastName = ?2
    OrfindByFirstNameOrLastName... WHERE x.firstName = ?1 OR x.lastName = ?2
    BetweenfindByAgeBetween... WHERE x.age BETWEEN ?1 AND ?2
    LessThanfindByAgeLessThan... WHERE x.age < ?1
    GreaterThanEqualfindByAgeGreaterThanEqual... WHERE x.age >= ?1
    LikefindByNameLike... WHERE x.name LIKE ?1
    ContainingfindByNameContaining... WHERE x.name LIKE %?1%
    InfindByAgeIn... WHERE x.age IN ?1
    True / FalsefindByActiveTrue... WHERE x.active = TRUE
    IsNull / IsNotNullfindByNameIsNull... WHERE x.name IS NULL
    OrderByfindByNameOrderByAgeDesc... WHERE x.name = ?1 ORDER BY x.age DESC

    Best Practices

    Use Named Parameters

    Prefer :paramName over positional parameters ?1 for better readability and maintainability.

    Return Optional for Single Results

    Use Optional<Entity> when expecting 0 or 1 result to handle null cases gracefully.

    Use Projections for Partial Data

    When you only need specific fields, use interface or class-based projections to improve performance.

    Add Indexes for Query Fields

    Ensure database indexes exist on columns frequently used in WHERE clauses for optimal query performance.

    💬 Comments & Discussion