JPA Stored-Procedure Queries

Step 1: Create a Stored Procedure in PostgreSQL

Suppose you have a users table:

CREATE TABLE student(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

Create a stored procedure that retrieves users older than a specified age:

CREATE OR REPLACE FUNCTION get_users_by_age(min_age INT)
RETURNS TABLE(id INT, name TEXT, email TEXT, age INT) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.name, u.email, u.age
    FROM users u
    WHERE u.age >= min_age;
END;
$$ LANGUAGE plpgsql;

---

Step 2: Create the JPA Entity

User Entity

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

    @Column(name = "age")
    private Integer age;

    // Getters and Setters
}

---

Step 3: Use EntityManager to Call the Stored Procedure

UserRepository

Instead of using Spring Data's @Procedure, you can call the stored procedure directly with EntityManager.

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.StoredProcedureQuery;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public List<User> getUsersByAge(Integer minAge) {
        // Create the stored procedure query
        StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("get_users_by_age", User.class);

        // Set the input parameter
        storedProcedure.registerStoredProcedureParameter(1, Integer.class, jakarta.persistence.ParameterMode.IN);
        storedProcedure.setParameter(1, minAge);

        // Execute the query and return the result
        return storedProcedure.getResultList();
    }
}