Spring Boot @Query Annotation | Custom Queries in Spring Data JPA

🚀 Introduction: What is the @Query Annotation in Spring Data JPA?

The @Query annotation in Spring Data JPA allows you to define custom database queries using JPQL (Java Persistence Query Language) or native SQL. It is useful when query methods (findByName, findByEmail) are not enough to meet complex requirements.

✅ Key Features of @Query:
✔ Supports JPQL (Object-oriented queries) and native SQL queries.
✔ Allows dynamic query execution with parameters.
✔ Works with named parameters (:param) and positional parameters (?1).
✔ Improves performance by executing optimized queries.

📌 In this guide, you’ll learn:
✅ How to use @Query for JPQL and native SQL queries.
✅ How to use named and positional parameters in queries.
✅ Best practices for writing efficient queries.

1️⃣ Using @Query with JPQL (Java Persistence Query Language)

JPQL queries work with entity objects instead of table names.

📌 Example: Fetch Users by Name Using JPQL

1. Entity (User.java)

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    
    // Getters and Setters
}

2. Repository (UserRepository.java)

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u WHERE u.name = ?1")
    List<User> findUsersByName(String name);
}

📌 JPQL Query Explanation:
✔ "SELECT u FROM User u" → User is an entity, not a table name.
✔ "WHERE u.name = ?1" → ?1 refers to the first method parameter.

📌 Usage in Service Layer:

List<User> users = userRepository.findUsersByName("Ramesh");

📌 Generated SQL Query (Behind the Scenes)

SELECT * FROM users WHERE name = 'Ramesh';

✅ Spring Data JPA automatically converts JPQL into SQL queries.

2️⃣ Using Named Parameters in @Query

📌 Example: Using Named Parameters (:paramName)

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

📌 Usage:

User user = userRepository.findUserByEmail("ramesh@example.com");

📌 Generated SQL:

SELECT * FROM users WHERE email = 'ramesh@example.com';

✅ Named parameters improve readability and avoid positional errors.

3️⃣ Using @Query with Native SQL Queries

By default, @Query uses JPQL. If you need raw SQL queries, set nativeQuery = true.

📌 Example: Using Native SQL Query

@Query(value = "SELECT * FROM users WHERE email = ?1", nativeQuery = true)
User findUserByEmailNative(String email);

📌 Generated SQL:

SELECT * FROM users WHERE email = 'ramesh@example.com';

✅ Use native queries when JPQL doesn't support database-specific features.

4️⃣ Using Native Queries with Named Parameters

📌 Example: Native Query with Named Parameters

@Query(value = "SELECT * FROM users WHERE name = :name AND email = :email", nativeQuery = true)
User findUserByNameAndEmail(@Param("name") String name, @Param("email") String email);

📌 Usage:

User user = userRepository.findUserByNameAndEmail("Ramesh", "ramesh@example.com");

📌 Generated SQL:

SELECT * FROM users WHERE name = 'Ramesh' AND email = 'ramesh@example.com';

✅ Ensures readability and prevents SQL injection.

5️⃣ Using @Query for Partial Data (DTO Projection)

If you need to return only selected fields, create a DTO (Data Transfer Object).

📌 Example: Fetch Only Name and Email (Using DTO Projection)

1. DTO (UserDTO.java)

public record UserDTO(String name, String email) {}

2. Repository (UserRepository.java)

@Query("SELECT new com.example.dto.UserDTO(u.name, u.email) FROM User u")
List<UserDTO> findAllUsersAsDTO();

📌 Usage:

List<UserDTO> users = userRepository.findAllUsersAsDTO();

📌 Generated SQL:

SELECT name, email FROM users;

✅ Improves performance by fetching only required fields.

6️⃣ Using @Modifying with @Query for Update/Delete Queries

Use @Modifying for update and delete queries.

📌 Example: Updating a User’s Email

@Modifying
@Query("UPDATE User u SET u.email = :email WHERE u.id = :id")
int updateUserEmail(@Param("id") Long id, @Param("email") String email);

📌 Usage:

int updatedRows = userRepository.updateUserEmail(1L, "new.email@example.com");

📌 Generated SQL:

UPDATE users SET email = 'new.email@example.com' WHERE id = 1;

✅ Returns the number of affected rows.

7️⃣ Using @Query with Pagination (Pageable)

📌 Example: Fetch Users with Pagination

@Query("SELECT u FROM User u WHERE u.name LIKE %:name%")
Page<User> searchUsersByName(@Param("name") String name, Pageable pageable);

📌 Usage:

Page<User> users = userRepository.searchUsersByName("Ram", PageRequest.of(0, 5));

📌 Generated SQL:

SELECT * FROM users WHERE name LIKE '%Ram%' LIMIT 5 OFFSET 0;

✅ Optimizes performance by fetching paginated results.

🎯 Summary: Best Practices for Using @Query

✅ Use JPQL for object-oriented queries (SELECT u FROM User u).
✅ Use native queries (nativeQuery = true) for database-specific features.
✅ Use named parameters (:paramName) for better readability.
✅ Use DTO projections for performance optimization.
✅ Use @Modifying for UPDATE and DELETE queries.
✅ Use pagination (Pageable) for large datasets.

🚀 Following these best practices ensures efficient, maintainable queries in Spring Data JPA!