JPQL select based on non null dto values-CodePudding

Hi everyone I am using spring JPA and I am trying to filter my query result based on nonnull DTO field values.

The reason is every time I have different DTO fields and I should get books based on just the existing fields or in other words the nonnull fields.

My DB table is books and I have the following DTO for it

public class BookDTO {
private String name;
private String title;
private String isbn;
private String author;
private int pages;
private String size;
}

I searched over the web but I didn't find a solution for this kind of issue anyway to achieve this using Spring JPQL

CodePudding user response：

You can do this using JpaSpecificationExecutor (scroll down to section 5

This way you can programmatically define what fields are you going to add to your where clause, like this .:

(Specification<Book>) (book, cq, cb) ->
  cb.and(
    // You can dynamically construct that array of predicates based on which fields are set in the form
    cb.like(book.get("author"), "%"   author   "%"),
    cb.like(book.get("title"), "%"   title   "%")
  )

CodePudding user response：

one alternative is to use Spring Data JPA Specification.

With that you can create your criteria search for the fields that are present.

public Predicate toPredicate
      (Root<User> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
 
        if (criteria.getOperation().equalsIgnoreCase(">")) {
            return builder.greaterThanOrEqualTo(
              root.<String> get(criteria.getKey()), criteria.getValue().toString());
        } 
        else if (criteria.getOperation().equalsIgnoreCase("<")) {
            return builder.lessThanOrEqualTo(
              root.<String> get(criteria.getKey()), criteria.getValue().toString());
        } 
        else if (criteria.getOperation().equalsIgnoreCase(":")) {
            if (root.get(criteria.getKey()).getJavaType() == String.class) {
                return builder.like(
                  root.<String>get(criteria.getKey()), "%"   criteria.getValue()   "%");
            } else {
                return builder.equal(root.get(criteria.getKey()), criteria.getValue());
            }
        }
        return null;
    }

reference: https://www.baeldung.com/rest-api-search-language-spring-data-specifications