A simple wrapper around Spring JDBC libraries that makes database CRUD operations less verbose. Use it along with Spring JdbcClient/JdbcTemplate.
github | Javadoc | Demo Application
//@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 '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.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.delete(Product.class, 5);
/*
The following method gets the sql for the columns. Works well with Spring row mappers like
BeanPropertyRowMapper(), SimplePropertyRowMapper() etc. Will create the needed column aliases
when the column name does not match the corresponding underscore case property name.
Note that 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
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");
// 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 with the initial setup.
// Thats all folks. Happy coding!!!JDK 21+
Spring framework 6.1.4+ or SpringBoot 3.2.3+
<dependency>
<groupId>io.github.spring-jdbc-crud</groupId>
<artifactId>simplejdbcmapper</artifactId>
<version>1.6.1</version>
</dependency>SimpleJdbcMapper should always be prepared in a Spring application context and given to services as a bean reference. It maintains state, for example caches table meta-data, insert/update SQL etc.
Note: An instance of SimpleJdbcMapper is thread safe
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).
@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
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 new 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 with the other annotations to map to a different column name.
@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 name attribute.
@Column(sqlType = somejavasqlTypes) This will override the sqlType of the database metadata for the column. Use this in cases where drivers don't return the correct sql type. For example some postgres drivers for column definition 'TIMESTAMP WITH TIMEZONE' return java.sql.Types.TIMESTAMP instead of Types.TIMESTAMP_WITH_TIMEZONE, which causes conversion failures when used with 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 this to map to a different 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 different 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 different 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 to map to a different 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 different 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.
}@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.
simpleJdbcMapper.setRecordAuditedBySupplier(() -> "tester");
simpleJdbcMapper.setRecordAuditedOnSupplier(() -> LocalDateTime.now());
return simpleJdbcMapper;
} JdbcClient jdbcClient = sjm.getJdbcClient();
JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();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
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;