Convert sql to JPA with a in-clause and a list of like

Question

I have a sql statement as below and want to convert it to JPA

SELECT * FROM employee
where user_id in ('id1', 'id2', 'id3')
AND (
    first_name like '%name1%' OR last_name like '%name1%'
    OR
    first_name like '%name2%' OR last_name like '%name2%'
    )
ORDER BY user_id ASC;

Input parameter

userIdList - a list of string to exact match user_id of size [1,100]

nameList - a list of string to like-wise match first_name or last_name of size [1,100] in character [A-Za-z0-9-_ ]

example for name list: ['Joe', 'Peter', 'White', 'X Y']

I have no idea about how to handle the List of like part and the closest I have is without the list of like

@Modifying
@Query(value = "select * from employee where user_id IN :ids ORDER BY user_id",
            nativeQuery = true)
List<UserGroupTable> findAllByUserIdList(@Param("ids") List<String> userIdList);

In clause reference: %Like% Query in spring JpaRepository

score 1 · Accepted Answer · answered Jan 12 '22 at 11:57

A regex query could be one way to achieve this, as described here: Searching multiple colums with a Contains query with a list of Items in Spring-Data JPA

Sample for MariaDB/MySQL:

@Query(nativeQuery = true, value = "SELECT * FROM my_entity WHERE "
      + "first_name REGEXP CONCAT_WS('|', :nameslist) " + "OR "
      +  "last_name REGEXP CONCAT_WS('|', :nameslist) ")
    List<MyEntity> findByNames( // (method name does not matter)
                     @Param("nameslist") Collection<String> nameslist);

(CONCAT_WS joins the provided list, e.g. ('val1', 'val2', 'val3') to the regex 'val1|val2|val3', which would match e.g. 'completeval2xxx'.)

Note that the exact REGEXP / CONCAT_WS functions depend on the used DB.

score 0 · Answer 2 · answered Jan 12 '22 at 04:01

0

Assuming the intention is for name1 and name2 to also be parameters, you only need to finish out your JPA query:

select *
from employee
where user_id IN :ids and (
      first_name like :name1 or last_name like :name1 or
      first_name like :name2 or last_name like :name2)
order by user_id

Your updated Java method:

@Modifying
@Query(value = "select * from employee where user_id IN :ids and (first_name like :name1 or last_name like :name1 or first_name like :name2 or last_name like :name2) order by user_id",
    nativeQuery = true)
List<UserGroupTable> findAllByUserIdList(@Param("ids") List<String> userIdList, @Param("name1") String name1, @Param("name2") String name2);

Note carefully here that you bind to :name1 and :name2 the actual wildcard expression. E.g. for a name of John Doe, you would bind %John% and %Doe% to the two placeholders.

answered Jan 12 '22 at 04:01

Tim Biegeleisen

502,043
27
286
360

name1 and name2 are list of name to match, not exactly 2. Let me update my question to make it more clear – hk6279 Jan 12 '22 at 04:05
@hk6279 ... you can't bind a list to a SQL `LIKE` expression. – Tim Biegeleisen Jan 12 '22 at 04:05
if it is impossible to do it by JPA, then I could do a in clause and handle the like clause by java code. Just want to check if this is possible before I move to java logic. – hk6279 Jan 12 '22 at 04:16
Tough one, because `IN` cannot be used with `LIKE`. – Tim Biegeleisen Jan 12 '22 at 04:27

Convert sql to JPA with a in-clause and a list of like

2 Answers2