Spring Data JPA @Query Annotation

author : Sai K

The @Query annotation in Spring Data JPA is used to define custom queries using JPQL (Java Persistence

Query Language) or native SQL. This annotation allows you to write complex queries and perform operations

that may not be possible with standard method naming conventions. In this tutorial, we will cover the use of the

@Query annotation in a Spring Boot 3.2 application with Spring Data JPA.


Prerequisites

  • JDK 17 or later

  • Maven or Gradle

  • IDE (IntelliJ IDEA, Eclipse, etc.)

Step 1: Set Up a Spring Boot Project

1.1 Create a New Spring Boot Project

Use Spring Initializr to create a new project with the following dependencies:

  • Spring Web

  • Spring Data JPA

  • H2 Database (or any other database of your choice)

    • Download and unzip the project, then open it in your IDE.

    1.2 Configure application.properties

    Set up the application properties for your project. This file is located in the src/main/resources directory.

    
 # src/main/resources/application.properties

# H2 Database configuration
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.datasource.platform=h2

# JPA configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

    Explanation:

    • Configures the H2 in-memory database.

    • Enables SQL logging.

    • Sets up JPA to update the database schema automatically.

    Step 2: Define the Entity Class

    2.1 Create the User Entity

    Create an entity class to represent a user in the database.

    
    package com.example.demo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    private int age;

    // Getters and setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

    Explanation:

    • @Entity: Specifies that the class is an entity and is mapped to a database table.

    • @Id and @GeneratedValue: Indicates the primary key and its generation strategy.

    Step 3: Create the Repository Interface

    3.1 Create the UserRepository

    Create a repository interface to perform CRUD operations on the User entity and define custom queries using the @Query annotation.

    
    package com.example.demo.repository;

import com.example.demo.entity.User;
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;

@Repository
public interface UserRepository extends JpaRepository {

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

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

    @Query(value = "SELECT * FROM User WHERE name = :name", nativeQuery = true)
    List findByNameNative(@Param("name") String name);
}

    Explanation:

    • @Repository: Indicates that the interface is a Spring Data repository.

    • @Query: Defines custom queries using JPQL or native SQL.

    • findByEmail: Finds a user by their email address using JPQL.

    • findUsersOlderThan: Finds users older than a specified age using JPQL.

    • findByNameNative: Finds users by their name using a native SQL query.

    Step 4: Create Service and Controller Layers

    4.1 Create the UserService

    Create a service class to handle business logic related to users.

    
    package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public List getAllUsers() {
        return userRepository.findAll();
    }

    public Optional getUserById(Long id) {
        return userRepository.findById(id);
    }

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

    public List getUsersOlderThan(int age) {
        return userRepository.findUsersOlderThan(age);
    }

    public List getUsersByName(String name) {
        return userRepository.findByNameNative(name);
    }

    public User createUser(User user) {
        return userRepository.save(user);
    }

    public void deleteUser(Long id) {
        userRepository.deleteById(id);
    }
}

    Explanation:

    • @Service: Marks the class as a service component in Spring.

    • UserRepository: Injected to interact with the database.

    4.2 Create the UserController

    Create a REST controller to expose endpoints for interacting with users.

    
    Create a REST controller to expose endpoints for interacting with users.

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping
    public List getAllUsers() {
        return userService.getAllUsers();
    }

    @GetMapping("/{id}")
    public Optional getUserById(@PathVariable Long id) {
        return userService.getUserById(id);
    }

    @GetMapping("/email")
    public User getUserByEmail(@RequestParam String email) {
        return userService.getUserByEmail(email);
    }

    @GetMapping("/older-than/{age}")
    public List getUsersOlderThan(@PathVariable int age) {
        return userService.getUsersOlderThan(age);
    }

    @GetMapping("/name")
    public List getUsersByName(@RequestParam String name) {
        return userService.getUsersByName(name);
    }

    @PostMapping
    public User createUser(@RequestBody User user) {
        return userService.createUser(user);
    }

    @DeleteMapping("/{id}")
    public void deleteUser(@PathVariable Long id) {
        userService.deleteUser(id);
    }
}

    Explanation:

    • @RestController: Marks the class as a REST controller.

    • @RequestMapping("/users"): Maps the controller to /users endpoint.

    • @GetMapping, @PostMapping, @DeleteMapping: Maps HTTP GET, POST, and DELETE requests respectively

    • @RequestBody: Binds the HTTP request body to the User parameter.

    • @PathVariable: Binds the URI template variable to the method parameter.

    • @RequestParam: Binds the query parameter to the method parameter.

    Step 5: Running and Testing the Application

    5.1 Run the Application

    Run the Spring Boot application using your IDE or the command line:

    ./mvnw spring-boot:run

    5.2 Test the Endpoints

    Use a tool like Postman or your browser to test the endpoints.

    Create a User

    • URL: http://localhost:8080/users

    • Method: POST

    • Body:

    
    {
        "name": "Ramesh Fadatare",
        "email": "ramesh.fadatare@example.com",
        "age": 30
    }

    Get All Users

    • URL: http://localhost:8080/users

    • Method: GET

    Get a User by ID

    • URL: http://localhost:8080/users/{id}

    • Method: GET

    Get a User by Email

    • URL: http://localhost:8080/users/email?email=john.doe@example.com

    • Method: GET

    Get Users Older Than a Certain Age

    • URL: http://localhost:8080/users/older-than/25

    • Method: GET

    Get Users by Name

    • URL: http://localhost:8080/users/name?name=John Doe

    • Method: GET

    Delete a User

    • URL: http://localhost:8080/users/{id}

    • Method: DELETE

    Conclusion

    In this tutorial, you have learned how to use the @Query annotation in a Spring Boot 3.2 application with Spring Data JPA to define custom queries. We covered:

    • Setting up a Spring Boot project with Spring Data JPA.

    • Defining an entity class and repository with custom queries.

    • Creating service and controller layers.

    • Running and testing the application using REST endpoints.