Spring data jpa @Query - Cannot create an "AS" alias for a result map

Question

I map the result of the following JPQL-Query directly to a SpecialCustomDto object instead of the used javax.persistency entity object MyEntity. But I do not know, how to access the COUNT(DISTINCT e.attributeB), which will be mapped to the SpecialCustomDto.

This is the query.

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {

  @Query("SELECT new com.test.SpecialCustomDto(e.attributeA, COUNT(DISTINCT e.attributeB)) as specialCustomDto "
         + "FROM MyEntity e WHERE 5 = specialCustomDto.count GROUP BY e.attributeA")
  List<SpecialCustomDto> getSpecialCustomDtos(); 
}

As soon as I start the spring-boot application, Hibernate throws me following error:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: as near line 1, column...

I don't know how to access the aggregated COUNT(DISTINCT e.attributeB) element of the newly created SpecialCustomDto. Without the additional WHERE-clause, the mapping works as expected.

Check if it is allowed with new com.test.SpecialCustomDto(e.attributeA, COUNT e.attributeB) — Antoniossss
– Antoniossss, Commented Mar 5, 2019 at 15:00
Thanks for the advice, I tried it and this was the result: Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: COUNT near line 1, — git-flo
– git-flo, Commented Mar 5, 2019 at 15:02

Peter Šály · Accepted Answer · 2019-03-05 15:32:15Z

3

Aggregate functions can be used as condition using HAVING. The same as in native SQL.

SELECT new com.test.SpecialCustomDto(e.attributeA, COUNT(e.attributeB))
FROM MyEntity e 
GROUP BY e.attributeA
HAVING COUNT(e.attributeB) = 5

edited Mar 5, 2019 at 15:32

answered Mar 5, 2019 at 15:14

Peter Šály

2,9332 gold badges14 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens Schauder · Accepted Answer · 2019-03-05 15:17:24Z

2

Remove the alias, move the condition to a HAVING-clause since it operates on an aggregate-value and just put the count-expression in there.

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {

  @Query("SELECT new com.test.SpecialCustomDto(e.attributeA, COUNT(DISTINCT e.attributeB)) "
         + "FROM MyEntity e "
         + "GROUP BY e.attributeA "
         + "HAVING COUNT(DISTINCT e.attributeB) = 5")
  List<SpecialCustomDto> getSpecialCustomDtos(); 
}

Note: The @Repository is superfluous.

answered Mar 5, 2019 at 15:17

Jens Schauder

82.7k35 gold badges199 silver badges379 bronze badges

2 Comments

git-flo Over a year ago

Thanks for your answer! It worked as suggested. But in general: is it somehow possible to create an "AS" alias for the response mapping?

Jens Schauder Over a year ago

I don't think so.

Collectives™ on Stack Overflow

Spring data jpa @Query - Cannot create an "AS" alias for a result map

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related