Calling Stored Procedures With IN and OUT Parameters From Spring Data JPA

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

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:

@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 

@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(?)}