Expand Authors Top

If you have a few years of experience in the Java ecosystem and you’d like to share that with the community, have a look at our Contribution Guidelines.

Expanded Audience – Frontegg – Security (partner)
announcement - icon User management is very complex, when implemented properly. No surprise here.

Not having to roll all of that out manually, but instead integrating a mature, fully-fledged solution - yeah, that makes a lot of sense.
That's basically what Frontegg is - User Management for your application. It's focused on making your app scalable, secure and enjoyable for your users.
From signup to authentication, it supports simple scenarios all the way to complex and custom application logic.

Have a look:

>> Elegant User Management, Tailor-made for B2B SaaS

1. Overview

In this tutorial, we'll demonstrate how to perform a count query using jOOQ Object-Oriented Querying, also known as just jOOQ. jOOQ is a popular Java database library that helps you to write typesafe SQL queries in Java.

2. jOOQ

jOOQ is an ORM alternative. Unlike most other ORMs, jOOQ is relational model-centric and not domain model-centric. Hibernate, for example, helps us to write Java code that is then automatically translated to SQL. However, jOOQ allows us to create relational objects in the database using SQL, and it then generates the Java code to map to those objects.

3. Maven Dependencies

We'll need the jooq module in this tutorial:

<dependency> 
    <groupId>org.jooq</groupId> 
    <artifactId>jooq</artifactId> 
    <version>3.14.8</version> 
</dependency>

4. Count Query

Let's say we have an author table in our database. The author table contains an id, first_name, and last_name.

Running a count query can be accomplished in a few different ways.

4.1. fetchCount

DSL.fetchCount has more than one way to count the number of records in the table.

First, let's look at the fetchCount​(Table<?> table) method to count the number of records:

int count = dsl.fetchCount(DSL.selectFrom(AUTHOR));
Assert.assertEquals(3, count);

Next, let's try the fetchCount​(Table<?> table) method with the selectFrom method and where clause to count the number of records:

int count = dsl.fetchCount(DSL.selectFrom(AUTHOR)
  .where(AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan")));
Assert.assertEquals(1, count);

Now, let's try the fetchCount​(Table<?> table, Condition condition) method to count the number of records:

int count = dsl.fetchCount(AUTHOR, AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan"));
Assert.assertEquals(1, count);

We can also use the fetchCount​(Table<?> table, Collection<? extends Condition> conditions) method for multiple conditions:

Condition firstCond = AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan");
Condition secondCond = AUTHOR.ID.notEqual(1);
List<Condition> conditions = new ArrayList<>();
conditions.add(firstCond);
conditions.add(secondCond);
int count = dsl.fetchCount(AUTHOR, conditions);
Assert.assertEquals(1, count);

In this case, we're adding filter conditions to a list and providing it to the fetchCount method.

The fetchCount method also allows varargs for multiple conditions:

Condition firstCond = AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan");
Condition secondCond = AUTHOR.ID.notEqual(1);
int count = dsl.fetchCount(AUTHOR, firstCond, secondCond);
Assert.assertEquals(1, count);

Alternatively, we can use and(Condition condition)  to combine inline conditions:

int count = dsl.fetchCount(AUTHOR, AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan").and(AUTHOR.ID.notEqual(1)));
Assert.assertEquals(1, count);

4.2. count

Let's try the count method to get the number of available records:

int count = dsl.select(DSL.count()).from(AUTHOR)
  .fetchOne(0, int.class);
Assert.assertEquals(3, count);

4.3. selectCount

Now, let's try to use the selectCount method to get the count of the available records:

int count = dsl.selectCount().from(AUTHOR)
  .where(AUTHOR.FIRST_NAME.equalIgnoreCase("Bryan"))
  .fetchOne(0, int.class);
Assert.assertEquals(1, count);

4.4. Simple select

We can also use a simple select method to get the count of the available records:

int count = dsl.select().from(AUTHOR).execute();
Assert.assertEquals(3, count);

4.5. Count With groupBy

Let's try to use the select and count methods to find the count of records grouped by a field:

Result<Record2<String, Integer>> result = dsl.select(AUTHOR.FIRST_NAME, DSL.count())
  .from(AUTHOR).groupBy(AUTHOR.FIRST_NAME).fetch();
Assert.assertEquals(3, result.size());
Assert.assertEquals(result.get(0).get(0), "Bert");
Assert.assertEquals(result.get(0).get(1), 1);

5. Conclusion

In this article, we've looked at how to perform a count query in jOOQ.

We've looked at using the selectCount, count, fetchCount, select, and count with groupBy methods to count the number of records.

As usual, all code samples used in this tutorial are available over on GitHub.

Persistence bottom

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

>> CHECK OUT THE COURSE
Persistence footer banner
Comments are closed on this article!