How to call a stored procedure in Java?

To call a stored procedure in Java, CallableStatement from the java.sql package is used.

Main steps:

1. Obtain a connection: Use a Connection object.

   // Example of obtaining a connection (depends on driver and configuration)
   Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/mydatabase", "user", "password");
   

2. Create a CallableStatement: Pass the SQL call to the constructor prepareCall(). The syntax depends on the DBMS, but often looks like { call procedure_name(?, ?, ...) }.

   // Calling a procedure without parameters
   CallableStatement cstmt = conn.prepareCall("{ call my_procedure() }");
   
   // Calling a procedure with parameters
   CallableStatement cstmtWithParams = conn.prepareCall("{ call my_procedure_with_params(?, ?) }");
   

3. Set input parameters: If the procedure takes parameters, set them using setXxx() methods, where Xxx corresponds to the data type (e.g., setString, setInt).

   // Setting input parameters
   cstmtWithParams.setString(1, "value1");
   cstmtWithParams.setInt(2, 123);
   

4. Register output parameters (if any): If the procedure returns values via output parameters, register them using registerOutParameter(), specifying the parameter index and JDBC data type.

   // Procedure with an output parameter
   CallableStatement cstmtWithOut = conn.prepareCall("{ call my_procedure_with_out(?) }");
   cstmtWithOut.registerOutParameter(1, Types.VARCHAR); // VARCHAR as an example
   

5. Execute the call: Use the execute() method. If the procedure returns a result set, you can use executeQuery().

   // Executing the procedure
   cstmt.execute(); // For procedures without a result set
   
   // Executing a procedure that returns a result set
   // CallableStatement cstmtWithResultSet = conn.prepareCall("{ call get_data() }");
   // ResultSet rs = cstmtWithResultSet.executeQuery();
   

6. Process results: If the procedure returns a result set, process it like a regular ResultSet. If there are output parameters, retrieve their values with getXxx() methods.

   // Processing output parameter
   String result = cstmtWithOut.getString(1);
   
   // Processing result set
   /*
   while (rs.next()) {
       // Read data from the result set
   }
   rs.close();
   */
   

7. Close resources: It's important to close CallableStatement and Connection. Preferably use a try-with-resources block.

   // Closing resources with try-with-resources
   try (Connection conn = DriverManager.getConnection("...", "...", "...")) {
       try (CallableStatement cstmt = conn.prepareCall("{ call my_procedure(?) }")) {
           cstmt.setString(1, "test");
           cstmt.execute();
           // Process results
       }
   } catch (SQLException e) {
       e.printStackTrace();
   }
   

Example of calling a procedure with input and return value (via RETURN, typical for PostgreSQL/MySQL):

// Database function: CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;
try (Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/mydatabase", "user", "password")) {
    // Call function SQL (often using syntax { ? = call function_name(?, ?) })
    try (CallableStatement cstmt = conn.prepareCall("{ ? = call add_numbers(?, ?) }")) {
        // Register return value
        cstmt.registerOutParameter(1, Types.INTEGER);
        // Set input parameters
        cstmt.setInt(2, 10);
        cstmt.setInt(3, 20);

        // Execute call
        cstmt.execute();

        // Get result
        int sum = cstmt.getInt(1);
        System.out.println("Sum: " + sum); // Output: Sum: 30
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Differences between CallableStatement and PreparedStatement:

Feature	PreparedStatement	CallableStatement
Purpose	Execute parameterized SQL queries	Call stored procedures and functions
Syntax	SQL query with ?	Dialect-specific call syntax ({ CALL ... })
Output parameters	No (only result set)	Yes (via registerOutParameter)
Return value	Only result set (executeQuery)	Can be via RETURN (as OUT parameter), result set

You need to check the syntax for calling stored procedures/functions ({ call ... } or other) and support for JDBC data types (Types) for your specific DBMS.