JPQL vs Native Queries in Depth — Interview Questions

How to use this lesson

The core idea: JPQL speaks the object model, native SQL speaks the database. Pick the one that expresses the query best, then check the generated SQL and execution plan. Performance comes from the SQL and the indexes, not from the query language.

Q1. When should you use JPQL, and when native queries?

Short answer:

• Use JPQL (or HQL) when the query fits the entity model:
  • CRUD-adjacent reads;
  • fetch joins to load aggregates;
  • constructor-expression projections;
  • portable code across databases;
  • queries that should survive column renames (they reference entity fields).
• Use native SQL when you need database-specific power:
  • window functions (JPQL has them since Hibernate 6, but not in portable JPA);
  • recursive CTEs;
  • LATERAL joins;
  • JSON or array operators;
  • full-text search;
  • INSERT … ON CONFLICT/MERGE;
  • hints;
  • tuned reporting queries;
  • bulk data operations, where every millisecond counts.

Keep native SQL behind repository methods, and cover it with integration tests on the real database (Testcontainers).

Q2. Does JPQL support joins between unrelated entities?

Short answer: Yes, since JPA 2.2 / Hibernate 5.1 (entity joins). You can join entities without a mapped association, using an explicit ON clause:

@Query("""
       select new com.shop.dto.CustomerSpend(c.id, c.name, sum(p.amount))
       from Customer c
       join Payment p on p.customerEmail = c.email          -- no mapped association between them
       where p.status = 'SETTLED'
       group by c.id, c.name
       """)
List<CustomerSpend> spendByCustomer();

In older JPA versions, you had to use theta-style joins in the WHERE clause (from A a, B b where a.x = b.y, which is an inner join only).

Q3. Can native queries be used with projections?

Short answer: Yes:

• Interface projections: column aliases must match the getter names (select o.id as id, o.total as total …).
• DTO or record projections from native queries: use @SqlResultSetMapping with @ConstructorResult, or Hibernate's result transformers. Spring Data 3.x can map native results to DTOs in some cases, and JdbcClient or JdbcTemplate with a RowMapper or DataClassRowMapper (records) is often simplest.
• Tuple or Object[] results, mapped manually.

Beware type mismatches (for example, BigInteger vs Long for counts), which are vendor-dependent.

Q4. How do you use constructor expressions in JPQL, and how do they work?

Short answer: select new fully.qualified.Dto(expr1, expr2, …) tells JPA to instantiate the DTO for each row, with the matching constructor. The result objects are not managed entities: there's no dirty checking, no persistence-context memory, and only the selected columns are fetched. That makes them ideal for read models.

public record OrderRow(UUID id, String customerName, BigDecimal total, OrderStatus status) {}

@Query("select new com.shop.dto.OrderRow(o.id, c.name, o.total, o.status) " +
       "from Order o join o.customer c where o.createdAt >= :since order by o.createdAt desc")
List<OrderRow> recentRows(@Param("since") Instant since);

The requirements:

• the fully qualified class name (Hibernate 6 also accepts imported or simple names in some cases);
• a constructor whose parameter types match the expressions exactly (watch Long vs Integer, and numeric promotion from aggregates like sum/count).

Q5. How do you call stored procedures with JPA?

Short answer: JPQL itself can't call procedures. Use JPA's stored-procedure support:

• @NamedStoredProcedureQuery on an entity, plus a @Procedure repository method in Spring Data;
• or EntityManager.createStoredProcedureQuery("proc_name"), registering IN/OUT/REF_CURSOR parameters;
• or a native CALL or SELECT function(...), through a native query or SimpleJdbcCall/JdbcClient.

public interface InvoiceRepository extends JpaRepository<Invoice, Long> {
    @Procedure(procedureName = "close_period")
    int closePeriod(@Param("period_id") long periodId);             // OUT parameter mapped to the return value
}

Key points to cover:

• Keep the business logic in the application when you can (testability, portability).
• Use procedures for set-based bulk operations close to the data, or for legacy integrations.

Q6. How do JPQL queries map to entity attributes rather than column names?

Short answer: JPQL is written against the entity model: entity names (@Entity(name), defaulting to the class name) and attribute paths (o.customer.address.city), not the table and column names. The provider translates it using the mapping metadata: @Table, @Column, and embeddables, including joins implied by path navigation. So:

• renaming a column only changes the @Column(name = ...), and the queries stay valid;
• @Query JPQL is validated at startup against the metamodel (typos fail fast);
• you can't reference unmapped columns. Use native SQL, or map them.

Q7. How do you pass collection parameters to JPQL queries?

Short answer: Bind a Collection to an IN clause:

@Query("select o from Order o where o.status in :statuses and o.region in :regions")
List<Order> find(@Param("statuses") Collection<OrderStatus> statuses, @Param("regions") List<String> regions);

The caveats:

• An empty collection gives invalid SQL in some databases (IN ()). Guard it in code, or use a Specification.
• Very large lists hit parameter limits (Oracle allows 1,000 items per IN, and SQL Server about 2,100 parameters in total) and pollute the plan cache, because every size is a different SQL statement. Use batching, hibernate.query.in_clause_parameter_padding=true (which pads to powers of two), a temporary table, or = ANY(:array) on Postgres (natively).

Q8. Can you run update and delete queries with JPQL?

Short answer: Yes: bulk JPQL with @Modifying @Query("update Order o set o.status = :s where …") (or executeUpdate()). The caveats:

• It bypasses the persistence context: managed entities in memory become stale. Use @Modifying(clearAutomatically = true, flushAutomatically = true).
• No lifecycle callbacks: @PreUpdate, entity listeners and cascades don't run, and @Version isn't incremented automatically (increment it in the query, if you rely on it).
• It needs an active transaction.
• It ignores the second-level cache entries for the affected rows (Hibernate evicts the entity region; configure this carefully).

It's great for batch maintenance (expiring carts, status transitions), and much faster than loading entities.

Q9. Can you paginate native SQL queries?

Short answer: Yes. Spring Data applies Pageable to native queries by appending LIMIT/OFFSET (dialect-specific), but for Page<T> you must supply a countQuery, because it can't reliably derive the count from arbitrary SQL:

@Query(value = "select * from orders where customer_id = :cid order by created_at desc",
       countQuery = "select count(*) from orders where customer_id = :cid",
       nativeQuery = true)
Page<Order> findPage(@Param("cid") UUID customerId, Pageable pageable);

The caveats:

• Sort from the Pageable may not work with complex native SQL. Put the ordering in the query, and allow-list any sort fields.
• OFFSET pagination slows down on deep pages. Use keyset (seek) pagination (where (created_at, id) < (:lastTs, :lastId) order by created_at desc, id desc limit :n).

Q10. What are the limitations of native queries in Spring Data JPA?

Short answer:

• No portability, and no startup validation (errors appear at runtime).
• Dynamic sorting through Sort is limited. Specifications and Examples don't work with native queries.
• Result mapping needs interfaces or aliases, @SqlResultSetMapping, or entity-shaped results. Nested DTOs are awkward.
• No automatic count query for paging.
• Returned entities must include all mapped columns (or you get errors or partial state).
• Persistence-context interaction: native updates don't update managed entities (clear or refresh them), and Hibernate may flush before native queries, to keep them consistent.
• Refactoring tools don't see them, so schema changes can break them silently (Q11).

Q11. What are the risks of native queries when the schema evolves? What's their impact on portability and maintainability?

Short answer:

• Schema evolution: native SQL references tables and columns directly. Renames, splits and type changes break queries at runtime, not at startup, and refactoring tools and the JPA metamodel can't help.
• Portability: vendor syntax (LIMIT vs FETCH FIRST, JSON operators, ILIKE) locks you into one database, and makes tests on H2 misleading.
• Maintainability: SQL strings scattered across repositories, duplicated column lists, and SQL knowledge needed for reviews.

Mitigations:

• keep native SQL few and centralised;
• use Testcontainers with the real database in CI;
• use migration-aware reviews (schema changes run the query test suite);
• use views, to decouple complex reporting SQL from the tables;
• or use jOOQ, which generates code from the schema, so schema changes break compilation instead of runtime.

Q12. Is there a performance difference between JPQL and native queries?

Short answer: Not inherently. JPQL is translated once (and cached) into SQL, so the database executes SQL either way. The differences come from:

• what's selected: entity queries load all mapped columns, and create managed entities (snapshots for dirty checking, and first-level-cache memory). Projections or native row mapping avoid that;
• the translation: complex JPQL may produce suboptimal SQL (for example, implicit joins, or subquery shapes), and native SQL lets you use hints, CTEs and window functions;
• the fetch strategy: N+1 queries from lazy associations after the JPQL.

Measure it: enable SQL logging with bind parameters, run EXPLAIN ANALYZE, and compare the timings. Optimise the SQL and the indexes, not the query language.

Q13. When should you switch from JPQL to the Criteria API?

Short answer: When the query is built dynamically at runtime (optional filters, user-chosen sorting, conditional joins), and string concatenation would be fragile or unsafe. The Criteria API (or Specifications or Querydsl on top of it) gives type-safe, composable predicates, especially with the JPA static metamodel (Order_.status). Stay with JPQL for static queries: it's more readable. Many teams keep JPQL for fixed queries, and Specifications or Querydsl for search endpoints.

Follow-up questions this topic invites — and their answers

Q: What does Hibernate 6 add to HQL? A: Set operations (union), CTEs (with), window functions, lateral joins, insert … select, limit/offset, richer functions, and better typed results. Many former native-only queries can now stay in HQL, and remain portable across supported dialects.

Q: How do you see the SQL, with its bind values? A: In development: logging.level.org.hibernate.SQL=DEBUG, and org.hibernate.orm.jdbc.bind=TRACE (Hibernate 6), or datasource-proxy/p6spy for formatted SQL with values. In production, log only slow queries, or use APM traces.

Q: What is the JPA static metamodel? A: Generated classes (Order_) with typed attributes (SingularAttribute<Order, OrderStatus> status), produced by an annotation processor. They make Criteria queries and Specifications type-safe and refactoring-friendly.

Q: How do you prevent SQL injection in JPQL and native queries? A: Always bind parameters (:param), and never concatenate user input into query strings. For dynamic ORDER BY columns (which can't be bound), map the user's input against an allow-list.