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

score 7 · Answer 1 · answered Feb 04 '19 at 18:08

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

score 5 · Accepted Answer · answered Mar 18 '12 at 17:48

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.

score 2 · Answer 3 · edited May 23 '17 at 12:22

2

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 '17 at 12:22

Community

1
1

answered Jul 22 '16 at 14:56

Ruifeng Ma

2,399
1
22
40

2

`function` is used to call a database function so this is not portable. – Archimedes Trajano Jul 11 '20 at 16:36

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

3 Answers3

Linked