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 Procedure | Function |
|---|---|
| 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:
The example to get the instance of CallableStatement is given below:
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.
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 you 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 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.
Now, let's write the simple program to call the function.
Output: 53