How To Write Count Query In Jooq

I am converting Pure SQL to jOOQ now I have this ('SELECT Count(*) Count From Table '); I have to write this in jOOQ how can we write it? selectQueryRecord.addSelect(Here Count F

Solution 1:

The most straight-forward way to implement what you're requesting is this, by using selectCount():

intcount= 
DSL.using(configuration)
   .selectCount()
   .from(Table)
   .fetchOne(0, int.class);

Alternatively, you can explicitly express the count() function:

intcount= 
DSL.using(configuration)
   .select(DSL.count())
   .from(Table)
   .fetchOne(0, int.class);

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():

intcount=
DSL.using(configuration)
   .fetchCount(DSL.selectFrom(Table));

Beware, though, that this renders a nested select like this:

SELECTCOUNT(*) FROM (SELECT a, b, ... FROMTable)

Solution 2:

I use the following syntax for this:

import org.jooq.impl.DSL.count

... 

intcount= 
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.

Solution 3:

Here is the solution We have to use like this

  selectQueryRecord.fetchCount();

Solution 4:

I used this:

Integer count = DSL.selectCount().from(Table).where(Table.FIELD.eq(value)).fetchOneInto(Integer.class);

Solution 5:

NOTE:

Deprecated. - 3.5.0 - [#3356] - This method is being removed as it is confusingly different from all the other types of ResultQuery.fetch() methods, in that it modifies the original Select statement by wrapping it. In particular, this method can be easily confused with ResultQuery.fetch(Field), or more concretely fetch(count()), which has an entirely different semantics. Use DSLContext.fetchCount(Select) instead. Execute this query in the context of its attached executor and return a COUNT(*) value.

I think the proper way to write get the count would be like this:

 SelectQuery<Record> selectQueryCount = transaction.selectQuery();
 selectQueryCount.addFrom(Table);
 
 Result<Record> resultObject = selectQueryRecord.fetchCount();