Casting Integer to String in JPQL

Question

I want to have a JPQL query that may look like:

    entityManager.createQuery("Select a.* from A a WHERE CAST(a.num AS TEXT) LIKE '%345%' ", A.class);

where a.num is an integer. I want to cast this to String to use the LIKE criteria. However above casting doesnt work in JPQL. Any idea about how can I implement this?

except that "SELECT *" doesnt exist in JPQL. Who knows what is "doesnt work" ... maybe some SQL is invoked? — , Sep 13 '17 at 15:09
@billyfrost edited the query to include a.*. I meant the casting doesnt work like this in jpql. The exception goes like... cannot CAST to TEXT. I am using JPA entitymanager to create the query. — Pratap, Sep 13 '17 at 17:18

Stas Shafeev · Accepted Answer · 2017-09-13T07:51:03.697

16

Could you be more specific, what your problem is? Do you have some kind of error or it the result of the query is just wrong?

Because this code works fine for me:

session.createQuery("from MyObject where CAST(id as text) like :id").setParameter("id", "%1").getResultList();

I am using Hibernate 5.2 and Postgresql 9.5. Also, if you are having trouble understanding what goes wrong, you could try editing hibernate.cfg.xml or whatever configuration file you are using to make Hibernate show queries it sends, by doing something like this:

<property name="hibernate.show_sql">true</property>

edited Sep 13 '17 at 07:51

answered Sep 13 '17 at 07:43

Stas Shafeev

606
6
8

2

Yes, this code will work as you are using hibernate's session to create the query. Whereas I am using JPA entitymanager to do the same. I had tried the same casting in my query too but failed with an exception, something like "Cannot CAST to text" (dont remember exactly as I dont have the code with me right now). Seems JPQL doesnt support castings. – Pratap Sep 13 '17 at 17:23
1

Well, the the only other option, that I know, is using native queries, as was adviced before. Though it usually seems a bit ugly (maybe, even seroiusly ugly), for some operations it is the only way, as far as I know. – Stas Shafeev Sep 14 '17 at 06:50
1

Argh it's case sensitive for casting types? `TEXT` produced an error but `text` works just fine. – medley56 Feb 21 '18 at 23:42

score 11 · Answer 2 · answered Oct 30 '19 at 11:05

11

From hibernate document, "cast(... as ...)", where the second argument is the name of a Hibernate type. According list of Hibernate Types it should be string (case sensitive!).

So request should be:

entityManager.createQuery("select a.* from A a WHERE CAST(a.num AS string) LIKE '%345%' ", A.class);

Was taken and checked from Caused by: org.hibernate.QueryException: Could not resolve requested type for CAST : INT answer.

answered Oct 30 '19 at 11:05

Grigory Kislin

16,647
10
125
197

thanks a ton for the clue on string's case sensitivity; String wasn't working.. – sarath Nov 17 '22 at 11:09

score 10 · Answer 3 · answered Jan 07 '18 at 12:16

10

I also have the same need. This should work with JPA 2.0 and Hibernate 5.0.2:

entityManager.createQuery(
    "Select a.* from A a WHERE CONCAT(a.num, '') LIKE '%345%' ", A.class);

answered Jan 07 '18 at 12:16

Hendy Irawan

20,498
11
103
114

score 2 · Answer 4 · answered Jul 03 '19 at 09:23

2

You can simply use CAST(num as string). It worked for me

answered Jul 03 '19 at 09:23

Amit Jain

97
4

score 1 · Answer 5 · edited Oct 04 '18 at 16:41

Well you can use to_char() function in the select clause but, you will need to select all the a.num field separately and not with *.

And in postgresql you will need to specify a mask for to_char()function, so it would be to_char(field, mask), for example we can supply 'FM999999999999999999' as a mask to accept the maximum possible digits.

Your query would be something like this:

Select *, to_char(a.num, 'FM999999999999999999') as num from A a WHERE num LIKE '%345%'

You can take a look at Postgresql Data Type Formatting Functions for further details.

To write the query in your code with EntityManager you can create a native query using .createNativeQuery() method, this is how should be your code:

em.createNativeQuery("Select *, to_char(a.num, 'FM999999999999999999') as num from A a WHERE num LIKE '%345%'");

The issue is more related to JPQL than to postgres. I am using JPA's entitymanager.createQuery(). I will have to check whether this function works as a part of JPQL query. — Pratap, Sep 13 '17 at 17:05
@Pratap Yes with `EntityManager` you can create a native query like this èm.createNativeQuery()`, take a look at my edit. — cнŝdk, Sep 13 '17 at 21:08

score 1 · Answer 6 · edited Feb 06 '20 at 14:13

1

The correct query is :

entityManager.createQuery("Select a.* from A a WHERE CAST(a.num AS String) LIKE '%345%' ", A.class);

edited Feb 06 '20 at 14:13

geisterfurz007

5,292
5
33
54

answered Oct 08 '17 at 05:22

Pratap

655
2
10
20

That would give: `org.hibernate.QueryException: Could not resolve requested type for CAST : String` – Hendy Irawan Jan 07 '18 at 12:08

Casting Integer to String in JPQL

6 Answers6

Linked