A library that simplifies Spring JdbcTemplate/JdbcClient CRUD operations by making them less verbose. Use its API where beneficial and keep using JdbcTemplate/JdbcClient for all other functionality.
Just by annotating the models you would use with JdbcTemplate/JdbcClient, you get single-line CRUD and will be able to use Spring's row mappers like BeanPropertyRowMapper and SimplePropertyRowMapper etc for certain custom queries, avoiding writing custom row mappers.
github | Javadoc | Demo Application | Dzone Article
<dependency>
<groupId>io.github.spring-jdbc-crud</groupId>
<artifactId>simplejdbcmapper</artifactId>
<version>1.7.2</version>
</dependency> //@Table annotation is required
@Table(name="product")
public class Product {
/*
The @Id annotation is required. It can be of any type.
@Id(type=IdType.AUTO_GENERATED) - Use for ids which are auto generated by the database. Id value will
be assigned to the object on insert.
@Id - The id value will have to be manually set before invoking insert().
*/
@Id(type=IdType.AUTO_GENERATED)
private Integer id;
// The 'name' property will map to 'product_name' column in database table.
@Column(name="product_name")
private String name;
// will map to column 'sku'
@Column
private string sku;
// 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;
...
}
...
@Autowired
private SimpleJdbcMapper sjm;
...
Product product = new Product();
product.setName("some product name");
product.setSku("sku1");
product.setPrice(10.25);
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);
sjm.updateSpecificProperties(product, "price");
// find all
List<Product> products = sjm.findAll(Product.class);
// delete by object
sjm.delete(product);
// delete by id
sjm.deleteById(Product.class, 5);
/*
For custom queries use getBeanFriendlySqlColumns() to get the columns sql. It creates the appropriate column aliases
when the column name does not match the corresponding underscore case property name. This allows the usage of
Spring row mappers like BeanPropertyRowMapper, SimplePropertyRowMapper etc instead of writing custom row mappers.
Note in this case the 'name' property is mapped to the 'product_name' column.
*/
String sql = "SELECT " + sjm.getBeanFriendlySqlColumns(Product.class) + " FROM product WHERE product_name = ?";
// Using Spring's JdbcClient api for the above sql. JdbcClient is using SimplePropertyRowMapper internally here.
List<Product> products = sjm.getJdbcClient().sql(sql).param("someProductName").query(Product.class).list();
// Using Spring's JdbcTemplate api for the above sql
List<Product> products = sjm.getJdbcTemplate().query(sql, BeanPropertyRowMapper.newInstance(Product.class), "someProductName");
// find by a property value
List<Product> products = sjm.findByPropertyValue(Product.class, "sku", "some sku#");
// find by multiple property values
String[] skus = { "sku1", "sku2"};
List<Product> products = sjm.findByPropertyValues(Product.class, "sku", Array.asList(skus));
// Accessing the underlying JdbcClient, JdbcTemplate and NamedParameterJdbcTemplate.
JdbcClient jdbcClient = sjm.getJdbcClient();
JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();
// See logging section for details to view the SQL
// See troubleshooting section if you have issues.
// Thats all folks. Happy coding!!!JDK 21+
SpringBoot 3.2.3+ or Spring framework 6.1.4+
SimpleJdbcMapper should always be prepared in a Spring application context and given to services as a bean reference. It maintains state for example it caches insert/update SQL etc.
Note: An instance of SimpleJdbcMapper is thread safe once configured.
Examples for different databases below. Depending on the versions of springboot/database/driver, there could be some differences to the properties. The properties are same as datasource properties used to configure Spring JdbcClient/JdbcTemplate.
PostgreSQL
# application.properties
spring.datasource.jdbc-url=jdbc:postgresql://HOST:PORT/SCHEMA_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver
...
// DataSource properties are read from application.properties.
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource sqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
}
MySQL
# application.properties
spring.datasource.jdbc-url=jdbc:mysql://HOST:PORT/DATABASE_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
...
// DataSource properties are read from application.properties.
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource sqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
return new SimpleJdbcMapper(dataSource, null, DATABASE_NAME); // For mysql, database is synonymous with catalog
}
Oracle
# application.properties
spring.datasource.jdbc-url=jdbc:oracle:thin:@HOST:PORT/SERVICE_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
...
// DataSource properties are read from application.properties.
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource sqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
}
SQLServer
# application.properties
spring.datasource.jdbc-url=jdbc:sqlserver://HOST:PORT;databaseName=CATALOG_NAME;encrypt=true;trustServerCertificate=true;
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
...
// DataSource properties are read from application.properties.
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource sqlDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
}
@Table
Required class level annotation. The table or view should exist in database. The schema/catalog attributes set with @Table will override corresponding values on the SimpleJdbcMapper() constructor (if any). Note that table names with spaces are not supported.
Multiple objects can be mapped to the same table. For example if you have a table with large number of columns you could have 2 objects mapped to it. One with a few commonly used columns and another with all the columns.
@Table(name="product")
class Product {
...
}
@Table(name="product", schema="someSchemaName")
class Product {
...
}
@Table(name="product", catalog="someCatalogName") // for mysql, database name is synonymous with catalog name
class Product {
...
}
@Table(name="product", catalog="someCatalogName", schema="someSchemaName")
class Product {
...
}
@Id
The id property can be of any non-primitive java type. @Id can only be mapped to a single database column. Multi-column ids are not supported.
There are 2 forms of usage for this.
@Table(name="product")
class Product {
@Id(type=IdType.AUTO_GENERATED)
private Integer productId;
...
}
After a successful insert() operation the productId property will be populated with the generated id.
@Table(name="customer")
class Customer {
@Id
private Integer id;
...
}In this case you will have to manually set the id value before invoking insert()
@Column
Properties that need be persisted to the database will need @Column annotation unless the property is already annotated with one of the other annotations (@Id, @Version, @CreatedOn @CreatedBy @UpdatedOn @UpdatedBy). @Column can be used along with the other annotations to map a property to a non-default column name. The default column name is camel case property name converted to underscore case name (e.g., property 'lastName' maps to column 'last_name' by default).
@Column
This will map the property to a column using the default naming convention of camel case to underscore case. For example property 'lastName' will map to column 'last_name' by default.
@Column(name="somecolumnname")
This will map the property to the column specified by the 'name' attribute. Note that column names with spaces are not supported.
@Column(sqlType = somesqltype) Use this in cases when you need to override the database metadata sqltype. For example some postgres drivers for column definition 'TIMESTAMP WITH TIMEZONE' return Types.TIMESTAMP instead of Types.TIMESTAMP_WITH_TIMEZONE, which causes conversion failures when used with java type OffsetDateTime. You can use the above attribute to override the database metadata sqlType.
@Version
This annotation is used for optimistic locking. It has to be of type Integer. Will be set to 1 when record is created and will incremented on updates. On updates if the version is stale an OptimisticLockingException will be thrown. @Column annotation can be used with the property to map to a non-default column name.
@CreatedOn
If a Supplier is configured using simpleJdbcMapper.setRecordAuditedOnSupplier(), it will be used to to set the value for the @CreatedOn property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.
@UpdatedOn
If a Supplier is configured using simpleJdbcMapper.setRecordAuditedOnSupplier(), it will be used to to set the value for the @UpdatedOn property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.
@CreatedBy
If a Supplier is configured using simpleJdbcMapper.setRecordAuditedBySupplier(), it will be used to to set the value for the @CreatedBy property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.
@UpdatedBy If a Supplier is configured using simpleJdbcMapper.setRecordAuditedBySupplier(), it will be used to to set the value for the @UpdatedBy property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.
Annotation examples:
@Table(name="product")
class Product {
@Id(type=IdType.AUTO_GENERATED)
private Integer productId;
@Column(name="product_name")
private String name; // maps to product_name column
@Column
private String productDescription // defaults to column product_description
@CreatedOn
private LocalDateTime createdTimestamp; // defaults to column name created_timestamp.
// If a Supplier is configured it will use the value from Supplier to populate this property.
// Make sure Supplier type and property type match
@CreatedBy
private String createdByUser; // defaults to column created_by_user.
// If a Supplier is configured it will use the value from Supplier to populate this property.
// Make sure Supplier type and property type match
@UpdatedOn
private LocalDateTime updatedAt; // defaults to column name updated_at.
// If a Supplier is configured it will use the value from Supplier to populate this property.
// Make sure Supplier type and property type match
@Column(name="last_update_user")
@UpdatedBy
private String updatedBy; // maps to column last_update_user.
// If a Supplier is configured it will use the value from Supplier to populate this property.
// Make sure Supplier type and property type match
@Version
private Integer version; // defaults to column version,
// Property type should be Integer. Used for optimistic locking.
// Gets incremented every successful update.
}Binary large object database columns should be mapped to java type byte[]. No other type is supported.
Character large object database columns should be mapped to java types String or other CharSequence or char[]. No other types are supported.
If there is a need to use InputStream/Reader use JdbcTemplate directly.
Enums can be mapped to a database column which stores strings.
public enum StatusEnum {
OPEN, CLOSED;
}
...
@Column
private StatusEnum status; // Mapped to a String column in the database (e.g., 'OPEN')
...@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
SimpleJdbcMapper simpleJdbcMapper = new SimpleJdbcMapper(dataSource);
// Provide your own custom Supplier. Make Sure the type returned by Supplier matches the type
// of the Property you are annotating. Generally 'audited by' is got from a thread local variable
// for example when using spring security.
simpleJdbcMapper.setRecordAuditedBySupplier(() -> "tester");
simpleJdbcMapper.setRecordAuditedOnSupplier(() -> LocalDateTime.now());
return simpleJdbcMapper;
} JdbcClient jdbcClient = sjm.getJdbcClient();
JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();There is no requirement that you have to use the underlying JdbcClient/JdbcTemplate for your custom queries. You can create your own JdbcClient/JdbcTemplate and use it.
Uses the same logging configurations as Spring. In application.properties:
# 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=TRACE
Use JdbcTemplate/JdbcClient to handle these cases.
1.Connection issues:
Try to connect to the database using Spring JdbcClient or JdbcTemplate without the SimpleJdbcMapper and issue a simple query. The datasource configuration parameters are exactly the same.
2.Table does not exist or a similar exception
For MySql try setting the 'catalog' parameter on constructor of SimpleJdbcMapper() (3rd argument) or set the 'catalog' attribute on the @Table annotation of the object. Database name is considered the same as catalog name for mysql.
Example:
new SimpleJdbcMapper(dataSource, null, "DATABASE_NAME");
Or
@Table(name="sometablename", catalog="DATABASE_NAME");For Postgres/Oracle/Sqlserver try setting the 'schema' parameter on constructor of SimpleJdbcMapper() (2nd argument) or set the 'schema' attribute on the @Table annotation of the object.
new SimpleJdbcMapper(dataSource, "SCHEMA_NAME");
Or
@Table(name="sometablename", schema="SCHEMA_NAME");3.Postgres and OffsetDateTime
Some postgres drivers for column definition 'TIMESTAMP WITH TIMEZONE' return java.sql.Types.TIMESTAMP instead of java.sql.Types.TIMESTAMP_WITH_TIMEZONE, which causes conversion failures when used with OffsetDateTime. Do the following to override the database metadata sql type.
@Column(sqlType = Types.TIMESTAMP_WITH_TIMEZONE)
private OffsetDateTime someOffsetDateTime;