Over a million developers have joined DZone.

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

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

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

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

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.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}