CODEWITHSUNDEEP
hibernatepostgresqlstored-proceduresjpa
Panos
Panos

Reputation: 7407

JPA call a Stored Procedure in database that contains multiple out parameters

I have a store procedure in database which contains multiple OUT parameters. I want to use JPA (with Hibernate) to get the resultset. Is there any way to get all results from the OUT parameters?

E.g. When using CallableStatement you can use ".registerOutParameter" for those parameters. I want the equivalent for JPA (if exists/possible).

Database used is PostgreSQL.

Upvotes: 0

Views: 7547

Answers (3)

Midhun KAKE
Midhun KAKE

Reputation: 11

This explanation is based on Spring Boot + Spring Data JPA + Hibernate.

  1. Define a @NamedStoredProcedureQuery for your stored procedure in the Example entity class as below. In this example the stored procedure is assumed to have IN, INOUT and OUT type parameters each.

    @NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "Example.myStoredProcedure",
        procedureName = "db.MY_STORED_PRODEDURE",
        parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "pInParam", type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.OUT, name = "pOutParam", type = Date.class),
            @StoredProcedureParameter(mode = ParameterMode.INOUT, name = "pInOutParam", type = Integer.class),
        }
    )
})
@Entity
public class Example {
    @Id
    Integer id;

    ...
}

  2. Define a method in the ExampleRepository class with only the IN and INOUT parameters of stored procedure. Set the values of the parameters procedureName and value of @Procedure correctly. The return type of this method should always be Map<String, ?>.

    @Repository
public interface ExampleRepository extends JpaRepository<Example, Integer> {
       @Procedure(value = "Example.myStoredProcedure", procedureName = "db.MY_STORED_PRODEDURE")
        Map<String, ?> executeMyStoredProcedure(@Param("pInParam") String pInParam, @Param("pInOutParam") String pInOutParam);

}

  3. Call the above defined method in the ExampleService class as below. You can now retrieve multiple OUT values (INOUTs too) from Map<String, ?> returned by the method using the @StoredProcedureParameter names defined in step 1. 

    @Service
public Class ExampleService {
   final ExampleRepository exampleRepository;

   public ExampleService(ExampleRepository exampleRepository) {
       this.exampleRepository = exampleRepository;
   }

   public void executeMyStoredProcedure(String someString, Integer someInteger) {
       Map<String, ?> result = exampleRepository.executeMyStoredProcedure(someString, someInteger);

       Date dateValue = (Date) result.get("pOutParam");
       Integer integerValue = (Integer) result.get("pInOutParam");

       ...

   }
}

Upvotes: 1

kj007
kj007

Reputation: 6254

here you can easily call stored procedure from spring data JPA repository by implementing custom repository..

  1. Create a interface and define your method.
  2. create impl for it annotate with @Repository

  3. under method implementation below is the way ti implement

     StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("Your Stored Procedure name");

// Set the parameters of the stored procedure.
String firstParam = "first param name";
storedProcedure.registerStoredProcedureParameter(firstParam, Long.class, ParameterMode.IN);
storedProcedure.setParameter(firstParam, first Param value);

// Set the second parameters of the stored procedure.
String secondParam = "second parameter name";
storedProcedure.registerStoredProcedureParameter(secondParam, String.class, ParameterMode.IN);
storedProcedure.setParameter(secondParam, "second paramter value);

//Your OUT parameters.
storedProcedure.registerStoredProcedureParameter("First OUT parameter name", Integer.class, ParameterMode.OUT);
storedProcedure.registerStoredProcedureParameter("OUT second parameter name", Integer.class, ParameterMode.OUT);
storedProcedure.execute();
//Replace MyObject with your actual POJO
MyObject myObject= new MyObject();
myObject.setFirstParameterOutput((Integer) storedProcedure.getOutputParameterValue("first OUT parameter name"));
myObject.setSecondParameterOutput((Integer) storedProcedure.getOutputParameterValue("second OUT parameter name"));
return MyObject;

Note: Noe even you can call from Repository itself by annotate @Procedure

@Procedure
public Integer procedure_name(String firstParameter, Date secondParameter);

 }

Upvotes: 0

DataNucleus
DataNucleus

Reputation: 15577

JPA2.1 will have StoredProcedure support as you require (not yet final). The only JPA implementation that provides a preview feature of this AFAIK is DataNucleus JPA. Other JPA impls may have their own non-standard support, so check your manual if wanting to go that route

Upvotes: 1

Related Questions