Saturday, March 24, 2018

CallableStatement Interface in Java-JDBC

In the post Statement Interface in Java-JDBC and PreparedStatement Interface in Java-JDBC we have already seen how you can use Statement to execute static SQL statements and PreparedStatement to execute precompiled parameterized SQL statements. On the same lines the JDBC API provides CallableStatement interface that extends PreparedStatement and used to execute SQL stored procedures.

Stored Procedure

Stored procedure is a subroutine which resides with in the database and may have DB specific way of writing it. If you have a huge SQL statement or a group of SQL statements involving more than one table, checking for conditions, looping it is better to write it as a stored procedure. That way you will need to make just one call to the DB server and your pre-compiled procedure would be executed in the same space as your DB server.

That brings you the advantages like efficiency as it is already compiled, reduced network traffic as its full execution happens in the DB server.

General form of the CallableStatement in Java

CallableStatement object can be created using the prepareCall() method of the Connection interface.

CallableStatement callableStatement = connection.prepareCall(“{call PROCEDURE_NAME(?, ?, ?)}”); 
Here ‘?’ is a place holder used to register IN, OUT and INOUT parameters.

You can also also call functions using Callable statement, in that case general form would be like -

CallableStatement callableStatement = connection.prepareCall(“? = {call PROCEDURE_NAME(?, ?, ?)}”);

How to use CallableStatement in JDBC

In order to pass values to the IN and INOUT parameters of the stored procedure you need to use the appropriate setter method. CallableStatement inherits setter methods from PreparedStatement and there are different setter methods for different data types i.e. setInt(), setString(), setDate() etc.

You also need to register OUT parameters of the stored procedure. For that you need to use registerOutParameter method which takes column index or column name and type as parameters. It has other overloaded methods too.

There are also various getter methods (like getString(), getLong(), getTime()) for getting the values from the OUT parameters.

In order to execute the CallableStatement you can use execute() methods -

  • execute()- Any SQL statement. Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
  • executeUpdate()- For DML statements like Insert, Update or DDL statements like Create.
  • ExecuteQuery()- For SQL statement that returns ResultSet.

Java CallableStatement examples

Let’s see some examples using CallableStatement in JDBC. Database used is MySql, schema is netjs and table employee with columns id, age and name, where id is auto-generated.

1. CallableStatement example-Executing stored procedure having IN params

In this example let’s execute a stored procedure that has only IN params using CallableStatement. The stored procedure inserts a new row into the table.

insert_employee_proc.sql

CREATE PROCEDURE `insert_employee_proc`(IN param_name VARCHAR(35), IN param_age int)
BEGIN
  INSERT into EMPLOYEE (name, age) values 
  (param_name, param_age);
END

You can see in the stored procedure that there are two IN parameters in the stored procedure.

Java Code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCCallableStmt {

 public static void main(String[] args) {
   Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    // Getting CallableStatement object
    CallableStatement cStatement = connection.prepareCall(
       "{call insert_employee_proc(?, ?)}");
    // Setting params
    cStatement.setString(1, "Jackie");
    cStatement.setInt(2, 45);
    
    int count = cStatement.executeUpdate();
    System.out.println("Count of rows inserted " + count);
   
   } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
   }finally{
       if(connection != null){
         //closing connection 
         try {
           connection.close();
         } catch (SQLException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
         }
       } // if condition
   }// finally
 }
}

2. CallableStatement example-Executing stored procedure having IN and OUT params

In this Java CallableStatement example let’s execute a stored procedure that has both IN and OUT params using CallableStatement. The stored procedure has a select query to which id is passed as an IN parameter and age and name for that id are send in OUT parameters.

select_employee_proc.sql

CREATE PROCEDURE `select_employee_proc`(IN param_id int, 
    OUT param_name varchar(35), OUT param_age int)
BEGIN
 SELECT name, age INTO param_name, param_age
 from EMPLOYEE where id = param_id;
END

Java Code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.JDBCType;
import java.sql.SQLException;

public class JDBCCallableStmt {

 public static void main(String[] args) {
   Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    // Getting CallableStatement object
    CallableStatement cStatement = connection.prepareCall(
      "{call select_employee_proc(?, ?, ?)}");
    // Setting params
    cStatement.setInt(1, 26);
    // Registering OUT parameters Using 
    // JDBCType enum which is added in Java 8
    cStatement.registerOutParameter(2, JDBCType.VARCHAR);
    
    cStatement.registerOutParameter(3, JDBCType.INTEGER);
    
    cStatement.executeQuery();
    
    // Reading the OUT paramter here 
    System.out.println("Fetched Result " + "Name: " + cStatement.getString(2) + 
      " Age: " + cStatement.getInt(3));
   
   } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
   }finally{
       if(connection != null){
          //closing connection 
          try {
            connection.close();
          } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
          }
       } // if condition
   }// finally
 }
}

3. CallableStatement example-Executing stored procedure returning multiple rows

Let’s see an example where stored procedure returns multiple rows as result. In that case you can use execute or executeQuery to execute the procedure using CallableStatement and that will return the resultset. In this example execute method is used in order to show how it uses other methods like getResultSet and getMoreResults.

all_employee_proc.sql

CREATE PROCEDURE `all_employee_proc`(IN param_age int)
BEGIN
  SELECT * from employee where age > param_age;
END

The stored procedure returns all employees whose age is greater than the passed age integer parameter.

Java code

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JDBCCallableStmt {

 public static void main(String[] args) {
   Connection connection = null;
   try {
    // Loading driver
    Class.forName("com.mysql.jdbc.Driver");
    
    // Creating connection
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                        "root", "admin");
    
    // Getting CallableStatement object
    CallableStatement cStatement = connection.prepareCall("{call all_employee_proc(?)}");
    // Setting params
    cStatement.setInt(1, 30);
    
    boolean hasResults = cStatement.execute();
    while(hasResults){
      ResultSet rs = cStatement.getResultSet();
      while(rs.next()){
        System.out.println("id : " + rs.getInt("id") + " Name : " 
          + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }
      hasResults = cStatement.getMoreResults();
    }
   
   } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
   } catch (SQLException e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
   }finally{
       if(connection != null){
          //closing connection 
          try {
            connection.close();
          } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
          }
        } // if condition
   }// finally
 }
}

Output

id : 6 Name : Tim Age : 40
id : 8 Name : Johnny Age : 35
id : 17 Name : Johnny Age : 35
id : 18 Name : Bob Age : 45
id : 25 Name : Jacky Age : 50
id : 26 Name : Jackie Age : 45

That's all for this topic CallableStatement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page


Related Topics

  1. Java JDBC Overview - JDBC Tutorial
  2. Java JDBC Steps to Connect to DB
  3. ResultSet Interface in Java-JDBC
  4. DataSource in Java-JDBC
  5. Transaction in Java-JDBC

You may also like-

  1. How ArrayList works internally in Java
  2. FlatMap in Java
  3. Serialization Proxy Pattern in Java
  4. Just In Time Compiler (JIT) in Java
  5. Lambda Expressions in Java 8
  6. Insert\Update using NamedParameterJDBCTemplate in Spring framework
  7. Invoking getters and setters using Reflection - Java Program
  8. finalize Method in Java