How to Use @Query Annotation in Spring Boot for Custom Queries

In Spring Boot, use the @Query annotation on repository methods to define custom database queries using JPQL or native SQL. This allows you to write specific queries directly in your repository interface instead of relying on method name conventions.

Syntax

The @Query annotation is placed above a repository method to specify a custom query. You can write JPQL (Java Persistence Query Language) or native SQL by setting the nativeQuery flag.

• @Query("JPQL query"): Defines a JPQL query.
• @Query(value = "SQL query", nativeQuery = true): Defines a native SQL query.
• Method parameters can be referenced using :paramName syntax.

java

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);

    @Query(value = "SELECT * FROM users WHERE status = :status", nativeQuery = true)
    List<User> findByStatusNative(@Param("status") String status);
}

Example

This example shows a Spring Boot repository using @Query to find users by their email and by status with a native SQL query.

java

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

public interface UserRepository extends JpaRepository<User, Long> {

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

    @Query(value = "SELECT * FROM users WHERE status = :status", nativeQuery = true)
    List<User> findByStatusNative(@Param("status") String status);
}

// Usage in a service class
@Service
public class UserService {
    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public User getUserByEmail(String email) {
        return userRepository.findByEmail(email);
    }

    public List<User> getUsersByStatus(String status) {
        return userRepository.findByStatusNative(status);
    }
}

Output

When calling getUserByEmail("[e-mail]"), it returns the User entity with that email.
When calling getUsersByStatus("ACTIVE"), it returns a list of users with status 'ACTIVE'.

Common Pitfalls

• Forgetting to use @Param annotation on method parameters causes errors because Spring cannot bind parameters.
• Writing incorrect JPQL syntax or using table names instead of entity names leads to runtime exceptions.
• Not setting nativeQuery = true when using SQL causes query failures.
• Using @Query unnecessarily when method name queries suffice can make code harder to maintain.

java

public interface UserRepository extends JpaRepository<User, Long> {
    // Wrong: missing @Param annotation
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(String email);

    // Right: with @Param
    @Query("SELECT u FROM User u WHERE u.email = :email")
    User findByEmail(@Param("email") String email);
}

Quick Reference

Feature	Description	Example
JPQL Query	Use entity names and fields in queries	@Query("SELECT u FROM User u WHERE u.name = :name")
Native SQL	Use actual database table and column names	@Query(value = "SELECT * FROM users WHERE age > :age", nativeQuery = true)
Parameter Binding	Use @Param to bind method parameters	@Query("... WHERE u.email = :email") with @Param("email")
Return Types	Supports entity, List, Optional, etc.	User, List, Optional
Modifying Queries	Use @Modifying for update/delete queries	@Modifying @Query("UPDATE User u SET u.active = false WHERE u.id = :id")

Key Takeaways

Use @Query to write custom JPQL or native SQL queries in Spring Data repositories.

Always annotate method parameters with @Param to bind query parameters correctly.

Set nativeQuery = true when writing native SQL queries.

Use JPQL with entity names, not database table names, unless nativeQuery is true.

Avoid @Query if method name query derivation can handle the query for simplicity.