jpa constructor expressions with multiple SELECT NEW statements

Question

Is there a way to have multiple SELECT NEW statements in a jpql query (Hibernate)?

This works for me:

@Query("SELECT NEW com.test.project.dto.ItemService(g,s,l,r) "
        +" FROM Item g, Service s, Service l , Service r"
        +" WHERE s.id = g.id" 
        +" AND s.location = l.name"
        +" AND s.serviceType = 'type'"
        +" AND l.serviceType = 'Location'"
        +" AND l.area = r.name" 
        +" AND r.serviceType = 'Region'")
public List<Item> getAllItemsWithServices();

I get the expected Result in my DTO.

@Component
public class ItemServiceDTO{

    private Item item;
    private Service serviceType;
    private Service serviceLocation;
    private Service serviceRegion;

    public ItemServiceDTO(item item, Service serviceType, Service serviceLocation, Service serviceRegion) {
        super();
        this.item = item;
        this.serviceType = serviceType;
        this.serviceLocation = serviceLocation;
        this.serviceRegion = serviceRegion;
    }

But what I want is to have a new instance of Language with its contructor.

For example like this:

 @Query("SELECT NEW com.test.project.dto.ItemService(g,s,l,r), new LanguageDTO()"
            +" FROM Item g, Service s, Service l , Service r"

Or in a subselect of ItemService

 @Query("SELECT NEW com.test.project.dto.ItemService(g,s,l,r, new LanguageDTO())"
                +" FROM Item g, Service s, Service l , Service r"

I also interested in using Map and List in my DTO Objects but I read thats not possible? Is that right?

My Spring boot application does start with errors while using the two examples.

At the end I want a Map of Map<List<Item>,Map<List<LanguageDTO>,List<ItemServiceDTO>>> map;

Answer 1

Technically, by definition of JPQL select clause, it would allow multiple constructor expressions.

• select_clause ::= SELECT [DISTINCT] select_expression {, select_expression}*
• select_expression ::= single_valued_path_expression | aggregate_expression | identification_variable |
  OBJECT(identification_variable) | constructor_expression
• constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* )
• constructor_item ::= single_valued_path_expression | aggregate_expression
• aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT]
  identification_variable | state_field_path_expression |
  single_valued_association_path_expression)

Example:

SELECT NEW com.test.model.UserName(u.firstname, u.lastname), NEW com.test.model.UserEmail(u.email) FROM User u

However, I just discovered that Hibernate does not allow it. When I switched JPA provider from Hibernate to EclipseLink, it works. So, you may need to consult with your provider if such query syntax is allowed.

Take note however, that when using NEW operator, your constructor has to have arguments (at least one). So this expression won't work:

SELECT NEW LanguageDTO()

On your second question, whether it is possible to use List and Map, I'm quite confused how you would want to use these collections in your query. However, take note that it is not possible to have collection valued path expressions in your SELECT clause as per definition of the JPQL SELECT_CLAUSE.