Custom Search Predicates with SpringBot and QueryDSL

Assumed Knowledge

Given that this article is an extension of Custom Search Queries with SpringBot it is expected that you have a good understanding of content that is covered within it.

Model

The model we will be using for this article is the Fishnatics models. Specifically the Fish entity and search related to the Fish entity.

Background

As covered in the SpringBot Technology List: Server-side article, SpringBot makes use of a library called QueryDSL which exists as a layer on top of Hibernate JPA to simplify the creation of complex queries.

QueryDSL allows us to create queries which remain database agnostic and assist in protecting against SQL injection attacks by not exposing the database connection directly. It exists as the binding logic between the service layer and the data layer of our N-tier SpringBot Architecture.

A starting point for the official documentation for QueryDSL is the QueryDSL Reference Guide. Additionally, there also exists Javadocs which can be used for more specific guidance.

QEntities

QEntities are generated classes which become available post build ¹. They are built using the QueryDSL library as part of the compilation step. A QEntity will available for each class defined within your project with the @Entity annotation.

The QEntities are named as Q<EntityName>Entity with the entity name matching the name of the upper camel case form of the name defined in the entity diagram.

For example,

These classes are used exclusively by QueryDSL.

Important Classes and Methods

The primary class we will be working with in this lesson is our entity service class; for all examples we will be making use of the FishService.java file found at serverside/src/main/java/com/springbot/<projectName>/services/FishService.java

FishService

Of the methods made available by the FishService we will be looking at processCondition.

This method is responsible for building a search predicate for each condition which is sent via the client-side.

Looking at this method,

/**
 * Create the predicate for the querydsl based on one condition
 *
 * @param condition Single condition used in the query
 * @return querydsl predicate
 */
protected Predicate processCondition(Where condition) {
    // % protected region % [Add any additional logic for processCondition before the main logic here] off begin
    // % protected region % [Add any additional logic for processCondition before the main logic here] end

    Predicate predicate = null;

    QFishEntity entity = QFishEntity.fishEntity;

    switch (condition.getPath()) {
        case "created":
            // % protected region % [Add any additional logic before the query parameters of created here] off begin
            // % protected region % [Add any additional logic before the query parameters of created here] end

            predicate = QuerydslUtils.getDefaultPredicate(entity.created, condition.getOperation(), condition.getValue());

            // % protected region % [Add any additional logic after the query parameters of created here] off begin
            // % protected region % [Add any additional logic after the query parameters of created here] end

            break;
        case "modified":
            // % protected region % [Add any additional logic before the query parameters of modified here] off begin
            // % protected region % [Add any additional logic before the query parameters of modified here] end

            predicate = QuerydslUtils.getDefaultPredicate(entity.modified, condition.getOperation(), condition.getValue());

            // % protected region % [Add any additional logic after the query parameters of modified here] off begin
            // % protected region % [Add any additional logic after the query parameters of modified here] end

            break;
        case "alive":
            // % protected region % [Add any additional logic before the query parameters of entity properties here] off begin
            // % protected region % [Add any additional logic before the query parameters of entity properties here] end

            predicate = QuerydslUtils.getDefaultPredicate(entity.alive, condition.getOperation(), condition.getValue());

            // % protected region % [Add any additional logic after the query parameters of entity properties here] off begin
            // % protected region % [Add any additional logic after the query parameters of entity properties here] end

            break;
        // % protected region % [Add any additional cases for the custom query parameters here] off begin
        // % protected region % [Add any additional cases for the custom query parameters here] end
    }

    // % protected region % [Add any additional logic for processCondition after the main logic here] off begin
    // % protected region % [Add any additional logic for processCondition after the main logic here] end

    return predicate;
}

We can see any attribute we have marked as searchable has a default predicate already defined.

QuerydslUtils

A collection of utility methods have been developed to assist in the creation of basic predicates as utilised in the FishService. These are adequate for basic searching but do not cover more complex use cases.

This class is available in the com.springbot.<projectName.services.utils package.

These cover the most common operations, but protected regions exists in the necessary places to add the implementation for any missing operations. For details please review this class.

Adding a Custom Predicate

Custom predicates can be added within the protected region labelled Add any additional cases for the custom query parameters here within the FishService.java file found at serverside/src/main/java/com/springbot/<projectName>/services/FishService.java. For the next sections we will be making use of this protected region to create some custom filters.

For the purposes of this exercise, two new search fields have been added, for details on where these are added, please revisit Custom Search Queries with SpringBot.

static searchFields: string[] = [
    'alive',
    // % protected region % [Add any additional searchable field names here] on begin
    'speciesName',
    'tankNameOrWidth'
    // % protected region % [Add any additional searchable field names here] end
];

Searching for a Fish Based on It’s Species Name

For this task we will be implementing the speciesName search field logic.

1. Add a new case inside of our protected region noted above, make it match on speciesName.

    // % protected region % [Add any additional cases for the custom query parameters here] on begin
    case "speciesName"::
        break;
    // % protected region % [Add any additional cases for the custom query parameters here] end
   

2. Define the instance of our QSpeciesEntity that we wish to use.

    // % protected region % [Add any additional cases for the custom query parameters here] on begin
    case "speciesName":
        var speciesEntity = QSpeciesEntity.speciesEntity;
        break;
    // % protected region % [Add any additional cases for the custom query parameters here] end
   

3. Now we need to build our query as a predicate. Given the nature of QueryDSL as a query language, the same principles that we may apply to an SQL statement apply here.

   Given we wish to find all Fish which have a linked Species with a given name we may write the SQL as follows:

    select fe.id
    from fish_entity fe
             left join species_entity se on fe.species_id = se.id
    where se.name like '{condition}'
   

   Given how this query is structured, we can then re-write it using QueryDSL to achieve the following:

    predicate = entity.id.in(
            JPAExpressions.select(entity.id)
                .from(entity)
                .leftJoin(entity.species, speciesEntity)
                .where(speciesEntity.name.containsIgnoreCase(condition.getValue())
             )
    );
   

   This achieves the same result with one small exception, our search here is case-insensitive.

4. Copy the resulting QueryDSL logic into our protected region to achieve the following:

    // % protected region % [Add any additional cases for the custom query parameters here] on begin
    case "speciesName":
        var speciesEntity = QSpeciesEntity.speciesEntity;
   
        predicate = entity.id.in(
                JPAExpressions.select(entity.id)
                    .from(entity)
                    .leftJoin(entity.species, speciesEntity)
                    .where(speciesEntity.name.containsIgnoreCase(condition.getValue())
                )
        );
        break;
    // % protected region % [Add any additional cases for the custom query parameters here] end
   

Combining Where Conditions to Find Fish that Have Matching Tank by Width or Name

In this example we will skip the first couple of steps as they are identical to the above. What we wish to find is all Fish which belong in a Tank either by name or width.

Additionally, for this exercise we will treat our TankEntity’s Width attribute as a String to simplify the code examples.

1. Defining the query. Altering the query from the previous example, we get the following:

    select fe.id
    from fish_entity fe
             left join tank_entity te on fe.tank_id = te.id
    where te.name like '{name condition}' or te.width = {width condition}
   

2. Translating this to QueryDSL we get the following result:

    predicate = entity.id.in(
            JPAExpressions.select(entity.id)
            .from(entity)
            .leftJoin(entity.tank, tankEntity)
            .where(
                ExpressionUtils.anyOf(
                        tankEntity.name.equalsIgnoreCase(condition.getValue()),
                        tankEntity.width.eq(Double.valueOf(condition.getValue()))
                )
            )
    );
   

   The only new addition here is the use of the ExpressionUtils which provides a variety of help methods. More detail can be found in the API docs.

   In this case, the anyOf method allows us to combine a collection of predicates into a single query.

3. Adding this to our protected region we get the following:

    case "tankNameOrWidth":
        var tankEntity = QTankEntity.tankEntity;
   
        predicate = entity.id.in(
                JPAExpressions.select(entity.id)
                    .from(entity)
                    .leftJoin(entity.tank, tankEntity)
                    .where(
                        ExpressionUtils.anyOf(
                                tankEntity.name.equalsIgnoreCase(condition.getValue()),
                                tankEntity.width.eq(Double.valueOf(condition.getValue()))
                        )
                    )
        );
        break;
   

Concatenation

QueryDSL can be used to create more advanced predicate expressions as well, for example if you wanted to search across the concatenation of two columns you would create a predicate expression similar to the following:

predicate = entity.id.in(
        JPAExpressions.select(entity.id)
                .from(entity)
                .where(
                        entity.name.concat(" ")
                                .concat(entity.name)
                                .containsIgnoreCase(condition.getValue())
                )
);

This example will allow for searching for the combined value of the name and name separated by a single space. This is similar to the SQL concat function.