How to pass variable number of parameters to Spring Data/Hibernate/JPQL query

Question

I need to pass in a variable number of parameters to a spring/JPA repo to mimick something like this.

select * from myTable 
where name like '%SOME_VALUE%'
or name like '%SOME_OTHER_VALUE%'
or name like '%SOME_OTHER_VALUE2%'
or an unknown number of other values

So far I haven't been able to determine what the correct way to to do this is. I'm using Spring 4.3.7, Hibernate 5.2.9, and Spring Data 1.11.1. I've googled around and it doesn't appear that there's way to do this with a normal CRUD repo, but so far I haven't found any examples that look like what I need. I think CriteriaBuilder is what I am supposed to use, but that seems to have fallen out of favor, so I'm not sure what the correct way to do this is.

Wouldn't it be just add a List<YourTableEntity> find(String param1, String param2....) method with an annotation like @Query("SELECT y FROM YourTableEntity y WHERE y.name like '%:param1%' or like y.name like '%:param2%' .... be sufficient? — Jorge Campos
– Jorge Campos, Commented Apr 4, 2017 at 20:37
If the number of parameters are unknown I would use Specification<> — Jorge Campos
– Jorge Campos, Commented Apr 4, 2017 at 20:43
You can just use JPQL like SELECT * FROM Table t WHERE t.col1 LIKE %?1% OR t.col2 LIKE %?2% OR t.col3 LIKE %?3% — PRATHAP S
– PRATHAP S, Commented Apr 5, 2017 at 7:23

Zipper · Accepted Answer · 2017-04-05 15:24:40Z

So I followed @Jorge Campos advice and used specification. My code looks like this now:

    public Stream<Product> findProductsContainingDesc(Collection<String> withDesc) {
        Specifications<Product> specifications = null;
        for (String s : withDesc) {
            if(specifications == null){
                specifications = where(hasDescriptionLike(s));
            }else{
                specifications = specifications.or(hasDescriptionLike(s));
            }
        }
        return internalProductRepository.findAll(specifications).stream();
    }

    public static Specification<Product> hasDescriptionLike(String desc) {
        return (root, query, builder) -> builder.like(root.get("description"), "%" + desc + "%");
    }

And my repo definition is this.

interface InternalProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor

Community · Accepted Answer · 2017-05-23 11:54:18Z

0

Perhaps you are looking for something like this?:

@Query("select e from Entity e "
      +"where (:field1 = '' or e.field1 like '%:field1%') "
      +"and (:field2 = '' or e.field2 like '%:field2%') "
      //...
      +"and (:fieldN = '' or e.fieldN like '%:fieldN%')"
Page<Entity> advancedSearch(@Param("field1") String field1,
                           @Param("field2") String field2,
                           //...
                           @Param("fieldN") String fieldN,
                           Pageable page);

Source.

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Apr 5, 2017 at 11:08

Cepr0

31.1k9 gold badges82 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to pass variable number of parameters to Spring Data/Hibernate/JPQL query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related