Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write Count Query In jOOQ

Tags:

java

sql

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 Function );
selectQueryRecord.addFrom(Table);
like image 266
Subodh Joshi Avatar asked Oct 29 '13 11:10

Subodh Joshi


2 Answers

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)
like image 102
Lukas Eder Avatar answered Nov 05 '22 15:11

Lukas Eder


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.

like image 4
Kevin Davin Avatar answered Nov 05 '22 16:11

Kevin Davin