How to Use Custom Query in Spring Boot: Simple Guide

In Spring Boot, you can use the @Query annotation in your repository interface to define custom database queries using JPQL or native SQL. This allows you to write specific queries beyond the standard method names, giving you more control over data retrieval.

Syntax

The @Query annotation is placed above a repository method to define a custom query. You can write JPQL (Java Persistence Query Language) or native SQL by setting nativeQuery = true. Parameters are passed using : followed by the parameter name.

• @Query("JPQL or SQL query"): Defines the query string.
• nativeQuery = true: Optional, specifies native SQL instead of JPQL.
• Method parameters: Use @Param to bind method parameters to query parameters.

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 with two custom queries: one using JPQL to find a user by email, and another using native SQL to find users by status.

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 User entities with status ACTIVE.

Common Pitfalls

• Forgetting to use @Param annotation when using named parameters causes errors.
• Mixing JPQL and native SQL syntax without setting nativeQuery = true leads to query failures.
• Using incorrect entity or table names in queries causes runtime exceptions.
• Not matching method parameter names with query parameter names causes binding issues.

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); // This will cause an error

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

Quick Reference

Use this quick guide to remember how to write custom queries in Spring Boot:

Feature	Usage
Define JPQL query	@Query("SELECT u FROM User u WHERE u.name = :name")
Define native SQL query	@Query(value = "SELECT * FROM users WHERE age > :age", nativeQuery = true)
Bind parameter	Use @Param("paramName") on method arguments
Return types	Entity, List, Optional, or projections
Use in interface	Place @Query above repository interface methods

Key Takeaways

Use @Query annotation in repository interfaces to write custom JPQL or native SQL queries.

Always use @Param to bind method parameters to query parameters when using named parameters.

Set nativeQuery = true to run native SQL instead of JPQL.

Ensure query syntax matches the query type (JPQL vs SQL) and entity/table names.

Custom queries allow flexible and precise data retrieval beyond method name conventions.