FetchableFluentQeury doesn't apply projection in the database level

[yassir78]

Description:

Hello everyone, and especially the Spring Boot community 👋

I'm currently facing a small issue while working with JPA Specifications. Because my entity has a large number of fields, I’d like to perform database-level projections instead of memory-based projections for performance reasons.

From my understanding, the only way to achieve this is by using the CriteriaBuilder API directly. However, before taking that route, I wanted to ask for guidance or best practices.

Questions:

Is there a recommended approach to perform projections at the database level when using JPA Specifications?

Can this be integrated cleanly with Spring Data’s specification infrastructure, or is CriteriaBuilder the only option?

Environment:

Spring Boot version: 3.4.4
JPA provider: Hibernate

Database: PostgreSQL

`

Sort sort = buildSort(searchQuery.getSortBy(), searchQuery.getSortDir());
PageRequest pageable = PageRequest.of(searchQuery.getPageIndex(), searchQuery.getPageSize(), sort);

return orderEntityRepository.findBy(
        specification,
        query -> query
                .as(OrderSearchView.class)
                .sortBy(sort)
                .page(pageable)
                .map(orderEntityMapper::toMechanicalShopSearchResponse));

}
`

[mp911de]

Interface projections work for Specifications, Query by Example (built on top of specifications) and for Querydsl. Please note that because of how JPA treats selections in relation to object graphs, projections can only limit top-level properties: A selection is essentially flat (SELECT foo, bar, …) and there is no way to compose object graphs using projections unless selecting nested object properties which would pull them to the top-level which does not reflect the object graph structure anymore.

So from your code, you seem to have everything you need for projections.
Let me know whether this helps.

[yassir78]

@mp911de Thank you for your reply. I completely understand what you're saying. I stuck to the basics and used the CriteriaBuilder while converting the specifications to predicates so that I could control my query selection.

[mp911de]

´SimpleJpaRepository.getQuery(…)´ sets the selection itself after evaluating the specification (CriteriaQuery.select(…) or CriteriaQuery.multiselect(…)) so you cannot control the selection from a Specification.

Since your question seems resolved, I'm closing this ticket as it isn't actionable.