DTO Projection not working with specification

[nidhikaushal]

I was trying to use projection with specification, but I am getting the error:
Caused by: org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [com.entity.ParticipationEntity] to type [com.entity.TenantOnly]. The reason being select query has all the columns and not just the ones present in TenantOnly.

projection interface

public interface TenantOnly {
    String getTenantId();
}

@Data
@Entity
@Table(name = "participation", schema = "public")
public class ParticipationEntity {

    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "programId")
    private String programId;

    @Column(name = "tenantId")
    private String tenantId;

}

Spring Data repository

public interface ParticipationSpringDataRepository extends JpaRepository<ParticipationEntity, Long>, JpaSpecificationExecutor<ParticipationEntity> {
    List<TenantOnly> findAll(Specification specification);
}

public List<String> getParticipatingTenants(List<String> tenantIds,  List<String> programIds, OffsetDateTime processedDate){
    List<TenantOnly> tenantOnlies = participationSpringDataRepository.findAll(
            Specification.where(tenantIdIn(tenantIds))
                    .and(programId(programIds)));

    return tenantOnlies.stream().map(tenantOnly -> tenantOnly.getTenantId()).toList();
}

private Specification<ParticipationEntity> tenantIdIn(List<String> tenantIds){
    return (root,  query, criteriaBuilder) -> {
        if(tenantIds == null || tenantIds.isEmpty()){
            return criteriaBuilder.conjunction();
        }
        return criteriaBuilder.in(root.get(ParticipationEntity_.TENANT_ID)).value(tenantIds);
    };
}

build versions
org.springframework.boot:spring-boot-starter-data-jpa:3.0.5
org.postgresql:postgresql:42.6.0

[nidhikaushal]

I have changed the repository invoking method to

public List<String> getParticipatingTenants(List<String> tenantIds,  List<String> programIds, OffsetDateTime processedDate){
    List<TenantOnly> tenantOnlies = participationSpringDataRepository.findAllDtoProjectedBy(
            Specification.where(tenantIdIn(tenantIds))
                    .and(programId(programIds)));

    return tenantOnlies.stream().map(tenantOnly -> tenantOnly.getTenantId()).toList();
}

and the repository interface to

public interface ParticipationSpringDataRepository extends JpaRepository<ParticipationEntity, Long>, JpaSpecificationExecutor<ParticipationEntity> {
    List<ParticipationDto> findAllDtoProjectedBy(Specification specification);
}

But it gives the following error now:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'participationSpringDataRepository' defined in com.entity.ParticipationSpringDataRepository defined in @EnableJpaRepositories declared on ParticipationApplication: Could not create query for public abstract java.util.List com.entity.ParticipationSpringDataRepository.findAllDtoProjectedBy(org.springframework.data.jpa.domain.Specification); Reason: Failed to create query for method public abstract java.util.List com.entity.ParticipationSpringDataRepository.findAllDtoProjectedBy(org.springframework.data.jpa.domain.Specification); At least 1 parameter(s) provided but only 0 parameter(s) present in query

[nidhikaushal]

I tried invoking the repository using findBy:

public List<String> getParticipatingTenants(List<String> tenantIds,  List<String> programIds, OffsetDateTime processedDate){
    List<TenantOnly> tenantOnlies = participationSpringDataRepository.findBy(Specification.where(tenantIdIn(tenantIds)),
                q -> q.as(TenantOnly.class).all());

    return tenantOnlies.stream().map(tenantOnly -> tenantOnly.getTenantId()).toList();
}

I also tried

public List<String> getParticipatingTenants(List<String> tenantIds,  List<String> programIds, OffsetDateTime processedDate){
    List<TenantOnly> tenantOnlies = participationSpringDataRepository.findBy(Specification.where(tenantIdIn(tenantIds)),
                q -> q.as(TenantOnly.class).project("tenantId").all());

    return tenantOnlies.stream().map(tenantOnly -> tenantOnly.getTenantId()).toList();
}

But the select query has all the columns.
How do I select only the columns mentioned in projection and its distinct values?

[nidhikaushal]

I looked at the classes FetchableFluentQueryByPredicate and FetchableFluentQueryBySpecification. Both of them add the properties in the hint and not the select part of the query, and the postgres driver probably is not honouring it.

I am using postgres solution provided by aws and as per documentation hints are not supported:
https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.tuning.hints.html

Probably In the "getQuery" method of SimpleJpaRepository we need to pass properties and change "query.select(root);" to "query.multiselect(root.get(propertyName))"

Is there any way to solve this problem

[mp911de]

Specifications aren't accepted as parameters in derived queries, therefore List<ParticipationDto> findAllDtoProjectedBy(Specification specification); fails.

We currently do not support DTO projections because we do not differentiate what to select, we just select the entity Root<U>.

[mp911de]

Closing this as duplicate of #487