program to execute a stored procedure in the database by using CallableStatement and display the results

Aim: Write a program to execute a stored procedure in the database by using CallableStatement and display the results.

Theory:

CallableStatement Interface:

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 precompiled.

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.

 To get the instance of CallableStatement:

The prepareCall() method of Connection interface returns the instance of CallableStatement. Syntax is given below:

1.    public CallableStatement prepareCall("{ call procedurename(?,?...?)}");  

The example to get the instance of CallableStatement is given below:

1.    CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");  

It calls the procedure myprocedure that receives 2 arguments.

Program: 

/*Need to write this procedure in sql command prompt

create or replace procedure "XYZ"(num in number,name in char)is begin insert into student36 values(num,name);
end;
/*/
import java.sql.*;
import java.util.Scanner;
public class InsertProg1
{
public static void main(String args[])
  {
Connection con= null;
CallableStatement stmt=null;
Scanner input = new Scanner(System.in);
System.out.println("Enter Student details\n");
System.out.println("Enter Student Number\n");
int num = Integer.parseInt(input.nextLine());
System.out.println("Enter Student Name:");
String name = input.nextLine();
try
{ Class.forName("oracle.jdbc.driver.OracleDriver");
Connection c =DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","sumanth");
stmt = c.prepareCall("{call XYZ(?,?)}");
stmt.setInt(1,num);
stmt.setString(2,name);
stmt.executeUpdate();
System.out.println("Update record save success::");
Statement st= c.createStatement();
ResultSet r1=st.executeQuery("select *from student36");
while(r1.next())
{

int x = r1.getInt("NUM");
String y= r1.getString("NAME");
System.out.println(x+"\t "+y);
}

}
catch(Exception e)
{
e.printStackTrace();
}
  }
}

Output: