How to write Count Query In jOOQ
I use the following syntax for this:
import org.jooq.impl.DSL.count
...
int count =
DSL.using(configuration)
.selectCount()
.from(Table)
.fetchOne(count());
This is less verbose and simpler.
Lukas's answer dates from 2013, maybe this solution did not exist at the time.
The most straight-forward way to implement what you're requesting is this, by using selectCount()
:
int count =
DSL.using(configuration)
.selectCount()
.from(Table)
.fetchOne(0, int.class);
Alternatively, you can explicitly express the count()
function:
int count =
DSL.using(configuration)
.select(DSL.count())
.from(Table)
.fetchOne(0, int.class);
Or, you can use this, if you don't like mapping the value:
int count =
DSL.using(configuration)
.fetchValue(selectCount().from(Table));
There's another alternative for fetching a count(*)
of any arbitrary select
expression, which helps you avoid specifying the result column index and type in the above fetchOne()
method. This uses fetchCount()
:
int count =
DSL.using(configuration)
.fetchCount(DSL.selectFrom(Table));
Beware, though, that this renders a nested select like this:
SELECT COUNT(*) FROM (SELECT a, b, ... FROM Table)