Named Query with like in where clause

Question

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

What happens if you take the second like out of the lower() function :) — Davos555, Jun 06 '12 at 10:24
As @Davos555 points, there is a parenthesis and a quote missing: `"lower(p.countryName) like :countryName");` — ypercubeᵀᴹ, Jun 06 '12 at 10:26
yes thanks - I copied this from a more complicated example and did not change it correctly. I'll update the question now — RNJ, Jun 06 '12 at 10:41

score 66 · Accepted Answer · answered Jun 06 '12 at 10:25

66

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 + "%");

answered Jun 06 '12 at 10:25

esej

3,059
1
18
22

How to do this when you have multiple city values. – Mohit Arora Apr 26 '22 at 18:45

score 14 · Answer 2 · answered Oct 07 '16 at 08:56

14

You can also use CONCAT function

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

answered Oct 07 '16 at 08:56

overmind1001

141
1
2

score 7 · Answer 3 · edited Dec 07 '16 at 22:00

7

@Query("select c from Curso c where c.descripcion like CONCAT(:descripcion,'%')")

List<Curso> findByDescripcionIgnoreCase(@Param("descripcion") String descripcion);

edited Dec 07 '16 at 22:00

miken32

42,008
16
111
154

answered Dec 07 '16 at 21:16

jean

71
1
1

1

Welcome to Stack Overflow! While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. Code-only answers are discouraged. – Ajean Dec 08 '16 at 01:13

score 1 · Answer 4 · answered Apr 29 '21 at 08:39

With JPA only, without Spring Data Jpa, you must define named query without % and then include the pattern itself with % as a part of the value, as described by @esej:

  @NamedQuery(
    name = ParametersQueryOnEntityEntity.Queries.BY_LIKE,
    query = "SELECT e FROM SomeEntity e WHERE name LIKE :name")

With Spring's @Query you can define the query itself with % included and pass only a pattern value in the method:

  @Query("SELECT e FROM SomeEntity e WHERE name LIKE %:name%")
  List<SomeEntity> byLike(@Param(PARAM_NAME) String name);

score 0 · Answer 5 · answered Oct 17 '20 at 14:45

0

If you are using positional parameters, you can use this way

@Query("select p from ABC p where p.name like CONCAT('%',?1,'%') ")

answered Oct 17 '20 at 14:45

LMK IND

472
1
5
19

Named Query with like in where clause

5 Answers5

Linked