Spring Data JPA calls a stored procedure instance

Spring Data JPA calls a stored procedure instance
Spring Data JPA calls a stored procedure instance

JPA connects to the database and calls stored procedures. This requirement is common. This article describes how to use Spring Data JPA to call a stored procedure.

1. Stored Procedure

Assume that the stored procedure is as follows:

CREATE OR REPLACE PACKAGE test_pkg AS   PROCEDURE in_only_test (inParam1 IN VARCHAR2);   PROCEDURE in_and_out_test (inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2);END test_pkg;/CREATE OR REPLACE PACKAGE BODY test_pkg AS   PROCEDURE in_only_test(inParam1 IN VARCHAR2) AS   BEGIN      DBMS_OUTPUT.PUT_LINE('in_only_test');   END in_only_test;   PROCEDURE in_and_out_test(inParam1 IN VARCHAR2, outParam1 OUT VARCHAR2) AS   BEGIN      outParam1 := 'Woohoo Im an outparam, and this is my inparam ' || inParam1;   END in_and_out_test;END test_pkg;

There are two stored procedures:

1) in_only_test
It requires an input parameter inParam1, but does not return a value.

2) in_and_out_test
It requires an input parameter inParam1 and the returned value outParam1

2. @ NamedStoredProcedureQueries

You can use the @ NamedStoredProcedureQueries annotation to call the stored procedure.

@Entity@Table(name = "MYTABLE")@NamedStoredProcedureQueries({        @NamedStoredProcedureQuery(name = "in_only_test", procedureName = "test_pkg.in_only_test", parameters = {                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inParam1", type = String.class) }),        @NamedStoredProcedureQuery(name = "in_and_out_test", procedureName = "test_pkg.in_and_out_test", parameters = {                @StoredProcedureParameter(mode = ParameterMode.IN, name = "inParam1", type = String.class),                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "outParam1", type = String.class) }) })public class MyTable implements Serializable {}

Key points:

The stored procedure uses annotation @ NamedStoredProcedureQuery and binds it to a JPA table. ProcedureName is the name of the stored procedure. name is the name of the stored procedure in jpa. Use annotation @ StoredProcedureParameter to define the IN/OUT parameters used by the stored procedure. 3. Create a Spring Data JPA database.

Next we will create the Spring Data JPA database:

public interface MyTableRepository extends CrudRepository
  
    {    @Procedure(name = "in_only_test")    void inOnlyTest(@Param("inParam1") String inParam1);    @Procedure(name = "in_and_out_test")    String inAndOutTest(@Param("inParam1") String inParam1);}
  

Key points:

@ Procedure's name parameter must match @ NamedStoredProcedureQuery's name @ Param must match @ StoredProcedureParameter annotation's name parameter return type must match: in_only_test the stored Procedure returns void, the in_and_out_test stored procedure must return String4 and call

We can call the stored procedure as follows:

// Pass parameters to the stored procedure and return the value String inParam = "Hi Im an inputParam"; String outParam = myTableRepository. inAndOutTest (inParam); Assert. assertEquals (outParam, "Woohoo Im an outparam, and this is my inparam Hi Im an inputParam"); // The parameter passed to the stored procedure does not return the myTableRepository value. inOnlyTest (inParam );

5. Other skills

If the above Code does not work, you can solve this problem. Custom Repository to call the Stored Procedure for local query last night.

Custom Repository:

public interface MyTableRepositoryCustom {    void inOnlyTest(String inParam1);}

Then make sure that the primary Repository class inherits this interface.

public interface MyTableRepository extends CrudRepository
  
   , MyTableRepositoryCustom {}
  

6. Create a Repository implementation class

Next we need to create a Repository implementation class:

Public class MyTableRepositoryImpl implements MyTableRepositoryCustom {
@ PersistenceContext
Private EntityManager em;

@Overridepublic void inOnlyTest(String inParam1) {    this.em.createNativeQuery("BEGIN in_only_test(:inParam1); END;").setParameter("inParam1", inParam1)            .executeUpdate();}

}

It can be called in the conventional way:

@ AutowiredMyTableRepository myTableRepository; // call the Stored Procedure myTableRepository. inOnlyTest (inParam1 );

Is it easy.