Detailed explanation
CallableStatement is a JDBC interface used to invoke stored procedures in a database. A stored procedure is a named routine whose SQL and procedural logic are maintained on the database side. Java code calls that routine rather than embedding its implementation in the application. This is useful when applications must reuse database-managed operations, work with an existing database API, or perform several related steps through one invocation. CallableStatement extends PreparedStatement and adds explicit support for registering and retrieving output parameters.
You create it through Connection.prepareCall(), commonly using JDBC escape syntax such as "{call getUserById(?, ?)}". The JDBC driver translates this syntax into the database's supported call mechanism. Parameter positions start at 1. An IN parameter supplies a value to the routine using setters such as setInt(). An OUT parameter receives a value from the routine and must be registered before execution using registerOutParameter() with an appropriate JDBC SQL type. An INOUT parameter does both, so the same position needs an input setter and output registration.
In the example, cs.setInt(1, 5) passes user ID 5 as the first argument, while cs.registerOutParameter(2, Types.VARCHAR) declares that the second argument returns character data. After cs.execute(), cs.getString(2) reads that output, assuming the procedure's actual signature matches these declarations. Procedures can also produce result sets and update counts; these are separate from OUT parameters. execute() accommodates calls with different result forms, and getResultSet(), getUpdateCount(), and getMoreResults() let the caller process those results. For maximum portability, process result sets and update counts before reading output parameters.
Interviewers usually expect you to distinguish CallableStatement from PreparedStatement without overstating the difference: PreparedStatement normally executes parameterised SQL, whereas CallableStatement provides JDBC's dedicated stored-routine calling API and OUT-parameter handling. Stored procedure calls are not automatically faster or immune to SQL injection inside dynamically constructed procedure SQL. Use try-with-resources to close the statement, handle SQLException appropriately, and follow the connection's transaction settings. Procedure signatures, supported types, and some calling conventions remain database- and driver-dependent.
Key points to remember
- CallableStatement invokes stored procedures and extends PreparedStatement.
- Create it with Connection.prepareCall(), usually using JDBC call escape syntax.
- Set IN parameters, register OUT parameters, and do both for INOUT parameters.
- Read registered output values with matching getters after execution.
- Handle result sets, update counts, resource cleanup, and transaction boundaries explicitly.
Common mistakes candidates make
- Forgetting to register an OUT parameter before executing the call.
- Using zero-based parameter positions or types that do not match the procedure signature.
- Assuming every procedure returns a ResultSet or that execute() returning false means failure.
Likely follow-up questions
How do you use an INOUT parameter?
Set its initial value and register its output type at the same parameter position before execution. After the call completes, use a matching getter to retrieve the resulting value.
What does CallableStatement.execute() return?
It returns true if the first result is a ResultSet, and false if it is an update count or there is no result. This boolean is not a success indicator; execution errors are generally reported through SQLException.
Can CallableStatement invoke a stored function?
Where supported by the database and JDBC driver, use syntax such as "{? = call function_name(?)}". Register position 1 for the function's return value and bind the function arguments starting at position 2.
Interview tip: Explain its purpose first, walk through the two parameters in the example, and finish by contrasting its output-parameter support with PreparedStatement.