Using Spring JdbcTemplate With JdbcTemplateMapper

Spring's JdbcTemplate provides data access using JDBC for relational databases. It is an option for applications where using an ORM with its nuances/complexities may not be a good fit.

JdbcTemplate abstracts away a lot of the JDBC low-level code, but remains verbose. When going the JdbcTemplate route, generally developers implement an abstraction layer to minimize the verbosity. 

The JdbcTemplateMapper library is a wrapper around JdbcTemplate with the goal of making the usage of JdbcTemplate less verbose. Sprinkle the models with a few annotations, and you get single-line CRUD and fluent style queries for relationships like hasOne, hasMany, hasMany through (many to many), etc., and some other developer-friendly features. Use it where appropriate and keep using JdbcTemplate for other features like stored procedures, custom queries, batch processing, etc.

Example for CRUD

@Table(name = "employee")
public class Employee {
  @Id(type = IdType.AUTO_INCREMENT)
  private Integer id; // maps to id column in table. The id gets assigned on insert.
  
  @Column
  private String lastName; // maps to last_name column by default 
  
  @Column
  private String firstName; // maps to first_name column by default

  @Column
  private LocalDateTime startDate; // maps to start_date column by default

  @Column
  private Integer departmentId; // maps to department_id. Foreign key

  private Department department; // there are no mappings for relationships
  ...
}

@Table(name = "department")
public class Department {
  @Id(type = IdType.AUTO_INCREMENT)
  private Integer id; // maps to id column in table. The id gets assigned on insert

  @Column(name = "department_name")
  private String name; // maps to a non default column department_name in table
  
  private List<Employee> employees = new ArrayList<>(); // there are no mappings for relationships
  ...
}

// Usage:
  ...
  @Autowired
  private JdbcTemplateMapper jtm; 
  ...
  Department dept = new Department();
  dept.setName("HR department");
  jtm.insert(dept); // auto assigns id on insert since id configured as auto increment
  
  Employee emp = new Employee();
  emp.setFirstName("John");
  emp.setLastName("Doe");
  emp.setStartDate(LocalDateTime.now());
  emp.setDepartmentId(dept.getId());
  jtm.insert(emp); // auto assigns id on insert since id configured as auto increment
  
  emp = jtm.findById(Employee.class, emp.getId());
  emp.setLastName("Smith");
  jtm.update(emp);
    
...                                

Querying Relationships

The JdbcTemplate verbosity comes into play when you need to query information from multiple tables, like querying relationships. Let's say you want to do a join between 2 tables to populate a relationship. You cannot use SELECT * in this case because of the multiple tables, so now you have to type out the names of the columns in your SELECT statement. In most cases this is not enough because column names from the multiple tables could conflict; for example, ids and audit fields, etc. This requires aliases to be added for the columns to prevent conflicts in JDBC. When dealing with real-world applications where tables tend to have quite a few columns, this gets extremely verbose. Note this is just for the SELECT statement. You will also need to implement custom RowMapper to map the values from the ResultSet to your object where you have to individually map each ResultSet column to the object. 

Compare that to the code below when using the Query feature of JdbcTemplateMapper. It takes care of generating the SQL. Behind the scenes, it is using JdbcTemplate.query() to retrieve the records. 

// query the employee hasOne department relationship
List<Employee> employees = Query.type(Employee.class) // owning class
                                .hasOne(Department.class) // related class
                                .joinColumnOwningSide("department_id") // join column (the foreign key) is on owning (employee) table
                                .populateProperty("department")
                                .execute(jtm);

// query the department hasMany employee relationship
List<Department> departments = 
        Query.type(Department.class)
             .hasMany(Employee.class)
             .joinColumnManySide("department_id") // join column (the foreign key) is on many side table employee
             .populateProperty("employees")
             .where("department.department_name like ?", "HR%")
             .orderBy("employee.last_name")
             .execute(jtm);

Other Querying Features

Pagination is supported by Query using the limitOffsetClause(). To get the total counts for pagination, QueryCount can be used.

QueryMerge merges the results of a query with the results of another. This comes in handy when multiple relationships need to be queried.

A simplistic example of pagination with Query, QueryMerge, and QueryCount is given below. For a more elaborate example see the JdbcTemplateMapper documentation.

// Paginated query for departments
List<Department> departments = 
        Query.type(Department.class)
             .where("department_name like ?", "HR%")
             .orderBy("department_name")
             .limitOffsetClause("LIMIT 10 OFFSET 0")  // MySQL syntax. Would be different for other databases.
             .execute(jtm);
      
// QueryMerge will issue an SQL 'IN' clause with department ids and populate the employees
// for the corresponding departments
QueryMerge.type(Department.class)
          .hasMany(Employee.class)
          .joinColumnManySide("department_id") // join column (the foreign key) is on many side table employee
          .populateProperty("employees")
          .execute(jtm, departments); // merges employees to their corresponding department  
      
// To get total count of records
Integer count = QueryCount.type(Department.class)
                          .where("department_name like ?", "HR%")
                          .execute(jtm);
      

Optimistic Locking for Updates

For some applications, optimistic locking is critical where you don't want updates trampling each other. The library provides support for optimistic locking using the @Version annotation. An OptimisticLocking exception is thrown when updating stale data.

pom.xml Entry for jdbctemplatemapper

<dependency>
   <groupId>io.github.jdbctemplatemapper</groupId>
   <artifactId>jdbctemplatemapper</artifactId>
   <version>2.3.1</version> <!-- Check and use lastest from jdbctemplatemapper site -->
</dependency>

Spring Bean Configuration

  @Bean
  public JdbcTemplateMapper jdbcTemplateMapper(JdbcTemplate jdbcTemplate) {
    return  new JdbcTemplateMapper(jdbcTemplate);
  }

Logging the SQL

Make the following entries in the application.properties file to log the SQL.

# log the sql
logging.level.org.springframework.jdbc.core.JdbcTemplate=TRACE

# 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

The library provides record audit annotations like @CreatedBy, @UpdatedBy, @CreatedOn, and @UpdatedOn which are automatically assigned if the models are annotated with these. It supports Java8, Java11, and Java17 (all the LTS Java versions) and the corresponding Spring Boot versions. For example, it works on Spring Boot 3.x versions, which use Java17. The tests for the library are run against PostgreSQL, MySQL, Oracle, and SQLServer. Depending on the versions of the database/drivers, it should work with other relational databases.

The only dependency for the library is spring-boot-starter-jdbc (which is the dependency needed for Spring JdbcTemplate).

The source code for this tutorial is available here on GitHub. The tutorial goes against a MySQL database, and the JdbcTemplate configuration is provided in the source code. Instructions to the run the code are also provided.

• The JdbcTemplateMapper GitHub project