Comparing Database Integration Approaches in Spring Boot: Spring Data JPA vs. Spring JDBC vs. jOOQ

Currently, the trending architecture involves applications being stateless and storing states in databases. It is very important what type of application you choose - blocking or non-blocking. Since each of them requires its own description and in-depth analysis, in this article, we will consider only the options for blocking the most popular applications.

Constraints and Requirements.

As the candidate for integration, we will consider a reactive application written in Kotlin, with Spring Boot as the primary framework. We need to determine the database integration approach.

So there are criteria. Our approach should:

• work with Spring
• be well-known in the community
• have an extensive documentation

Compatibility with Spring is essential, encompassing aspects like configuration, health monitoring, metrics tracking, and transaction management. Non-blocking capabilities are imperative, given our utilization of WebFlux and Reactor. It should facilitate straightforward query execution with a minimalistic implementation, reducing the need for excessive boilerplate code.

The solution should offer flexibility for intricate queries, allowing for customization through custom queries or even dynamic SQL statements. Ideally, it should support both the Repository pattern and the Active Record pattern. Ideally, it should provide a streamlined method for constructing complex objects in a single database request.

Here are some of the most popular and trending solutions you might want to consider using:

• Spring Data JPA
• Spring JDBC
• jOOQ

Let's take a look at each of them.

Spring Data JPA.

It is a framework within the Spring ecosystem that simplifies database access in Java applications using the Java Persistence API (JPA). It provides a higher-level, repository-centric approach to interact with relational databases, enabling developers to write data access code more easily and efficiently. Spring Data JPA abstracts many common data access tasks, reducing the need for boilerplate code and provides a consistent and standardized way to work with databases across various JPA implementations. It also supports features like query creation from method names and custom query definitions, making it a valuable tool for developing data-centric applications in a Spring environment.

It's particularly well-suited for building classic RESTful applications with clearly defined models and CRUD endpoints.

Here's a breakdown of its strengths and weaknesses:

Strengths of Spring Data JPA:

• It eliminates the need for writing plain SQL, allowing you to create queries through conventional interface methods.
• Beneficial: It offers numerous tools for parameterized queries and SQL generation.
• Effective: Provides in-box solutions for handling entity relations, such as many-to-many and one-to-one associations.
• Versatile: Readily accommodates custom SELECT queries with dynamic parameters.

Weaknesses of Spring Data JPA:

• Drawback: Tends to be verbose, resulting in a wordy codebase.
• Shortcoming: Requires an additional layer for entity DTOs, necessitating mappers to convert entities to business-level DTOs.
• Challenge: Employs a fair amount of in-box magic, including various intricate triggers that demand monitoring.
• Issue: Requires some workarounds to function effectively in Kotlin, like sacrificing immutability at the entity level."

This revised version provides a clearer and more structured presentation of the strengths and weaknesses of the Spring ORM realization.

However, we will undoubtedly find ourselves asking whether we want to use the ORM pattern. The initial response might be, 'Yes, absolutely!' However, delving deeper into this question reveals that there is more to analyze.

So, what is ORM?

Object-Relational Mapping (ORM) is a programming technique and framework used in software development to bridge the gap between object-oriented programming languages and relational databases. It provides an abstraction layer that allows developers to interact with a database using object-oriented code and concepts rather than writing raw SQL queries.

If you are ready to use it, be sure to use Spring Data JPA.

Spring JDBC

For those who have to write complex SQL queries and want more controls on their DAO level.

Spring JDBC provides a simplified and efficient approach to working with relational databases in Java applications. It offers a set of APIs and abstractions that streamline database access, making it easier for developers to manage database connections, execute SQL queries, and handle results.

There are Spring JDBC pros:

• Advantageous Control with SQL: It excels in offering direct access to plain SQL queries, granting developers a high degree of control over database interactions. This capability is particularly beneficial when specific or complex queries are required.
• User-Friendly with No Mysteries: The framework's user-friendliness stands out as there is no reliance on obscure "magic" behaviors. Developers can work with clarity and predictability, making it suitable for those who prefer transparent database interactions.
• Native Database Interaction: It not only facilitates queries but also extends its utility to execute functions and interact with the complete database API in a native manner. This opens doors to a broader range of database operations beyond conventional querying, enhancing its versatility.
• Robust Entity-Level Model Control: One of its strengths lies in affording full control at the entity level, enabling developers to define attributes like immutability and nullability. This level of customization is vital for shaping data models to meet specific project requirements.

There are Spring JDBC cons:

• Dependency on RowMappers for Data Mapping: A drawback is the need for developers to create and manage rowMappers to map result data to business-level DTOs. This can introduce additional complexity to data mapping processes.
• Manual SQL Query Writing: In certain scenarios, manual SQL query writing is necessary, which may increase development effort, particularly for complex queries.
• INSERT/UPDATE Query Authoring for CRUD: For basic CRUD operations it necessitates the manual crafting of INSERT and UPDATE queries. This can be seen as a potential drawback when compared to frameworks that offer automatic query generation.
• Custom SELECT Query Limitations with Dynamic Parameters: It may face limitations when dealing with custom SELECT queries that require dynamic parameters. This can be a challenge when dealing with more complex scenarios.

jOOQ

jOOQ - is a Java-based database access library and code generation tool that simplifies database interaction for Java applications. It stands for "Java Object Oriented Querying" and provides a domain-specific language (DSL) for querying relational databases. jOOQ focuses on offering a strongly typed, SQL-centric approach to database access, which enables developers to write database queries in a more structured and type-safe manner.

Advantages of jOOQ:

• Type Safety: jOOQ generates Java classes representing your database schema, tables, and records. This means that you can write database queries using a fluent and type-safe DSL (Domain Specific Language) in Java, which reduces the chances of runtime errors in your SQL queries.
• Performance: jOOQ can help you write optimized SQL queries by taking advantage of database-specific features and optimizations. This can lead to improved query performance.
• Integration with Spring Boot: Spring Boot makes it easy to integrate jOOQ into your application. You can configure jOOQ in your application's configuration files and inject the generated DSL context into your services and repositories.
• Code Generation: jOOQ supports code generation from your database schema, which means you can automatically generate Java classes that correspond to your tables and records. This reduces the amount of boilerplate code you need to write.
• Database Agnostic: jOOQ supports various databases, making it a good choice if you need to work with multiple databases or if you want the flexibility to switch databases in the future without rewriting your queries.
• Reduced SQL Injection Risk: Since jOOQ uses parameterized queries by default, it helps mitigate the risk of SQL injection attacks.

Disadvantages of using jOOQ:

• Learning Curve: Learning jOOQ's DSL and how to use it effectively can take some time, especially for developers who are not familiar with it. Understanding the generated code and how to customize it may also require a learning curve.
• Code Generation Overhead: jOOQ relies on code generation, which can introduce an additional build step in your development process. This can be seen as an overhead for some developers who prefer more dynamic approaches.
• Limited Abstraction: jOOQ is closer to SQL and provides a relatively low-level abstraction. If you prefer a higher-level Object-Relational Mapping (ORM) solution, jOOQ may not be the best choice.
• Non-standard SQL: jOOQ might not support all the database-specific features or syntax you need, and you may need to resort to writing custom SQL in some cases.

As you can see, jOOQ has quite significant negative aspects. Considering that it requires additional configuration to work with the spring boot application, this lowers it to last place in the list of proposed options. But you should definitely keep it in mind if, for some reason, you are limited in the use of Spring Data JPA or Spring JDBC.

Photo by Tobias Fischer on Unsplash