JOOQ multiple select count in one connection with PostgreSQL

Question

I have a table SUBSCRIPTION, I want to run multiple selectCount written with JOOQ in one connection with different predicates to the database. To do so, I have created a list of queries:

List<Query> countQueries = channels.stream().map(c ->
                selectCount().from(SUBSCRIPTION)
                        .innerJoin(SENDER).on(SENDER.ID.equal(SUBSCRIPTION.SENDER_ID))
                        .innerJoin(CHANNEL).on(CHANNEL.ID.equal(SUBSCRIPTION.CHANNEL_ID))
                        .where(SENDER.CODE.equal(senderCode))
                        .and(CHANNEL.CODE.equal(c))
        ).collect(toList());

And finally, I have launched this list of queries using batch:

using(configuration).batch(countQueries).execute();

I have expected to have the results of the above queries in the return values of execute, but I get an array of integer filled with 0 values.

Is this the right way to run multiple selectCount using JOOQ? What is the signification of the integer array returned by the execute method?

I have checked this link, in the JOOQ blog, talking about "How to Calculate Multiple Aggregate Functions in a Single Query", but It's just about SQL queries, no JOOQ dialects.

Answer 1

Comments on your assumptions

I have expected to have the results of the above queries in the return values of execute, but I get an array of integer filled with 0 values.

The batch() API can only be used for DML queries (INSERT, UPDATE, DELETE), just like with native JDBC. I mean, you can run the queries as a batch, but you cannot fetch the results this way.

I have checked this link, in the JOOQ blog, talking about "How to Calculate Multiple Aggregate Functions in a Single Query", but It's just about SQL queries, no JOOQ dialects.

Plain SQL queries almost always translate quite literally to jOOQ, so you can apply the technique from that article also in your case. In fact, you should! Running so many queries is definitely not a good idea.

Translating that linked query to jOOQ

So, let's look at how to translate that plain SQL example from the link to your case:

Record record =
ctx.select(
      channels.stream()
              .map(c -> count().filterWhere(CHANNEL.CODE.equal(c)).as(c))
              .collect(toList())
   )
   .from(SUBSCRIPTION)
   .innerJoin(SENDER).on(SENDER.ID.equal(SUBSCRIPTION.SENDER_ID))
   .innerJoin(CHANNEL).on(CHANNEL.ID.equal(SUBSCRIPTION.CHANNEL_ID))
   .where(SENDER.CODE.equal(senderCode))
   .and(CHANNEL.CODE.in(channels)) // Not strictly necessary, but might speed up things
   .fetch();

This will produce a single record containing all the count values.

As always, this is assuming the following static import

import static org.jooq.impl.DSL.*;

Using classic GROUP BY

Of course, you can also just use a classic GROUP BY in your particular case. This might even be a bit faster:

Result<?> result =
ctx.select(CHANNEL.CODE, count())
   .from(SUBSCRIPTION)
   .innerJoin(SENDER).on(SENDER.ID.equal(SUBSCRIPTION.SENDER_ID))
   .innerJoin(CHANNEL).on(CHANNEL.ID.equal(SUBSCRIPTION.CHANNEL_ID))
   .where(SENDER.CODE.equal(senderCode))
   .and(CHANNEL.CODE.in(channels)) // This time, you need to filter
   .groupBy(CHANNEL.CODE)
   .fetchOne();

This now produces a table with one count value per code. Alternatively, fetch this into a Map<String, Integer>:

Map<String, Integer> map =
ctx.select(CHANNEL.CODE, count())
   .from(SUBSCRIPTION)
   .innerJoin(SENDER).on(SENDER.ID.equal(SUBSCRIPTION.SENDER_ID))
   .innerJoin(CHANNEL).on(CHANNEL.ID.equal(SUBSCRIPTION.CHANNEL_ID))
   .where(SENDER.CODE.equal(senderCode))
   .and(CHANNEL.CODE.in(channels))
   .groupBy(CHANNEL.CODE)
   .fetchMap(CHANNEL.CODE, count());