Spring data jpa. Return Map as result of group by operation

Question

I have a query in my jpa repository interface.

@Query("select e.campaignId, e from CampaignCrTargetingProfile e where e.campaignId in :ids group by e.campaignId")
public Map<Integer, List<CampaignCrTargetingProfile>> findByCampaignIdIn(@Param("ids") Iterable<Integer> ids);

campaignId is Integer.

But when I try to execute this query, I caught an exception.

Caused by: org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type java.lang.Integer to type java.util.Map<?, ?>
at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:311) ~[spring-core-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:192) ~[spring-core-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.core.convert.support.ArrayToObjectConverter.convert(ArrayToObjectConverter.java:66) ~[spring-core-4.1.5.RELEASE.jar:4.1.5.RELEASE]
at org.springframework.core.convert.support.ConversionUtils.invokeConverter(ConversionUtils.java:35) ~[spring-core-4.1.5.RELEASE.jar:4.1.5.RELEASE]
... 192 common frames omitted

Is it possible to groping entites by its field and return result as Map? if it is impossible, is there another way to make something similar?

Answer 1

You may get a list from your JPQL query and then convert it to map using the Java 8 Stream API.

// This method in your Repository interface
@Query("SELECT e FROM CampaignCrTargetingProfile e WHERE e.campaignId IN :ids")
public List<CampaignCrTargetingProfile findByCampaignIdIn(@Param("ids") Iterable<Integer> ids);

// This source from your Service class
List<CampaignCrTargetingProfile> list = repo.findByCampaignIdIn(iterableArg);
Map<Integer, List<CampaignCrTargetingProfile>> mapResult = list.stream()
    .collect(Collectors.groupingBy(CampaignCrTargetingProfile::getCampaignId));

It is better to let the DBMS prepare the GROUP BY operation but in order to get a Map as a result then the aforementioned code can prepare it with just one line of code, using Java 8 Stream API.
As far as I have read, spring data does not support a result type of Map.
It is inefficient, but I suggest it for small data sets.

Important

I suggest to use this solution only when your data set consists of a small amount of entries and you are absolutely sure that this amount will not grow in the future, ex. when you use paging in order to limit your result set or a table of enumerated values because, as @adamwilliams comment, it is inefficient the grouping to be done in memory by the application.