Invoking Function and Stored Procedure using JDBC

Calling Database Functions:

Using CallableStatement:

1. Establish a Connection: Establish a connection to the database.

2. Create a CallableStatement: Create a CallableStatement object with the function call syntax using the connection.

3. Register Out Parameters (if any): If the function returns a value, register the out parameter using registerOutParameter() method.

4. Execute Function: Execute the CallableStatement using execute() or executeQuery() method.

5. Retrieve Results: If the function returns a value, retrieve it using the appropriate method (getInt(), getString(), etc.) after executing the CallableStatement.

Example Code for Calling a Function (Java):

import java.sql.*;

public class FunctionCallExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "username";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            // Calling a Function
            String functionCall = "{ ? = call my_function() }";
            try (CallableStatement callableStatement = connection.prepareCall(functionCall)) {
                callableStatement.registerOutParameter(1, Types.INTEGER); // Assuming the function returns an integer
                callableStatement.execute();

                int result = callableStatement.getInt(1); // Retrieve the returned value
                System.out.println("Result from the function: " + result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Calling Stored Procedures:

Using CallableStatement:

1. Establish a Connection: Establish a connection to the database.

2. Create a CallableStatement: Create a CallableStatement object with the stored procedure call syntax using the connection.

3. Set Input Parameters (if any): Set input parameters using setXXX() methods.

4. Register Out Parameters (if any): If the stored procedure has out parameters, register them using registerOutParameter() method.

5. Execute Procedure: Execute the CallableStatement using execute().

6. Retrieve Out Parameters (if any): If the stored procedure has out parameters, retrieve them using appropriate methods (getInt(), getString(), etc.) after executing the CallableStatement.

Example Code for Calling a Stored Procedure (Java):

import java.sql.*;

public class StoredProcedureCallExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "username";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            // Calling a Stored Procedure
            String procedureCall = "{ call my_procedure(?, ?) }";
            try (CallableStatement callableStatement = connection.prepareCall(procedureCall)) {
                callableStatement.setInt(1, 123); // Set input parameters
                callableStatement.registerOutParameter(2, Types.VARCHAR); // Register out parameter

                callableStatement.execute();

                String result = callableStatement.getString(2); // Retrieve the out parameter value
                System.out.println("Result from the stored procedure: " + result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

• FunctionCallExample: Demonstrates calling a function using CallableStatement. It registers an out parameter to capture the function's return value.

• StoredProcedureCallExample: Demonstrates calling a stored procedure using CallableStatement. It sets input parameters, registers an out parameter, and retrieves the out parameter value after execution.