> For the complete documentation index, see [llms.txt](https://muhammad-tri-wibowo.gitbook.io/java-tutorials/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://muhammad-tri-wibowo.gitbook.io/java-tutorials/java-database-connectivity-jdbc/invoking-function-and-stored-procedure-using-jdbc.md).

# 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):**

```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):**

```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.