Trending
- DZone
- Data Engineering
- Data
- Calling Stored Procedures With IN and OUT Parameters From Spring Data JPA
Calling Stored Procedures With IN and OUT Parameters From Spring Data JPA
In this post, explore what was learned while configuring the OUT parameter in calling the stored procedures using Spring JPA.
Join the DZone community and get the full member experience.
Join For FreeWriting this blog is a result of how it took a while for me to figure out how to configure the OUT parameter in calling the stored procedures using Spring JPA.
Consider the following stored procedure:
PLSQL
CREATE OR REPLACE
PROCEDURE in_only (inParam IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('in_only');
END in_only;
/
CREATE OR REPLACE
PROCEDURE in_and_out (inParam IN VARCHAR2, outParam OUT VARCHAR2) AS
BEGIN
outParam := 'Done outParam , and this is my inParam ' || inParam;
END in_and_out;
/
Here we have two procedures:
- in_only: takes an input parameter(inParam) but doesn't return a value
- in_and_out: takes an input parameter(inParam) and returns a value(outParam)
We can then call the stored procedures using Spring Data JPA repository implementation as below:
Java
@Repository
public interface MytableRepository extends JpaRepository<Mytable, String> {
@Procedure(procedureName = "in_only")
void inOnly(@Param("inParam") String inParam);
@Procedure(procedureName = "in_and_out", outputParameterName = "outParam")
String inAndOut(@Param("inParam") String inParam);
}
Key Points
- procedureName: Must match the name of the Stored procedure created.
- Define the IN parameter using @Param.
- Define the OUT parameter using outputParameterName =””.
- @Param and outputParameterName: Must match the parameter name in the Stored procedure.
- Return types must match so in_only is void, and in_and_out returns String.
Testing
Java
@SpringBootTest
public class WebApplicationTests {
@Test
public void contextLoads() {
}
@Autowired
private MytableRepository repo;
@Test
public void inOnlyTest() {
String inParam = "Hi Im an inputParam";
repo.inOnly(inParam);
}
@Test
public void inAndOutTest() {
String inParam = "From inputParam";
String outParam = repo.inAndOut(inParam);
System.out.println(outParam);
}
}
Output
2021-10-31 13:43:21 DEBUG org.hibernate.SQL - {call in_and_out(?,?)}
Hibernate: {call in_and_out(?,?)}
Done outParam , and this is my inParam From inputParam
2021-10-31 13:43:22 DEBUG org.hibernate.SQL - {call in_only(?)}
Hibernate: {call in_only(?)}
Spring Data
Spring Framework
Data (computing)
Opinions expressed by DZone contributors are their own.
Related
-
Upgrade Guide To Spring Boot 3.0 for Spring Data JPA and Querydsl
-
Less Code With Spring Data Rest
-
Spring Data: Easy MongoDB Migration Using Mongock
-
Spring Data and R2DBC by Example
Partner Resources
×