ProjectionQuery

Summary

Debug

The debug utilities provide tools for analyzing and troubleshooting queries during development. The primary feature is SQL Preview, which extracts SQL without executing the query when Hypersistence Utils is available. The fallback mode executes a limited query against the database.

Important: Debug utilities are intended for development and debugging purposes only. Do not use in production code paths or performance-critical sections.

SQL Preview ↑

SQL Preview allows you to inspect the SQL query that ProjectionQuery will generate before actually executing it. This is invaluable for:

Debugging - Verify query correctness
Learning - Understand how ProjectionQuery translates to SQL
Testing - Validate SQL structure in automated tests
Documentation - Generate SQL examples

To use SQL Preview, create a ProjectionProcessorDebug instance with your EntityManagerFactory:

EntityManagerFactory entityManagerFactory = // ... your EntityManagerFactory

ProjectionProcessorDebug debug = new ProjectionProcessorDebug(entityManagerFactory);

ProjectionQuery<Customer, CustomerProjection> query = ProjectionQuery
    .fromTo(Customer.class, CustomerProjection.class)
    .filter("age", ProjectionFilterOperator.GREATER_THAN, 18)
    .order("name", OrderDirection.ASC)
    .paging(0, 20);

String sql = debug.previewSQL(query);
System.out.println(sql);

Illustrative output (actual columns follow your projection):

SELECT
    c1_0.id,
    c1_0.name,
    c1_0.age
FROM
    customer c1_0
WHERE
    c1_0.age > ?
ORDER BY
    c1_0.name
LIMIT ?

Two Operating Modes ↑

SQL Preview operates in two modes, automatically detecting which one to use based on available dependencies:

Enhanced Mode - SQL extraction without query execution using Hypersistence Utils
Basic Mode - Fallback mode that executes query with LIMIT 1

The mode is selected automatically at runtime. If Hypersistence Utils is available on the classpath, Enhanced Mode is used. Otherwise, Basic Mode is used as fallback.

Enhanced Mode (Recommended) ↑

Enhanced Mode provides the best experience by extracting SQL directly from Hibernate’s query compilation without executing any database operations.

Setup

Add this optional dependency to your project:

Maven:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-63</artifactId>
    <version>3.15.2</version>
</dependency>

Note: This dependency matches the optional dependency declared in this repository, which uses Hibernate 6.6.29.Final. Align it with your application’s Hibernate version; this guide does not establish compatibility with other Hibernate versions.

Benefits

✅ Zero database access - No queries executed
✅ No transaction required - Works without active transaction
✅ Returns formatted SQL - Ready to read and analyze

Example

ProjectionProcessorDebug debug = new ProjectionProcessorDebug(entityManagerFactory);

ProjectionQuery<Customer, CustomerProjection> query = ProjectionQuery
    .fromTo(Customer.class, CustomerProjection.class)
    .filter("status", ProjectionFilterOperator.EQUAL, "ACTIVE")
    .filter("address.city.name", ProjectionFilterOperator.LIKE, "São%");

String sql = debug.previewSQL(query);
System.out.println(sql);

Output:

SELECT
    c1_0.id,
    c1_0.name,
    c1_0.status
FROM
    customer c1_0
INNER JOIN
    address a1_0 ON c1_0.address_id=a1_0.id
INNER JOIN
    city c2_0 ON a1_0.city_id=c2_0.id
WHERE
    c1_0.status=?
    AND c2_0.name LIKE ?

Basic Mode (Fallback) ↑

Basic Mode is used when Hypersistence Utils is not available. It calls setMaxResults(1) and executes the query so Hibernate can log the SQL statement. The database dialect determines the limiting syntax. Any offset already configured on the query is retained.

Setup

Enable SQL logging in your application configuration:

application.properties:

# Show SQL statements
logging.level.org.hibernate.SQL=DEBUG

# Format SQL for readability (optional)
spring.jpa.properties.hibernate.format_sql=true

application.yml:

logging:
  level:
    org.hibernate.SQL: DEBUG

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true

Characteristics

Executes query with LIMIT 1
Returns at most one row; database work still depends on joins, filters, sorting, and aggregations
Requires active database connection
SQL appears in logs
Returns informational message

Example

ProjectionProcessorDebug debug = new ProjectionProcessorDebug(entityManagerFactory);

ProjectionQuery<Customer, CustomerProjection> query = ProjectionQuery
    .fromTo(Customer.class, CustomerProjection.class)
    .filter("age", ProjectionFilterOperator.GREATER_THAN, 18);

String message = debug.previewSQL(query);
System.out.println(message);

Console Output:

The SQL will appear in your logs:

Hibernate: 
    select
        c1_0.id,
        c1_0.name,
        c1_0.age 
    from
        customer c1_0 
    where
        c1_0.age>? 
    limit ?

The method returns an informational message beginning with SQL preview generated via logging, followed by dependency and logging setup hints. It does not return the SQL string in this mode.

hibernate.format_sql only formats SQL; enable org.hibernate.SQL logging as shown above to display it.

Comparison: Enhanced vs Basic Mode ↑

Feature	Enhanced Mode	Basic Mode
Dependency	Hypersistence Utils	None (built-in)
Database Access	❌ No	✅ Yes (1 row)
Transaction handling	Does not execute the query	Opens its own EntityManager; does not begin a transaction
Work performed	Query compilation and SQL extraction	Database query execution, returning at most one row
Output Format	SQL string	Logs + message
Use in CI/CD	✅ Recommended	⚠️ Requires DB
Intended use	Development and debugging	Development and debugging with a database

Best Practices ↑

✅ DO:

Use in development and debugging
Add Hypersistence Utils for best experience
Preview SQL before executing complex queries
Use for test assertions
Generate documentation examples

❌ DON’T:

Use in production code paths
Use in performance-critical sections
Rely on Basic Mode in CI/CD (add Hypersistence Utils)
Execute sensitive queries for preview
Use as query validation (use test database)

← Previous: Logging · ↑ Back to top · Next → For Spring Boot users

This site is open source. Improve this page.