JPA: build a query with wildcards using entities

[Xavier Callejas]

Hi,

I need to create a query with wildcard so the user could filter the data.

In a simple SQL query I can use the word 'LIKE' to specify a value for a
paramter in a WHERE clause or if the user did'nt choose any filter I pass
'%' to the LIKE clause and I get all the rows.

But using JPQL I cannot pass a '%' to a parameter especting a entity object,
nither use the LIKE clause for a paramter especting a entity object.

In simply SQL, the query I would like is like this:

SELECT * FROM Clients WHERE Clients.Country LIKE ( IF(? == NULL, '%', ?) );

That way I have not to build "dinamically" the query (a lot of work).

How can I do something similar in JPQL (Country is a entity object in JPQL,
so I cannot use LIKE on it)?

I imagin to use WHERE Clients.Country.Name, because Name is a String not a
entity, so I could use LIKE, but is this way recommended?

Thank you in advance.

[Unmesh Chowdhury]

Hello,
You would try something like this –

“select c from Clients c where c.country.name like :name”

Where,
Clients and Country are entity classes and there is a Many-to-One relationship between Clients and Country. In the Clients instance country represents the Country instance with which the present client is related to. And name is an attribute of Country class.

Now you can pass the parameter (name) value with wildcard.

Thanks
u.c.