Sunday, 10 March 2019

Spring - Calling a stored procedure with SimpleJdbcCall | Spring JDBC tutorial

🚀 Want to Master Spring & Java Development?

Subscribe to Ram N Java for beginner-friendly coding tutorials and practical guides!

Calling Stored Procedures with SimpleJdbcCall in Spring JDBC

Executing database stored procedures in standard JDBC traditionally involves configuring verbose CallableStatement instances, registering OUT parameters manually, and handling raw SQL strings. Spring JDBC provides SimpleJdbcCall, a multi-threaded, metadata-aware class designed to simplify calling stored procedures and database functions with minimal configuration.

1. What is SimpleJdbcCall?

SimpleJdbcCall is part of the org.springframework.jdbc.core.simple package. It automatically reads database metadata to inspect parameter names, types, and directions (IN, OUT, and INOUT):

Automatic Parameter Detection: Discovers procedure signature parameters from the database catalog, reducing manual registration.
Zero Boilerplate: Eliminates explicit SQL execution queries like {call procedure_name(?, ?)}.
Result Mapping: Easily captures returned scalar values, output parameter maps, or multiple ResultSet rows using RowMappers.

2. How to Configure SimpleJdbcCall

To set up SimpleJdbcCall inside your DAO or repository layer:

Instantiate with DataSource or JdbcTemplate: Initialize the instance by passing your configured DataSource or JdbcTemplate bean.
Declare the Procedure Name: Call withProcedureName("procedure_name") to specify the target procedure.
Register Out Parameters (Optional): If the database metadata does not supply return definitions, declare parameters explicitly with declareParameters(...).
Compile the Call: The configuration compiles automatically on the first call, making subsequent invocations fast and thread-safe.

3. Passing IN Parameters and Retrieving OUT Results

Invoking the procedure and obtaining results is straightforward:

Supplying Parameters: Use MapSqlParameterSource to add IN parameters by chaining .addValue("paramName", value).
Executing the Call: Call simpleJdbcCall.execute(paramSource), which returns a Map<String, Object> containing all OUT parameter values indexed by parameter name.
Accessing Outputs: Retrieve values cleanly from the map using the corresponding output parameter name key.

4. Key Advantages

Using SimpleJdbcCall provides significant improvements over low-level JDBC approaches:

Cleaner DAO Layer: Cuts out dozens of lines of repetitive setup code.
Thread Safety: Configure once during bean initialization and safely reuse across multiple concurrent requests.
Fewer Runtime Bugs: Eliminates mismatched index positions and incorrect SQL type registrations.

📺 Explore More Spring JDBC Tutorials

Continue expanding your Java and Spring knowledge with these related tutorials from our channel: