CallableStatement

 In JDBC, a CallableStatement is used to execute SQL stored procedures and functions. It allows you to call database functions or stored procedures from Java, passing parameters and retrieving results.

For PostgreSQL, you can use CallableStatement to execute stored procedures or functions that have been defined in the database.

Steps for Using CallableStatement:

1. Create a stored procedure or function in PostgreSQL.
2. Call the stored procedure or function from Java using CallableStatement.
3. Handle input and output parameters.

Example 1: Creating a Stored Procedure in PostgreSQL

Before using CallableStatement in JDBC, let's first create a stored procedure or function in the PostgreSQL database.

Creating a Stored Function in PostgreSQL:

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

This function takes two integers as input and returns their sum.

Example 2: Calling a PostgreSQL Function with CallableStatement in JDBC

Now, we will write a Java program that calls this function using CallableStatement.

Java Code to Call a Stored Function

import java.sql.*;

public class CallableStatementExample {

    private static final String DB_URL = "jdbc:postgresql://localhost:5432/mydatabase";
    private static final String USER = "postgres";
    private static final String PASSWORD = "root";
    
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            
            // Calling the add_numbers function
            String sql = "{ ? = call add_numbers(?, ?) }"; // The SQL for calling a function
            
            // Create the CallableStatement
            try (CallableStatement stmt = conn.prepareCall(sql)) {
                // Register the output parameter (return type of the function)
                stmt.registerOutParameter(1, Types.INTEGER);
                
                // Set the input parameters for the function
                stmt.setInt(2, 5);  // First parameter (a)
                stmt.setInt(3, 10); // Second parameter (b)
                
                // Execute the function
                stmt.execute();
                
                // Retrieve the result (sum of the two numbers)
                int result = stmt.getInt(1);
                
                // Output the result
                System.out.println("Result of add_numbers function: " + result);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation of the Java Code:

1. Create a CallableStatement:

   
   String sql = "{ ? = call add_numbers(?, ?) }";
   CallableStatement stmt = conn.prepareCall(sql);
   

   • ? is a placeholder for the output parameter.
   • call add_numbers(?, ?) is the call to the stored function add_numbers, where the first ? represents the return value, and the other two ? represent the input parameters.

2. Register the Output Parameter:

   
   stmt.registerOutParameter(1, Types.INTEGER);
   

   • Registers the first parameter (the return value of the function) as an output parameter of type INTEGER.

3. Set the Input Parameters:

   
   stmt.setInt(2, 5);
   stmt.setInt(3, 10);
   

   • Sets the input parameters for the stored function (5 and 10).

4. Execute the Callable Statement:

   
   stmt.execute();
   

   • Executes the stored function.

5. Retrieve the Output Parameter:

   
   int result = stmt.getInt(1);
   

   • Retrieves the result of the function from the first parameter (the return value).

6. Close Resources:

   • The try-with-resources statement ensures that the Connection and CallableStatement are automatically closed.