Stored Procedure in JDBC

it is highly recommended develop a JDBC application in such way that write separate logic for Java and write a separate logic for database. Database logic is nothing but stored procedures.


def:  a stored procedure is nothing but group of SQL Queries combined in a single unit by following PL/SQL Standards
                                                            or
      The block of statements written by following PL/SQL Standards in a single unit is known as Stored Procedures

These are two types a)Functions  b)Procedures

Functions: a function is a group of sql statements. a function always returns either a single value or no value at all 
i.e function may(or) may not return a value

write a pl/sql program for developing a function for adding two numbers

Create or replace function fun1(a in number, b in number) return number as
c number;
begin
c:=a+b;
return c;
end;

Steps for calling pl/sql function from JDBC Application
1. Obtain an object of java.sql.CallableStatement Interface

java.sql.Connection(interface)
               |
public CallableStatement prepareCall(String)

2. Set the values for all input parameters
In order to set input parameter values we have the following generalised method present in callablestatement interface
                             java.sql.CallableStatement
                                            |
                          public void setXXX(int,value)
here XXX represents any fundamental datatype.

3.Register or Specify which parameter is out parameter for holding result which is coming from the database.
                      java.sql.CallableStatement
                                      |
                      public void registerOutParameter(int,JDBCType)
here int parameter represents the position of out parameter

4. Execute the StoredProcedure from jdbc application to execute.
                      java.sql.CallableStatement
                                      |
                       public boolean execute();
5. Obtain the result from out parameter after executing the stored procedure
                        java.sql.CallableStatement
                                        |
                        public XXX getXXX(int)
Example Program for Calling Functions:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class class2 {
public static void main(String arg[]) throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");
CallableStatement cs=con.prepareCall("{?=call fun1(?,?)}");
cs.setInt(2,10);
cs.setInt(3, 30);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
int res=cs.getInt(1);
System.out.println("result is "+res);
}
}

Example Program for Calling Procedures:

create procedure in pl/sql as shown below

create or replace procedure ope(a in number,b in number,c out number,d out number,e out number) as

begin

c:=a+b;

d:=a-b;

e:=a*b;

end;

create class for calling procedures

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class class3 {
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", "system123");
CallableStatement cs=con.prepareCall("{call ope(?,?,?,?,?)}");
cs.setInt(1,10);
cs.setInt(2, 30);
cs.registerOutParameter(3, Types.INTEGER);
cs.registerOutParameter(4, Types.INTEGER);
cs.registerOutParameter(5, Types.INTEGER);
cs.execute();
int res=cs.getInt(3);
int res1=cs.getInt(4);
int res2=cs.getInt(5);
System.out.println("result is "+res+"    "+res1+"   "+res2);
}
}