Return only specific columns when returning a collection of projections while using the JpaSpecificationExecutor#findBy

[radarsh]

I would like to be able to return a collection of projections while using specifications to filter them. I found the newly introduced findBy convenience in the JpaSpecificationExecutor interface but when I try to return a page of projections, it simply ends up retrieving all the columns from the database.

// MyProjection is an interface with nothing special about it

// Here specification is dynamically constructed
return myRepository.findBy(specification, q -> q
            .as(MyProjection.class)
            .page(pageable));

This problem is very similar to the closed #2632. However, I cannot resort to using JPQL or native queries because I have a need to use complex and dynamically constructed specifications to filter out the results.

The table I am querying contains a few JSONB (Postgres) columns that can contain huge JSON documents. So, even though I'm declaring projection interfaces without the JSONB columns, the actual query being made to the database ends up still retrieving the unwanted columns. This causes a massive performance degradation and the approach doesn't scale well.

I'm really out of my depth here and would appreciate any insights or tips.

[quaff]

It's a very useful feature.

I have a naive idea to implement it but have two problems need to be resolved.

1. ReturnedType::of is not public
2. ProjectionFactory need injected by constructor

spring-data-jpa/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/support/FetchableFluentQueryBySpecification.java

Lines 103 to 112 in 699d3a2

	public <NR> FetchableFluentQuery<NR> as(Class<NR> resultType) {
	Assert.notNull(resultType, "Projection target type must not be null");
	if (!resultType.isInterface()) {
	throw new UnsupportedOperationException("Class-based DTOs are not yet supported.");
	}
	return new FetchableFluentQueryBySpecification<>(spec, entityType, resultType, sort, limit, properties, finder,
	scroll, countOperation, existsOperation, entityManager);
	}

		Collection<String> propertiesToUse = this.properties;
		ReturnedType returnedType = ReturnedType.of(resultType, entityType, projectionFactory);
		if (returnedType.isProjecting()) {
			propertiesToUse = returnedType.getInputProperties();
		}
		return new FetchableFluentQueryBySpecification<>(spec, entityType, resultType, sort, limit, propertiesToUse, finder,
				scroll, countOperation, existsOperation, entityManager);

[mp911de]

Projection queries do not yet support tuple queries to constrain the selected fields. This is a duplicate of #3144.