How to properly cast string to number with JPA2 Criteria API?

Question

I am trying to write a query with subselect where a string is cast to a long. I'm probably missing something?

Query looks like:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Task> query = cb.createQuery(Task.class);

Root<Task> from = query.from(Task.class);

Subquery<Long> subquery = query.subquery(Long.class);
Root<EntityKeyword> fromKeyword = subquery.from(EntityKeyword.class); 
subquery.select(fromKeyword.get(EntityKeyword_.relatedToId).as(Long.class));
subquery.where(cb.like(fromKeyword.get(EntityKeyword_.keyword), term));

query.where(cb.in(from.get(ModelEntity_.id)).value(subquery));

Where EntityKeyword_.relatedToId a is String that requires cast to Long.

But underlying Hibernate fails with exception:

Last cause: No data type for node: org.hibernate.hql.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'cast' {originalText=cast}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[DOT] DotNode: 'entitykeyw1_.keyword' {propertyName=keyword,dereferenceType=ALL,propertyPath=keyword,path=generatedAlias1.keyword,tableAlias=entitykeyw1_,className=l.i.s.m.s.EntityKeyword,classAlias=generatedAlias1}
       |  +-[ALIAS_REF] IdentNode: 'entitykeyw1_.id' {alias=generatedAlias1, className=l.i.s.m.s.EntityKeyword, tableAlias=entitykeyw1_}
       |  \-[IDENT] IdentNode: 'keyword' {originalText=keyword}
       \-[IDENT] IdentNode: 'int8' {originalText=int8}

No idea what's wrong. Any help is appreciated.

I'm using Hibernate 3.6.8-Final

Dinesh Chv · Accepted Answer · 2019-02-04 18:08:53Z

Below method worked for me to type cast String to other data types using CriteriaBuilder

CriteriaBuilder queryBuilder = em.getCriteriaBuilder();
Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Integer.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Long.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Double.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Date.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Boolean.class));

Initially I faced many errors using CriteriaBuilder and tried checking the DB logs to check the query which is getting executed. This helped in correcting the HQL as required. The above approach is working fine in PostgreSQL but the solution varies per Database

Mikko Maunu · Accepted Answer · 2012-03-18 17:48:35Z

5

There is no method in Criteria API that performs conversion from String to Long.

You try to use method as in Expression to make this conversion. Javadoc explains why you end up to have runtime problems:

Perform a typecast upon the expression, returning a new expression object. This method does not cause type conversion: the runtime type is not changed. Warning: may result in a runtime failure.

CriteriaBuilder does have bunch of methods for typecast, but also no support for string-to-numeric conversion.

answered Mar 18, 2012 at 17:48

Mikko Maunu

42.3k11 gold badges143 silver badges142 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:22:34Z

3

A type casting can be done by the CriteriaBuilder though.

CriteriaBuilder queryBuilder = em.getCriteriaBuilder(); 
Expression roomIdStr = root.get("room").get("id");
Expression roomIdInt = queryBuilder.function("INT", Integer.class, roomId);

See answer also from StackOverflow question 23728108

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Jul 22, 2016 at 14:56

Ruifeng Ma

2,8471 gold badge31 silver badges47 bronze badges

1 Comment

Archimedes Trajano Over a year ago

function is used to call a database function so this is not portable.

michal.jakubeczy · Accepted Answer · 2024-12-12 15:37:19Z

3

In Hibernate prior to version 6.6.1 it seemed to worked as @Dinesh describes.

queryBuilder.desc(root.get("room").get("id")).as(Integer.class))

Since Hibernate 6.6.1 this has changed: https://docs.jboss.org/hibernate/orm/6.6/migration-guide/migration-guide.html#criteria-query

Current usage is following:

((JpaExpression)queryBuilder.desc(root.get("room").get("id"))).cast(Integer.class))

answered Dec 12, 2024 at 15:37

michal.jakubeczy

9,7771 gold badge71 silver badges84 bronze badges

Collectives™ on Stack Overflow

How to properly cast string to number with JPA2 Criteria API?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related