A small library that simplifies Spring JdbcTemplate/JdbcClient CRUD operations and relationship queries by making them less verbose. Use its API where beneficial and keep using JdbcTemplate/JdbcClient for other functionality.
Just by annotating the models that you would use with JdbcTemplate/JdbcClient, you get single-line CRUD and a fluent API to assemble relationships from your custom queries.
New Feature Alert: Relationships can be assembled from your custom queries. 🎇
Features
Maven coordinates
Example code
JDK and Spring version requirements
Spring bean configuration for SimpleJdbcMapper
Annotations
Assembling relationships from custom queries 🎇
BLOB CLOB mapping
Enum mapping
Configuration for auto assigning @CreatedBy, @UpdateBy, @CreatedOn, @UpdatedOn
Accessing JdbcClient JdbcTemplate
Logging
Limitations
Upgrading to 2.x from 1.x
Troubleshooting
Bug Report
- One liners for CRUD. All it takes is to use the 3 annotations @Table, @Id and @Column.
- A fluent API to assemble relationships from your custom queries.
- Simple configuration similar to JdbcTemplate/JdbClient configuration.
- No need to write custom row mappers for your queries by using the library's EntityRowMapper.
- Transactions are managed using Spring as the library uses JdbcTemplate for database interaction.
- To view the SQL use Spring logging (See logging).
- Tests are run against PostgreSQL, MySQL, Oracle, SQLServer. Should work with other databases.
- Only dependency is Spring JDBC libraries. No other external dependencies.
//@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);
// find all with sort (ORDER BY clause)
List<Product> products = sjm.findAll(Product.class, new SortBy("availabeDate"), new SortBy("price", "DESC"));
// delete by object
sjm.delete(product);
// delete by id
sjm.deleteById(Product.class, 5);
/*
For custom queries which retrieve mapped objects use getEntitySqlColumns() to get the columns for the sql
to work with EntityRowMapper (see its javadoc). Note in this case the 'name' property is mapped to the 'product_name' column.
*/
String sql = """
SELECT %s
FROM product
WHERE product_name = ?"
""".formatted(sjm.getEntitySqlColumns(Product.class));
// Using Spring's JdbcTemplate and library's EntityRowMapper to get the results for the above sql
List<Product> products = sjm.getJdbcTemplate().query(sql, sjm.newEntityRowMapper(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 relationships section on how to populate relationships from your custom queries.
// The demo application (link provided at top) is easy to install and run where you can see example code in action.
// 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);
}
Using the 3 annotations @Table, @Id and @Column you get single line CRUD and also will be able to assemble relationships from custom queries. Other annotations are for audit fields and optimistic locking.
@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 classes 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 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.
- auto generated id usage
@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.
- NON auto generated id usage
@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)
SimpleJdbcMapper tries to infer the correct SQL type from the Java types but some times it cannot (mostly byte[] and database driver specific java types). In these cases explicitly declaring the SQL type is a best practice to ensure correctness, improve performance, and correctly handle NULL values.
To identify which properties in the mappings the SQL type is unknown do the following:
- Turn on sql logging. (See logging section)
- Issue an insert/update for the entity.
- Check for 'SQL type unknown' in the logs. Below is example of the logs. Line 1 has the SQL type while line 2 the SQL type is unknown:
Setting SQL statement parameter value: column index 6, parameter value [true], value class [java.lang.Boolean], SQL type 16
Setting SQL statement parameter value: column index 7, parameter value [[B@56d742ad], value class [[B], SQL type unknown
- For your specific database and database column type find the corresponding SQL type and assign it using @Column(sqlType = somesqltype).
@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 be 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.
}An implementation of the relationship examples below and other features of the library are available in the Demo Application. It has an embedded H2 database with tables populated. There is no configuration required and is simple to install and run.
- Order has many OrderLine
/*
Define the multiple mapped entities you want to select. Make sure the table aliases match that in query.
Mapped Class | Table |Alias
------------------------------------
Order.class | orders | "o"
OrderLine.class | order_line | "ol"
*/
MultiEntity multiEntity = new MultiEntity().add(Order.class, "o").add(OrderLine.class, "ol");
// Get the columns for your 'SELECT' using getMultiEntitySqlColumns().
// Using java text blocks makes the queries more readable.
String sql = """
SELECT %s
FROM orders o
LEFT JOIN order_line ol ON o.id = ol.order_id
WHERE o.total_amount >= ?
ORDER BY o.order_date DESC, ol.id
""".formatted(sjm.getMultiEntitySqlColumns(multiEntity));
// Use JdbcTemplate with the library's ResultSetExtractor to execute the query. RelationshipMapper holds the query results.
RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity), someAmount);
// Define the toMany relationship between Order and OrderLine.
Relationship orderToManyOrderLine = Relationship.type(Order.class).toMany(OrderLine.class).joinOn("id", "orderId").populate("orderLines");
// Assemble the relationship from the query results.
List<Order> orders = relationshipMapper.assemble(orderToManyOrderLine).getList(Order.class);
- The sql columns generated by getMultiEntitySqlColumns() and the ResultSetExtractor work together. The extractor expects columns to be in a specific order, so do not modify the sql columns string from getMultiEntitySqlColumns().
- RelationshipMapper holds the query results and works with the information provided by the Relationship. It does not access the database or use SimpleJdbcMapper. It just assembles the relationship tree.
- A Relationship is thread safe. This allows you to define a relationship once and use it with different query results which have the same relationship.
- Order has many OrderLine
- OrderLine has one Product
// Define your entities. The aliases should exactly match the aliases used in the query.
MultiEntity multiEntity = new MultiEntity().add(Order.class, "o").add(OrderLine.class, "ol").add(Product.class,"p");
// Build your custom sql using the sql columns from sjm.getMultiEntitySqlColumns(multiEntity)
String sql = """
SELECT %s
FROM orders o
LEFT JOIN order_line ol ON o.id = ol.order_id
LEFT JOIN product p ON ol.product_id = p.id
WHERE o.total_amount >= ?
ORDER BY o.order_date DESC, ol.id
""".formatted(sjm.getMultiEntitySqlColumns(multiEntity));
// Use JdbcTemplate with the library's ResultSetExtractor to execute the query. RelationshipMapper holds the query results.
RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity), someAmount);
// Define the toOne relationship between OrderLine and Product.
Relationship orderLineToOneProduct = Relationship.type(OrderLine.class).toOne(Product.class).joinOn("productId", "id").populate("product");
// Define the toMany relationship between order and orderLine.
Relationship orderToManyOrderLine = Relationship.type(Order.class).toMany(OrderLine.class).joinOn("id", "orderId").populate("orderLines");
// Assemble the relationships. getList() returns the orders.
List<Order> orders = relationshipMapper.assemble(orderLineToOneProduct, orderToManyOrderLine).getList(Order.class);
- Employee has many Skill through intermediate table 'employee_skill'
// Define the entities. The intermediate table employe_skill (in this case corresponds to EmployeeSkill class) needs to be selected also.
MultiEntity multiEntity = new MultiEntity().add(Employee.class, "emp").add(EmployeeSkill.class, "es").add(Skill.class, "s");
// Build your custom sql using the sql columns from sjm.getMultiEntitySqlColumns(multiEntity)
String sql = """
SELECT %s
FROM employee emp
LEFT JOIN employee_skill es ON emp.id = es.employee_id
LEFT JOIN skill s ON es.skill_id = s.id
ORDER BY emp.id, s.id
""".formatted(sjm.getMultiEntitySqlColumns(multiEntity));
// Use JdbcTemplate with the library's ResultSetExtractor to execute the query. RelationshipMapper holds the query results.
RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity));
// Define the toMany relationship between Employee and Skill through the intermediate class EmployeeSkill.
Relationship employeeToManySkill =
Relationship.type(Employee.class).toMany(Skill.class).through(EmployeeSkill.class, "employeeId", "skillId").populate("skills");
List<Employee> employees = relationshipMapper.assemble(employeeToManySkill).getList(Employee.class);
RelationshipMapper is agnostic of the source of the data. Results from multiple queries can be used to populate relationships.
- Order paginated list - 1st query
- OrderLine has one Product - 2nd query to get the corresponding OrderLine and Product for the orders
From the results of these 2 queries the relationships can be assembled.
// The paginated query. Note the PAGINATED SYNTAX is different for different databases. Use the one for your database.
// Since its a single entity use getEntitySqlColumns() to get the sql columns
String orderSql = """
SELECT %s
FROM orders
ORDER BY orders.id
OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
""".formatted(sjm.getEntitySqlColumns(Order.class));
// For a single entity use EntityRowMapper with JdbcTemplate to get the results.
List<Order> orders = sjm.getJdbcTemplate().query(orderSql, sjm.newEntityRowMapper(Order.class), 0, 10);
// Get the order id list
List<Integer> orderIds = orders.stream().map(Order::getId).toList();
// 2nd query. Get the corresponding OrderLine/Product for the order ids. For the IN clause we have to use a named parameter.
MultiEntity multiEntity = new MultiEntity().add(OrderLine.class, "ol").add(Product.class, "p");
String sql = """
SELECT %s
FROM order_line ol
LEFT JOIN product p ON ol.product_id = p.id
WHERE ol.order_id IN (:orderIds)
ORDER BY ol.id
""".formatted(sjm.getMultiEntitySqlColumns(multiEntity));
// Create the named parameter
MapSqlParameterSource param = new MapSqlParameterSource().addValue("orderIds", orderIds);
// Since its a named parameter query use NamedParameterJdbcTemplate.
RelationshipMapper relationshipMapper = sjm.getNamedParameterJdbcTemplate().query(sql, param, sjm.resultSetExtractor(multiEntity));
// Add orders from the first query to the relationshipMapper so that we can assemble a relationship from it.
relationshipMapper.addEntityResult(Order.class, orders, "id");
// Define the toOne relationship between OrderLine and Product
Relationship orderLineToOneProduct = Relationship.type(OrderLine.class).toOne(Product.class).joinOn("productId", "id").populate("product");
// Define the toMany relationship between Order and OrderLine.
Relationship orderToManyOrderLine = Relationship.type(Order.class).toMany(OrderLine.class).joinOn("id", "orderId").populate("orderLines");
// Assemble the relationships and getList() returns the orders
orders = relationshipMapper.assemble(orderLineToOneProduct, orderToManyOrderLine).getList(Order.class);
- Binary large object database columns should be mapped to java type byte[].
No other java type is supported. The 'sqlType' attribute of the @Column annotation with the following values are considered as Binary Large Objects by SimpleJdbcMapper:
Types.BLOB
Types.ARRAY
Types.LONGVARBINARY
Types.VARBINARY
Use the pertinent SQL type for your database and database column type.
- Character large object database columns should be mapped to java type String. No other java types are supported.
The 'sqlType' attribute of the @Column annotation with the following values are considered as Character Large Objects by SimpeJdbcMapper:
Types.CLOB
Types.NCLOB
Types.LONGVARCHAR
Types.LONGNVARCHAR
Use the pertinent SQL type for your database and database column type.
In both the cases above the whole object (images, files etc) will be read into memory. For very large objects this could create memory issues and you may want to use InputStream/Reader. To use InputStream/Reader you will have to use JdbcTemplate directly since SimpleJdbcMapper does not support these.
Some BLOB/CLOB examples below. Keep in mind depending on the versions of the databases and database column types these could be different.
Postgres:
@Column(sqlType = Types.ARRAY) //mapped to a 'bytea' database column type
private byte[] image;
@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'text' database column type
private String clobData;
MySql:
@Column(sqlType = Types.BLOB) // mapped to a 'blob' database column type
private byte[] image;
@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'text' database column type
private String textData;
Oracle:
@Column(sqlType = Types.BLOB) // mapped to a 'blob' database column type
private byte[] image;
@Column(sqlType = Types.CLOB) // mapped to a 'clob' database column type
private String clobData;
SQL Server:
@Column(sqlType = Types.LONGVARBINARY) // mapped to a 'VARBINARY(MAX)' database column type
private byte[] image;
@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'VARCHAR(MAX)' database column type
private String clobData;
Enums should be mapped to a database column which stores strings. It uses the enum.name() to get the string value.
public enum StatusEnum {
OPEN, CLOSED;
}
...
@Column
private StatusEnum status; // Mapped to a String column in the database (e.g., value '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
- @Id can only be mapped to a single database column. Multi-column ids are not supported.
- No support for table/column names with spaces in them.
- Primitives not supported. Use the corresponding java wrapper classes in your mappings.
Use JdbcTemplate/JdbcClient directly to handle these cases.
The 2.x release has removed the dependency on database table column meta-data for mapping totally.
Difference from 1.x:
- SimpleJdbcMapper 2.x tries to infer the correct SQL type from the Java types but some times it cannot (Mostly byte[] and database driver specific java types). In these cases explicitly declaring the SQL type is a best practice to ensure correctness, improve performance, and correctly handle NULL values. See documentation on @Column(sqlType="somesqltype") and BLOB/CLOB mapping further above on how to figure out and set the SQL type value.
- Since 2.x does not use the database table column meta data, it cannot provide detailed messages on what went wrong with a mapping. Mapping issues will surface through sql errors thrown, which is similar to what happens when using JdbcTemplate/JdbcClient directly.
- Primitives not supported. Use corresponding java wrapper classes in your mappings
Generally the upgrade should be straight forward since API remains the same.
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.