How to find multiple like in JpaRepository Spring

Question

i would like to do like query in spring using JpaRepository. i cant find the way.

If in native query i want something like

select * from staff where ( 
name like '%param1%' OR
address like '%param1%' OR
staffNo like '%param1%')
order by name limit 10 offset 0

And i already succeed in convert it into JpaRepository for limit and offset except for 'like' function. My current code:

List<Staff> findByIdNotNull(Pageable pageable);

How to insert 'multiple like' in JpaRepo, i try to use containing but it doesot work even for one column

List<Staff> findByNameContaining(String name, Pageable pageable);

this is my question too but i want the input be a list. how to do that? — Amir, Oct 06 '22 at 13:39

score 2 · Accepted Answer · answered Dec 13 '17 at 04:58

2

You are almost there. Use multiple or queries like:

List<Staff> findByNameContainingOrAddressContainingOrStaffNoContaining(
  String name, String address, int staffNo, Pageable pageable);

This way, you can OR more than one conditions for like queries. Make sure you have entity attributes named as name, address and staffNo.

answered Dec 13 '17 at 04:58

Shubham A.

2,446
4
36
68

1

thx a lot..it work but my method name so long..ahaha..List findByNameContainingIgnoreCaseOrCodeContainingIgnoreCaseOrStateContainingIgnoreCaseOrCityIgnoreCaseOrPhoneNoContainingIgnoreCase(String name, String code, String state, String city, String phoneNo, Pageable pageable) – FlyingTurtle Dec 13 '17 at 06:17
@FlyingTurtle Yes, that is always an issue with using JPA queries. I always go for `Spring JdbcTemplate` as it gives me much more control over how my queries are written but increases boilerplate a little. Well, this is just a matter of choice. – Shubham A. Dec 13 '17 at 13:26

score 1 · Answer 2 · answered May 23 '18 at 08:32

You can do it with entity manager

Query query = em.createQuery("select s from staff s where ( 
s.name like :param1 OR
s.address like :param2 OR
s.staffNo like :param3 )
order by name limit 10 of`enter code here`fset 0");
                query.setParameter("param1","%"+param1+"%");
                query.setParameter("param2","%"+param2+"%");
                 query.setParameter("param3","%"+param3+"%");

                 List<staff> l=query.getResultList();

give an alias to the table. set the parameters with setParameter(name,value) definition. get the resultList with a staff list.

score 0 · Answer 3 · answered Jan 04 '22 at 18:25

You can use the @Query annotation

@Query("select * from staff where "
        + " ( name like %:param1% or "
        + "address like %:param2% or "
        + "staffNo like %:param3% ) "
        + "order by name limit 10 offset 0")
Optional<List<Staff>> findByNameContaining(@Param("param1") String param1,
                                                      @Param("param2") String param2,
                                                      @Param("param3") String param3);

How to find multiple like in JpaRepository Spring

3 Answers3

Linked