@Query for custom JPQL in Spring Boot

Introduction

The @Query annotation lets you write your own database queries in Java code. It helps when you want to get data in a special way that the usual methods can't do.

You want to find records using a complex condition not covered by method names.

You need to join multiple tables and fetch specific fields.

You want to write a query with sorting or grouping that is not automatic.

You want to update or delete records with a custom query.

You want to optimize performance by writing a precise query.

Syntax

Spring Boot

@Query("JPQL query here")
ReturnType methodName(Parameters);

JPQL is like SQL but works with Java entity names and fields, not table names.

Place @Query above repository interface methods.

Examples

Find a user by email using a custom JPQL query.

Spring Boot

@Query("SELECT u FROM User u WHERE u.email = ?1")
User findByEmail(String email);

Find users older than a certain age using a named parameter.

Spring Boot

@Query("SELECT u FROM User u WHERE u.age > :age")
List<User> findUsersOlderThan(@Param("age") int age);

Update users' status with a custom update query. Requires @Modifying.

Spring Boot

@Query("UPDATE User u SET u.status = 'ACTIVE' WHERE u.lastLogin < :date")
@Modifying
int activateOldUsers(@Param("date") LocalDate date);

Sample Program

This example shows a repository method using @Query to find users older than a given age. It uses a named parameter :age and returns a list of users.

Spring Boot

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

@Entity
public class User {
    @Id
    private Long id;
    private String name;
    private String email;
    private int age;
    // getters and setters
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.age > :age")
    List<User> findUsersOlderThan(@Param("age") int age);
}

// In a service or test class:
// List<User> users = userRepository.findUsersOlderThan(30);
// This returns all users older than 30 years.

Important Notes

Always use entity names and field names in JPQL, not database table or column names.

For update or delete queries, add @Modifying annotation and run inside a transaction.

Use @Param to name parameters clearly and avoid confusion.

Summary

@Query lets you write custom JPQL queries in Spring Data repositories.

Use it when method names can't express the query you want.

Remember to use entity names and parameters properly for clear and working queries.