Unable to use size function in HQL

Question

List<Object> outputList = session.createQuery(
   "select usr from User usr where size(usr.user_messages) > 2 "
).list();

When I run the above query I get the below exception. The DB used is Postgres. How can I use size in the HQL?

@OneToMany(mappedBy  = "user", cascade = CascadeType.PERSIST)
private Set<UserMessages> user_messages = new HashSet<UserMessages>();

Exception in thread "main" java.lang.ClassCastException: org.hibernate.query.sqm.tree.expression.SqmCollectionSize cannot be cast to org.hibernate.sql.ast.tree.expression.Expression
    at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:2271)
    at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:205)
    at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.accept(SqmComparisonPredicate.java:67)
    at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:423)
    at org.hibernate.query.sqm.sql.internal.StandardSqmSelectTranslator.visitSelectStatement(StandardSqmSelectTranslator.java:185)
    at org.hibernate.query.sqm.sql.internal.StandardSqmSelectTranslator.translate(StandardSqmSelectTranslator.java:127)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:226)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.resolveCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:193)
    at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:153)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:437)
    at org.hibernate.query.spi.AbstractQuery.list(AbstractQuery.java:1356)
        at com.learning.hibernate.HibernateMain.main(HibernateMain.java:103)

https://stackoverflow.com/questions/3760711/hql-query-to-check-if-size-of-collection-is-0-or-empty hope this link helps you fix this. — Aayush Sahu, Nov 16 '20 at 19:41
@SternK you are correct to point i was using Hibernate 5 but in hibernate 4 it works fine.. can u tell why the support for size has been withdrawn in higher version — tin_tin, Nov 18 '20 at 17:53

SternK · Accepted Answer · 2020-11-18T20:42:11.897

Hibernate 6.0 is not stable now. The size expression perfectly works in the latest stable hibernate 5.4 release (5.4.24.Final).

The following hql:

List<User> users = em.createQuery(
   "select u from User u where size(u.messages) > 1",
   User.class).getResultList();

will generate the following sql:

select
   user0_.usr_id as usr_id1_1_,
   user0_.usr_name as usr_name2_1_ 
from TEST_SCHEMA.TST3_USERS user0_ 
where (
   select count(messages1_.msg_user_id) 
   from TEST_SCHEMA.TST3_MESSAGE messages1_ 
   where user0_.usr_id = messages1_.msg_user_id
) > 1

P.S. I would suggest you to use jpa 2.0 typed query:

TypedQuery<T> createQuery(String qlString, Class<T> resultClass)

instead of old-fashioned

Query createQuery(String qlString)

score 0 · Answer 2 · answered Nov 18 '20 at 17:55

with the below dependency size function works fine

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>4.0.0.Final</version>
    </dependency>

But the for this dependency size function does not work. Don't know why support has ben withdrawn but my problem has been solved

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>6.0.0.Alpha6</version>
    <type>pom</type>
</dependency>

Unable to use size function in HQL

2 Answers2