Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Calling Stored Procedures From Spring Data JPA

DZone's Guide to

Calling Stored Procedures From Spring Data JPA

It is a common requirement to JPA connect to stored procedures or database functions. This article looks at how you can do this using Spring Data JPA.

· Java Zone
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

Consider the following stored 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;


Here we have two different procedures:

  • in_only_test - takes an input parameter(inParam1), but doesn't return a value.

  • in_and_out_test - takes an input parameter(inParam1), and returns a value(outParam1).

We can then call the stored procedures 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 {
}


The key points are:

  •  The Stored Procedure uses the annotation @NamedStoredProcedureQuery and is bound to a JPA table.

  • procedureName – This is the name of the stored procedure.

  • name – This is the name of the StoredProcedure in the JPA ecosystem.

  • Define the IN/OUT parameter using @StoredProcedureParameter.

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 are:

  • @Procedure – the name parameter must match the name on @NamedStoredProcedureQuery

  • @Param – Must match @StoredProcedureParameter name parameter

  • Return types must match - so in_only_test is void, and in_and_out_test returns String

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

Other Tricks

The wide range of possiblities for stored procedures has resulted in a few occasions when the above approach hasnt worked. I've solved these problems by defining a custom repository to call the stored procedures as a native query.
 
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 create our custom repository implementation:

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

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:
java ,spring data

Published at DZone with permission of Martin Farrell, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}