AF
HomeTagSubmit NotesAsk AnythingLoginSubscribe Us
AF
1. Feel Free to ask and submit anything on Anyforum.in and get satisfactory answer
2. Registration is not compulsory, you can directly login via google or facebook
3. Our Experts are looking for yours ?.



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

which interface is used to call stored procedure and function from java application? Please provide source code also.

java x 211
jdbc x 32
Posted On : 2013-11-28 21:48:10.0
profile Garima Gupta - anyforum.in Garima Gupta
596129554551
up-rate
4
down-rate

Answers


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));

}
}

Posted On : 2013-11-28 22:12:37
Satisfied : 1 Yes  0 No
profile Saksham Kumar - anyforum.in Saksham Kumar
73433936114
Reply This Thread
up-rate
5
down-rate



Post Answer
Please Login First to Post Answer: Login login with facebook - anyforum.in