Spring Data JPA calls a stored procedure instance

Spring Data JPA calls the stored procedure instance Chszs and cannot be reproduced without the permission of the blogger. Permission to reprint should be marked by the author and blog home: Http://blog.csdn.net/chszs

The need for JPA to connect to a database and invoke stored procedures is common. This article describes how to call a stored procedure using spring Data JPA.

1. Stored Procedures

Suppose 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 of inParam1 and a return value of OUTPARAM1

2. @NamedStoredProcedureQueries

We can use @namedstoredprocedurequeries annotations to invoke stored procedures.

@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 comment @namedstoredprocedurequery and binds to a JPA table.
• ProcedureName is the name of the stored procedure
• Name is the names of stored procedures in JPA
• Use annotation @storedprocedureparameter to define in/out parameters used by stored procedures

3. Create a 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 of the @Procedure must match the name of the @namedstoredprocedurequery
• @Param must match the name parameter of the @storedprocedureparameter comment
• The return type must match: The in_only_test stored procedure returns that the Void,in_and_out_test stored procedure must return a string

4. Call

We can call a stored procedure like this:

    // 向存储过程传递参数并返回值    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");    // 向存储过程传递参数不返回值    myTableRepository.inOnlyTest(inParam);

5. Other Skills

If the above code does not work, this can be resolved. Defines a custom repository to call a stored procedure for local queries last night.

To define a custom repository:

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

Then make sure that the main repository class inherits this 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;

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

}

Can be called in the usual way:

@AutowiredMyTableRepository myTableRepository;// 调用存储过程myTableRepository.inOnlyTest(inParam1);

is not very simple.

Spring Data JPA calls a stored procedure instance