DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • The Magic of Spring Data
  • Constructing Secure and Strong Data Access Layers With JPA
  • Distributed Task Synchronization: Leveraging ShedLock in Spring
  • Kafka JDBC Source Connector for Large Data

Trending

  • *You* Can Shape Trend Reports: Join DZone's Software Supply Chain Security Research
  • Scalable System Design: Core Concepts for Building Reliable Software
  • Accelerating AI Inference With TensorRT
  • Unlocking AI Coding Assistants Part 1: Real-World Use Cases
  1. DZone
  2. Coding
  3. Frameworks
  4. Using Spring JdbcTemplate With JdbcTemplateMapper

Using Spring JdbcTemplate With JdbcTemplateMapper

In this article, learn about JdbcTemplateMapper, a simple library that makes the usage of Spring JdbcTemplate less verbose for CRUD and relationship queries.

By 
Antony Joseph user avatar
Antony Joseph
·
Oct. 10, 23 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
8.0K Views

Join the DZone community and get the full member experience.

Join For Free

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

Java
 
@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. 

Java
 
// 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.

Java
 
// 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

XML
 
<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

Java
 
  @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.

Properties files
 
# 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
Data access Java Database Connectivity Relational database Spring Framework Visual Component Library

Published at DZone with permission of Antony Joseph. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • The Magic of Spring Data
  • Constructing Secure and Strong Data Access Layers With JPA
  • Distributed Task Synchronization: Leveraging ShedLock in Spring
  • Kafka JDBC Source Connector for Large Data

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!