Ok Ive struggled on Calling Stored Procedures From Spring Data JPA for a while. My issue is that my SP’s dont return any values, so the normal JPA and Spring Data approaches outlined in my previous posts Spring Data JPA – Custom Repository.
Consider the following procedure –
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;
Ensure that you have granted the appropriate permissions to this class – eg –
GRANT EXECUTE ON TEST_PKG TO 'MYDB';
@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 { }
The key points are –
We then create the Spring Data JPA repository –
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); }
The key points –
We can then call them –
// This version shows how a param can go in an be returned from a stored procedure 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"); // This version shows how to call a Stored Procedure which doesnt return any parameter - myTableRepository.inOnlyTest(inParam);
Ive occasionally struggled with the above approach, and resorted to accessing the stored procedure as a native query through a custom repository.
This is done by defining a custom repository –
public interface MyTableRepositoryCustom { void inOnlyTest(String inParam1); }
We then make sure our main repository extends this interface –
public interface MyTableRepository extends CrudRepository<MyTable, Long>, MyTableRepositoryCustom { }
We then define our custom repository –
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(); } }
This can then be called in the normal way –
@Autowired MyTableRepository myTableRepository; // And to call the method - myTableRepository.inOnlyTest(inParam1);
Calling Stored Procedures From Spring Data JPA can be delivered using custom Spring Repositories.