Experience with Blaze-Persistence

2026-01-28

I am documenting what I learned to use 'WITH' clauses dynamically in data retrieval environments requiring complex queries.

Blaze-Persistence

This is an extension library based on the JPA Criteria API.

It enables the use of advanced SQL features not supported by standard JPA or QueryDSL-JPA.

Common Table Expression (CTE)

A CTE refers to a temporary table that can be used while a query is executing.

Officially, JPA does not yet support the WITH clause (CTE).
Hibernate 6.1 and above started supporting the WITH clause in HQL, but since it must be written in static HQL syntax within the @Query annotation, there are limitations in constructing dynamic queries.

Caution

Indexes are not applied to temporary tables (CTEs).
Therefore, you should avoid approaches that query large amounts of data at once.

build.gradle

Add the following dependencies to use Blaze Persistence.

Plain Text

ext {
    blazebitVersion = "1.6.11"
}
dependencies {
    // JPA and QueryDSL Extensions
    // Docs: https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/
    implementation "com.blazebit:blaze-persistence-core-impl-jakarta:NULL"
    implementation "com.blazebit:blaze-persistence-integration-querydsl-expressions-jakarta:NULL"
    implementation "com.blazebit:blaze-persistence-integration-hibernate-6.2:NULL"
}

Configuration

You must register CriteriaBuilderFactory as a Bean to use CTE features.

CriteriaBuilderFactory is a Singleton object created only once globally in the application.

It is created by injecting the EntityManagerFactory, the DB connection object registered in the Spring container.

Java

@Configuration
public class BlazebitConfig {

    @PersistenceUnit
    private EntityManagerFactory entityManagerFactory;

    @Bean
    @Scope(ConfigurableBeanFactory.SCOPE_SINGLETON)
    public CriteriaBuilderFactory criteriaBuilderFactory() {
        CriteriaBuilderConfiguration config = Criteria.getDefault();
        config.setProperty(ConfigurationProperties.INLINE_CTES, "false");
        return config.createCriteriaBuilderFactory(entityManagerFactory);
    }

}

Disabling INLINE_CTES

Turns off the CTE Inlining optimization feature, which is enabled by default.

If this option is on, the optimization feature might force the query to be converted into a subquery (Inline View) merged into the main query instead of generating a WITH clause. It is recommended to set this to false to use the WITH clause as intended.

Example: Recursive CTE

Entity Setup (Entity & CTE Entity)

To use Blaze Persistence, you need a CTE-specific Entity to hold the CTE results, in addition to the JPA Entity mapped to the actual DB table.

Basic JPA Entity (Category)

Category.class

Java

import com.blazebit.persistence.CTE;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;

@CTE
@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class CategoryCTE {
    @Id
    private Long id;
    
    private Long rowNum;
}

CTE-Specific Entity (CategoryCTE)

This is the class to map the CTE result set. Annotate it with @CTE.

CategoryCTE.class

Java

import com.blazebit.persistence.CTE;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;

@CTE
@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class CategoryCTE {
    @Id
    private Long id;
    
    private Long rowNum;
}

Repository Implementation

Use Spring Data JPA's Custom Repository pattern.

CategoryRepositoryQueryDslImpl.class

Java

import com.blazebit.persistence.CriteriaBuilderFactory;
import com.blazebit.persistence.querydsl.BlazeJPAQuery;
import com.querydsl.core.types.dsl.*;
import jakarta.persistence.EntityManager;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Repository;

import java.util.List;

import static com.example.demo.entity.QCategory.category;
import static com.example.demo.entity.QCategoryCTE.categoryCTE; 

@Repository
@RequiredArgsConstructor
public class CategoryRepositoryQueryDslImpl implements CategoryRepositoryCustom {

    private final EntityManager em;
    private final CriteriaBuilderFactory cbf;

    @Override
    public List<Category> findAllSubCategories(long rootId) {
        return new BlazeJPAQuery<Category>(em, cbf)        
                // CTE Definition: Start recursive query
	        .withRecursive(categoryCTE, new BlazeJPAQuery<>()
            		 // 1. Anchor Member (Starting point)
	                .from(category)
	                .where(category.id.eq(rootId))
	                .bind(categoryCTE.id, category.id)
	                .bind(categoryCTE.rowNum, Expressions.constant(1))
	                .unionAll(
	                    // 2. Recursive Member (Recursive logic)
	                    new BlazeJPAQuery<>()
	                        .from(category)
	                        .join(categoryCTE).on(category.parent.id.eq(categoryCTE.id))
			                .bind(categoryCTE.id, category.id)
			                .bind(categoryCTE.rowNum, categoryCTE.rowNum.add(1))
	                )
	        )
	        // 3. Main Query
	        .select(category)
	        .from(category)
	        .join(categoryCTE).on(category.id.eq(categoryCTE.id)) // Join CTE results (IDs) with the actual table
	        .fetch();
    }
}

General Example: Finding Previous/Next Posts (Using Window Function)

Java

import com.querydsl.core.types.Expression

@Override
public List<CategoryNeighborsVo> findNeighbors(Pageable pageable, long currentId, Map<CategoryFilter, String> filter) {
    // 1. Set sort criteria
    Path<? extends Comparable> orderPath = category.id;
    boolean isAscending = false; // 기본: 최신순(내림차순)

    // Extract sort information from Pageable
    if (pageable.getSort().isSorted()) {
        Sort.Order order = pageable.getSort().stream().findFirst().orElse(null);
        if (order != null) {
            isAscending = order.getDirection().isAscending();
            switch (order.getProperty()) {
                case "code" -> orderPath = category.code;
                case "name" -> orderPath = category.name;
            }
        }
    }

    // 2. Create Alias for CTE (For Self-Join)
    QCategoryCTE target = new QCategoryCTE("target");

    return new BlazeJPAQuery<CategoryNeighborsVo>(em, cbf)
            .with(categoryCTE, new BlazeJPAQuery<>()
                    .from(category)
                    .bind(categoryCTE.id, category.id)
                    // Generate ROW_NUMBER based on sort criteria
                    .bind(categoryCTE.rowNum, getRowNumField(orderPath, isAscending))
                    .where(getListCondition(filter)) // Search filter
                    .orderBy(
                            new OrderSpecifier<>(isAscending ? Order.ASC : Order.DESC, Expressions.asComparable(orderPath)),
                            isAscending ? category.id.asc() : category.id.desc() // Guarantee order with PK (Tie-Breaker)
                    )
            )
            .select(Projections.fields(
                    CategoryNeighborsVo.class,
                    categoryCTE.id,
                    // If rowNum is smaller than target, 'previous'; if larger, 'next'
                    new CaseBuilder()
                        .when(categoryCTE.rowNum.lt(target.rowNum))
                        .then(false)
                        .otherwise(true).as("next")
            ))
            .from(categoryCTE)
            .join(target).on(target.id.eq(currentId)) // Find current post (Reference point)
            .where(categoryCTE.rowNum.eq(target.rowNum.add(1)) // Next
                    .or(categoryCTE.rowNum.eq(target.rowNum.subtract(1)))) // Previous
            .orderBy(categoryCTE.rowNum.asc())
            .fetch();
}

private Expression<Long> getRowNumField(Path<? extends Comparable> path, boolean isAscending) {  
    OrderSpecifier<?> orderSpecifier = isAscending  
            ? new OrderSpecifier<>(Order.ASC, path)  
            : new OrderSpecifier<>(Order.DESC, path);  
  
    OrderSpecifier<?> idSpecifier = isAscending ? category.id.asc() : category.id.desc();  
  
    // SQL: ROW_NUMBER() OVER (ORDER BY {sortColumn} {direction}, id {direction})
    // Guarantee order using category.id as a secondary sort key
    return JPQLNextExpressions.rowNumber()  
            .over()  
            .orderBy(orderSpecifier, idSpecifier);  
}

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
@NoArgsConstructor
public class CategoryNeighborsVo {
    private Long id;
    private Boolean next;
}