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

Stored procedures are a set of SQL instructions that are compiled and stored in the database. They are executed as a single unit and can accept parameters and return values. They allow encapsulating business logic, improving performance, and security.

To call stored procedures via JDBC, the CallableStatement interface is used.

import java.sql.*;

public class StoredProcedureExample {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase"; // Your database URL
        String user = "myuser";
        String password = "mypassword";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            // Calling a procedure without parameters
            String sqlNoParams = "{CALL my_procedure_no_params()}";
            try (CallableStatement stmtNoParams = conn.prepareCall(sqlNoParams)) {
                stmtNoParams.execute();
                System.out.println("Procedure without parameters executed.");
            }

            // Calling a procedure with input parameters
            String sqlWithInParams = "{CALL my_procedure_with_in(?, ?)}";
            try (CallableStatement stmtWithInParams = conn.prepareCall(sqlWithInParams)) {
                stmtWithInParams.setString(1, "Value1"); // Setting first IN parameter
                stmtWithInParams.setInt(2, 42);           // Setting second IN parameter
                stmtWithInParams.execute();
                System.out.println("Procedure with input parameters executed.");
            }

            // Calling a procedure with an output parameter
            String sqlWithInOutParams = "{CALL my_procedure_with_out(?, ?)}";
            try (CallableStatement stmtWithInOutParams = conn.prepareCall(sqlWithInOutParams)) {
                stmtWithInOutParams.setInt(1, 123); // Setting IN parameter
                stmtWithInOutParams.registerOutParameter(2, Types.VARCHAR); // Registering OUT parameter
                stmtWithInOutParams.execute();
                String outValue = stmtWithInOutParams.getString(2);
                System.out.println("Procedure with output parameter executed. Output value: " + outValue);
            }

            // Calling a procedure that returns a ResultSet
             String sqlWithResultSet = "{CALL my_procedure_return_rs()}";
             try (CallableStatement stmtWithResultSet = conn.prepareCall(sqlWithResultSet)) {
                 boolean hasResultSet = stmtWithResultSet.execute();

                 if (hasResultSet) {
                     try (ResultSet rs = stmtWithResultSet.getResultSet()) {
                         while (rs.next()) {
                             // Processing data from ResultSet
                             System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
                         }
                     }
                 }
             }


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

Main steps:

1. Obtain a connection to the database (Connection).
2. Create a CallableStatement object using connection.prepareCall(). The call string should be in the format {CALL procedure_name(...)}.
3. If the procedure has input parameters (IN or INOUT), set their values using setXyz() methods. Parameter indexing starts at 1.
4. If the procedure has output parameters (OUT or INOUT), register them beforehand with registerOutParameter(int parameterIndex, int sqlType).
5. Execute the stored procedure using execute() or executeQuery() (if it returns a ResultSet).
6. Retrieve output parameter values using getXyz() methods.
7. Process the ResultSet if the procedure returns one.
8. Close CallableStatement, ResultSet, and Connection. It is recommended to use try-with-resources.

Using CallableStatement allows working with various parameter types of stored procedures, including arrays, structures, and database-specific object types.