7

Calling Stored Procedures: IN and OUT Parameters - DZone Java

 2 years ago
source link: https://dzone.com/articles/calling-stored-procedures-with-in-and-out-paramete
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

Nov. 17, 21 · Java Zone · Code Snippet

Join the DZone community and get the full member experience.

Join For Free

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:

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:

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK