JPQL: cast Long to String to perform LIKE search

java, jpa, jpql, long-integer, sql-like

Solution

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 can't use the `nbr` index (if `nbr` column is indexed).

Problem

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?

Original source