How can I cast a Integer to a String in EJBQL

Question

I got a Entity with a Integer

@Entity(name=customer)
public Customer {
    ...
    @Column
    private int number;
    ...
    //getter,setter
}

Now I want to cast this Integer in a query to compare it with an other value.

I tried this Query:

"SELECT c FROM customer c WHERE CAST(c.number AS TEXT) LIKE '1%'"

But it doesn't work.

try this SELECT c FROM customer c WHERE CAST(c.number AS varchar(5)) LIKE '1%' — Mennan, Jul 01 '12 at 00:22

score 3 · Answer 1 · answered Jul 01 '12 at 11:07

3

This works in some of my code using Hibernate:

SELECT c FROM customer c WHERE STR(c.number) LIKE '1%'

In general, this is what the Hibernate docs (14.10 Expressions) say on casting:

str() for converting numeric or temporal values to a readable string

cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database

answered Jul 01 '12 at 11:07

Istvan Devai

3,962
23
21

ejbql is a bit different from jpql and hql. I get the same exception: Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing the query [SELECT c FROM customer c WHERE STR(c.number) LIKE '1%'], line 1, column 31: unexpected token [(]. – – Paul Wasilewski Jul 02 '12 at 14:48
@Istvan Devai Man I was looking for this whole day! Thank you. – matus Jan 15 '13 at 23:52

Paul Wasilewski · Accepted Answer · 2016-05-14T20:19:38.573

3

Since EJB3 EJBQL has been (almost) replaced by JPQL. In EJBQL and according to http://docs.oracle.com/cd/E11035_01/kodo41/full/html/ejb3_langref.html in JPQL as well there is no functionality to CAST a property of an entity.

So like I already told there are two options left:

Use a native Query.
Add special cast methods to the entities.

edited May 14 '16 at 20:19

answered Jul 02 '12 at 20:36

Paul Wasilewski

9,762
5
45
49

Jon Crowell · Answer 3 · 2012-07-06T12:39:47.977

0

You need to specify the column you're selecting from table alias c, and since EJBQL doesn't support a cast function, pass a string into the query instead of text. (This effectively allows your program to do the cast before it gets to EJBQL.)

The example below is in SQL Server, but should give you the idea:

 declare @numberText as varchar(50)
 set @numberText = '1'

 SELECT c.CustomerNumber FROM customer c 
 WHERE c.CustomerNumber  LIKE @numbertext + '%'

So instead of private int number use private string numberText.

NOTE: I edited this answer after OP confirmed EJBQL does not support a CAST function.

edited Jul 06 '12 at 12:39

answered Jul 01 '12 at 00:41

Jon Crowell

21,695
14
89
110

Sorry I make a big mistake it's not jpql, it's ejb-ql. – Paul Wasilewski Jul 01 '12 at 00:48
Try my query. It should work. I just edited it to include your column and table. (I originally posted my answer that was based on the table I tested it on.) – Jon Crowell Jul 01 '12 at 00:49
Unfortunely, it doesn't. Same exception: Caused by: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing the query [SELECT c.number FROM customer c WHERE CAST(c.number AS TEXT) LIKE '1%'], line 1, column 45: unexpected token [(]. – Paul Wasilewski Jul 01 '12 at 01:00
Hmmm... can you try the query without the where clause to see if it works? Does johntotetwoo's updated query work? – Jon Crowell Jul 01 '12 at 01:04
No, johntotetwoo's solution doesn't work also. Sure if I'm not using the CAST function, everything works fine. – Paul Wasilewski Jul 01 '12 at 01:11
Sorry first I forgot. Sure if I'm not using the CAST function everything works fine. – Paul Wasilewski Jul 01 '12 at 01:13
I guess EJB-QL has not CAST functionality. Sorry for taken your time. – Paul Wasilewski Jul 01 '12 at 01:16
It has to have CAST functionality. I re-wrote my answer -- give it one more shot. I think we're almost there. – Jon Crowell Jul 01 '12 at 01:24
No, same exception again: Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing the query [SELECT c.number FROM customer c WHERE c.number LIKE CAST(c.number as varchar(50)) + '1%'], line 1, column 52: unexpected token [(]. I'm almost sure there is no CAST functionality in ejb-ql. I'm feeling very sorry for asking such a stupid question and taken your time. – Paul Wasilewski Jul 01 '12 at 01:35
Don't give up yet, @Andi! I added an alternative answer that doesn't require a cast. – Jon Crowell Jul 01 '12 at 01:55

How can I cast a Integer to a String in EJBQL

3 Answers3