Java CallableStatement Interface

In Java, the CallableStatement interface is used to execute SQL stored procedures and functions in a database. The interface used to execute SQL stored procedures. These procedures contain precompiled business logic, which improves performance and reduces code complexity. It helps in executing database logic that is already defined in the database.

Syntax:

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

Suppose, we have given employee’s list and their date of birth. We have to find the age of employees. In this case, we will create a function that receive date of birth as input and returns the age of the employee as output.

Differences Between Stored Procedures and Functions

Getting an instance of CallableStatement

The prepareCall() method of Connection interface returns the instance of CallableStatement.

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

It calls the procedure myprocedure that receives 2 arguments.

Example: Calling the Stored Procedure Using JDBC

To call the stored procedure, we need to create it in the database. Here, we are assuming the following stored procedure.

The table structure is given below:

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

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

Example: Calling 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.

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

Output:

53