Like repositories, jOOQ’s DAOs are solely helpful for quite simple stuff

jOOQ’s DAO API is one in all jOOQ’s most controversial options. When it was first carried out, it was carried out merely:

• As a result of it was really easy to implement
• As a result of it appeared so helpful for easy CRUD duties
• As a result of that’s what many builders need

There’s a robust trace concerning the third bullet given how fashionable Spring Knowledge’s repository “sample” is. A number of builders simply wish to rapidly fetch and retailer information, with out giving particular person queries a lot thought.

A enjoyable truth is that most individuals use Spring Knowledge only for CRUD, regardless of that the framework has been created with a robust opinion about DDD and the entailing ideas, like aggregates, combination roots, and so on.

The jOOQ DAO was simple to implement as a result of it consists merely of:

• A generic DAO API with a number of frequent strategies
• A generated class per desk that implements the bindings to generated POJOs and a few auxiliary question strategies

In different phrases, for each desk (equivalent to ACCOUNT) you’ll get a “free” Account POJO and a “free” AccountDao DAO, which you need to use as follows:

// DAOs are sometimes injected in a technique or one other
@Autowired
AccountDao dao;

// After which:
dao.insert(new Account(1, "title"));
Account account = dao.findById(1);

Appears helpful sufficient, no?

Why not use SQL, as a substitute?

Similar to Spring Knowledge is usually used for fast information entry (fairly than thorough DDD utility), so are DAOs. And each approaches practice customers to often favour the short entry over eager about particular person queries when it comes to information units. What a pity!

How a lot code do you see that appears like this, as quickly as you begin utilizing DAOs or repositories?

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

The dreaded N+1 downside manifests within the above code snippet, as we run a question fetching transactions for each account!

Regrettably, it’s usually not whilst apparent as above, the place two nested loops are situated proper on the similar spot in your code. A number of instances, these loops are hidden inside “reusable” strategies, and referred to as in loops with out giving them a lot thought.

When actually, the next question wouldn’t be a lot tougher to jot down:

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

Seems to be fairly clear, no?

Word the above instance is utilizing a jOOQ 3.19 function referred to as implicit path correlation, the place the correlated subquery will be expressed utilizing an implicit be part of path TRANSACTION.account(), fairly than including a extra verbose, however equal predicate of the shape TRANSACTION.ACCOUNT_ID.eq(ACCOUNT.ID).

Taking it one step additional, it’s fairly seemingly you’ll be able to optimise the projection as effectively as you may not want all of the columns from TRANSACTION.

Extra optimisation with writes

In reality, let’s have a look at doSomething(), which could even be utilizing a DAO:

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

So, not solely did we N+1 with our queries, however the whole UPDATE assertion might be carried out in bulk (not simply batch!) as follows:

ctx
    .replace(TRANSACTION)
    .set(TRANSACTION.SOME_COUNTER, TRANSACTION.SOME_COUNTER.plus(1))
    .the place(exists(
        selectOne()
        .from(TRANSACTION.account())
        .the place(TRANSACTION.account().TYPE.eq("Some Kind"))
    ))
    .execute();

Doesn’t that look rather more neat, along with being a lot sooner?

To DAO or to not DAO

Bear in mind: The DAO API was added to jOOQ not as a result of it’s a good factor, however as a result of it was simple to do and use, and since it’s a preferred “sample.” It’s a very restricted API helpful just for very primitive CRUD.

However jOOQ’s greatest powers aren’t with the DAO. They’re with the massive quantity of standardised and vendor particular SQL help, the dynamic SQL capabilities, saved process help, and a lot extra. Whereas the DAO could lure in of us used to utilizing repositories, it suffers from the identical downside as repositories:

The straightforward proven fact that it’s hardly sufficient for any critical database interplay.

So, use the DAO API in the event you should, however use it sparingly, and at all times needless to say SQL is a lot extra than simply CRUD.