Count Query In jOOQ – jOOQ中的计数查询

1. Overview

1.概述

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.

在本教程中，我们将演示如何使用jOOQ面向对象的查询来执行计数查询，也仅仅是jOOQ。jOOQ是一个流行的Java数据库库，可以帮助你在Java中编写类型安全的SQL查询。

2. jOOQ

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.

jOOQ是一个ORM的替代品。与大多数其他ORM不同，jOOQ是以关系模型为中心，而不是以领域模型为中心。例如，Hibernate可以帮助我们编写Java代码，然后自动翻译成SQL。然而，jOOQ允许我们使用SQL在数据库中创建关系对象，然后它生成Java代码以映射到这些对象。

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

4. Count Query

4.计数查询

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

假设我们的数据库中有一个author表。作者表包含一个id、first_name和last_name。

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

运行计数查询可以通过几种不同的方式来完成。

4.1. fetchCount

4.1.fetchCount

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

DSL.fetchCount有不止一种方法来计算表中的记录数。

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

首先，让我们看看fetchCount(Table<?> table) 方法来计算记录的数量。

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:

接下来，让我们试试fetchCount(Table<?> table)方法，用selectFrom方法和where子句来计算记录的数量。

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:

现在，让我们试试the fetchCount(Table<?> table, Condition condition)方法来计算记录的数量。

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:

我们也可以使用fetchCount(Table<?> table, Collection<? extends Condition> 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.

在这种情况下，我们将过滤条件添加到一个列表中，并将其提供给fetchCount方法。

The fetchCount method also allows varargs for multiple conditions:

fetchCount 方法也允许varargs用于多个条件。

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:

另外，我们可以使用and(Condition condition)来结合内联条件。

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

4.2. count

4.2.计数

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

让我们试试 count 方法来获得可用记录的数量。

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

4.3. selectCount

4.3.selectCount

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

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

4.5. Count With groupBy

4.5.用groupBy计数

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

让我们尝试使用 select和count方法来查找按字段分组的记录数。

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

5.总结

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

在这篇文章中，我们已经看了如何在jOOQ中执行计数查询。

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

我们已经看过使用selectCount、count、fetchCount、select、和count withgroupBy方法来计算记录的数量。

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

像往常一样，本教程中使用的所有代码样本都可以在GitHub上找到。。