5. Querying with Hibernate

Table of Contents

• 1. Why Querying Strategy Matters
• 2. HQL (Hibernate Query Language)
• 3. Criteria API
• 4. Native SQL Queries
• 5. HQL vs Criteria vs Native SQL
• 6. Best Practices and Common Pitfalls
• 7. Conclusion

1. Why Querying Strategy Matters

In Hibernate, querying is not just about reading data. Your query style impacts readability, maintainability, portability, and performance. Hibernate gives you three major ways to query:

• HQL: object-oriented query language based on entity names and fields.
• Criteria API: programmatic and dynamic query construction.
• Native SQL: direct SQL for database-specific features.

A mature codebase usually uses all three, each where it fits best.

2. HQL (Hibernate Query Language)

HQL looks like SQL but works with entities and entity fields instead of table/column names. This makes it easier to refactor your model without rewriting many queries.

2.1. Simple Select with Parameters

String hql = "FROM Employee e WHERE e.department.name = :deptName";

List<Employee> employees = session.createQuery(hql, Employee.class)
    .setParameter("deptName", "Engineering")
    .getResultList();

Use named parameters (`:deptName`) to keep queries safe and readable.

2.2. Projection (Selecting Specific Columns)

String hql = "SELECT e.id, e.firstName, e.lastName FROM Employee e WHERE e.active = true";
List<Object[]> rows = session.createQuery(hql, Object[].class).getResultList();

Projection reduces payload when you do not need full entities.

2.3. Join and Aggregation

String hql = """
    SELECT d.name, COUNT(e.id)
    FROM Employee e
    JOIN e.department d
    GROUP BY d.name
    ORDER BY COUNT(e.id) DESC
""";

List<Object[]> stats = session.createQuery(hql, Object[].class).getResultList();

HQL supports joins, aggregates, and ordering similarly to SQL while staying entity-centric.

3. Criteria API

Criteria API is ideal when filters are dynamic (e.g., search forms with optional fields). You build queries as Java objects, which improves composability and type safety.

3.1. Dynamic Filtering Example

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> root = cq.from(Employee.class);

List<Predicate> predicates = new ArrayList<>();

if (departmentName != null) {
    predicates.add(cb.equal(root.get("department").get("name"), departmentName));
}
if (minSalary != null) {
    predicates.add(cb.greaterThanOrEqualTo(root.get("salary"), minSalary));
}
if (activeOnly) {
    predicates.add(cb.isTrue(root.get("active")));
}

cq.select(root)
  .where(predicates.toArray(new Predicate[0]))
  .orderBy(cb.asc(root.get("lastName")));

List<Employee> result = session.createQuery(cq).getResultList();

3.2. Criteria Projection Example

CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Employee> employee = query.from(Employee.class);

query.multiselect(
        employee.get("id"),
        employee.get("firstName"),
        employee.get("salary"))
     .where(cb.isTrue(employee.get("active")));

List<Object[]> rows = session.createQuery(query).getResultList();

Tip: Criteria can become verbose. Keep query-building logic in reusable helper methods/specifications.

4. Native SQL Queries

Native SQL gives full access to database-specific features, advanced joins, window functions, and tuned SQL statements when HQL/Criteria are not enough.

4.1. Native Query Returning Entities

String sql = "SELECT * FROM employees WHERE status = :status";

List<Employee> employees = session.createNativeQuery(sql, Employee.class)
    .setParameter("status", "ACTIVE")
    .getResultList();

4.2. Native Query with Scalar Result

String sql = """
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
""";

List<Object[]> rows = session.createNativeQuery(sql).getResultList();

4.3. Named Native Query Example

@NamedNativeQuery(
    name = "Employee.findByEmailDomain",
    query = "SELECT * FROM employees WHERE email LIKE CONCAT('%', :domain)",
    resultClass = Employee.class
)
@Entity
public class Employee { ... }

Use named native queries for repeated complex SQL to centralize and document them.

5. HQL vs Criteria vs Native SQL

Approach	Best For	Trade-off
HQL	Readable static queries with entity model	Less flexible for highly dynamic filtering
Criteria API	Dynamic search/filter screens	Verbose and harder to read without structure
Native SQL	Advanced DB features and optimized SQL	Lower portability and tighter DB coupling

6. Best Practices and Common Pitfalls

6.1. Use Parameters Everywhere

Never build query strings with user input. Always use `setParameter` to avoid SQL injection and parsing issues.

6.2. Avoid N+1 Query Problems

Use fetch joins or batch strategies when loading related entities in loops.

6.3. Return DTOs for API Layers

Do not expose entities directly in read-heavy APIs. Use projections/DTOs to reduce payload and control structure.

6.4. Keep Query Logic Organized

• Group reusable query methods by aggregate root.
• Use named queries for stable, frequently used statements.
• Document non-obvious native SQL assumptions.

7. Conclusion

Effective Hibernate querying is about choosing the right tool for the right context: HQL for readability, Criteria API for dynamic scenarios, and native SQL for advanced database capabilities. With a balanced strategy and clean query design, your persistence layer becomes easier to maintain and faster in production.

Next step: move to caching and performance tuning to reduce round trips and improve response time under load.