What are stored procedures and how can they be called…

A stored procedure is a set of SQL instructions stored in a database that can be invoked by name.

Advantages of using:

Performance: Code executes on the database side, reducing network traffic.
Security: Access to the procedure can be granted without direct access to tables.
Reusability: Single code for performing typical operations.
Data integrity: Guarantees execution of complex operations as an atomic transaction.

Calling using JDBC:

Create a CallableStatement: Used to call stored procedures and functions.

// Create connection
Connection conn = DriverManager.getConnection(url, user, password);

// Prepare procedure call
// Example for a procedure without parameters
String sql = "{call my_procedure()}";
CallableStatement stmt = conn.prepareCall(sql);

Example for a procedure with parameters:

// Procedure with input parameters
String sqlIn = "{call my_procedure_in(?, ?)}";
CallableStatement stmtIn = conn.prepareCall(sqlIn);

// Set values for input parameters
stmtIn.setString(1, "value1");
stmtIn.setInt(2, 123);

// Procedure with input and output parameters
String sqlInOut = "{call my_procedure_in_out(?, ?)}";
CallableStatement stmtInOut = conn.prepareCall(sqlInOut);

// Set value for input parameter
stmtInOut.setString(1, "input_value");

// Register output parameter type
stmtInOut.registerOutParameter(2, Types.VARCHAR);

Execute the call:

// Execute procedure without return value
stmt.execute();

// Execute procedure returning ResultSet (if applicable)
boolean results = stmt.execute();
while (results) {
    ResultSet rs = stmt.getResultSet();
    // Process ResultSet
    while (rs.next()) {
        // Read data
    }
    rs.close();
    results = stmt.getMoreResults();
}

Process results (for output parameters):

// Get value of output parameter
String outputValue = stmtInOut.getString(2);

Close resources:

// Close statement
stmt.close();

// Close connection
if (conn != null) {
    conn.close();
}

Important points:

The syntax {call procedure_name(?, ...)} is standard for JDBC.
For output parameters, use registerOutParameter() before execution.
The Types type from java.sql.Types is used to specify SQL types.
To handle ResultSet from stored procedures, use getResultSet() and getMoreResults() after execute().

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