LIKE Query in JPA using List of String - Spring

Question

Have used containing method in repository for %LIKE% SQL equal in JPA, as below,

Page<Obj> findByNameContaining(String name, Pageable pageable);

which will work like below,

select * from Obj where name like '%John%';

But how do i pass the List<String> names which would query like below using JPA,

select * from Obj where name like '%John%' or name like '%Jiva%' or name like 'etc' .....;

Is there any Spring JPA way for this? i also checked Specification classes too, is that the only way of doing this or am i missing any easy ways or any other dynamic query is recommended?

Maybe this can help: https://stackoverflow.com/questions/52497673/how-to-use-multiple-like-keyword-in-sping-jpa-on-same-column — zappee, Feb 08 '21 at 10:47
@zappee this i have already looked and its for IN clause with SET values. But my requirement is performing %LIKE% operation in same column with list values, and the matching query for the sql i have placed in the question. Though LIKE and IN are not same so — Satscreate, Feb 08 '21 at 10:50
Maybe [this](https://stackoverflow.com/questions/22167730/dynamic-named-query-in-entity-class-using-jpql-example) one, OR [paragraph 9 of this guide](https://www.baeldung.com/spring-data-jpa-query) can help. Please check them. — zappee, Feb 08 '21 at 11:09

score 2 · Answer 1 · answered Feb 08 '21 at 13:04

The most Spring Data JPA way to do this is to use a Specification which adds a like-predicate for every element in the list. You would call it like so

repository.findAll(containingOneOf(listOfNames))

with Specification containingOneOf(List<String> listOfNames) is a method you need to create.

See https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/ for more details about specifications in Spring Data JPA.

score 0 · Answer 2 · answered Feb 08 '21 at 11:39

It is not possible to create a query with multiple like operator inside a repository interface But there is a workaround to do this using EntityManager.

EntityManager is extensively used in SimpleJpaRepository. We can get instance of this class using @PersistenceContext like this:

@PersistenceContext
private EntityManager entityManager;

and call its method .createQuery(QUERY).getResultList() to execute a database call. For pagination , You can check this Pagination using entity manager in spring

To create query you can write a simple logic like this:

String createQuery(List<String> names){
    StringBuilder query = new StringBuilder("select e from Employee e where ");
    int size = names.size();
    for(int i = 0; i < size; i++){
        query.append("e.name like '%").append(names.get(i)).append("%'");
        if(i != size-1){
            query.append(" OR ");
        }
    }
    return query.toString();
}

In the above code , Employee is my entity class for which i am creating a select query with multiple like operator.

Complete code:

List<Employee> getEmployees(List<String> names){
    String query = createQuery(names);
    return entityManager.createQuery(query).getResultList();
}

One should be extremely careful when assembling query Strings because they a susceptible of SQL injection attacks.. — Jens Schauder, Feb 08 '21 at 13:01

LIKE Query in JPA using List of String - Spring

2 Answers2