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:${blazebitVersion}"
implementation "com.blazebit:blaze-persistence-integration-querydsl-expressions-jakarta:${blazebitVersion}"
implementation "com.blazebit:blaze-persistence-integration-hibernate-6.2:${blazebitVersion}"
}
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;
}
참고
https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/
https://j-k4keye.tistory.com/68
https://sightstudio.tistory.com/53
