CODEWITHSUNDEEP
hibernatejpa
tin_tin
tin_tin

Reputation: 488

Unable to use size function in HQL

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)

Upvotes: 1

Views: 974

Answers (2)

SternK
SternK

Reputation: 13101

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)

Upvotes: 1

tin_tin
tin_tin

Reputation: 488

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>

Upvotes: 0

Related Questions