| |
JPA connect to the database, call a stored procedure, this demand is very common. This article will address this point, how to use Spring Data JPA call a stored procedure approach.
1, stored procedures
Suppose 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;
There 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 the return value outParam1
2, @ NamedStoredProcedureQueries
We can use @NamedStoredProcedureQueries comments to call a stored procedure.
@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:
Stored procedures used comments @NamedStoredProcedureQuery, and bind to a JPA table.
procedureName is the name of the stored procedure
name is the name of the stored procedure JPA
To define the stored procedure used IN / OUT parameters using annotations @StoredProcedureParameter
3. Create a database Spring Data JPA
Let's create the Spring Data JPA database:
public interface MyTableRepository extends CrudRepository {
@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:
@Procedure The name parameter must match the name @NamedStoredProcedureQuery
@Param Must match the name parameter @StoredProcedureParameter comment
The return type must match: in_only_test stored procedure that returns a void, in_and_out_test stored procedure must return String
4, call
We can call a stored procedure:
// Pass parameters to the stored procedure and returns a 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 parameters to the stored procedure does not return value
myTableRepository.inOnlyTest (inParam);
5. Other tips
If the above code does not work, so to solve. Define custom Repository to call the stored procedure last night local query.
Define custom Repository:
public interface MyTableRepositoryCustom {
void inOnlyTest (String inParam1);
}
Then we want to ensure that the primary Repository classes inherit this interface.
public interface MyTableRepository extends CrudRepository , MyTableRepositoryCustom {
}
6. Create Repository implementation class
Then create the implementation class of the 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 ();
}
}
It can be called in a conventional manner:
@Autowired
MyTableRepository myTableRepository;
// Call a stored procedure
myTableRepository.inOnlyTest (inParam1);
It is not very simple. |
|
|
|