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

jOOQ’s DAO API is considered one of 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 powerful trace concerning the third bullet given how fashionable Spring Information’s repository “sample” is. Loads of builders simply need to rapidly fetch and retailer information, with out giving particular person queries a lot thought.

A enjoyable reality is that most individuals use Spring Information only for CRUD, regardless of that the framework has been created with a powerful opinion about DDD and the entailing ideas, like aggregates, mixture roots, and so forth.

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 (resembling ACCOUNT) you’ll get a “free” Account POJO and a “free” AccountDao DAO, which you should use as follows:

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

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

Appears helpful sufficient, no?

Why not use SQL, as a substitute?

Similar to Spring Information is usually used for fast information entry (relatively than thorough DDD software), so are DAOs. And each approaches prepare customers to often favour the short entry over excited about particular person queries by way of 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. Loads 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 put in writing:

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 characteristic referred to as implicit path correlation, the place the correlated subquery may be expressed utilizing an implicit be a part of path TRANSACTION.account(), relatively 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 probably 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

The truth is, 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 complete UPDATE assertion could possibly 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 quicker?

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 indisputable fact that it’s infrequently sufficient for any critical database interplay.

So, use the DAO API if you happen to should, however use it sparingly, e.g. as a typical base class on your extra subtle and extra specialised DAO subclasses, which implement precise queries, and at all times needless to say SQL is a lot extra than simply CRUD.