Spring Data JPA: A Generic Specification Query Language

Image from DataCentric.es

When working on projects with Spring Framework and specifically Spring Data JPA, we encounter different strategies for writing database queries, such as:

• Derived query methods (query creation from method names).
• JPA named queries (in entities).
• Declared Queries — @Query annotation (written native SQL or JPQL queries).

The real question is: Are these practical methods provided by the framework enough to build a dynamic structure for searching and filtering over entities and to avoid boilerplate code?.

In this article, we’ll review a strategy to transform API consumer searches into dynamically built SQL where clauses, applying Spring Predicate and Specification through the already incorporated Criteria API.

Introduction

As our application grows, we come across the requirement to satisfy API consumers’ search and filter necessity over the project entities.

In this scenario, we usually ask ourselves, should we manually write each filter query in repositories? What if the API consumer aspires to combine one or more attributes generating ‘AND-OR’ queries?.

It would be impossible and tedious to consider every attribute filter combination, generating tons of distinct unmaintainable queries in our repositories.

Here is where Criteria API, Predicates, and Specification make their appearance.

Criteria Api, Predicate and Specification

Criteria API offers a programmatic way to create typed queries. The Specification interface can be used to group a set of Predicates with SQL AND/OR operators.

Let’s review some official Spring JPA documentation sections:

JPA 2 introduces a Criteria API that you can use to build queries programmatically. By writing a Criteria, you define the where clause of a query for a domain class. Taking another step back, these criteria can be regarded as a predicate over the entity that is described by the JPA Criteria API constraints.

Specifications can easily be used to build an extensible set of predicates on top of an entity that then can be combined and used with JpaRepository without the need to declare a query (method) for every needed combination.

Spring Documentation Example: Specification for a Customer

Perfect! We can use Criteria API to build Specifications for every entity as a combination of Predicates with a diversity of SQL operations (lessThan, greaterThan, equal, like, etc.).

To succeed in our purpose, we would need to put together these concepts by building a generic approach capable of dynamically creating Entity Specifications based on the API consumer filter desires.

The Generic Specification Query Language

GitHub Repository: https://github.com/msampietro/spring-spec-query-language

Query Language Structure and Requirements

As we’ll be employing a pseudo-language to construct Predicates from string inputs, we are required to respect a specific search format for the search to be correctly interpreted and parsed.

For this intent, I wrote detailed documentation that you can find in the Project Wiki Section.

Code Analysis

I’ll try to keep the analysis as simple as possible.

Let’s get right into it.

Instantiating

SpecificationBuilder<Movie> specificationBuilder = new SpecificationBuilderImpl<>(objectMapper, Movie.class);

The SpecificationBuilder<T> implementation class receives two parameters:

• Object Mapper (Jackson): responsible for converting the input value into the required value type regarding the requested entity attribute. Convenient when defining a singleton Object Mapper to avoid the SpecificationBuilder<T> implementation to create a new instance of it every time.
• Class<T> (Entity return class): mainly utilized to determine if the entity Primary Key is defined as a generic Serializable Id. Consider the following example:

@MappedSuperclass
@Getter
@Setter
public class BaseEntity<I extends Serializable> implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator =    "base_seq_gen")
    @Column(updatable = false, nullable = false)
    private I id;
}
--------------------------------------------------------------------
@Entity
@Table(name = "movie")
@Getter
@Setter
public class Movie extends BaseEntity<Long> {...}

Usage

The SpecificationBuilder<T> contract contains only one method, parseAndBuild, that interprets a valid search string from the query language and returns the corresponding entity Specification.

Optional<Specification<Movie>> specification = specificationBuilder.parseAndBuild(search);

Let’s explain the main steps of the parseAndBuild method:

1. Parsing the Search String.
2. Building Predicates.
3. Returning a Specification as a concatenation of Predicates.

1. Parsing the Search String

private static final List<ParseCommand> PARSE_COMMANDS = new ArrayList<>(Arrays.asList(
        new SimpleParseCommand(),
        new NestedParseCommand(),
        new ReferencedSimpleParseCommand(),
        new ReferencedCompoundParseCommand()
));

The SpecificationBuilder<T> implementation statically instantiates a List of ParseCommands that evaluates each search input with RegEx expressions and builds the corresponding SearchCriteria objects.

@Override
public Optional<Specification<T>> parseAndBuild(String search) {
    if (StringUtils.isBlank(search))
        return Optional.empty();
    var params = new ArrayList<SearchCriteria>();
    var splitOperation = SpecificationUtils.determineSplitOperation(search).orElse(null);
    var isOrPredicate = StringUtils.equals(splitOperation, OR_PREDICATE_FLAG);
    var searchQueries = SpecificationUtils.splitSearchOperations(search, splitOperation);
    for (var parseCommand : PARSE_COMMANDS)
        params.addAll(parseCommand.parse(searchQueries, isOrPredicate));
    return build(params);
}

SearchCriteria is the result of transforming a raw search string into an object containing all the principal search data: the entity key name, the search value, the SQL operation itself (enum called SearchOperation), and the isOrPredicate boolean as the result of the splitOperation variable evaluation.

Next, the Optional<Specification<T>> build(List<SearchCriteria> params) method referenced in the last line of parseAndBuild creates a SpecificationFactory for the requested entity, which shall construct the search Predicates regarding each SearchCriteria by calling the getSpecification method.

public Specification<T> getSpecification(SearchCriteria searchCriteria) {
    BasePredicate<T> predicate = operationMap.get(searchCriteria.getOperation());
    predicate.setSearchCriteria(searchCriteria);
    return predicate;
}

2. Building Predicates

The SpecificationFactory getSpecification method receives a SearchCriteria object as a parameter, and relying on its inner SearchOperation attribute, applies the corresponding Predicate builder class.

Available Predicate Builders:

• EqualityPredicate.java
• NegationPredicate.java
• GreaterThanOrEqualPredicate.java
• LessThanOrEqualPredicate.java
• LikePredicate.java
• ContainsPredicate.java
• StartsWithPredicate
• EndsWithPredicate.java
• CollectionContainsPredicate.java

LikePredicate example:

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    return builder.like(builder.upper(getCriteriaStringExpressionKey(root).as(String.class)), StringUtils.upperCase(getCriteriaObjectValue().toString()));
}

Each builder will automatically determine the corresponding SQL where clause operation from the resolved SearchCriteria and the necessary entity joins.

Joins operations make use of the default Join Type (Inner). We can search attributes of an entity itself or maybe of a related one-to-one, one-to-many, or even many-to-many nested associations.

3. Returning a Specification as a concatenation of Predicates

In the last step, going back to the build method of the first step, we can observe the following code block:

Specification<T> result = specFactory.getSpecification(params.get(0));
for (var i = 1; i < params.size(); i++)
    result = params.get(i).isOrPredicate()
            ? Objects.requireNonNull(Specification.where(result)).or(specFactory.getSpecification(params.get(i)))
            : Objects.requireNonNull(Specification.where(result)).and(specFactory.getSpecification(params.get(i)));
return Optional.of(result);

First of all, the SpecificationFactory<T> is creating the initial Specification<T> object called “result” as the first Predicate encapsulation.

If the search is multiple, the orPredicate variable of the SearchCriteria object is evaluated whether to concatenate the followings Predicates in the list as an AND or as an OR operation in the resulting query where clause.

Finally, the Specification<T> result is returned, ready to be used by the owner entity JPA repository, to filter results.

Considerations

1. You cannot use this approach if you are required to resolve filters like the next one:

(X =1 or X = 2) and (Y > 3).

Firstly, AND in combination with OR Predicates in the same search string is not yet supported. Secondly, the SQL where clause would be resolved by Hibernate as X =1 or X = 2 and Y > 3 which is not the same.

2. A drawback of the above implementation is that we are unable to control attribute types and existence. For instance, a RuntimeException will be thrown if:

• Invalid argument: e.g. A field of type LocalDateTime is evaluated as an Integer value, or a String value is forwarded to a greaterThan operation.
• Field not found: The specified field in the search string is not present in the entity.

The RuntimeException will be thrown ONLY WHEN THE JPA REPOSITORY EXECUTES THE SPECIFICATION’S toPredicate INNER METHOD, NOT DURING THE parseAndBuild METHOD CALL.

3. There are some cases where Hibernate generates extra joins from the resolved Specification that are not necessary:

As exemplify in the Wiki Equality Operation:

/movies?search=actors.id:535

Hibernate: select […] from movie movie0_ inner join movie_actor actors1_ on movie0_.id=actors1_.movie_id inner join actor actor2_ on actors1_.actor_id=actor2_.id where actor2_.id=535

The last join (inner join actor) is not necessary, we could have written:

where actors1_.actor_id=2.

4. This implementation was tested in entities with AutoIncrement and Sequence primary keys. We have not tested it (yet) in entities with Composite primary keys (@Embedded and @EmbeddedId).

Conclusion

Spring Data JPA Specification provides a convenient and sophisticated manner to build dynamic SQL where clauses. By adding some extra logic and considering some pitfalls, we are capable of offering API consumers a zero-effort generic mechanism for filtering entities.

Feel free to fork and submit pull requests to the repository.

Repository

msampietro/spring-spec-query-language

Wiki

msampietro/spring-spec-query-language

Contact Info

Martin Sampietro - Argentina | Professional Profile | LinkedIn

Email: sampietromartin1@gmail.com