How to make a dynamic Spring (Boot) JPA query with paging?

Question

To oversimplify the problem: we have a class/table Wine (table "wines") which has, among other attributes:

• name: String
• description: String
• origin: Origin

... where Origin is another class (with table "origins") with just region: String and country: String.

What I'm trying to make is a search method in my repository for the RestController to use.

The method declaration in the RestController is like this:

@GetMapping("/search")
public Wine searchProduct(
        @RequestParam Optional<String> searchTerm,
        @RequestParam Optional<Origin> origin) {
    // ???
}

What I'm trying to do now is the following: creating a query for the database where the searchTerm is used if given, same with origin. And it should be pageable. Example:

SELECT * FROM wines JOIN origins ON wines.origin_id = origins.id 
    WHERE (name LIKE $searchTerm OR description LIKE $searchTerm) AND (/*origin check*/)

If no search term is given, the whole "() AND" part should not be in the query. If no Origin is given... you get it.

Things I've tried:

1. (Naively) building a massive query in my repository (implements CrudRepository) like here.

   Page<Wine> findWinesByNameLikeOrDescriptionLikeAndOriginEquals(..., Pageable pageable);

   • Which (apart from being super ugly, especially for more attributes) probably doesn't work because:
     1. It's not defined whether OR or AND are more important (no brackets).
     2. No idea whether I can just shove an "Origins" object in there for it to work.
     3. No idea whether Pageable even works if that's a custom method.

2. Using "Specifications and Querydsl" as suggested by spring.io here.

   • Which I'm just to stupid to understand, especially with those Q classes at the bottom of the post or the _ classes in the beginning. It just seems way too compicated for what it should do.
   • Also there are no pagination options. There is, however, a possible fix like here but I didn't get to it since using those _ and/or Q classes was to much of an overhead to start with.

3. Also this is just an example I found from 2013 which I don't even understand in general but kinda looks like it would be very fitting.

Answer 1

If you find it difficult to understand Specifications, only have those two search parameters and assuming Wine entity has a @ManyToOne to Origin, you can do the following:

    @Query("SELECT w FROM wines w join w.origin o where " +
            "(   :searchTerm is null or " +
            "    w.name like CONCAT('%',:searchTerm ,'%') or " +
            "    w.description like CONCAT('%', :searchTerm ,'%')" +
            ") " +
            "AND (:origin is null or  o = :origin)")
    Page<Wine> searchWines(String searchTerm, Origin origin, Pageable p);

And when you call it,

    wineRepository.searchWines(searchTerm.orElse(null), origin.orElse(null));

Answer 2

So I actually got a solution working! For curious people, here's how I did it:

ProductController.java

@GetMapping("search")
public Page<Wine> searchProducts(
        @RequestParam(name = "text", required = false) String searchTerm,
        @RequestParam(required = false) Origin origin,
        @RequestParam(required = false) Integer page) {
    // generate PageRequest based on Integer page if given:
    Pageable pageRequest = PageRequest.of(page != null ? page : 0, 10);
    if(Objects.isNull(searchTerm) && Objects.isNull(origin)) {
        return wineService.findAll(pageRequest);
    }
    return wineService.searchWines(
            searchTerm,
            origin,
            pageRequest
    );
}

WineService.java

public Page<Wine> searchWines(String searchTerm, Origin origin, Pageable pageable) {
    List<Specification<Wine>> specifications = new LinkedList<>();
    if (searchTerm != null) {
        specifications.add(ProductSpecification.hasSearchStringInNameOrDescription(searchTerm));
    }
    if (origin != null) {
        specifications.add(ProductSpecification.hasOrigin(origin));
    }
    if (specifications.isEmpty()) {
        return wineRepository.findAll(pageable);
    } else {
        Specification<Wine> query = Specification.where(specifications.remove(0));
        for (Specification<Wine> wineSpecification : specifications) {
            query = query.and(wineSpecification);
        }
        return wineRepository.findAll(query, pageable);
    }
}

as an example, here's the specification for the name/description string:

ProductSpecification.java

public static Specification<Wine> hasSearchStringInNameOrDescription(String input) {
    final String searchTerm = input.toLowerCase();
    return (root, criteriaQuery, criteriaBuilder) -> {
        log.info("SearchTerm: " + searchTerm);
        Predicate pName = criteriaBuilder.like(
                criteriaBuilder.lower(root.get(Wine_.NAME)),
                "%" + searchTerm + "%"
        );
        Predicate pDescription = criteriaBuilder.like(
                criteriaBuilder.lower(root.get(Wine_.DESCRIPTION)),
                "%" + searchTerm + "%"
        );
        return criteriaBuilder.or(pName, pDescription);
    };
}

The generation of those Wine_ classes is automatic with a Maven plugin. See this. Additionally, for the conversion of request body pieces to complex objects like Origin, you need to include a converter class like here.