CallableStatement Examples!

..using the Sakila Database!

Stored Functions - Stored Procedures

Databases provide the possibility to write "small programs" directly in the Database itself. These are called Stored Functions and Stored Procedures.

Difference Between Stored Functions and Stored Procedures
              +---------------------------------+----------------------------------------+
              | Stored Procedure (SP)           | Function (UDF - User Defined           |
              |                                 | Function)                              |
              +---------------------------------+----------------------------------------+
              | SP can return zero , single or  | Function must return a single value    |
              | multiple values.                | (which may be a scalar or a table).    |
              +---------------------------------+----------------------------------------+
              | We can use transaction in SP.   | We can't use transaction in UDF.       |
              +---------------------------------+----------------------------------------+
              | SP can have input/output        | Only input parameter.                  |
              | parameter.                      |                                        |
              +---------------------------------+----------------------------------------+
              | We can call function from SP.   | We can't call SP from function.        |
              +---------------------------------+----------------------------------------+
              | We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
              | WHERE/ HAVING statement.        | HAVING statement.                      |
              +---------------------------------+----------------------------------------+
              | We can use exception handling   | We can't use Try-Catch block in UDF.   |
              | using Try-Catch block in SP.    |                                        |
              +---------------------------------+----------------------------------------+
(source)

CallableStatement

JavaDocs already has good explanation, so quoting..
The interface used to execute SQL Stored Procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.
   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
   {call <procedure-name>[(<arg1>,<arg2>, ...)]} 
IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.
A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

Examples on Sakila Database


Stored Function Example
The Stored Function that we will be calling: inventory_held_by_customer.
import java.sql.*;
 
public class CallableStatementStoredFunctionExample {
 
    private static final String url = "jdbc:mysql://localhost:3306/sakila?useSSL=false";
    private static final String user = "root";
    private static final String pass = "root";
 
    public static void main(String[] args) throws SQLException {
        final Connection connection = DriverManager.getConnection(url, user, pass);
 
        // Stored Function: inventory_held_by_customer(p_inventory_id);
        final CallableStatement callableStatement = connection.prepareCall("{? = CALL inventory_held_by_customer(?)}");
        callableStatement.registerOutParameter(1, Types.INTEGER);
 
        final int customerId = 9;
        callableStatement.setInt(2, customerId);
 
        callableStatement.execute();
 
        final int inventoryHeldByCustomer = callableStatement.getInt(1);
        System.out.println(inventoryHeldByCustomer);
 
        connection.close();
    }
}

And the output will be..
366

Stored Procedure Example
Example on: film_in_stock Stored Procedure
import java.sql.*;
 
public class CallableStatementStoredProcedureExample {
 
    private static final String url = "jdbc:mysql://localhost:3306/sakila?useSSL=false";
    private static final String user = "root";
    private static final String pass = "root";
 
    public static void main(String[] args) throws SQLException {
        final Connection connection = DriverManager.getConnection(url, user, pass);
 
        // Stored Procedure: film_in_stock(p_film_id, p_store_id, p_film_count);
        final CallableStatement callableStatement = connection.prepareCall("{CALL  film_in_stock(?, ?, ?)}");
 
        callableStatement.setInt(1, 1);
        callableStatement.setInt(2, 1);
        callableStatement.registerOutParameter(3, Types.INTEGER);
 
        callableStatement.execute();
 
        final int filmInStock = callableStatement.getInt(3);
        System.out.println(filmInStock);
 
        connection.close();
    }
}

And the output will be..
4

Stored Procedure Example #2
As stated in the table, a Stored Procedure can also return multiple values, so lets modify the first example slightly:
import java.sql.*;
 
public class CallableStatementStoredProcedureExample {
 
    private static final String url = "jdbc:mysql://localhost:3306/sakila?useSSL=false";
    private static final String user = "root";
    private static final String pass = "root";
 
    public static void main(String[] args) throws SQLException {
        final Connection connection = DriverManager.getConnection(url, user, pass);
 
        // Stored Procedure: film_in_stock(p_film_id, p_store_id, p_film_count);
        final CallableStatement callableStatement = connection.prepareCall("{CALL  film_in_stock(?, ?, ?)}");
 
        callableStatement.setInt(1, 1);
        callableStatement.setInt(2, 1);
        callableStatement.registerOutParameter(3, Types.INTEGER);
 
        final ResultSet resultSet = callableStatement.executeQuery();
 
        final int filmInStock = callableStatement.getInt(3);
        System.out.println("Film in stock count: " + filmInStock);
        System.out.println("");
        System.out.println("== Films In Stock ==");
        while (resultSet.next()) {
            System.out.println(resultSet.getString("inventory_id"));
        }
 
        connection.close();
    }
}

And the output will be..
Film in stock count: 4
 
== Films In Stock ==
1
2
3
4

Before You Leave..
Because stored procedures are code that you, as a JDBC developer, may not have insight or control over, you may or may not know if a stored procedure returns a ResultSet. In fact, invoking executeQuery() on a stored procedure that does not return a ResultSet object will throw a SQLException. A good practice is to use the execute() method instead and test for a ResultSet after executing a stored procedure by using the method getMoreResults(); for example:
cstmt.execute(); // we executed some stored procedure
if (cstmt.getMoreResults()) { // returns true if the next result is
    // a ResultSet
    // ... process the ResultSet
}