SQL query not optimized when using Projection with a Specification (FetchableFluentQuery)

[gonzalad]

Hello,

Summary

Atm, using projection with specifications doesn't change the generated SQL query, the JPA entity is still loaded under the hood.

Could we expect query optimization in the future when using Projections with Specifications ? (limiting the fields to be loaded to those of the projection interface/class ?)

Detailed issue

I just tried SB 3.0 Projection support with Specifications:

    @Test
    void testProjectionBySpecification() {
        repository.save(new Customer("victor"));

        List<CustomerProjectionInterfaceBased> result = repository.findBy(
                customerHasFirstnameLike("v"),
                q -> q.as(CustomerProjectionInterfaceBased.class).all()
        );

        assertThat(result).hasSize(1);
    }

    private interface CustomerProjectionInterfaceBased {
        String getFirstname();
    }

This works nicely, bu when I look at the sql, I see:

select c1_0.id,c1_0.firstname,c1_0.lastname from customer c1_0 where cast(c1_0.firstname as varchar(2147483647)) like ?

I would have expected the SQL to fetch only the properties from the projection, i.e.:

select c1_0.firstname from customer c1_0 where cast(c1_0.firstname as varchar(2147483647)) like ?

Even if I use project(<property>):

        List<CustomerProjectionInterfaceBased> result = repository.findBy(
                customerHasFirstnameLike("v"),
                q -> q.as(CustomerProjectionInterfaceBased.class)
                        .project("firstname")
                        .all()
        );

I still get the same sql.

Looking at the source code, I see in FetchableFluentQueryBySpecification:

The project(property) method updates the properties attribute. This attribute is only used to set the fetchgraph

if (!properties.isEmpty()) {
  query.setHint(EntityGraphFactory.HINT, EntityGraphFactory.create(entityManager, entityType, properties));
}

The as(Class) method is used after executing the jpa query to convert the jpa entity to the projection type, hence this one doesn't change the sql query generation behaviour atm.

So, to optimize the generated query, the only way I found atm would be to rely on project(String), activate bytecode enhacement and annotate each basic attribute with @Basic(fetch=LAZY).

But setting @basic(fetch=LAZY) seems to me to be too much hassle because it impacts all the queries I already have in my application.

[schauder]

Related: #1524

[skZeppelin]

any workaround

[dimkich]

any workaround

public interface ExtendedRepository<T, ID extends Serializable> extends Repository<T, ID> {
    <S> Page<S> findAll(Specification<T> spec, Pageable pageable, List<String> fields, Class<S> type);
}

@NoRepositoryBean
public class ExtendedRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements ExtendedRepository<T, ID> {
    private final EntityManager entityManager;
    private final Method applySpecificationToCriteria;
    private final Method applyRepositoryMethodMetadata;

    @SneakyThrows
    public ExtendedRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;

        applySpecificationToCriteria = SimpleJpaRepository.class.getDeclaredMethod(
                "applySpecificationToCriteria", Specification.class, Class.class, CriteriaQuery.class);
        applySpecificationToCriteria.setAccessible(true);

        applyRepositoryMethodMetadata = SimpleJpaRepository.class.getDeclaredMethod(
                "applyRepositoryMethodMetadata", TypedQuery.class);
        applyRepositoryMethodMetadata.setAccessible(true);
    }

    public <S> Page<S> findAll(Specification<T> spec, Pageable pageable, List<String> fields, Class<S> type) {
        TypedQuery<S> query = getQuery(spec, getDomainClass(), Sort.unsorted(), fields, type);
        return pageable.isUnpaged() ? new PageImpl<>(query.getResultList())
                : (Page<S>) readPage((TypedQuery<T>) query, getDomainClass(), pageable, spec);
    }

    @SneakyThrows
    protected <S, U extends T> TypedQuery<S> getQuery(@Nullable Specification<U> spec, Class<U> domainClass, Sort sort,
                                                      List<String> fields, Class<S> type) {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<S> query = builder.createQuery(type);

        Root<U> root = (Root<U>) applySpecificationToCriteria.invoke(this, spec, domainClass, query);
        if (fields == null || fields.isEmpty()) {
            query.select((Root<S>) root);
        } else {
            List<Selection<?>> selections = new ArrayList<>();
            for (String field : fields) {
                selections.add(root.get(field));
            }
            query.multiselect(selections);
        }

        if (sort.isSorted()) {
            query.orderBy(toOrders(sort, root, builder));
        }

        return (TypedQuery<S>) applyRepositoryMethodMetadata.invoke(this, entityManager.createQuery(query));
    }
}

@EnableJpaRepositories(repositoryBaseClass = ExtendedRepositoryImpl.class)
public class MySpringApplication {
    public static void main(final String[] args) {
        SpringApplication.run(MySpringApplication .class, args);
    }
}

Usage

Page<Integer> page = repo.findAll(spec, Pageable.unpaged(), List.of("id"), Integer.class);

[rlreis90]

Any updates on this issue? I have this exact use case as described in the OP for implementing (optimized) GraphQL queries that only retrieve requested fields/columns from database.