CallableStatement

import java.sql.*;

public class CallableStatementInParams {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret")) {

            // ── Stored procedure with only IN parameters, no return value ──
            // Underlying procedure signature (illustrative): 
            //   PROCEDURE apply_discount(p_product_id IN BIGINT, p_percent IN NUMERIC)
            try (CallableStatement cs = conn.prepareCall("{call apply_discount(?, ?)}")) {
                cs.setLong(1, 42L);
                cs.setBigDecimal(2, new java.math.BigDecimal("0.15"));
                cs.execute();
            }

            // ── Stored function returning a single scalar value ────────────
            // Underlying function signature (illustrative):
            //   FUNCTION calculate_tax(p_amount NUMERIC) RETURNS NUMERIC
            try (CallableStatement cs = conn.prepareCall("{? = call calculate_tax(?)}")) {
                cs.registerOutParameter(1, Types.NUMERIC);  // the function's return value
                cs.setBigDecimal(2, new java.math.BigDecimal("199.99"));
                cs.execute();
                java.math.BigDecimal tax = cs.getBigDecimal(1);
                System.out.println("Tax: " + tax);
            }
        }
    }
}

import java.sql.*;
import java.math.BigDecimal;

public class CallableStatementOutParams {
    // Illustrative procedure signature:
    //   PROCEDURE process_refund(
    //       p_order_id   IN     BIGINT,
    //       p_amount     INOUT  NUMERIC,   -- caller supplies requested amount,
    //                                      -- procedure may cap/adjust it
    //       p_new_status OUT    VARCHAR    -- procedure reports resulting status
    //   )
    public static void processRefund(Connection conn, long orderId, BigDecimal requestedAmount)
            throws SQLException {
        String sql = "{call process_refund(?, ?, ?)}";
        try (CallableStatement cs = conn.prepareCall(sql)) {
            cs.setLong(1, orderId);                              // pure IN

            cs.setBigDecimal(2, requestedAmount);                // INOUT: input half
            cs.registerOutParameter(2, Types.NUMERIC);           // INOUT: output half

            cs.registerOutParameter(3, Types.VARCHAR);           // pure OUT

            cs.execute();

            BigDecimal actualAmount = cs.getBigDecimal(2);  // possibly adjusted by the procedure
            String status = cs.getString(3);

            System.out.println("Refund processed: $" + actualAmount + " — status: " + status);
        }
    }
}

import java.sql.*;

public class CallableStatementMultipleResultSets {
    // Illustrative procedure (SQL Server-style) returning two result sets:
    // a summary row, then a detail table.
    public static void runReportProcedure(Connection conn, long reportId) throws SQLException {
        try (CallableStatement cs = conn.prepareCall("{call generate_report(?)}")) {
            cs.setLong(1, reportId);

            boolean hasResultSet = cs.execute();

            while (hasResultSet) {
                try (ResultSet rs = cs.getResultSet()) {
                    ResultSetMetaData meta = rs.getMetaData();
                    System.out.println("--- Result set with " + meta.getColumnCount() + " columns ---");
                    while (rs.next()) {
                        StringBuilder row = new StringBuilder();
                        for (int i = 1; i <= meta.getColumnCount(); i++) {
                            row.append(rs.getString(i)).append("  ");
                        }
                        System.out.println(row);
                    }
                }
                // Advance to the next result set; false means none remain
                hasResultSet = cs.getMoreResults();
            }
        }
    }
}