Is it possibe to map a query with nested select statements to a DTO?

I have a query with nested select

Query query = manager.createQuery("Select a, (Select b from some_table where condition)....");

There is a DTO Class

public class Result
{
private int a;
private int b;

//getters and setters
}

The method of new packageName.Result(a,b) in the query wont work here because of nested select, so what can be done here? Thanks in advance

Answer

The JPQL constructor expression is really just syntax sugar, so you could just as well transform the resulting list afterwards.

TypedQuery<Object[]> query = manager.createQuery("Select a, (Select b from some_table where condition)....", Object[].class);
List<Object[]> list = query.getResultList();
list.stream().map(array -> new Result((int) array[0], (int) array[1])).collect(toList())

I think this is a perfect use case for Blaze-Persistence Entity Views, especially if you have the need for more complex or nested DTOs.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(MainEntity.class)
public interface Result {
    int getA();
    @MappingSubquery(MySubqueryProvider.class)
    int getB();

    class MySubqueryProvider implements SubqueryProvider {
        @Override
        public <T> T createSubquery(SubqueryInitiator<T> subqueryBuilder) {
            return subqueryBuilder.from(SubEntity.class, "subEntity")
                       .select("subEntity.b")
                       .where("subEntity.c").eqExpression("OUTER(c)")
                   .end();
        }
    }
}

Which will create the subquery just as you expect it. Depending on your subquery, you could also use this simpler variant

@EntityView(MainEntity.class)
public interface Result {
    int getA();
    @Mapping("SubEntity[c = VIEW(c)].b")
    int getB();
}

Which will produce a left join query like this:

select e.a, s.b
from MainEntity e
left join SubEntity b on b.c = e.c

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

Result a = entityViewManager.find(entityManager, Result.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<Result> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!