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 joimn 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?

CodePudding user response：

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

    @Query("SELECT e FROM Entity e WHERE e.field1 = :value1 AND e.field2 = :value2")
    List<Entity> findByFields(@Param("value1") String value1, @Param("value2") String value2, Specification<Entity> spec);
}

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.findByFields("value1", "value2", spec);

CodePudding user response：

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();

    }