JPQL: cast Long to String to perform LIKE search
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 a java.lang.Long .
Q :
How do you perform a LIKE search on a java.lang.Long using JPQL?
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).
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.. )
我已经通过在实体中创建@transient字段解决了同样的问题，然后使用下面的查询进行搜索：
id LIKE CONCAT('%',:txnId)
上一篇: 蓝色“光环”在Safari中的标签上