Chapter 4: Building a DAO Layer (Evolving the Generated DAO Layer)

At this point, we know how to enable the jOOQ Code Generator and how to express queries via the jOOQ DSL API, and we have a decent level of understanding of how jOOQ works. In other words, we know how to start and configure a Spring Boot application relying on jOOQ for the persistence layer implementation.

In this chapter, we tackle different approaches for organizing our queries in a Data Access Object (DAO) layer. Being a Spring Boot fan, you are most probably familiar with a DAO layer that is repository-centric, therefore, you'll see how jOOQ fits into this context. By the end of this chapter, you'll be familiar with the following:

• Hooking the DAO layer
• Shaping the DAO design pattern and using jOOQ
• Shaping the generic DAO design pattern and using jOOQ
• Extending the jOOQ built-in DAO

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter04.

Hooking the DAO layer

DAO is a design pattern that stands for Data Access Object. Following the separation of logic principle, DAO separates the data persistence logic in a dedicated layer and abstracts away the low-level database operations. Typically, the DAO is sketched around three main components:

• A model representing the data that is transferred between layers (for example, the Sale model corresponds to the SALE database table)
• An interface containing the API that should be implemented for the model (for example, SaleDao, or in Spring terms, SaleRepository)
• A concrete implementation of this interface (for example, SaleDaoImpl, or in Spring terms, SaleRepositoryImpl)

The following diagram represents the relationships between these components using Sale, SaleRepository, and SaleRepositoryImpl:

Figure 4.1 – DAO design pattern

If you are a JdbcTemplate fan, you most probably recognize this pattern in your own applications. On...

Shaping the DAO design pattern and using jOOQ

Let's assume that we have a bunch of SQLs written in jOOQ for the SALE table, and we want to shape a simple DAO implementation around them. This is quite simple because all we have to do is to follow Figure 4.1 from the previous section.

First of all, the model is provided as POJOs by the jOOQ generator (we can have user-defined POJOs as well), therefore, we already have the Sale POJO. Next, we write SaleRepository:

@Repository
@Transactional(readOnly=true)
public interface SaleRepository {
  public List<Sale> findSaleByFiscalYear(int year);
  public List<Sale> findSaleAscGtLimit(double limit);    
}

SaleRepositoryImpl provides a jOOQ implementation for these two methods:

@Repository
public class SaleRepositoryImpl implements SaleRepository {
  private final DSLContext ctx;
  public SaleRepositoryImpl(DSLContext ctx) {
    this...

Shaping the generic DAO design pattern and using jOOQ

Trying to implement the generic DAO from Figure 4.2 starts with the generic interface, ClassicModelsRepository:

@Repository
@Transactional(readOnly = true)
public interface ClassicModelsRepository<T, ID> {
  List<T> fetchAll();
  @Transactional
  void deleteById(ID id);
}

While ClassicModelsRepository contains the common query methods, SaleRepository extends it to add specific query methods, as follows:

@Repository
@Transactional(readOnly = true)
public interface SaleRepository
        extends ClassicModelsRepository<Sale, Long> {
  public List<Sale> findSaleByFiscalYear(int year);
  public List<Sale> findSaleAscGtLimit(double limit);
}

The implementation of SaleRepository provides implementations for methods from both interfaces:

@Repository
public class SaleRepositoryImpl implements SaleRepository...

Extending the jOOQ built-in DAO

Let's assume that you have configured the jOOQ generator to output the generated DAO layer in the jooq.generated.tables.daos package. While the generated DAO exposes common query methods such as insert(), update(), delete(), and a few specific queries of the fetchBy...() or fetchRange...() types, we want to extend it with our own query methods.

Important Note

This is one of my favorite ways of writing a DAO layer in a Spring Boot and jOOQ application.

The jOOQ DAO layer contains a set of generated classes that mirrors the database tables and extends the built-in org.jooq.impl.DAOImpl class. For example, the jooq.generated.tables.daos.SaleRepository class (or, jooq.generated.tables.daos.SaleDao if you keep the default naming strategy used by jOOQ) corresponds to the SALE table. In order to extend SaleRepository, we have to take a quick look at its source code and highlight a part of it as follows:

@Repository
public class SaleRepository...

Summary

In this chapter, we have covered several approaches to developing, from scratch, a DAO layer or evolving the jOOQ-generated DAO layer in a Spring Boot and jOOQ application. Each of the presented applications can serve as a stub application for your own applications. Just choose the one that is suitable for you, replace the schema, and start developing.

In the next chapter, we'll use jOOQ to express a wide range of queries involving SELECT, INSERT, UPDATE, and DELETE.