Hibernate – Count the Number of Records

Hibernate – Count the Number of Records

Counting rows in Hibernate can be done with HQL, the Criteria API, or native SQL. Unlike loading full entities, a COUNT query returns a single number without fetching any entity data — which is far more efficient for large tables. This tutorial shows all three approaches.

Prerequisites

• Java 8 or later
• Hibernate 5.x
• MySQL database
• Maven project

Database Table

CREATE TABLE product (
  id          INT          NOT NULL AUTO_INCREMENT,
  name        VARCHAR(45)  DEFAULT NULL,
  price       DECIMAL(10,0) DEFAULT NULL,
  quantity    INT          DEFAULT NULL,
  description VARCHAR(450) DEFAULT NULL,
  active      TINYINT(1)   DEFAULT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost:3306/java9rdb</property>
    <property name="connection.username">root</property>
    <property name="connection.password">yourpassword</property>
    <property name="dialect">org.hibernate.dialect.MySQL8Dialect</property>
    <property name="show_sql">true</property>
    <property name="hbm2ddl.auto">update</property>
    <mapping class="com.java9r.model.Product"/>
  </session-factory>
</hibernate-configuration>

Product Entity

package com.java9r.model;

import javax.persistence.*;

@Entity
@Table(name = "product")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name", length = 45)
    private String name;

    @Column(name = "price")
    private Long price;

    @Column(name = "active")
    private Boolean active;

    public Product() {}

    public Integer getId()     { return id; }
    public String  getName()   { return name; }
    public Long    getPrice()  { return price; }
    public Boolean getActive() { return active; }
}

HibernateUtil

package com.java9r.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

    private static final SessionFactory sessionFactory =
            new Configuration().configure().buildSessionFactory();

    public static SessionFactory getSessionFactory() { return sessionFactory; }
    public static void shutdown()                    { sessionFactory.close(); }
}

ProductDAO – Count Approaches

package com.java9r.dao;

import com.java9r.model.Product;
import com.java9r.util.HibernateUtil;
import org.hibernate.Session;

import javax.persistence.criteria.*;

public class ProductDAO {

    // ------------------------------------------------------------------
    // Method 1: HQL COUNT — simplest and most readable
    // ------------------------------------------------------------------
    public long countAllHQL() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            return (Long) session.createQuery("SELECT COUNT(p) FROM Product p")
                                 .uniqueResult();
        }
    }

    // ------------------------------------------------------------------
    // Method 2: HQL COUNT with WHERE condition
    // ------------------------------------------------------------------
    public long countActiveProducts() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            return (Long) session.createQuery(
                    "SELECT COUNT(p) FROM Product p WHERE p.active = true")
                .uniqueResult();
        }
    }

    // ------------------------------------------------------------------
    // Method 3: Criteria API COUNT — type-safe
    // ------------------------------------------------------------------
    public long countAllCriteria() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            CriteriaBuilder cb = session.getCriteriaBuilder();
            CriteriaQuery<Long> cq = cb.createQuery(Long.class);
            Root<Product> root = cq.from(Product.class);
            cq.select(cb.count(root));
            return session.createQuery(cq).getSingleResult();
        }
    }

    // ------------------------------------------------------------------
    // Method 4: Criteria API COUNT with condition (price > threshold)
    // ------------------------------------------------------------------
    public long countExpensiveProducts(Long minPrice) {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            CriteriaBuilder cb = session.getCriteriaBuilder();
            CriteriaQuery<Long> cq = cb.createQuery(Long.class);
            Root<Product> root = cq.from(Product.class);
            cq.select(cb.count(root))
              .where(cb.greaterThan(root.get("price"), minPrice));
            return session.createQuery(cq).getSingleResult();
        }
    }

    // ------------------------------------------------------------------
    // Method 5: Native SQL COUNT
    // ------------------------------------------------------------------
    public long countNativeSQL() {
        try (Session session = HibernateUtil.getSessionFactory().openSession()) {
            Object result = session.createNativeQuery("SELECT COUNT(*) FROM product")
                                   .uniqueResult();
            return ((Number) result).longValue();
        }
    }
}

Main Class

package com.java9r;

import com.java9r.dao.ProductDAO;
import com.java9r.util.HibernateUtil;

public class CountProductsMain {

    public static void main(String[] args) {
        ProductDAO dao = new ProductDAO();

        System.out.println("=== Hibernate – Count Products ===");

        long total = dao.countAllHQL();
        System.out.println("Total products (HQL):          " + total);

        long active = dao.countActiveProducts();
        System.out.println("Active products (HQL):         " + active);

        long criteria = dao.countAllCriteria();
        System.out.println("Total products (Criteria API): " + criteria);

        long expensive = dao.countExpensiveProducts(20000L);
        System.out.println("Products priced > 20000:       " + expensive);

        long native_ = dao.countNativeSQL();
        System.out.println("Total products (Native SQL):   " + native_);

        HibernateUtil.shutdown();
    }
}

Expected Output

=== Hibernate – Count Products ===
Hibernate: select count(product0_.id) as col_0_0_ from product product0_
Total products (HQL):          5

Hibernate: select count(product0_.id) as col_0_0_ from product product0_ where product0_.active=1
Active products (HQL):         4

Hibernate: select count(product0_.id) as col_0_0_ from product product0_
Total products (Criteria API): 5

Hibernate: select count(product0_.id) as col_0_0_ from product product0_ where product0_.price>?
Products priced > 20000:       3

Hibernate: SELECT COUNT(*) FROM product
Total products (Native SQL):   5

Choosing the Right COUNT Approach

Method	Type Safe?	Conditional?	Best For
HQL COUNT	No (string)	Yes	Simple counts, most readable
Criteria API COUNT	Yes	Yes	Dynamic where clauses built at runtime
Native SQL COUNT	No	Yes	Database-specific functions, reporting

Summary

Counting records in Hibernate should always use a COUNT query rather than loading all entities and calling list.size() — that would be extremely inefficient for large tables. Use HQL SELECT COUNT(p) FROM Product p for the simplest syntax. Use the Criteria API when the WHERE clause is built dynamically at runtime. Use native SQL only for database-specific COUNT variations or when joining non-mapped tables. All three approaches return a long and issue a single, efficient SQL COUNT query.