Home > Blockchain >  how to pass value to LIKE statement in jpa native query
how to pass value to LIKE statement in jpa native query

Time:09-17

Here, I'm trying to pass string parameter value to native query which has like command in JPA Repository

@Query(value = "select u.* from users where u.name like '%?1%'",nativeQuery = true)
List<User> findAllByNameContaining(String searchString);

When I tried to debug with show sql flag. I see, below result in console

Hibernate: 
/* dynamic native SQL query */ select
    u.* 
from
    users u 
join
    customer c 
        on u.id = c.userId 
where
    c.trainerId = ? 
    and u.name like '%?2%'

I'm assing its adding extra single quote (i.e) like '%'searchvalue'%'

Note : Query is more complex than mentioned here. so, I don't want solutions like findAllByNameContaining or findByNameIgnoreCaseContaining etc...

CodePudding user response:

No use nativeQuery = true with jpql

Use Like keyword :

List<User> findByNameLike(String searchString);

Also you can try this

@Query(value = "select u.* from users where u.name like %:searchString%")

CodePudding user response:

When you put '%?2%' in the query that doesn't expand to a parameter that gets concatenated with % on both sides. You can't put parameters inside a string literal.

  • Related