java-jdbc: How to call the stored procedure and function from java application?

In java to call the stored procedures and functions, CallableStatement interface is used.
------------------------------------------------------------------------------------------------------------------
We can have business logic on the database by the use of stored procedures and functions that will make the performance better because these are pre-compiled. Suppose you need the get the age of the employee based on the date of birth, you may create a function that receives date as the input and returns age of the employee as the output.

**************** Example to call the stored procedure from the java application ****************

To call the stored procedure, you need to create it in the database. Here, we are assuming that stored procedure looks like this.

create or replace procedure "INSERTR"
(id IN NUMBER,
name IN VARCHAR2)
is
begin
insert into user values(id,name);
end;
/

In this example, we are going to call the stored procedure INSERTR that receives id and name as the parameter and inserts it into the table user. Note that you need to create the user table as well to run this application.

import java.sql.*;
public class Proc {
public static void main(String[] args) throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");
stmt.setInt(1,1011);
stmt.setString(2,"Amit");
stmt.execute();

System.out.println("success");
}
}


******************** Example to call the function from the java application ********************

In this example, we are calling the sum function that receives two input and returns the sum of the given number. Here, we have used the registerOutParameter method of CallableStatement interface, that registers the output parameter with its corresponding type. It provides information to the CallableStatement about the type of result being displayed.

The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB etc.

Let´s create the simple function in the database first.

create or replace function sum
(n1 in number,n2 in number)
return number
is
temp number(8);
begin
temp :=n1+n2;
return temp;
end;
/
Now, let´s write the simple program to call the function.

import java.sql.*;

public class FuncSum {
public static void main(String[] args) throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

CallableStatement stmt=con.prepareCall("{?= call sum(?,?)}");
stmt.setInt(2,10);
stmt.setInt(3,43);
stmt.registerOutParameter(1,Types.INTEGER);
stmt.execute();

System.out.println(stmt.getInt(1));

}
}