Spring Data JPA invokes the stored procedure instance code

The need for JPA to connect to the database and invoke stored procedures is common. This article addresses this point by showing how to use spring Data JPA to invoke methods of stored procedures.

1. Stored procedures

Assume 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;

Here 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 returns the value outParam1

2, @ NamedStoredProcedureQueries

We can invoke the stored procedure using the @NamedStoredProcedureQueries annotation.

@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 the @NamedStoredProcedureQuery annotation and is bound to an JPA table. procedureName is the name of the stored procedure name is the name of the stored procedure in JPA Use the @StoredProcedureParameter annotation to define the IN/OUT parameters used by the stored procedure

3. Create Spring Data JPA database

Let's create the Spring Data JPA database:

public interface MyTableRepository extends CrudRepository<MyTable, Long> {
  @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:

The name parameter for @Procedure must match the name parameter for @NamedStoredProcedureQuery @Param must match the name parameter of the @StoredProcedureParameter annotation The return type must match: in_only_test stored procedure returns void, in_and_out_test stored procedure must return String

4, call

We can call the stored procedure like this:

  //  Pass the parameter 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");
  //  Pass a parameter to a stored procedure without returning a value 
  myTableRepository.inOnlyTest(inParam);

5. Other skills

If the above code does not work, this can be done. Define custom Repository to invoke the stored procedure last night local query.

Define custom Repository:

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

Then make sure that the main Repository class inherits the interface.

public interface MyTableRepository extends CrudRepository<MyTable, Long>, MyTableRepositoryCustom {}

6. Create Repository implementation class

Then it's time to create the Repository implementation class:

public class MyTableRepositoryImpl implements MyTableRepositoryCustom { 
@PersistenceContext 
private EntityManager em;

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

It can be called in the usual way:

@Autowired
MyTableRepository myTableRepository;
//  Calling a stored procedure 
myTableRepository.inOnlyTest(inParam1);