| Given the following example entity:
@Entity
public class MyEntity implements Serializable {
@Id
@GeneratedValue
private long id;
@Basic
private String valueA;
@Basic
private String valueB;
}
I want to count the instances of that entity in the database, distinct by valueA and valueB. For that I am using something like this:
entityManager.createQuery("SELECT count(distinct (me.valueA || me.valueB)) from MyEntity me").getSingleResult();
entityManager.createQuery(
query.select(
criteriaBuilder.countDistinct(
criteriaBuilder.concat(
from.get("valueA"),
from.get("valueB")
)
)
)
).getSingleResult();
When I execute the statement on the database, it is working fine. But sadly, Hibernate throws an QuerySyntaxException:
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 23 [SELECT count(distinct (me.valueA || me.valueB)) from MyEntity me]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:296)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:188)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704)
If I add this to the example entity
@Formula("valueA || valueB")
private String valueAB;
and change the query to
entityManager.createQuery("SELECT count(distinct me.valueAB) from MyEntity me").getSingleResult()
everything is working fine, although the generated SQL is the same:
select count(distinct myentity0_.valueA || myentity0_.valueB) as col_0_0_ from MyEntity myentity0_
 Note: When I remove the brackets around the concatenation in the JPQL statement, another exception is raised:
org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '||' near line 1, column 33 [SELECT count(distinct me.valueA || me.valueB) from MyEntity me]
Am I doing something wrong? Is there maybe an alternative way to do what I am looking for? The @Formula annotation sadly is not JPA, so we cannot use it  |
