JPQL: cast Long to String to perform LIKE search
Solution 1
You can use the CAST
in HQL:
SELECT il
FROM InsiderList il
WHERE ( il.deleteFlag IS NULL OR il.deleteFlag = '0' )
AND il.clientId = :clientId
AND ( LOWER( il.name ) LIKE :searchTerm
OR CAST( il.nbr as string ) LIKE :searchTerm
OR LOWER( il.type ) LIKE :searchTerm
OR LOWER( il.description ) LIKE :searchTerm )
But you can have serious performance problems doing this, because the database will cannot use the nbr
index (if nbr
column is indexed).
Solution 2
You can simply use CAST(num as string)
or CONCAT(num,'')
. It worked for me
Solution 3
simple.. CAST( field as text/varchar) LIKE
It must be a type knows by the database (not string like in HQL)
And looking at your query there is a more efficient way to do it:
With CONCAT you don't have to cast NON String arguments (WHEN there is more than one and AT LEAST one is an String)
This works: LOWER(CONCAT(name, nbr, description)) LIKE
This doesn't: CONCAT(nbr), I guess because it doesn't recognize a JPQL function CONCAT(Long.. )
Related videos on Youtube
Kawu
Updated on October 11, 2022Comments
-
Kawu over 1 year
I have the following JPQL query:
SELECT il FROM InsiderList il WHERE ( il.deleteFlag IS NULL OR il.deleteFlag = '0' ) AND il.clientId = :clientId AND ( LOWER( il.name ) LIKE :searchTerm OR il.nbr LIKE :searchTerm OR LOWER( il.type ) LIKE :searchTerm OR LOWER( il.description ) LIKE :searchTerm )
The customer wants us to be able to search be the
nbr
field, which is ajava.lang.Long
.Q:
How do you perform a LIKE search on a
java.lang.Long
using JPQL?-
Neil StocktonYou don't. LIKE is for Strings only, as per the JPQL BNF datanucleus.org/products/accessplatform_4_2/jpa/…
-
-
Kawu over 8 yearsToo bad the question was clearly about JPQL not HQL... we're using various JPA providers...
-
Dherik over 8 yearsMy mistake, @Kawu. Native query is an option for you? Do you think about create a new column with the
nbr
as VARCHAR type?