Wednesday, 6 March 2019

Spring - Specifying columns for SimpleJdbcInsert | Spring JDBC tutorial | Spring JDBC

🚀 Want to Master Spring & Java Development?

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

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: