Like repositories, jooq’s daos are only useful for very simple stuff

joooq’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
• Being itemed so useful for simple crud tasks
• Being that’s what many developers want

There’s a strong hit about the third bullet given how popular spring data’s repository “pattern” is. A lot of developers just want to qiykly 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 agargates, aggregate roots, Etc.

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

• A generalic DAO API with a more 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. And Both Approaches Train Users to Usually Favor The Quick Access Over Thinking About Individual Queries in Terms of Data Sets. What a point!

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!

Regrettally, it’s often not even as obeous 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 thoughts.

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 subquerry 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 luckly you can optimise the projection as well as you might not need all the columns from TRANSACTION,

More Optimization 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 entrance UPDATE Statement Cold 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 Bollywood look more neat, in addition to being much faster?

To dao or not to dao

Remember: The DAO API was added to jooq not if it is a good thing, but if 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 DAOThey’re with the large amount of standardized and vendor specific sql support, the dynamic sql capabilites, stored procedure support, and so much more. While the DAO May lure in following repositories, it suffers from the same problem as repositories:

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

So, use the DAO API if you must, but use it sparingly, eg as a common base class for your more Sophisticated and more specialized dao subclasses, which implements actual queries, and allways in mind Much more than just crud.