Named Query with like in where clause

java sql jpa named-query
RNJ picture RNJ · Jun 6, 2012 · Viewed 45.8k times · Source

Is it possible to have a like in a where clause in a named query? I am trying to do the following but am getting exceptions

@NamedQuery(name = "Place.getPlaceForCityAndCountryName",
query = "SELECT p FROM Place p WHERE " +
        "lower(p.city) like :city and " +
        "lower(p.countryName) like :countryName");

I tried adding % as you would do in normal SQL but get exceptions compiling.

Any pointers greatly appreciated!

Thanks

Answer

esej picture esej · Jun 6, 2012

You can't have the % in the NamedQuery, but you can have it in the value you assign the parameter.

As in:

String city = "needle";
query.setParamter("city", "%" + city + "%");

Related questions

How to call a stored procedure from Java and JPA

I am writing a simple web application to call a stored procedure and retrieve some data. Its a very simple application, which interacts with client's database. We pass employee id and company id and the stored procedure will return employee …

Read More
JPA Native Query select and cast object

I have got an Object Admin which extends User. By default both Objects are in the table User_ of my Derby Database (included fields from Admin). Normally I'd select an User like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<User&…

Read More
JPQL SELECT between date statement

I would like to convert this SQL statement to a JPQL equivalent. SELECT * FROM events WHERE events_date BETWEEN '2011-01-01' AND '2011-03-31'; This correctly retrieves the information from the table events. In my Events …

Read More