Retrieve All Records from Stored Procedures Using SimpleJdbcCall
Stored procedures in relational databases are often used not just to perform updates or compute single output values, but to return entire result sets. In Spring JDBC, SimpleJdbcCall provides an elegant, multi-threaded mechanism to call stored procedures and map returned rows directly into domain objects or lists using RowMapper.
1. Returning ResultSets from Stored Procedures
When a database stored procedure executes a SELECT query, it opens a cursor or returns a ResultSet:
Database Encapsulation: Complex join queries or business filtering logic remain encapsulated inside the database procedure.
Automatic ResultSet Binding: Spring's SimpleJdbcCall intercepts the returned cursor and binds it directly to Java objects without manual iteration over JDBC result sets.
Thread Safety: Like other Spring JDBC template classes, a configured SimpleJdbcCall is thread-safe and can be reused concurrently across multiple requests.
2. Configuring returningResultSet with RowMapper
To map multiple returned rows to Java objects, configure returningResultSet on your SimpleJdbcCall instance:
Specify Procedure Name: Call withProcedureName("procedure_name") to identify the target stored procedure.
Bind ResultSet to RowMapper: Use returningResultSet("resultSetKey", new BeanPropertyRowMapper<>(TargetClass.class)) or supply a custom RowMapper implementation.
Zero Boilerplate: Spring automatically handles column-to-property conversion, connection management, and resource cleanup.
3. Executing the Call and Extracting the Record List
Executing the procedure and accessing your records takes only a couple of lines:
Execute the Call: Call simpleJdbcCall.execute() (or pass a MapSqlParameterSource if the procedure accepts filter parameters).
Extract the List: Access the returned Map<String, Object> using the key specified in returningResultSet.
Typed List Result: Cast the map entry directly to List<TargetClass> to obtain the complete record collection ready for use in your application.
4. Practical Summary & Best Practices
Key tips for production environments:
Always configure the SimpleJdbcCall instance during DAO initialization or bean definition rather than inside method calls.
Use BeanPropertyRowMapper for quick POJO mapping when column names match Java property names (camelCase to snake_case).
📺 Explore More Spring JDBC Tutorials
Continue expanding your Java and Spring knowledge with these related tutorials from our channel:
Calling Stored Functions with SimpleJdbcCall in Spring JDBC
In addition to calling stored procedures, Spring JDBC's SimpleJdbcCall provides seamless support for executing database stored functions. Unlike stored procedures which return values through output parameters or result sets, stored functions compute and return a single scalar value directly. With SimpleJdbcCall, invoking stored functions requires minimal setup and zero SQL boilerplate.
1. Procedures vs. Functions in SimpleJdbcCall
While both are database routines, stored functions have specific differences:
Direct Return Value: Stored functions return an explicit scalar value via a RETURN statement rather than through output parameters.
Configuration Method: Instead of withProcedureName(...), you configure withFunctionName(...) on your SimpleJdbcCall instance.
Single-Value Execution: Using executeFunction(...) allows you to directly retrieve the typed return value without extracting it from a map.
2. Configuring SimpleJdbcCall for Stored Functions
Setting up SimpleJdbcCall to target a database function is clean and intuitive:
Initialize with DataSource: Instantiate SimpleJdbcCall by injecting your configured DataSource or JdbcTemplate bean.
Specify the Function Name: Call withFunctionName("function_name") to indicate the target function routine.
Compile Once: Configure the call instance once inside your DAO bean or repository constructor to ensure thread-safe reuse.
3. Executing the Function and Retrieving Results
Invoking the database function takes only a couple of lines:
Supply Input Parameters: Use MapSqlParameterSource to pass arguments corresponding to the function's parameter signature.
Execute with Return Type: Call simpleJdbcCall.executeFunction(ReturnType.class, paramSource), specifying the expected return type (such as String.class, Integer.class, or Double.class).
Direct Value Access: The method returns the typed result directly, eliminating manual casting.
4. Key Advantages
Utilizing SimpleJdbcCall for database functions provides significant benefits:
Type Safety: Direct mapping into target Java wrapper classes eliminates casting bugs.
Zero Boilerplate: No need for manual CallableStatement setups or raw SQL syntax like {? = call function_name(?)}.
Metadata Awareness: Automatically resolves parameter bindings and types directly from the database schema.
📺 Explore More Spring JDBC Tutorials
Continue expanding your Java and Spring knowledge with these related tutorials from our channel:
Calling Stored Procedures with Explicit Parameters Using SimpleJdbcCall
While Spring JDBC’s SimpleJdbcCall is capable of discovering stored procedure parameters automatically via database metadata, certain databases or driver configurations restrict metadata access. In enterprise production environments, explicitly declaring parameters using declareParameters(...) provides fine-grained control, optimal performance, and deterministic parameter mapping.
1. Why Declare Parameters Explicitly?
Defining parameters explicitly offers key structural and architectural benefits:
Avoid Database Metadata Lookups: Bypasses catalog queries at startup, which speeds up initialization and works even when database user permissions restrict metadata visibility.
Precise SQL Type Mapping: Explicitly defines SQL types (such as Types.VARCHAR or Types.INTEGER) for both input and output parameters.
Consistent Cross-Platform Execution: Ensures predictable behavior across different database engines like MySQL, Oracle, PostgreSQL, and SQL Server.
2. Using declareParameters with SqlParameter & SqlOutParameter
Spring provides specialized parameter classes to declare procedure signatures clearly:
SqlParameter: Used to define an IN parameter, specifying its name and standard JDBC type from java.sql.Types.
SqlOutParameter: Used to declare an OUT parameter that captures return values from the procedure.
SqlInOutParameter: Used when a parameter supplies an initial value and receives a modified output upon execution.
3. Executing the Procedure and Handling Output
Once configured, executing the stored procedure and parsing output takes minimal effort:
Bind Values: Use MapSqlParameterSource to provide values corresponding to each declared SqlParameter.
Run Execute: Call simpleJdbcCall.execute(parameters) to invoke the database routine.
Extract Results: The returned Map<String, Object> contains values mapped by the exact names specified in your SqlOutParameter declarations.
4. Practical Summary & Best Practices
For critical enterprise applications:
Combine withoutProcedureColumnMetaDataAccess() with declareParameters(...) when connecting to locked-down production databases.
Initialize and configure the SimpleJdbcCall instance once inside your DAO bean for thread-safe concurrent execution.
📺 Explore More Spring JDBC Tutorials
Continue expanding your Java and Spring knowledge with these related tutorials from our channel:
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:
Efficient Data Insertion: SimpleJdbcInsert with MapSqlParameterSource
In Spring JDBC, SimpleJdbcInsert streamlines database write operations by reading table metadata directly and eliminating the need for boilerplate INSERT INTO SQL statements. When coupled with MapSqlParameterSource, mapping input parameters becomes structured, highly readable, and type-safe.
1. Why Use MapSqlParameterSource?
While SimpleJdbcInsert can accept standard Java Map objects, MapSqlParameterSource provides key benefits tailored for JDBC operations:
Explicit SQL Type Specification: Supports explicit SQL types (e.g., Types.VARCHAR, Types.INTEGER) when inserting null or custom values.
Spring-Native Integration: Implements the SqlParameterSource interface natively, allowing seamless interoperability across Spring JDBC components.
2. Setting Up SimpleJdbcInsert
Configuring SimpleJdbcInsert in your data access layer is quick and clean:
Instantiate with DataSource: Initialize the instance by passing your configured DataSource bean.
Set Table Name: Use withTableName("table_name") to specify the target database table.
Restrict Columns (Optional): Optionally define specific columns using usingColumns(...) to let database defaults handle the rest.
Generated Keys: Use usingGeneratedKeyColumns(...) if your table features auto-increment primary keys.
3. Executing Inserts with MapSqlParameterSource
Executing the insert takes just two concise steps:
Build the Parameters: Instantiate new MapSqlParameterSource() and chain .addValue("column", value) for each database field.
Execute the Statement: Call simpleJdbcInsert.execute(paramSource) or executeAndReturnKey(paramSource) to run the insert and retrieve generated identifiers.
4. Key Takeaways
Using MapSqlParameterSource with SimpleJdbcInsert prevents common syntax mistakes, eliminates positional parameter bugs, and keeps your DAO implementation concise and easy to maintain.
📺 Explore More Spring JDBC Tutorials
Continue expanding your Java and Spring knowledge with these related tutorials from our channel:
Inserting Data with SimpleJdbcInsert and SqlParameterSource
When working with database persistence in Spring applications, SimpleJdbcInsert provides a clean way to insert records without writing repetitive SQL queries. Pairing it with the SqlParameterSource interface makes parameter binding structured, type-safe, and effortless.
1. What is SqlParameterSource?
In Spring JDBC, SqlParameterSource is an interface designed to provide named parameter values for SQL operations. Instead of manually putting entries into a standard Java Map, it provides structured mechanisms to supply parameter values:
MapSqlParameterSource: A flexible implementation that allows chaining parameter name-value pairs fluently via .addValue().
BeanPropertySqlParameterSource: Directly binds properties from a Java Bean or POJO to database column names using reflection.
Type Safety: Provides better support for explicit SQL type mapping when inserting custom or nullable types.
2. Using MapSqlParameterSource with SimpleJdbcInsert
MapSqlParameterSource is ideal when inserting dynamic data or when your data fields do not directly map to a single entity object:
Fluently Add Values: Use new MapSqlParameterSource().addValue("columnName", value) to map database fields.
Direct Execution: Pass the parameter source instance directly into simpleJdbcInsert.execute(parameters).
Specifying Columns with SimpleJdbcInsert in Spring JDBC
While Spring JDBC’s SimpleJdbcInsert can automatically read all table columns through database metadata, real-world database design often requires inserting data only into selected columns. By explicitly configuring target columns with usingColumns(...), you gain precise control over your SQL insert statements while skipping default or auto-managed columns.
1. Why Restrict Columns with usingColumns()?
Explicitly declaring the columns for an insert operation provides key technical advantages:
Database Defaults: Allows the database to assign predefined default values (such as timestamps or UUIDs) to columns you choose to omit.
Improved Performance: Restricts metadata retrieval and query preparation strictly to the specified fields.
Prevent Unwanted Overwrites: Guarantees that unassigned entity properties do not accidentally push NULL into columns meant to retain system defaults.
2. Configuring usingColumns in SimpleJdbcInsert
Setting up usingColumns is straightforward and chains fluently with other configuration methods during initialization:
Define the Table Name: Use withTableName("table_name") to specify the target database table.
Chain usingColumns: Call usingColumns("col1", "col2", "col3") with the specific column names to be included in the generated INSERT query.
Thread Safety: Configure the SimpleJdbcInsert instance once during DAO initialization or bean creation so it remains thread-safe for all incoming requests.
3. Passing Parameters to the Operation
Once configured, you can execute the insert using Spring’s standard parameter mapping utilities:
MapSqlParameterSource: Provide named parameters via key-value chaining matching the specified column names.
BeanPropertySqlParameterSource: Pass a domain POJO; Spring matches getter method names directly to your declared columns.
Map<String, Object>: Use a standard Java map to dynamically supply values at runtime.
4. Practical Summary & Best Practices
Whenever a database table contains auto-generated IDs, audit timestamps, or default trigger columns, explicitly specifying columns with usingColumns(...) ensures clean, error-free inserts without generating manual SQL statements.
📺 Explore More Spring JDBC Tutorials
Continue expanding your Java and Spring knowledge with these related tutorials from our channel:
Inserting Data Using SimpleJdbcInsert in Spring JDBC
In standard JDBC and basic JdbcTemplate setups, executing insert statements often requires writing boilerplate SQL strings and mapping parameters manually. SimpleJdbcInsert is a powerful multi-threaded class provided by Spring JDBC that dramatically simplifies insert operations by utilizing database metadata to configure the insert statement automatically.
1. What is SimpleJdbcInsert?
SimpleJdbcInsert is part of the org.springframework.jdbc.core.simple package. It inspects table metadata directly through the JDBC driver, allowing you to execute inserts without manually writing the standard INSERT INTO table_name (...) VALUES (...) SQL statement:
Metadata Discovery: Reads column definitions and primary key configurations automatically.
How to Retrieve Auto-Generated Keys using Spring JdbcTemplate
When working with relational databases, it is very common to have primary keys that are automatically generated (like AUTO_INCREMENT in MySQL or SERIAL in PostgreSQL). After performing an insert operation, you often need that specific ID to use it in subsequent logic.
The Challenge
Normally, a standard update or insert query returns only the number of rows affected. To get the actual key that the database just created, we need a more specialized approach within the Spring framework.
The Solution: GeneratedKeyHolder
Spring JDBC provides a handy interface called KeyHolder and its primary implementation, GeneratedKeyHolder. This object acts as a container for the keys returned by the database.
Step-by-Step Implementation
Create a KeyHolder: Instantiate a new GeneratedKeyHolder().
Use PreparedStatementCreator: Instead of a simple SQL string, you provide a callback that defines how the statement is created and specifies which columns are auto-generated.
Execute the Update: Pass both the PreparedStatementCreator and the KeyHolder to the jdbcTemplate.update() method.
Extract the Key: Access the ID using keyHolder.getKey().
Why is this useful?
This method is cleaner and more reliable than trying to query for the "last inserted ID" manually, which can cause issues in high-traffic environments where multiple users are inserting data at the same time.
Continue Learning
Check out these other Spring JDBC tutorials from the channel:
Feb 20, 2019 10:36:51 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@736e9adb: startup date [Wed Feb 20 10:36:51 IST 2019]; root of context hierarchy
Feb 20, 2019 10:36:52 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Feb 20, 2019 10:36:52 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Wed Feb 20 10:36:53 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Auto generated Key is = 10
How to Create a Database Table using Spring JdbcTemplate
Creating a table directly from your Java code is a powerful way to manage your database schema, especially during the early stages of development. In this tutorial, we will use Spring JdbcTemplate to execute a Data Definition Language (DDL) statement to create a table.
1. The "execute" Method
The JdbcTemplate class provides a method called execute(String sql). This method is typically used for DDL statements like CREATE, DROP, or ALTER. It's the simplest way to run a query that doesn't return data.
2. Defining the SQL Query
To create a table, you need a standard SQL query. In our example, we are creating a table named "address" with two columns:
ID: To store the unique identifier.
CITY: To store the city name.
3. Spring Configuration
Before we can run our code, the Spring Container needs to know how to connect to the database. We define a DataSource object in our configuration file with:
Driver Class Name
Database URL
Username and Password
4. Putting it All Together
We create a DAO (Data Access Object) class that uses the JdbcTemplate. Once the application context is loaded, we call our create method, and Spring handles the connection and execution for us!
Pro Tip:
Always check your database after running the code to verify that the table was created successfully with the correct schema.
More Spring JDBC Tutorials
Expand your knowledge with these related videos from my channel:
EmployeeDAO employeeDAO = (EmployeeDAO) context
.getBean("employeeDAO");
employeeDAO.createTable(); System.out.println("Table is created successfully."); } }
Output:
Feb 20, 2019 9:52:03 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Wed Feb 20 09:52:03 IST 2019]; root of context hierarchy
Feb 20, 2019 9:52:04 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Feb 20, 2019 9:52:05 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Wed Feb 20 09:52:16 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Table is created successfully.
