Casting Integer to String in JPQL

jpa jpa-2.0 jpql
Pratap picture Pratap · Sep 13, 2017 · Viewed 24.6k times · Source

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?

Answer

Stas Shafeev picture Stas Shafeev · Sep 13, 2017

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>

Related questions

JPA concat operator

Is there a JPA concat operator for string concatenation? I know there is a JPA CONCAT function, however its ugly to use for concatenating multiple strings. CONCAT(CONCAT(CONCAT(cola,colb),colc),cold) Vendors like Oracle offer || some other like …

Read More
Compare date only (without time) in JPA2 (JPQL)

Im trying to compare Calendars with JPA2. The query looks somewhat like that: TypedQuery<X> q = em.createQuery("select r from Record r where r.calendar= :calendar", X.class); Calendar c = foo(); // setting fields and stuff q.setParameter("…

Read More
JPQL statement returning boolean value

Is it possible to write JPQL query like following: select count(*) > 0 from Scenario scen where scen.name = :name that would return true/false boolean values depending of whether entity filling criteria exists or not? I would like to use …

Read More