A Complete Guide For CRUD Operations Using Spring JPA with Native Queries

Spring Data JPA provides an abstraction over traditional JPA, making database interactions easier. While JPA’s derived and JPQL queries are powerful, sometimes we need to execute raw SQL queries for better performance or complex operations. This is where native queries come into play.

Why Use Native Queries?

When JPQL is not powerful enough to perform complex operations.
To utilize database-specific SQL features.
To optimize performance for large datasets.

Example: Using Native Queries in Spring JPA

Step 1: Setup Spring Boot with JPA

Ensure that you have the necessary dependencies in your pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Step 2: Define an Entity

Create an entity class to map to the database table.

import jakarta.persistence.*;

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

    // Getters and Setters
}

Step 3: Define the Repository Interface

Use @Query with the nativeQuery = true flag to execute raw SQL queries.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    @Query(value = "SELECT * FROM employees WHERE department = :dept", nativeQuery = true)
    List<Employee> findByDepartment(@Param("dept") String department);
    
    @Query(value = "SELECT COUNT(*) FROM employees", nativeQuery = true)
    int getTotalEmployees();
    
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO employees (name, department, salary) VALUES (:name, :department, :salary)", nativeQuery = true)
    int createEmployee(@Param("name") String name, @Param("department") String department, @Param("salary") double salary);
    
    @Modifying
    @Transactional
    @Query(value = "UPDATE employees SET salary = :salary WHERE id = :id", nativeQuery = true)
    int updateEmployeeSalary(@Param("id") Long id, @Param("salary") double salary);
    
    @Modifying
    @Transactional
    @Query(value = "DELETE FROM employees WHERE id = :id", nativeQuery = true)
    int deleteEmployeeById(@Param("id") Long id);
}

Step 4: Implement Service Layer

Create a service class to use the repository.

import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class EmployeeService {
    private final EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

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

    public int getTotalEmployees() {
        return employeeRepository.getTotalEmployees();
    }
    
    public int createEmployee(String name, String department, double salary) {
        return employeeRepository.createEmployee(name, department, salary);
    }
    
    public int updateSalary(Long id, double salary) {
        return employeeRepository.updateEmployeeSalary(id, salary);
    }
    
    public int deleteEmployee(Long id) {
        return employeeRepository.deleteEmployeeById(id);
    }
}

Step 5: Create a REST Controller

Expose the service via a REST API.

import org.springframework.web.bind.annotation.*;
import java.util.List;

@RestController
@RequestMapping("/employees")
public class EmployeeController {
    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @GetMapping("/department/{dept}")
    public List<Employee> getByDepartment(@PathVariable String dept) {
        return employeeService.getEmployeesByDepartment(dept);
    }

    @GetMapping("/count")
    public int getTotalEmployees() {
        return employeeService.getTotalEmployees();
    }
    
    @PostMapping("/create")
    public int createEmployee(@RequestParam String name, @RequestParam String department, @RequestParam double salary) {
        return employeeService.createEmployee(name, department, salary);
    }
    
    @PutMapping("/updateSalary/{id}/{salary}")
    public int updateSalary(@PathVariable Long id, @PathVariable double salary) {
        return employeeService.updateSalary(id, salary);
    }
    
    @DeleteMapping("/delete/{id}")
    public int deleteEmployee(@PathVariable Long id) {
        return employeeService.deleteEmployee(id);
    }
}

Testing the Application

Run the Spring Boot application and make the following API calls:

Fetch Employees by DepartmentGET /employees/department/IT
Get Total Employee CountGET /employees/count
Create a New EmployeePOST /employees/create?name=John&department=IT&salary=60000
Update Employee SalaryPUT /employees/updateSalary/1/75000
Delete Employee by IDDELETE /employees/delete/1

Conclusion

Using native queries in Spring JPA allows developers to execute raw SQL queries while still leveraging the power of Spring Data repositories. This approach is useful for optimizing database operations and handling complex queries that JPQL might not support efficiently. Additionally, native queries enable developers to perform create, update, and delete operations efficiently using @Modifying and @Transactional annotations.