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

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

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.

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

Related

Recent Posts