Spring JPA @Procedure Annotation Example

Yatin BatraOctober 23rd, 2024Last Updated: October 18th, 2024

0 212 7 minutes read

The @Procedure annotation in Spring Data JPA is used to map a repository method to a stored procedure in the database. Let us delve into understanding Spring JPA @Procedure annotation.

1. Introduction

The @Procedure annotation in Spring Data JPA is used to map a repository method to a stored procedure in the underlying database. It allows developers to execute stored procedures directly from the repository layer, without having to write native SQL queries. When using the @Procedure annotation, you can either specify the name of the procedure with the procedureName attribute or rely on Spring to infer the procedure name based on the method name. The method can accept parameters that match the input parameters of the stored procedure, and it will return the result of the procedure. For more information on the @Procedure annotation in Spring Data JPA, you can refer to the official Spring Data JPA documentation.

The @Procedure annotation in Spring Data JPA provides several benefits when working with stored procedures, especially in comparison to using Spring’s JdbcTemplate for similar tasks:

Declarative Syntax: The @Procedure annotation allows for a declarative approach to calling stored procedures. You can map a stored procedure directly to a repository method, simplifying code readability and reducing boilerplate.
Seamless Integration with JPA: Since @Procedure is part of Spring Data JPA, it integrates smoothly with other JPA features like entity management, transactions, and query derivation. It eliminates the need for manual result mapping.
Automatic Type Mapping: The annotation automatically handles the conversion of input and output parameters between Java types and database types, reducing the risk of manual errors.
Less Boilerplate Code: Unlike the JdbcTemplate, which requires explicit SQL calls and manual result handling, @Procedure simplifies the process by using JPA’s repository abstraction.

1.1 Comparison with JdbcTemplate

Boilerplate Code:
- @Procedure: Requires minimal code. The stored procedure call is directly mapped to a method in the repository interface, leading to cleaner, more readable code.
- JdbcTemplate: Requires explicit SQL query execution, parameter binding, and result mapping, which can lead to more verbose and complex code.
Integration with JPA:
- @Procedure: Naturally integrates with the JPA lifecycle, allowing you to work within the entity model and utilize JPA’s transactional context, cache, and entity mappings.
- JdbcTemplate: Works independently of JPA, requiring manual handling of transactions and mapping between result sets and entities, which can be tedious and error-prone.
Type Conversion:
- @Procedure: Automatically handles type conversion between Java types and database types, leveraging JPA’s type system.
- JdbcTemplate: Requires explicit mapping of data from the database to Java objects, often requiring custom row mappers or result set extractors.
Customization:
- @Procedure: Ideal for simpler scenarios where stored procedures are directly mapped to repository methods with minimal custom logic.
- JdbcTemplate: Provides more flexibility for complex queries, custom SQL logic, or handling scenarios that don’t fit well into JPA’s abstractions.

1.2 When to use Each Approach?

@Procedure: Best suited when you are already using Spring Data JPA, need simple procedure calls with minimal customization, and want to keep your code concise and integrated with the JPA ecosystem.
JdbcTemplate: Ideal for more complex or high-performance scenarios where fine-grained control over SQL execution is required, or if you are not using JPA at all.

Here is an example of a simple Spring boot application with PostgreSQL that demonstrates the usage of the annotation.

2. Code Example

2.1 Create a Stored Procedure in PostgreSQL

To begin with, we need a database but setting one up is tedious. However, with Docker, it becomes much simpler. You can watch this video to learn how to install Docker on Windows. Once installed, open the terminal and run the command below to set up and launch PostgreSQL.

-- Remember to change the password –
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres

-- command to stop the Postgres docker container --
docker stop postgres

-- command to remove the Postgres docker container --
docker rm postgres

Remember to set a password of your choice. If everything goes well, the PostgreSQL database server will be running on port 5432, and you can connect to it using the DBeaver GUI tool.

Let’s start with creating a simple stored procedure that takes an employee’s name as input and returns the employee’s salary.

-- Create an employee table
CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary NUMERIC
);

-- Insert sample data
INSERT INTO employee (name, salary) VALUES 
('John', 60000),
('Jane', 75000),
('Alex', 50000),
('Emma', 85000),
('Michael', 95000);

-- Stored procedure
CREATE OR REPLACE FUNCTION get_employee_salary(emp_name VARCHAR)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT salary FROM employee WHERE name = emp_name);
END;
$$ LANGUAGE plpgsql;

2.2 Spring boot dependencies

Add the required dependencies in pom.xml for Spring Data JPA and PostgreSQL.

<dependencies>
    <!-- Spring Boot Dependencies -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- PostgreSQL Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Lombok for simplifying model classes -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

2.3 Create a model entity

The code defines an Employee entity class in Java, which represents a table in a database. It uses the @Entity annotation to mark the class as a JPA entity, indicating that it corresponds to a table in the database. The class includes three fields: id, name, and salary.

package com.example.demo.entity;

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

@Entity
@Data
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private Double salary;
}

The id field is annotated with @Id, marking it as the primary key of the table, and with @GeneratedValue(strategy = GenerationType.IDENTITY), specifying that its value will be auto-generated by the database (typically as an auto-incrementing value). The name and salary fields represent the employee’s name and salary, respectively, and they will be mapped to corresponding columns in the database.

Additionally, the class is annotated with @Data from the Lombok library, which automatically generates common methods like getters, setters, equals(), hashCode(), and toString() at compile time. This simplifies the class by reducing boilerplate code. Overall, this class models an Employee table in the database, with id, name, and salary as its columns.

2.4 Create a repository interface

The code defines an interface called EmployeeRepository, which extends JpaRepository from Spring Data JPA. This interface is used to interact with the database for the Employee entity. By extending JpaRepository, it provides standard CRUD (Create, Read, Update, Delete) operations and additional methods for querying the Employee table. The interface specifies Employee as the entity type and Long as the type of the primary key.

package com.example.demo.repository;

import com.example.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Procedure(procedureName = "get_employee_salary")
    Double getEmployeeSalary(String emp_name);
}

The @Repository annotation indicates that this interface is a Spring repository, allowing Spring to detect it and handle database interactions automatically. Additionally, a custom method is defined using the @Procedure annotation. The method getEmployeeSalary is mapped to the stored procedure get_employee_salary in the database. This method takes a string parameter emp_name (the employee’s name) and returns a Double, which is the employee’s salary retrieved from the stored procedure.

2.5 Create a controller class

The code defines a EmployeeController class, which serves as a REST controller in a Spring Boot application. It is annotated with @RestController, indicating that this class will handle incoming HTTP requests and return responses in JSON format. The controller is responsible for handling operations related to the Employee entity.

package com.example.demo.controller;

import com.example.demo.repository.EmployeeRepository;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmployeeController {

    private final EmployeeRepository employeeRepository;

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

    @GetMapping("/employee/salary")
    public Double getSalary(@RequestParam String name) {
        return employeeRepository.getEmployeeSalary(name);
    }
}

This class has a constructor that takes an EmployeeRepository object as a parameter, which is injected by Spring. This repository allows the controller to access methods for interacting with the database, including a custom method to retrieve employee salaries.

The controller defines a method getSalary that is mapped to an HTTP GET request via the @GetMapping annotation. This method listens for requests at the endpoint /employee/salary. It accepts a query parameter name using the @RequestParam annotation, which represents the name of the employee. The method then calls the getEmployeeSalary method from the repository to fetch and return the salary of the specified employee from the database.

2.6 Configure properties

The code represents the configuration settings for connecting a Spring Boot application to a PostgreSQL database. These properties are typically placed in the application.properties or application.yml file of a Spring Boot project to define how the application interacts with the database.

spring.datasource.url=jdbc:postgresql://localhost:5432/testdb
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update

The property spring.datasource.url specifies the JDBC URL for connecting to the PostgreSQL database. In this example, the database is hosted locally on localhost at port 5432, and the database name is testdb. The spring.datasource.username and spring.datasource.password properties define the credentials (username and password) required to access the database.
The property spring.datasource.driver-class-name specifies the JDBC driver class that Spring Boot uses to communicate with PostgreSQL, which is org.postgresql.Driver. Additionally, spring.jpa.properties.hibernate.dialect sets the Hibernate dialect to PostgreSQLDialect, ensuring Hibernate uses the appropriate SQL syntax for PostgreSQL.
Lastly, spring.jpa.hibernate.ddl-auto=update tells Hibernate to automatically update the database schema based on the entity classes. This setting ensures that Hibernate synchronizes the database schema with the model whenever the application runs, without deleting existing data.

2.7 Run the application

After creating the stored procedure and starting PostgreSQL, you can run the Spring Boot application. To test the stored procedure, you can send a GET request to:

GET http://localhost:8080/employee/salary?name=John

This will return the salary of the employee named “John” from the database.

3. Conclusion

In conclusion, the @Procedure annotation in Spring Data JPA provides a convenient and declarative way to call stored procedures, making it easier to integrate with the JPA ecosystem while minimizing boilerplate code. It simplifies parameter handling and type conversion, and is ideal for applications that are already leveraging Spring Data JPA for ORM (Object-Relational Mapping). However, for more complex use cases or when fine-grained control over SQL execution is needed, Spring’s JdbcTemplate remains a powerful alternative, offering greater flexibility at the cost of additional complexity and manual coding. Choosing between the two depends on the specific requirements of your application and your need for simplicity or customization.