Skip to main content

Java: CallableStatement Interface

Java CallableStatement Interface

CallableStatement interface is used to call the stored procedures and functions.

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.

What is the difference between stored procedures and functions.

The differences between stored procedures and functions are given below:

Stored ProcedureFunction
is used to perform business logic.is used to perform calculation.
must not have the return type.must have the return type.
may return 0 or more values.may return only one values.
We can call functions from the procedure.Procedure cannot be called from function.
Procedure supports input and output parameters.Function supports only input parameter.
Exception handling using try/catch block can be used in stored procedures.Exception handling using try/catch can't be used in user defined functions.

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

Full example to call the stored procedure using JDBC

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

  1. create or replace procedure "INSERTR"  
  2. (id IN NUMBER,  
  3. name IN VARCHAR2)  
  4. is  
  5. begin  
  6. insert into user420 values(id,name);  
  7. end;  
  8. /     

The table structure is given below:

  1. create table user420(id number(10), name varchar2(200));  

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 user420. Note that you need to create the user420 table as well to run this application.

  1. import java.sql.*;  
  2. public class Proc {  
  3. public static void main(String[] args) throws Exception{  
  4.   
  5. Class.forName("oracle.jdbc.driver.OracleDriver");  
  6. Connection con=DriverManager.getConnection(  
  7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
  8.   
  9. CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");  
  10. stmt.setInt(1,1011);  
  11. stmt.setString(2,"Amit");  
  12. stmt.execute();  
  13.   
  14. System.out.println("success");  
  15. }  
  16. }  

Now check the table in the database, value is inserted in the user420 table.

Example to call the function using JDBC

In this example, we are calling the sum4 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.

  1. create or replace function sum4  
  2. (n1 in number,n2 in number)  
  3. return number  
  4. is   
  5. temp number(8);  
  6. begin  
  7. temp :=n1+n2;  
  8. return temp;  
  9. end;  
  10. /  

Now, let's write the simple program to call the function.

  1. import java.sql.*;  
  2.   
  3. public class FuncSum {  
  4. public static void main(String[] args) throws Exception{  
  5.   
  6. Class.forName("oracle.jdbc.driver.OracleDriver");  
  7. Connection con=DriverManager.getConnection(  
  8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
  9.   
  10. CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");  
  11. stmt.setInt(2,10);  
  12. stmt.setInt(3,43);  
  13. stmt.registerOutParameter(1,Types.INTEGER);  
  14. stmt.execute();  
  15.   
  16. System.out.println(stmt.getInt(1));  
  17.           
  18. }  
  19. }  
Output: 53


Anurag Rana Educator CSE/IT

Comments

Post a Comment

Popular posts from this blog

Standard and Formatted Input / Output in C++

The C++ standard libraries provide an extensive set of input/output capabilities which we will see in subsequent chapters. This chapter will discuss very basic and most common I/O operations required for C++ programming. C++ I/O occurs in streams, which are sequences of bytes. If bytes flow from a device like a keyboard, a disk drive, or a network connection etc. to main memory, this is called   input operation   and if bytes flow from main memory to a device like a display screen, a printer, a disk drive, or a network connection, etc., this is called   output operation . Standard Input and Output in C++ is done through the use of  streams . Streams are generic places to send or receive data. In C++, I/O is done through classes and objects defined in the header file  <iostream> .  iostream  stands for standard input-output stream. This header file contains definitions to objects like  cin ,  cout , etc. /O Library Header Files There are...
Post a Comment
Read more

Genetic Algorithm: Population, Fitness Function, Parent Selection, Cross over, Mutation

Genetic Algo Population Population is a subset of solutions in the current generation. It can also be defined as a set of chromosomes. There are several things to be kept in mind when dealing with GA population − The diversity of the population should be maintained otherwise it might lead to premature convergence. The population size should not be kept very large as it can cause a GA to slow down, while a smaller population might not be enough for a good mating pool. Therefore, an optimal population size needs to be decided by trial and error. The population is usually defined as a two dimensional array of –  size population, size x, chromosome size . Population Initialization There are two primary methods to initialize a population in a GA. They are − Random Initialization  − Populate the initial population with completely random solutions. Heuristic initialization  − Populate the initial population using a known heuristic for the problem. It has been observed that the e...
Post a Comment
Read more

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database

Normalization   is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.  Anomalies in DBMS There are three types of anomalies that occur when the database is not normalized. These are – Insertion, update and deletion anomaly. Let’s take an example to understand this. Example : Suppose a manufacturing company stores the employee details in a table named employee that has four attributes: emp_id for storing employee’s id, emp_name for storing employee’s name, emp_address for storing employee’s address and emp_dept for storing the department details in which the employee works. At some point of time the table looks like this: emp_id emp_name emp_address emp_dept 101 Nikhil Kangra D001 101 Nikhil Kangra D002 123 Ashish Shimla D890 166 Rahul Pathankot D900 166 Rahul Pathankot D004 The above table is not normalized.  Update anomaly : In the above table we have two rows for employee Nikhil as he belongs ...
Post a Comment
Read more