Skip to content
Advertisement

Dynamic search term SQL query with Spring JPA or QueryDSL

I am trying to learn QueryDSL in order to return the results of a single search term from Postgres:

    @GetMapping("/product/contains/{matchingWords}")
    public List<ProductModel> findByTitleContaining(@PathVariable String matchingWords) {

        QProductModel product = QProductModel.productModel;
        JPAQuery<?> query = new JPAQuery<>(em);

        List<ProductModel> products = query.select(product)
                .from(product)
                .where(product.title.toLowerCase()
                        .contains(matchingWords.toLowerCase()))
                .fetch();
        return products;
    }

But I also want to search for any number of search terms, for example, say this is my list of search terms divided by the plus symbol:

   String[] params = matchingWords.split("[+]");

How can I dynamically create contains(params[0]) AND/OR contains(params[1] AND/OR … contains(params[n]) using either QueryDSL or any Java/Spring query framework? I see QueryDSL has a predicate system, but I still don’t understand how to dynamically create a query based on a variable number of parameters searching in the same column.

Advertisement

Answer

I figured it out. It’s a little non-intuitive, but using BooleanBuilder and JPAQuery<?> you can create a dynamic series of boolean predicates, which return a list of matches.

Example:

QProductModel product = QProductModel.productModel;
JPAQuery<?> query = new JPAQuery<>(//entity manager goes here//);

// example list of search parameters separated by +
String[] params = matchingWords.split("[+]");

BooleanBuilder builder = new BooleanBuilder();
for(String param : params) {
    builder.or(product.title.containsIgnoreCase(param));
}

// then you can put together the query like so:
List<ProductModel> products = query.select(product)
    .from(product)
    .where(builder)
    .fetch();

return products;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement