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?
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.
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>
TEXT produced an error but text works just fine.
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);
You can simply use CAST(num as string). It worked for me
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%'");
EntityManager you can create a native query like this èm.createNativeQuery()`, take a look at my edit.The correct query is :
entityManager.createQuery("Select a.* from A a WHERE CAST(a.num AS String) LIKE '%345%' ", A.class);
org.hibernate.QueryException: Could not resolve requested type for CAST : StringYou can use native SQL functions in JQPL code to perform this CAST.
For example converting String to Number in Oracle DB can look like this:
SELECT t
FROM TestEntity t
WHERE t.numeric_column = (SELECT FUNCTION('to_number', t2.string_column) FROM TestEntity2 t2)
