Building a CRUD Application With Spring and SimpleJdbcMapper
In this article, learn about SimpleJdbcMapper, a simple wrapper around Spring JDBC libraries, making CRUD operations less verbose.
·
Join the DZone community and get the full member experience.
Join For FreeSpring Framework's JDBC core package, designed to simplify database interactions using JDBC, is a popular option for applications to persist data to a relational database. The central classes used are JdbcClient with its fluent API and JdbcTemplate with the older classic API.
When using these APIs, the CRUD operations tend to be verbose. The SimpleJdbcMapper mitigates this verbosity and also stays out of the way so you can keep using all the features of JdbcClient/JdbcTemplate.
How to Do CRUD Operations With SimpleJdbcMapper
@Table(name = "product")
public class Product {
@Id(type = IdType.AUTO_GENERATED)
private Integer id;
// maps to 'sku' column in database.
@Column
private String sku;
// The 'name' property will map to 'product_name' column in database.
@Column(name = "product_name")
private String name;
// maps to 'description' column in database
@Column
private String description;
// will map to column 'available_date' by default using camel case to underscore case naming convention
@Column
private LocalDateTime availableDate;
// will map to 'price' column by default
@Column
private Double price;
// No annotations for this property so excluded from inserts/updates/queries etc
private String someNonDatabaseProperty;
// There are no mappings for relationships. SimpleJdbcMapper is not an ORM.
...
}
...
@Autowired
private SimpleJdbcMapper sjm;
...
Product product = new Product();
product.setSku("sku1");
product.setName("some product name");
product.setPrice(10.25);
product.setDescription("some description");
product.setAvailableDate(LocalDateTime.now());
// because id type is AUTO_GENERATED its value will be assigned on insert.
sjm.insert(product);
// find by id
product = sjm.findById(Product.class, product.getId());
// update product
product.setPrice(11.50);
sjm.update(product);
// updateSpecificProperties() updates only the specified properties passed as arguments.
// Will issue an SQL update only for price.
product.setPrice(12.50);
product.setDescription("New description");
sjm.updateSpecificProperties(product, "price", "description");
// find all
List<Product> products = sjm.findAll(Product.class);
// delete by object
sjm.delete(product);
// delete by id
sjm.delete(Product.class, 5);Querying
Spring's Simple PropertyRowMapper and BeanPropertyRowMapper do help reduce verbosity when querying, but have the limitation that Java property names must match the database column names. For most applications, DBAs determine the database naming convention, which generally does not match the Java property names. If this is the case, you will have to write custom row mappers or 'SELECT' statements with all the column names and their corresponding column aliases to map to the Java property names. SimpleJdbcMapper simplifies this.
The getBeanFriendlySqlColumns() method, as the name suggests, will create the column names and their corresponding column aliases so that the queries can be used with Spring's SimplePropertyRowMapper and BeanPropertyRowMapper, etc., to populate the models.
String sql =
"SELECT " + sjm.getBeanFriendlySqlColumns(Product.class)
+ " FROM product WHERE product_name = ?";
// Using JdbcClient fluent api for the above sql.
// In this case JdbcClient is using SimplePropertyRowMapper behind the scenes.
List<Product> products =
sjm.getJdbcClient()
.sql(sql)
.param("someProductName")
.query(Product.class)
.list();
// Using JdbcTemplate api for the above sql
List<Product> products =
sjm.getJdbcTemplate()
.query(
sql,
BeanPropertyRowMapper.newInstance(Product.class),
"someProductName"
);The Spring row mappers like SimplePropertyRowMapper and BeanPropertyRowMapper use reflection (behind the scenes, these row mappers use BeanWrapperImpl class). For most use cases, the performance of these row mappers is good enough. Where performance is critical, you should write a custom row mapper.
Configuration
The SimpleJdbcMapper configuration is similar to that of JdbcTemplate/JdbcClient.
# application.properties
# H2 database configuration. For other database configurations see SimpleJdbcMapper documentation at:
# https://github.com/spring-jdbc-crud/SimpleJdbcMapper#spring-bean-configuration-for-simplejdbcmapper
spring.datasource.jdbc-url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
...
@Component
public class SimpleJdbcMapperConfig {
// see application.properties for spring.datasource configuration
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource sqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
// This configuration is for H2 database.
// See SimpleJdbcMapper documentation for other database configurations.
return new SimpleJdbcMapper(dataSource);
}
}Maven Details
SimpleJdbcMapper is available on Maven Central:
<dependency>
<groupId>io.github.spring-jdbc-crud</groupId>
<artifactId>simplejdbcmapper</artifactId>
<version>1.6.0</version> <!-- check maven central or SimpleJdbMapper github site and use the latest version -->
</dependency>Logging
Logging is done using the underlying Spring JDBC classes, so you can see the SQL being issued. You probably don't want to log the sql parameters (the last entry below) in production unless you are troubleshooting something, because it will flood the logs with SQL parameter details.
# log the sql
logging.level.org.springframework.jdbc.core.JdbcTemplate=TRACE
# need this to log the INSERT statements
logging.level.org.springframework.jdbc.core.simple.SimpleJdbcInsert=TRACE
# log the parameters of sql statement
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACEJdbcClient/JdbcTemplate
You can get the underlying JdbcClient/JdbcTemplate with the code below. You can also create your own JdbcClient/JdbcTemplate and use it, since you already have the Datasource information.
JdbcClient jdbcClient = sjm.getJdbcClient();
JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();Requirements
One thing to note is that you will need Java 21+ and Spring 6.1.4+ or Spring Boot 3.2.3+ to use the SimpleJdbcMapper library.
The library also provides other developer-friendly features. Its annotations allow automatic setting of Created By, Updated By, Created On, and Updated On fields, so developers won't be forced to set those properties manually. It provides optimistic locking via versioning, which is particularly useful in web applications to prevent multiple users from updating the same record with stale data.
Source Code and Instructions
SimpleJdbcMapper and source code for this tutorial are both available on GitHub, along with instructions for running the code and links to the pertinent classes. The tutorial comes with an embedded database, so no setup is required. When you run it, you will see all the SQL issued on the console.
Opinions expressed by DZone contributors are their own.
Comments