1

In my Spring Boot app, I created a custom filtering using JPA Specification as mentioned on Searching And Filtering Using JPA Specification - Spring Boot. However, I need to join multiple tables and build a WHERE clause for my specific search via @Query.

I checked https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications page, but could not build a proper structure.

So, how can create a dynamic WHERE clause for my query?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Jack
  • 1
  • 21
  • 118
  • 236

2 Answers2

3

You can use Specification. To create:

Specification<Entity> spec = (root, query, cb) -> {
    List<Predicate> predicates = new ArrayList<>();

    // Add conditions to the predicates list

    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
};

The predicates list is used to hold conditions for the WHERE. You can add conditions to this list using the cb (CriteriaBuilder) object and the root and query parameters. These parameters provide access to the entity and the query being constructed.

You can then use the Specification object in a @Query annotation on your repository method to apply the dynamic WHERE clause to the query.

Repository example:

@Repository
public interface EntityRepository extends JpaRepository<Entity, Long>, JpaSpecificationExecutor<Entity> {

    // Other repository methods

    List<Entity> findAll(Specification<Entity> spec, Pageable pageable);
}

The above repository extends the JpaSpecificationExecutor to allow working with the JPA criteria API. The findByFields method also takes a Specification object as an argument. This Specification dynamically constructs the WHERE clause for the query.

So running the query:

List<Entity> entities = entityRepository.finAll(spec, pageable);
Funsaized
  • 1,972
  • 4
  • 21
  • 41
  • Thanks a lot for this useful example, voted up... I integrated a specification example on https://blog.piinalpin.com/2022/04/searching-and-filtering-using-jpa-specification/ I have the following issues, could you clarify me pls? >>> – Jack Dec 05 '22 at 07:27
  • **1.** There are 3 entities joined via Hibernate using proper relationship. I have Recipe, RecipeIngredient and Ingredient entities. I use Recipe as type for my specification. But I need one filter on Ingredient field. In this case, should I filter that field by passing value? Or can I also filter it using the same filter for Recipe? – Jack Dec 05 '22 at 07:30
  • **2.** I have an ENUM field that I need to filter. As you see Enumeration of Field Type section on that page, I use STRING as type in my filter definition. But I get error and as far as I see from my search, there is not a workaround. So, do you have any idea about that problem? Maybe you had experience for filtering enum field. – Jack Dec 05 '22 at 07:33
  • By the way, when I use specs and query parameters ta the same time I get this error --> **"Using named parameters for method public abstract Page RecipeRepository.findByFields(Specification, Pageable) but parameter 'Optional[spec]' not found in annotated query 'SELECT ..."**. My method signature is `Page findByFields(@Param("text") String text, @Nullable Specification spec, @Nullable Pageable pageable);`. Any idea? – Jack Dec 05 '22 at 07:56
  • I think I cannot mix @Query and Specification, right? https://stackoverflow.com/questions/26379522/can-i-combine-a-query-definition-with-a-specification-in-a-spring-data-jpa-repo/26381869#26381869 – Jack Dec 05 '22 at 08:15
  • If so, as I need to pass these filter parameters (4 parameter) to my @Query, then how can I build my WHERE clause dynamically? I cannot use spec, but I am not sure if there is an aşternative for creating WHERE clause in my Query. Any idea? – Jack Dec 05 '22 at 08:16
  • Eh been a minute, so not 100% on the mixing of specification and @Query; however, I believe you may be correct, good call! In that case the same behavior can be achieved with just Specification and Pageable objects, per your recent comments. I have edited the response to match. – Funsaized Dec 05 '22 at 16:00
  • Thanks for reply. Actually I created a custom filter using Specifications before asking the question. But, although it works well in my Recipe entity, I cannot make it filter related entities e.g. Ingredient. I use Hibernate and actually I implemented all the necessary relationship between entities properly. When I retrieve a Recipe data, I can receive Ingredient data belonging to this Recipe, but I am not sure if I can filter data on Ingredient as well. – Jack Dec 05 '22 at 17:35
  • I have Recipe, RecipeIngredient (bridge table) and Ingredient tables and they have necessary relationship implementations – Jack Dec 05 '22 at 17:35
1

It's something like this:

       Specification<BugData> bugDataSpecification = new Specification<BugData>() {
            @Override
            public Predicate toPredicate(Root<BugData> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Object> bugName = root.get("bugName");
                Path<Object> bugType = root.get("bugType");
                Path<Object> bugLevel = root.get("bugLevel");
                List<Predicate> predicateListAnd = new ArrayList<>();
                List<Predicate> predicateListOr = new ArrayList<>();

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugLevel())) {
                    Predicate pLevel = cb.equal(bugLevel, bugRequestParam.getBugLevel()); // ==
                    predicateListAnd.add(pLevel);
                }
                for (int i = 0; i < bugRequestParam.getBugTypeList().size(); i++) {

                    Predicate p1 = cb.equal(bugType, bugRequestParam.getBugTypeList().get(i));
                    predicateListOr.add(p1);
                }

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugName())) {
                    Expression<Integer> findStr = cb.locate(bugName.as(String.class), bugRequestParam.getBugName());  //LOCATE
                    Predicate pName = cb.greaterThan(findStr, 0);   // >
                    predicateListAnd.add(pName);
                }
                Predicate resultAnd[] = predicateListAnd.toArray(new Predicate[predicateListAnd.size()]);
                Predicate resultOr[] = predicateListOr.toArray(new Predicate[predicateListOr.size()]);
                Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));
                return end;
            }
        };

The whole part of this code:

    @GetMapping(value = "specification")
    public List<BugData> whereTiaojian() {
        BugRequestParam bugRequestParam = new BugRequestParam();
        bugRequestParam.setBugLevel("mid");
        bugRequestParam.setBugName("CVE-2019-8331");
        bugRequestParam.setLimit(100);
        bugRequestParam.setPage(0);
        List<String> bugTypeList = new ArrayList<>(4);
        bugTypeList.add("CWE-79");
        bugTypeList.add("CWE-502");
        bugTypeList.add("CWE-284");
        bugRequestParam.setBugTypeList(bugTypeList);

        Pageable pageable = PageRequest.of(bugRequestParam.getPage(), bugRequestParam.getLimit());

        Specification<BugData> bugDataSpecification = new Specification<BugData>() {
            @Override
            public Predicate toPredicate(Root<BugData> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Object> bugName = root.get("bugName");
                Path<Object> bugType = root.get("bugType");
                Path<Object> bugLevel = root.get("bugLevel");
                List<Predicate> predicateListAnd = new ArrayList<>();
                List<Predicate> predicateListOr = new ArrayList<>();

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugLevel())) {
                    Predicate pLevel = cb.equal(bugLevel, bugRequestParam.getBugLevel());
                    predicateListAnd.add(pLevel);
                }
                for (int i = 0; i < bugRequestParam.getBugTypeList().size(); i++) {

                    Predicate p1 = cb.equal(bugType, bugRequestParam.getBugTypeList().get(i));
                    predicateListOr.add(p1);
                }

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugName())) {
                    Expression<Integer> findStr = cb.locate(bugName.as(String.class), bugRequestParam.getBugName());
                    Predicate pName = cb.greaterThan(findStr, 0);
                    predicateListAnd.add(pName);
                }
                Predicate resultAnd[] = predicateListAnd.toArray(new Predicate[predicateListAnd.size()]);
                Predicate resultOr[] = predicateListOr.toArray(new Predicate[predicateListOr.size()]);
                Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));
                return end;
            }
        };
        Page<BugData> bugDataPage = bugDataVersionFiveDao.findAll(bugDataSpecification, pageable);
        // This findAll method is the most important part of this all;
        return bugDataPage.getContent();

    }
OGtwelve
  • 46
  • 4
  • Thanks a lot for your help. Actually I applied a custom filter by following https://blog.piinalpin.com/2022/04/searching-and-filtering-using-jpa-specification/ However, I cannot pass extra filter parameters besides specs to my custom repository method. My method is `Page findByFields(@Param("text") String text, @Nullable Specification spec, @Nullable Pageable pageable);`. Any idea? – Jack Dec 05 '22 at 08:05
  • It's all the same , at my last few lines of codes shows how to use my `findAll(Specification,Pageble)` method; Just add ur text parameter at `findByFields(text,spec,pageble)` then ur all set; The WHERE use is all about the Specification; – OGtwelve Dec 05 '22 at 10:55
  • Thanks for reply. Actually I created a custom filter using Specifications before asking the question. But, although it works well in my Recipe entity, I cannot make it filter related entities e.g. Ingredient. I use Hibernate and actually I implemented all the necessary relationship between entities properly. When I retrieve a Recipe data, I can receive Ingredient data belonging to this Recipe, but I am not sure if I can filter data on Ingredient as well. – Jack Dec 05 '22 at 17:32
  • I have Recipe, RecipeIngredient (bridge table) and Ingredient tables and they have necessary relationship implementations. – Jack Dec 05 '22 at 17:33
  • Is it possible for u to attch the code somewhere so I can understand; My english wasn't that good to understand from ur descriptions; – OGtwelve Dec 06 '22 at 07:06
  • Thanks a lot for your helps, voted up. I added the related entities to [this](https://stackoverflow.com/questions/74685356/dynamic-query-with-query-in-spring-data-jpa/74698090#74698090) page. I have 3 related entities (Recipe, RecipeIngredient and Ingredient) and while retrieving data from Recipe using `recipeRepository.findAll()` method, I need to pass `ingredient.name` as key parameter (please see **Filter by Name and Sort by Release Date ASC** section on [this](https://blog.piinalpin.com/2022/04/searching-and-filtering-using-jpa-specification/) article that I followed). – Jack Dec 06 '22 at 08:31
  • So, you may first have a look at the answer @M.Denium posted on [that](https://stackoverflow.com/questions/74685356/dynamic-query-with-query-in-spring-data-jpa/74698090#74698090) page and share your comments based on this answer. Thanks in advance... – Jack Dec 06 '22 at 08:31
  • This answer below is the right answer for ur question; https://stackoverflow.com/questions/74685356/dynamic-query-with-query-in-spring-data-jpa/74698090#74698090 – OGtwelve Dec 06 '22 at 09:22