How to call a stored procedure from Java and JPA

1. For a simple stored procedure that using IN/OUT parameters like this

   CREATE OR REPLACE PROCEDURE count_comments (  
      postId IN NUMBER,  
      commentCount OUT NUMBER )  
   AS 
   BEGIN 
       SELECT COUNT(*) INTO commentCount  
       FROM post_comment  
       WHERE post_id = postId; 
   END;
   

   You can call it from JPA as follows:

   StoredProcedureQuery query = entityManager
       .createStoredProcedureQuery("count_comments")
       .registerStoredProcedureParameter(1, Long.class, 
           ParameterMode.IN)
       .registerStoredProcedureParameter(2, Long.class, 
           ParameterMode.OUT)
       .setParameter(1, 1L);
   
   query.execute();
   
   Long commentCount = (Long) query.getOutputParameterValue(2);
   

2. For a stored procedure which uses a SYS_REFCURSOR OUT parameter:

   CREATE OR REPLACE PROCEDURE post_comments ( 
      postId IN NUMBER, 
      postComments OUT SYS_REFCURSOR ) 
   AS 
   BEGIN
       OPEN postComments FOR
       SELECT *
       FROM post_comment 
       WHERE post_id = postId; 
   END;
   

   You can call it as follows:

   StoredProcedureQuery query = entityManager
       .createStoredProcedureQuery("post_comments")
       .registerStoredProcedureParameter(1, Long.class, 
            ParameterMode.IN)
       .registerStoredProcedureParameter(2, Class.class, 
            ParameterMode.REF_CURSOR)
       .setParameter(1, 1L);
   
   query.execute();
   
   List<Object[]> postComments = query.getResultList();
   

3. For a SQL function that looks as follows:

   CREATE OR REPLACE FUNCTION fn_count_comments ( 
       postId IN NUMBER ) 
       RETURN NUMBER 
   IS
       commentCount NUMBER; 
   BEGIN
       SELECT COUNT(*) INTO commentCount 
       FROM post_comment 
       WHERE post_id = postId; 
       RETURN( commentCount ); 
   END;
   

   You can call it like this:

   BigDecimal commentCount = (BigDecimal) entityManager
   .createNativeQuery(
       "SELECT fn_count_comments(:postId) FROM DUAL"
   )
   .setParameter("postId", 1L)
   .getSingleResult();
   

   At least when using Hibernate 4.x and 5.x because the JPA StoredProcedureQuery does not work for SQL FUNCTIONS.

For more details about how to call stored procedures and functions when using JPA and Hibernate, check out the following articles

• How to call Oracle stored procedures and functions with JPA and Hibernate
• How to call SQL Server stored procedures and functions with JPA and Hibernate
• How to call PostgreSQL functions (stored procedures) with JPA and Hibernate
• How to call MySQL stored procedures and functions with JPA and Hibernate

From JPA 2.1 , JPA supports to call stored procedures using the dynamic StoredProcedureQuery, and the declarative @NamedStoredProcedureQuery.

the simplest way is to use JpaRepository

1- Create a stored procedure
CREATE PROCEDURE dbo.getEmployeeDetails
(
@employeeId         int,
@companyId          int
)  AS
BEGIN
 SELECT firstName,lastName,gender,address
 FROM employee et
 WHERE et.employeeId = @employeeId and et.companyId = @companyId
END


2- Create Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class EmployeeDetails {
    @Id
    private String firstName;
    private String lastName;
    private String gender;
    private String address;
 }


3- Create Repository
public interface EmployeeDetailsRepository extends 
JpaRepository<EmployeeDetails,String> {
@Query(value = "EXEC dbo.getEmployeeDetails @employeeId=:empId, 
                                          @companyId=:compId",nativeQuery =true)
List<EmployeeDetails> getEmployeeList(@Param("employeeId") Integer empId, 
                                      @Param("companyId") Integer compId);
}

4- create Controller
@CrossOrigin(origins = "*")
@RestController
@RequestMapping(value = "/api/employee")
public class EmployeeController {

@Autowired
private EmployeeDetailsRepository empRepo;

@GetMapping(value = "/details")
public ResponseEntity<List<EmployeeDetails>> getEmployeeDetails(@RequestParam 
            String empId, @RequestParam String compId) {
try {
   List<EmployeeDetails> result = empRepo.getEmployeeList(
                                Integer.valueOf(empId),Integer.valueOf(compId));
        return ResponseEntity.status(HttpStatus.OK).body(result);
    }
    catch (Exception ex)
    {
        return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(null);
    }
}
}

you can now call http://localhost:8080/api/employee/details?empId=1&compId=25

If you're not too attached to calling this particular procedure with JPA or JDBC, you could use jOOQ, a third party library that generates stubs for all of your stored procedures to simplify calling them, and making the calls type safe.

Calling procedures returning unspecified cursors

In your particular case, the procedure returns an untyped, undeclared cursor (it could return several cursors and interleaved update counts). So, you could call the procedure like this with jOOQ:

GetEmployeeDetails proc = new GetEmployeeDetails();
proc.setEmployeeId(1);
proc.setCompanyId(2);
proc.execute(configuration);

// Iterate over potentially multiple results
for (Result<?> result : proc.getResults()) {

    // Print the first result set (your employee query)
    System.out.println(result);

    // Use your implicit knowledge of the content of the query
    // Without type safety
    for (Record record : result) {

        // All tables / columns are also generated
        System.out.println("First name: " + record.get(EMPLOYEE.FIRSTNAME));
        System.out.println("Last name: " + record.get(EMPLOYEE.LASTNAME));
        System.out.println("Gender: " + record.get(EMPLOYEE.GENDER));
        System.out.println("Address: " + record.get(EMPLOYEE.ADDRESS));
    }
}

Using an actual table valued function, instead

Personally, I don't really like that feature of a few RDBMS (including SQL Server, MySQL) of returning arbitrary untyped cursors. Why not just declare the result type? SQL Server has powerful table valued functions. E.g. just use this syntax here:

CREATE FUNCTION getEmployeeDetails (@employeeId int, @companyId int)
RETURNS TABLE
AS RETURN
  SELECT
    firstName,
    lastName,
    gender,
    address
  FROM employee et
  WHERE et.employeeId = @employeeId
  AND et.companyId = @companyId

Now, you have the full type information associated with this function in your catalog, and if you're still using jOOQ, that information will be available to the code generator, so you can call the function like this:

for (GetEmployeeDetailsRecord record : ctx.selectFrom(getEmployeeDetails(1, 2))) {
    System.out.println("First name: " + record.getFirstName());
    System.out.println("Last name: " + record.getLastName());
    System.out.println("Gender: " + record.getGender());
    System.out.println("Address: " + record.getAddress());
}

Disclaimer: I work for the company behind jOOQ