Spring Boot @Query Example

1. Introduction

The @Query annotation in Spring Boot with Spring Data JPA allows you to define custom queries directly on repository methods. This feature facilitates executing complex queries that extend beyond the capabilities of Spring Data's standard query derivation mechanism.

Key Points

1. @Query can be used for both JPQL (Java Persistence Query Language) and native SQL queries.

2. Supports named parameters and indexed parameters in the queries.

3. Can be used for both reading and modifying data (with @Modifying for the latter).

2. Development Steps

1. Define an entity class.

3. Create a repository interface with @Query annotated methods for various use cases.

3. Code Program

// Step 1: Define the entity class
import jakarta.persistence.Entity;
import jakarta.persistence.Id;

@Entity
public class User {
    @Id
    private Long id;
    private String name;
    private String email;
    private Integer age;

    // Constructors, Getters, and Setters
}

// Step 2: Create the repository interface
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;

public interface UserRepository extends JpaRepository<User, Long> {

    // Example of a simple JPQL query
    @Query("SELECT u FROM User u WHERE u.email = ?1")
    User findByEmail(String email);

    // Example of a query using named parameters
    @Query("SELECT u FROM User u WHERE u.name = :name")
    List<User> findByName(@Param("name") String name);

    // Example of a native SQL query
    @Query(value = "SELECT * FROM user WHERE age > ?1", nativeQuery = true)
    List<User> findByAgeGreaterThan(int age);

    // Example of a modifying query
    @Modifying
    @Query("UPDATE User u SET u.email = :email WHERE u.id = :id")
    int updateUserEmail(@Param("id") Long id, @Param("email") String email);
}

Explanation:

1. The entity class User represents a simple user with attributes like id, name, email, and age, mapped to a corresponding database table.

2. UserRepository extends JpaRepository, which provides CRUD operations on the User entities. It includes custom methods annotated with @Query for different use cases:

- findByEmail uses a positional parameter to fetch a user by their email.

- findByName uses named parameters to allow for more readable code and easier maintenance.

- findByAgeGreaterThan demonstrates the use of a native SQL query when you might need to utilize database-specific features or syntax.

- updateUserEmail is annotated with both @Modifying and @Query, allowing for data modification operations directly through the repository.