How to specify a JPA named parameter surrounded by wildcards?

Question

How would I specify a JPA query like:

Query q = 
  em.createQuery(
    "SELECT x FROM org.SomeTable x WHERE x.someString LIKE '%:someSymbol%'"
  );

followed by:

q.setParameter("someSymbol", "someSubstring");

and not triggering a

org.hibernate.QueryParameterException: could not locate named parameter [id]

Much appreciated!

Have you looked at http://stackoverflow.com/questions/3144235/jpa-hibernate-native-queries-do-not-recognize-parameters — Andriy Drozdyuk, May 12 '11 at 19:26
I'm trying to avoid doing a native query, rather I'm hoping that it is somehow possible in JPA to use named parameters with 'LIKE' and wildcards. — Reuben Peter-Paul, May 12 '11 at 19:30

score 93 · Accepted Answer · answered May 12 '11 at 19:28

93

How about

Query q = 
  em.createQuery(
    "SELECT x FROM org.SomeTable x WHERE x.someString LIKE :someSymbol"
);
q.setParameter("someSymbol", "%someSubstring%");

I'm pretty sure I once solved your problem like that.

answered May 12 '11 at 19:28

musiKk

14,751
4
55
82

1

Seems like when I go from raw SQL to JPA, I "think backwards" or something, thanks for straightening me out @musiKk. – fusion27 Aug 26 '14 at 19:45
Seems to work also well with positional parameters such as in the following: `@NamedQuery(name = User.BY_USERNAME, query = "SELECT e from User AS e where e.USERNAME like CONCAT('%', ?1, '%')")` – Johannes Tuikkala Feb 24 '22 at 13:17

score 66 · Answer 2 · answered Jan 04 '15 at 14:15

66

For reference, you could also use CONCAT:

like CONCAT('%', :someSymbol, '%')

answered Jan 04 '15 at 14:15

xtian

2,908
2
30
43

1

Thanks for sharing this. It helps. +1 – OO7 Feb 17 '16 at 14:38
3

This should be an accepted solution. It works also in frameworks like sprint-data where query can be declared using @Query annotation. – Łukasz Wachowicz Aug 13 '19 at 12:28
3

Or `CONCAT('%', CONCAT(:someSymbol, '%'))` for Oracle SQL – ChickenFeet Aug 26 '19 at 07:47

score 4 · Answer 3 · answered Jun 30 '16 at 09:11

4

query.setParameter(someSymbol, "%" + someSymbol+ "%");

answered Jun 30 '16 at 09:11

Chinmoy

1,391
13
14

How to specify a JPA named parameter surrounded by wildcards?

3 Answers3

Linked

Related