Query in Spring Boot - Naukri Code 360

Introduction

Spring Boot is a widely-used Java-based framework for building web applications quickly and efficiently. Queries are an essential part of working with data in Spring Boot applications, allowing developers to interact with databases seamlessly.

This article will discuss query in Spring Boot, different types of queries in Spring Boot, focusing on JPQL, Native Queries, and their practical implementations.

Example Spring Boot Project

To demonstrate query usage, let’s create a simple Spring Boot application. Assume we are building a system to manage employees. We'll use a MySQL database and JPA for database interactions.

Step 1: Create an Entity Class

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String department;
    private double salary;

    // 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 getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

Step 2: Set Up the Repository

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}

JPQL vs Native Query

JPQL (Java Persistence Query Language): JPQL is an object-oriented query language. It works with JPA entities instead of database tables and uses entity attributes in queries.

Native Query: Native queries use raw SQL syntax directly and interact with the database tables. These queries are useful for complex database operations.

JPQL Example

@Query("SELECT e FROM Employee e WHERE e.department = :department")
List<Employee> findByDepartment(@Param("department") String department);

Native Query Example

@Query(value = "SELECT * FROM employee WHERE department = :department", nativeQuery = true)
List<Employee> findByDepartmentNative(@Param("department") String department);

Spring JPA @Query Example with Spring Boot

The @Query annotation allows developers to write both JPQL and Native queries in their Spring Boot applications.

Example: Fetching Employees by Salary

@Query("SELECT e FROM Employee e WHERE e.salary > :salary")
List<Employee> findEmployeesWithSalaryGreaterThan(@Param("salary") double salary);

Explanation:

The query fetches all employees whose salary is greater than the specified value.
@Param is used to bind method parameters to query parameters.

Define Repository with JPA Custom Query Methods

Spring Data JPA enables custom query methods by following a naming convention. For example:

Example: Find by Name

List<Employee> findByName(String name);

Example: Find by Department and Salary

List<Employee> findByDepartmentAndSalaryGreaterThan(String department, double salary);

These methods are automatically implemented by Spring Data JPA.

JPA Select Query with WHERE Condition Example

Using the @Query annotation, developers can add WHERE clauses to their queries.

Example: Fetch Employees in a Specific Department

@Query("SELECT e FROM Employee e WHERE e.department = :department")
List<Employee> findEmployeesByDepartment(@Param("department") String department);

Explanation: This query fetches employees whose department matches the provided parameter.

JPA Query Order By Desc/Asc

When working with databases, sorting data is a common requirement. In Spring Boot, JPA (Java Persistence API) makes it easy to sort data using the `ORDER BY` clause. You can sort data in ascending (ASC) or descending (DESC) order based on specific columns.

Let’s say you have a `Student` entity with fields like `id`, `name`, & `marks`. You want to fetch all students sorted by their marks in descending order. Let’s see how you can do it:

Step 1: Define the Entity

First, create the `Student` entity class.

import javax.persistence.;
@Entity
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private int marks;
    // Getters & 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 int getMarks() {
        return marks;
    }

    public void setMarks(int marks) {
        this.marks = marks;
    }
}

Step 2: Create the Repository

Next, create a repository interface for the `Student` entity. Use the `@Query` annotation to write a custom query for sorting.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {

    @Query("SELECT s FROM Student s ORDER BY s.marks DESC")
    List<Student> findAllStudentsOrderByMarksDesc();
}

Step 3: Use the Repository in Service

Now, use this repository in a service class to fetch the sorted data.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    public List<Student> getAllStudentsSortedByMarks() {
        return studentRepository.findAllStudentsOrderByMarksDesc();
    }
}

Step 4: Test the Query

Finally, test the query by calling the service method.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class TestQuery implements CommandLineRunner {

    @Autowired
    private StudentService studentService;

    @Override
    public void run(String... args) throws Exception {
        List<Student> students = studentService.getAllStudentsSortedByMarks();
        for (Student student : students) {
            System.out.println(student.getName() + " - " + student.getMarks());
        }
    }
}

In this code:

The `@Query` annotation allows you to write custom SQL-like queries.
`ORDER BY s.marks DESC` sorts the students by their marks in descending order.
You can change `DESC` to `ASC` for ascending order.

JPA Query Sort By

Sorting data is a fundamental operation when working with databases. In Spring Boot, JPA provides an easy way to sort data dynamically using the `Sort` class. Unlike the `ORDER BY` clause in custom queries, the `Sort` class allows you to sort data at runtime based on specific fields.

Let’s continue with the `Student` entity example. Suppose you want to sort students by their `name` or `marks` dynamically. Let’s see how we can achieve this:

Step 1: Use Sorting in the Repository

You don’t need to write a custom query for sorting. Instead, you can use the `Sort` class provided by Spring Data JPA.

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
    // Method to sort students by a specific field
    List<Student> findAll(Sort sort);
}

Step 2: Implement Sorting in the Service

In the service layer, you can pass the sorting criteria dynamically.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    // Method to get students sorted by a specific field
    public List<Student> getAllStudentsSortedBy(String field, String direction) {
        Sort sort = Sort.by(Sort.Direction.fromString(direction), field);
        return studentRepository.findAll(sort);
    }
}

Step 3: Test the Sorting Functionality

You can test the sorting functionality by calling the service method with different sorting criteria.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
public class TestSorting implements CommandLineRunner {

    @Autowired
    private StudentService studentService;

    @Override
    public void run(String... args) throws Exception {
        // Sort by name in ascending order
        List<Student> studentsByNameAsc = studentService.getAllStudentsSortedBy("name", "ASC");
        System.out.println("Students sorted by name (ASC):");
        for (Student student : studentsByNameAsc) {
            System.out.println(student.getName() + " - " + student.getMarks());
        }
        // Sort by marks in descending order
        List<Student> studentsByMarksDesc = studentService.getAllStudentsSortedBy("marks", "DESC");
        System.out.println("Students sorted by marks (DESC):");
        for (Student student : studentsByMarksDesc) {
            System.out.println(student.getName() + " - " + student.getMarks());
        }
    }
}

In this code:

The `Sort.by()` method creates a `Sort` object based on the field & direction provided.
`Sort.Direction.fromString(direction)` converts the string "ASC" or "DESC" into a `Sort.Direction` enum.

The `findAll(Sort sort)` method in the repository fetches the data sorted according to the provided criteria.

JPA Query Pagination

When dealing with large datasets, fetching all records at once can be inefficient & slow. Pagination helps by splitting the data into smaller chunks or "pages." In Spring Boot, JPA makes pagination straightforward using the `Pageable` interface.

Let’s continue with the `Student` entity example. Suppose you want to fetch students in pages, with each page containing a specific number of records. Let’s see how you can implement pagination:

Step 1: Update the Repository

Modify the repository to support pagination by extending the `JpaRepository` & using the `Pageable` parameter.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
    // Method to fetch students with pagination
    Page<Student> findAll(Pageable pageable);
}

Step 2: Implement Pagination in the Service

In the service layer, you can specify the page number & page size to fetch the data.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    // Method to get students with pagination
    public Page<Student> getAllStudents(int page, int size) {
        PageRequest pageable = PageRequest.of(page, size);
        return studentRepository.findAll(pageable);
    }
}

Step 3: Test the Pagination Functionality

You can test the pagination by calling the service method with different page numbers & sizes.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class TestPagination implements CommandLineRunner {

    @Autowired
    private StudentService studentService;

    @Override
    public void run(String... args) throws Exception {
        // Fetch the first page with 5 records
        System.out.println("Page 1 (5 records):");
        Page<Student> page1 = studentService.getAllStudents(0, 5);
        page1.forEach(student -> System.out.println(student.getName() + " - " + student.getMarks()));

        // Fetch the second page with 5 records
        System.out.println("Page 2 (5 records):");
        Page<Student> page2 = studentService.getAllStudents(1, 5);
        page2.forEach(student -> System.out.println(student.getName() + " - " + student.getMarks()));
    }
}

In this code:

The `Pageable` interface represents a request for a specific page of data.
`PageRequest.of(page, size)` creates a `Pageable` object with the specified page number & page size.
The `findAll(Pageable pageable)` method in the repository returns a `Page` object containing the requested data & additional pagination details like total pages & total records.

Additional Features of Pagination

Total Pages: You can access the total number of pages using `page.getTotalPages()`.
Total Records: You can access the total number of records using `page.getTotalElements()`.
Next & Previous Pages: You can navigate between pages by changing the page number in the `PageRequest`.

JPA Query Update

Updating data in a database is a common operation in any application. In Spring Boot, JPA provides two ways to update records:

1. Using the `save()` method to update an existing entity.

2. Using custom update queries with the `@Modifying` & `@Query` annotations.

Method 1: Using the `save()` Method

The `save()` method in JPA can be used to update an existing entity. If the entity already exists (i.e., it has an ID), JPA will update it.

Step 1: Update the Repository

No changes are needed in the repository for this method.

Step 2: Implement the Update in the Service

In the service layer, fetch the entity, modify its fields, & save it back.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Optional;

@Service
public class StudentService {
    @Autowired
    private StudentRepository studentRepository;
    // Method to update a student's marks using save()
    public void updateStudentMarks(Long id, int newMarks) {
        Optional<Student> studentOptional = studentRepository.findById(id);
        if (studentOptional.isPresent()) {
            Student student = studentOptional.get();
            student.setMarks(newMarks);
            studentRepository.save(student);
            System.out.println("Student marks updated successfully!");
        } else {
            System.out.println("Student not found with ID: " + id);
        }
    }
}

Step 3: Test the Update Functionality

Call the service method to update a student’s marks.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
@Component
public class TestUpdate implements CommandLineRunner {

    @Autowired
    private StudentService studentService;

    @Override
    public void run(String... args) throws Exception {
        // Update marks for student with ID 1
        studentService.updateStudentMarks(1L, 95);
    }
}

In this code:

The `findById()` method fetches the entity by its ID.
The `setMarks()` method updates the marks field.
The `save()` method updates the entity in the database.

Method 2: Using Custom Update Queries

For bulk updates or updates without fetching the entity, you can use custom update queries with the `@Modifying` & `@Query` annotations.

Step 1: Update the Repository

Add a custom update query in the repository.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {
    // Custom query to update student marks
    @Modifying
    @Query("UPDATE Student s SET s.marks = :marks WHERE s.id = :id")
    void updateMarksById(Long id, int marks);
}

Step 2: Implement the Update in the Service

Call the custom query from the service layer.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class StudentService {

    @Autowired
    private StudentRepository studentRepository;

    // Method to update student marks using a custom query
    public void updateStudentMarksUsingQuery(Long id, int newMarks) {
        studentRepository.updateMarksById(id, newMarks);
        System.out.println("Student marks updated using custom query!");
    }
}

Step 3: Test the Update Functionality

Call the service method to update a student’s marks using the custom query.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class TestUpdate implements CommandLineRunner {

    @Autowired
    private StudentService studentService;

    @Override
    public void run(String... args) throws Exception {
        // Update marks for student with ID 2 using custom query
        studentService.updateStudentMarksUsingQuery(2L, 90);
    }
}

Explanation

The `@Modifying` annotation indicates that the query will modify the database.
The `@Query` annotation defines the custom update query.
The `:id` & `:marks` are placeholders for the method parameters.

Which Method to Use?

Use the `save()` method when you need to fetch & modify an entity before updating.
Use custom update queries for bulk updates or when you don’t need to fetch the entity.

JPA Query Greater Than or Equal To

This query retrieves records where a field is greater than or equal to a specific value.

Example: Fetch Employees with High Salaries

@Query("SELECT e FROM Employee e WHERE e.salary >= :salary")
List<Employee> findHighEarningEmployees(@Param("salary") double salary);

Output: If salary = 50000, the query will return all employees earning 50,000 or more.

JPA Query Between

The BETWEEN operator is useful for selecting records within a specific range.

Example: Fetch Employees with Salaries in a Range

@Query("SELECT e FROM Employee e WHERE e.salary BETWEEN :minSalary AND :maxSalary")
List<Employee> findEmployeesInSalaryRange(@Param("minSalary") double minSalary, @Param("maxSalary") double maxSalary);

Explanation: minSalary and maxSalary define the salary range. The query returns employees with salaries within this range.

Frequently Asked Questions

What is the difference between JPQL and Native Query?

JPQL is object-oriented and works with JPA entities, while Native Query uses raw SQL to interact directly with database tables.

How do I write a custom query method in Spring Data JPA?

Custom query methods can be created by following naming conventions like findByName or using the @Query annotation for complex queries.

Can I use @Query for both JPQL and Native Queries?

Yes, @Query supports both JPQL (default) and Native Queries by setting nativeQuery = true.

Conclusion

In this article, we discussed how query in Spring Boot works, including JPQL and Native Queries. We discussed creating custom query methods and implementing the @Query annotation for advanced queries. By learning these techniques, developers can effectively interact with databases in their Spring Boot applications.