What are stored procedures and how can they be invoked via JDBC?

Stored procedures are executable blocks of code (PL/SQL, T-SQL, etc.) stored in a database. They encapsulate business logic, improve performance (by caching the execution plan), and enhance security.

They can be invoked via JDBC Connection using CallableStatement.

Example:

import java.sql.*;

public class StoredProcedureCall {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             CallableStatement cstmt = conn.prepareCall("{CALL my_stored_procedure(?, ?)}")) { // Syntax for calling a procedure

            // Set input parameters
            cstmt.setInt(1, 10);
            cstmt.setString(2, "input_value");

            // Register output parameters (if any)
            // cstmt.registerOutParameter(3, Types.VARCHAR); // If the 3rd parameter is VARCHAR

            // Execute the procedure
            cstmt.execute();

            // Retrieve output parameters (if any)
            // String outputValue = cstmt.getString(3);
            // System.out.println("Output value: " + outputValue);

            // Get the result set if the procedure returns one
             if (cstmt.getMoreResults()) {
                 try (ResultSet rs = cstmt.getResultSet()) {
                    while (rs.next()) {
                        // Process ResultSet rows
                        System.out.println(rs.getString(1));
                    }
                 }
             }


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Main steps:

1. Obtain a Connection.
2. Create a CallableStatement using connection.prepareCall(). The call syntax depends on the DBMS, but usually {CALL procedure_name(?, ?, ...)} for procedures or {? = CALL function_name(?, ?, ...)} for functions.
3. Set input parameter values using set<Type>().
4. Register output parameters (if any) using registerOutParameter(), specifying the index and data type from java.sql.Types.
5. Execute the procedure with execute().
6. Retrieve output parameter values (if any) using get<Type>().
7. Process the ResultSet if the procedure returns a result, using getResultSet()."}]}]}