How to write Count Query In jOOQ

Question

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 Function );
selectQueryRecord.addFrom(Table);

Answer 1

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)

Answer 2

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.

Answer 3

Here is the solution We have to use like this

  selectQueryRecord.fetchCount();

Answer 4

I used this:

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

Answer 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();