CODEWITHSUNDEEP
javajpajpqlsql-likelong-integer
Kawu
Kawu

Reputation: 14003

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?

Upvotes: 15

Views: 23766

Answers (5)

Dherik
Dherik

Reputation: 19050

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).

Upvotes: 8

Carlos Mario Giraldo Puerta
Carlos Mario Giraldo Puerta

Reputation: 1

have you consider trying with the JPQL TRIM(num) ?

Upvotes: 0

Amit Jain
Amit Jain

Reputation: 127

You can simply use CAST(num as string) or CONCAT(num,''). It worked for me

Upvotes: 2

Sanchi Girotra
Sanchi Girotra

Reputation: 1370

I have fixed the same issue by creating a @transient field in the entity and then used below query for search :

id LIKE CONCAT('%',:txnId)

Upvotes: 1

FiruzzZ
FiruzzZ

Reputation: 826

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.. )

Upvotes: 2

Related Questions