NUHMAN.com

When working with Spring Data JPA, you often rely on JPQL (Java Persistence Query Language) to interact with your database. However, there are cases where you might need to execute a native SQL query for complex database operations or to leverage specific database features. This can be done easily using the @Query annotation in Spring Data JPA by setting the nativeQuery attribute to true.

In this guide, we'll walk through how to execute native SQL queries in JPA repositories, and we'll show you how to integrate it seamlessly into your Spring Boot application.

🛠 Setting Up Your Entity

Let's start by creating a simple Employee entity for this example. This entity will represent employees in our system and will include fields like id, name, and department.

Step 1: Define the Entity

java

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

@Entity
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;

    // getters and setters
}

📦 Create the Repository Interface

Once the entity is ready, you need to create a JPA repository interface for the Employee entity. In this interface, we’ll use the @Query annotation to execute a native SQL query.

Step 2: Define the Repository

java

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 EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "SELECT * FROM Employee e WHERE e.department = :department", nativeQuery = true)
    List<Employee> findEmployeesByDepartment(@Param("department") String department);
}

In this repository:

• The @Query annotation contains the native SQL query (SELECT * FROM Employee e WHERE e.department = :department).
• The nativeQuery = true flag ensures that Spring Data JPA treats the query as a native SQL query.
• The @Param annotation binds the department parameter to the query.

💡 Service Layer

Next, you will create a service that uses this repository method. This service will contain the logic to call the repository method and return the result.

Step 3: Define the Service

java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    public List<Employee> getEmployeesByDepartment(String department) {
        return employeeRepository.findEmployeesByDepartment(department);
    }
}

🚀 Main Application

Finally, you can use the service in your main application to test and run the query. In this case, we will run the query when the application starts up.

Step 4: Main Application Class

java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class MyApplication implements CommandLineRunner {

    @Autowired
    private EmployeeService employeeService;

    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        // Fetching employees by department (e.g., "IT")
        List<Employee> employees = employeeService.getEmployeesByDepartment("IT");
        employees.forEach(employee -> System.out.println(employee.getName()));
    }
}

Here:

• The CommandLineRunner interface is implemented to run the logic when the application starts.
• We call employeeService.getEmployeesByDepartment("IT") to fetch employees who belong to the "IT" department and print their names.

🔑 Key Points

Native SQL Query:

• The @Query annotation in Spring Data JPA allows you to execute custom SQL queries.
• By setting nativeQuery = true, you indicate that the query is a native SQL query, not a JPQL query.

Benefits:

• Flexibility: Sometimes, you may need to execute complex queries that are hard or impossible to express with JPQL.
• Performance: Native SQL queries can take full advantage of database-specific optimizations.

When to Use:

• When you need to execute database-specific operations or use complex SQL features.
• When performance is critical, and native SQL queries provide a more efficient solution than JPQL.

💬 Summary

In this post, we covered how to execute native SQL queries using the @Query annotation in Spring Data JPA. This method allows you to write custom SQL directly in your repository, bypassing the abstraction layer that JPA provides with JPQL.

Steps:

1. Define the JPA entity.
2. Create a repository with native SQL queries using @Query.
3. Use the repository in a service to fetch or modify data.
4. Call the service in your main application.