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: stackoverflow.com/questions/52497673/… — zappee
– zappee, Commented Feb 8, 2021 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<String> values, and the matching query for the sql i have placed in the question. Though LIKE and IN are not same so — Satscreate
– Satscreate, Commented Feb 8, 2021 at 10:50
Maybe this one, OR paragraph 9 of this guide can help. Please check them. — zappee
– zappee, Commented Feb 8, 2021 at 11:09

Jens Schauder · Accepted Answer · 2021-02-08 13:04:26Z

2

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.

answered Feb 8, 2021 at 13:04

Jens Schauder

82.7k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajit Soman · Accepted Answer · 2021-02-08 11:39:01Z

0

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();
}

answered Feb 8, 2021 at 11:39

Ajit Soman

4,0693 gold badges26 silver badges43 bronze badges

1 Comment

Jens Schauder Over a year ago

One should be extremely careful when assembling query Strings because they a susceptible of SQL injection attacks..

Collectives™ on Stack Overflow

LIKE Query in JPA using List of String - Spring

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related