How to Call a Stored Procedure from Java and JPA

Calling stored procedure using JPA @Query()

I am not able to find a way to use @Query to execute MS-SQL SP, but I used the below code to execute the MS-SQL Stored Procedure and it is working fine.

package com.ford.cevs.scheduler.repository;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;

@Repository
public class CommandStatusRepositoryImpl implements CommandStatusRepository {

@PersistenceContext
protected EntityManager em;

@Override
public String executeCommandStatusPurge(Integer minutes) {

        StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("MyProcedureName");

        // set parameters
        storedProcedure.registerStoredProcedureParameter("minutes", Integer.class, ParameterMode.IN);
        storedProcedure.setParameter("minutes", minutes);
        storedProcedure.execute();

        String result = (String)storedProcedure.getSingleResult();

        return result;
}
}  

MS-SQL Store Procedure

ALTER PROCEDURE [dbo].[MyProcedureName] @minutes int 
AS 
   SET nocount ON

    DECLARE @Message VARCHAR(40); 

      SET @Message = ' Completed, ' 

      -- <Your code> --- @minutes 

        SELECT @Message

The below link shows different ways:
https://www.programcreek.com/java-api-examples/index.php?api=javax.persistence.StoredProcedureQuery

JPA: How to call a stored procedure

This is actually they way you create a query.

Query query = entityManager.createNamedQuery("delete_entity_prod")
          setParameter("lineId",lineId);

To call it you must execute:

query.executeUpdate();

Of course, the DB must already contain the procedure. So if you have it defined in your SQL file, have a look at Executing SQL Statements from a Text File(this is for MySQL but other database systems use a similar approach to execute scripts)

Correctly call a stored procedure using Spring Data JPA

In the end i have discovered that Spring Data JPA does not currently support stored procedures with multiple output parameters. It's an open issue on the project and it doesn't appear to be any progress on that since it was opened 2 years ago

https://github.com/spring-projects/spring-data-examples/issues/80

https://jira.spring.io/browse/DATAJPA-707

https://jira.spring.io/browse/DATAJPA-748

EDIT: It looks like the issue has been resolved on version 2.2 RC1

Call Oracle StoredProcedure with multiple output parameters

Spring boot JPA starter calls StoredProcedureJpaQuery class on initialization.
That class contains a property called useNamedParameters.

Calling your stored procedure like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName();

means that spring JPA will map your IN and OUT parameters by position
To avoid this, you have to use the annotation @Param in your repository, like this ...

@Procedure(name = "MyPackage.spName")
Map<String, Object> spName(@Param("PO_VALUE1") String value1);

using the @Param annotation will set the useNamedParameters property to true and Spring JPA will not start looking by position instead by name. But, I don't have any IN parameters in my query. So I changed the method to INOUT like this ...

@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "PO_VALUE1", type = String.class)

and passing an empty string when I call the repository ...

Map<String, Object> spNameMap = tableNameRepository.spName("");

That solution works for me, but I'm open to see a better solution for this.

How can i call stored procedure using spring with jpa

Use EntityManager.createNativeQuery() instead. I don't think it's possible to call a stored procedure through a JPA query.

public List doInJpa(EntityManager em) throws PersistenceException {
  javax.persistence.Query query=em.createNativeQuery("call st_proc_getusers()"); 
  return query.getResultList(); 
}

You could also use @NamedNativeQuery.

---

Related Topics