CallableStatement in Java Example

CallableStatement in java is used to call stored procedure from java program. Stored Procedures are group of statements that we compile in the database for some task. Stored procedures are beneficial when we are dealing with multiple tables with complex scenario and rather than sending multiple queries to the database, we can send required data to the stored procedure and have the logic executed in the database server itself.

CallableStatement

JDBC API provides support to execute Stored Procedures through CallableStatement interface.

Stored Procedures requires to be written in the database specific syntax and for my tutorial, I will use Oracle database. We will look into standard features of CallableStatement with IN and OUT parameters.

Later on we will look into Oracle specific STRUCT and Cursor examples.

Let’s first create a table for our CallableStatement example programs with below SQL query.

create_employee.sql

Let’s first create a utility class to get the Oracle database Connection object. Make sure Oracle OJDBC jar is in the build path of the project.

DBConnection.java

CallableStatement Example

Let’s write a simple stored procedure to insert data into Employee table.

insertEmployee.sql

As you can see that insertEmployee procedure is expecting inputs from the caller that will be inserted into the Employee table.

If insert statement works fine, it’s returning TRUE and incase of any exception it’s returning FALSE.

Let’s see how we can use CallableStatement to execute insertEmployee stored procedure to insert employee data.

JDBCStoredProcedureWrite.java

We are reading user input to be stored in Employee table. The only thing different from PreparedStatement is the creation of CallableStatement through “{call insertEmployee(?,?,?,?,?,?)}” and setting OUT parameter with CallableStatement registerOutParameter() method.

We have to register the OUT parameter before executing the stored procedure. Once the stored procedure is executed, we can use CallableStatement getXXX() method to get the OUT object data. Notice that while registering the OUT parameter, we need to specify the type of OUT parameter through java.sql.Types.

The code is generic in nature, so if we have same stored procedure in other relational database like MySQL, we can execute them with this program too.

Below is the output when we are executing above CallableStatement example program multiple times.

Notice that second execution failed because name passed is bigger than the column size. We are consuming the exception in the stored procedure and returning false in this case.

CallableStatement Example – Stored Procedure OUT Parameters

Now let’s write a stored procedure to get the employee data by id. User will enter the employee id and program will display the employee information.

getEmployee.sql

Java CallableStatement example program using getEmployee stored procedure to read the employee data is;

JDBCStoredProcedureRead.java

Again the program is generic and works for any database having same stored procedure. Let’s see what is the output when we execute the above CallableStatement example program.

CallableStatement Example – Stored Procedure Oracle CURSOR

Since we are reading the employee information through ID, we are getting single result and OUT parameters works well to read the data. But if we search by role or country, we might get multiple rows and in that case we can use Oracle CURSOR to read them like result set.

getEmployeeByRole.sql

JDBCStoredProcedureCursor.java

This program is using Oracle OJDBC specific classes and won’t work with other database. We are setting OUT parameter type as OracleTypes.CURSOR and then casting it to ResultSet object. Other part of the code is simple JDBC programming.

When we execute above CallableStatement example program, we get below output.

Your output may vary depending on the data in your Employee table.

CallableStatement Example – Oracle DB Object and STRUCT

If you look at the insertEmployee and getEmployee stored procedures, I am having all the parameters of the Employee table in the procedure. When number of column grows, this can lead to confusion and more error prone. Oracle database provides option to create database Object and we can use Oracle STRUCT to work with them.

Let’s first define Oracle DB object for Employee table columns.

EMPLOYEE_OBJ.sql

Now let’s rewrite the insertEmployee stored procedure using EMPLOYEE_OBJ.

insertEmployeeObject.sql

Let’s see how we can call insertEmployeeObject stored procedure in java program.

JDBCStoredProcedureOracleStruct.java

First of all we are creating an Object array of same length as the EMPLOYEE_OBJ database object. Then we are setting values according to the EMPLOYEE_OBJ object variables. This is very important otherwise the data will get inserted into wrong columns.

Then we are creating oracle.sql.STRUCT object with the help of oracle.sql.StructDescriptor and our Object array. Once the STRUCT object is created, we are setting it as IN parameter for the stored procedure, register the OUT parameter and executing it. This code is tightly couple with OJDBC API and will not work for other databases.

Here is the output when we are executing this program.

We can use the Database object as OUT parameter also and read it to get the values from database.

That’s all for CallableStatement in java example to execute Stored Procedures, I hope you learned something from it.

By admin

Leave a Reply