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

jOOQ’s DAO API is certainly one of jOOQ’s most controversial options. When it was first applied, it was applied 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 common Spring Information’s repository “sample” is. Numerous 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 Information simply CRUD, regardless of that the framework has been created with a powerful opinion about DDD and the entailing ideas, like aggregates, combination 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 (corresponding to 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 method 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 generally used for fast information entry (slightly than thorough DDD utility), so are DAOs. And each approaches prepare customers to normally favour the short entry over serious 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 Sort")
) {
    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 at the same time as apparent as above, the place two nested loops are positioned proper on the similar spot in your code. Numerous occasions, these loops are hidden within “reusable” strategies, and referred to as in loops with out giving them a lot thought.

When the truth is, the next question wouldn’t be a lot more durable to write down:

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

Seems fairly clear, no?

Observe the above instance is utilizing a jOOQ 3.19 function referred to as implicit path correlation, the place the correlated subquery could be expressed utilizing an implicit be part of path TRANSACTION.account(), slightly 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 may optimise the projection as properly as you may not want all of the columns from TRANSACTION.

Extra optimisation with writes

The truth is, let’s take 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 your entire UPDATE assertion could possibly be applied 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 Sort"))
    ))
    .execute();

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

To DAO or to not DAO

Keep 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 well-liked “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 might lure in people used to utilizing repositories, it suffers from the identical downside as repositories:

The easy undeniable fact that it’s infrequently sufficient for any critical database interplay.

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