Like repositories, jOOQ’s DAOs are only useful for very simple stuff

jOOQ’s DAO API is one of jOOQ’s most controversial features. When it was first implemented, it was implemented merely:

• Because it was so easy to implement
• Because it seemed so useful for simple CRUD tasks
• Because that’s what many developers want

There’s a strong hint about the third bullet given how popular Spring Data’s repository “pattern” is. A lot of developers just want to quickly fetch and store data, without giving individual queries much thought.

A fun fact is that most people use Spring Data just for CRUD, despite that the framework has been created with a strong opinion about DDD and the entailing concepts, like aggregates, aggregate roots, etc.

The jOOQ DAO was easy to implement because it consists simply of:

• A generic DAO API with a few common methods
• A generated class per table that implements the bindings to generated POJOs and some auxiliary query methods

In other words, for every table (such as ACCOUNT) you’ll get a “free” Account POJO and a “free” AccountDao DAO, which you can use as follows:

// DAOs are often injected in one way or another
@Autowired
AccountDao dao;

// And then:
dao.insert(new Account(1, "name"));
Account account = dao.findById(1);

Seems useful enough, no?

Why not use SQL, instead?

Just like Spring Data is mostly used for quick data access (rather than thorough DDD application), so are DAOs. And both approaches train users to usually favour the quick access over thinking about individual queries in terms of data sets. What a pity!

How much code do you see that looks like this, as soon as you start using DAOs or repositories?

for (Account account : 
    accountDao.fetchByType("Some Type")
) {
    for (Transaction transaction : 
        transactionDao.fetchByTransactionId(account.getId()
    ) {
        doSomething(transaction);
    }
}

The dreaded N+1 problem manifests in the above code snippet, as we run a query fetching transactions for every account!

Regrettably, it’s often not even as obvious as above, where two nested loops are located right at the same spot in your code. A lot of times, these loops are hidden inside of “reusable” methods, and called in loops without giving them much thought.

When in fact, the following query wouldn’t be so much harder to write:

for (Transaction transaction : ctx
    .selectFrom(TRANSACTION)
    .where(exists(
        selectOne()
        .from(TRANSACTION.account())
        .where(TRANSACTION.account().TYPE.eq("Some Type"))
    ))
    .fetchInto(Transaction.class)
) {
    doSomething(transaction);
}

Looks pretty clear, no?

Note the above example is using a jOOQ 3.19 feature called implicit path correlation, where the correlated subquery can be expressed using an implicit join path TRANSACTION.account(), rather than adding a more verbose, but equivalent predicate of the form TRANSACTION.ACCOUNT_ID.eq(ACCOUNT.ID).

Taking it one step further, it’s quite likely you can optimise the projection as well as you might not need all the columns from TRANSACTION.

More optimisation with writes

In fact, let’s look at doSomething(), which might also be using a DAO:

void doSomething(Transaction transaction) {
    transaction.setSomeCounter(transaction.getSomeCounter + 1);
    transactionDao.update(transaction);
}

So, not only did we N+1 with our queries, but the entire UPDATE statement could be implemented in bulk (not just batch!) as follows:

ctx
    .update(TRANSACTION)
    .set(TRANSACTION.SOME_COUNTER, TRANSACTION.SOME_COUNTER.plus(1))
    .where(exists(
        selectOne()
        .from(TRANSACTION.account())
        .where(TRANSACTION.account().TYPE.eq("Some Type"))
    ))
    .execute();

Doesn’t that look much more neat, in addition to being much faster?

To DAO or not to DAO

Remember: The DAO API was added to jOOQ not because it is a good thing, but because it was easy to do and use, and because it’s a popular “pattern.” It’s a very limited API useful only for very primitive CRUD.

But jOOQ’s biggest powers aren’t with the DAO. They’re with the large amount of standardised and vendor specific SQL support, the dynamic SQL capabilities, stored procedure support, and so much more. While the DAO may lure in folks used to using repositories, it suffers from the same problem as repositories:

The simple fact that it is hardly ever enough for any serious database interaction.

So, use the DAO API if you must, but use it sparingly, e.g. as a common base class for your more sophisticated and more specialised DAO subclasses, which implement actual queries, and always keep in mind that SQL is so much more than just CRUD.