Introduction

In JPA, counting rows is usually done with a COUNT query in JPQL or Criteria API. The important detail is that JPA counts entities or selected expressions, not raw tables in the same way as plain SQL, so the query should be written against the entity model.

Basic JPQL count query

Suppose you have an entity called Employee:

1import jakarta.persistence.Entity;
2import jakarta.persistence.Id;
3
4@Entity
5public class Employee {
6    @Id
7    private Long id;
8    private String department;
9}

The basic count query is:

1Long count = entityManager
2    .createQuery("select count(e) from Employee e", Long.class)
3    .getSingleResult();
4
5System.out.println(count);

This returns the number of Employee rows represented by that entity.

Count with conditions

You can add a where clause just as you would in other JPQL queries.

1Long count = entityManager
2    .createQuery(
3        "select count(e) from Employee e where e.department = :department",
4        Long.class)
5    .setParameter("department", "Sales")
6    .getSingleResult();

This is common for:

• pagination
• dashboards
• reporting
• conditional existence checks

Using Long is important because JPA count results are typically returned as Long, not int.

COUNT(*) versus COUNT(e)

In SQL, people often write COUNT(*). In JPQL, the idiomatic form is usually:

select count(e) from Employee e

because JPQL works with entities and entity aliases rather than table syntax. Some providers support other count forms, but count(e) is the conventional portable approach.

Count distinct values when joins introduce duplicates

If you join related entities, a plain count(e) can overcount because the join may duplicate the root entity across rows.

For example:

1Long count = entityManager
2    .createQuery(
3        "select count(distinct e) from Employee e join e.projects p",
4        Long.class)
5    .getSingleResult();

Without distinct, one employee assigned to three projects might be counted three times in the result set.

This is one of the most common reasons count queries return confusing numbers.

Criteria API version

If your application builds queries dynamically, the Criteria API can express the same count.

1import jakarta.persistence.criteria.CriteriaBuilder;
2import jakarta.persistence.criteria.CriteriaQuery;
3import jakarta.persistence.criteria.Root;
4
5CriteriaBuilder cb = entityManager.getCriteriaBuilder();
6CriteriaQuery<Long> cq = cb.createQuery(Long.class);
7Root<Employee> root = cq.from(Employee.class);
8
9cq.select(cb.count(root));
10
11Long count = entityManager.createQuery(cq).getSingleResult();

This is useful when filters are optional and the query must be assembled programmatically.

Count queries for pagination

A common pagination pattern is:

1. run a select query for one page of data
2. run a matching count query for the total number of rows

Be careful that the count query should match the filtering logic of the main query, but often should omit fetch joins and unnecessary ordering.

For example, order by is usually irrelevant in a count query and can be removed.

Native SQL when needed

If the query depends on database-specific behavior or a very complex aggregation, you can use native SQL:

1Number count = (Number) entityManager
2    .createNativeQuery("select count(*) from employee")
3    .getSingleResult();
4
5long total = count.longValue();

This can be useful, but it gives up some portability and entity-level abstraction.

Common Pitfalls

The biggest mistake is expecting the count result to be an int. In JPA, COUNT typically returns Long, so use the right Java type.

Another issue is counting after a join without thinking about duplication. If the join expands rows, you may need count(distinct e) instead of count(e).

Developers also copy the full select query into the count query, including fetch joins and ordering. That often makes the count slower or even invalid. A count query should usually be simpler than the data-fetch query.

Finally, remember that JPQL counts entities and mapped fields, not raw table names. If you want entity portability, write the count against entity names and aliases.

Summary

• Use select count(e) from Entity e as the standard JPQL row-count pattern.
• Count results are usually Long, not int.
• Add where clauses normally when you need filtered counts.
• Use count(distinct e) when joins would otherwise duplicate the root entity.
• Keep pagination count queries simple and aligned with the filtering logic of the main query.