How to easily do conditional querying with Spring Data JPA

​”Utilizing Specification Pattern from the Domain-Driven Design while talking with a database.”

It is tough to find a modern-day application that does not use one of many forms of data persistence. whether it is an application that does heavy and complex operation/analysis on data streams or a simple online app that keep the records of its users and the ways they interact with it, there is almost always a place for the app to persist data for when the app stops occupying any space on a volatile form of memory but still needs to keep track of some data for the future reference.

If you happened to be reading this, you probably are already using Spring and most likely know how the team behind the Spring Framework invests heavily in creating libraries to make coding easier, faster, cleaner, and in my personal opinion, more fun.

Considering the weight and the vitality of the data persistence role in modern development, you can guess even without checking that Spring already provided multiple ways and methods to accomplish the task.

Spring Data JPA, JDBC, MongoDB, R2DBC, Redis are only a bunch of popular libraries from a bigger collection available in there.

Here we look into JPA and how to implement more advanced (Dynamic and Conditional) querying scenarios, which turn out to be a necessity while either the business logic of the product or the UI gets more complex.

I will assume you are familiar with basic data access concepts in general and Spring Data in particular like Repository, Entity, and … but still, I’ll try to at least mention the required topic along the way if you need to familiarize yourself with them.

OK, that’s enough introduction; let’s see how it works!

Imagine developing a hypothetical app for a parcel insurance company that insures parcels for the duration of their journey from source to destination! Here is how the simplified version of your insurance entities might look like:

@Entity
public class Insurance {
    @Id
    @GeneratedValue
    private id Long;
    private String insuranceNumber;

    @OneToOne(optional = false, cascade = CascadeType.ALL)
    private Client client;

    @OneToOne(optional = false, cascade = CascadeType.ALL)
    private Parcel parcel;

    @Enumerated(EnumType.STRING)
    private Status insuranceStatus;

    @ManyToOne
    private Trip trip;
    @ManyToOne
    private PostCompany postCompany;
    @ElementCollection
    private List<Damage> damages = new ArrayList<>();

    @OneToMany(mappedBy = "insurance", cascade = CascadeType.ALL)
    private List<Payment> payments = new ArrayList<>();
    protected Insurance() {}
    // accessors and mutators ...
}

As you can see, we have a few other entities related to our insurance entity in the object-oriented hierarchy and hence a few other tables in our database representing them.

There is an obvious need to search for a bunch of insurance records with specific details in such an application.

Let’s start with simple search queries up to the more complex and conditional ones.

1. First scenario:

We want to search for the list of all insurances that are still active. (not archived) So what we need to do is to query based on the value of property insuranceStatus while it’s not set to ARCHIVED.

here our Insurance repository would look like this:

public interface InsuranceRepository
    extends CrudRepository<Insurance, Long>
{

    // Some generic CRUD methods ...
    List<Insurance> findAllByStatusNot(Insurance.Status status);
}

What we did above is using a technique called Derived Query Methods, in which you instruct Spring JPA to build a query for you based on the method’s name you write. In this example, we just told JPA to find all the insurance instances that their Status property value is NOT what we passed to the method as a parameter. So it will return a list consist of all the insurance records that are not set to archive. Easy, isn’t it?

There are quite a handful of defined keywords for building this type of method that you can use to imitate database queries, and then JPA will create those queries for you behind the scene. Find the whole list here.

Since you are getting a list in the above result and depend on the real-life situation, the list can contain from just a few to more than a thousand; in most cases, it is better to handle paging and sorting on the back-end side instead of fetching every there is and then handle those tasks on the front-end side for the obvious reasons.

You can quite easily do that in Spring JPA by extending your repository from JpaRepository instead of CrudRepository and then passing the Pageable or Sort objects to the query methods you write. ( JpaRepository extends two other interfaces that one of which is PagingAndSortingRepository. We will get to the other one later in this article.)

Here is our example that now uses paging:

public interface InsuranceRepository
    extends JpaRepository<Insurance, Long>
{

    // Some generic CRUD methods ...
    Page<Insurance> findAllByStatusNot(Insurance.Status status,               .                                      Pageable pageable);
}

That is important for you to know that when using Spring JPA, it is always possible to write your queries directly in the repository either using Native query or JPQL query via the @Query annotation. I am not going to explain how to utilize @Query annotation to write native or JPQL queries in this article but it is noteworthy that while using it one would lose type safety and in case of more complex and conditional querying you will end up concatenating multiple strings to create the final query which is error-prone and difficult to maintain in case of any change in the future.

2. Second Scenario:

Now let’s extend our previous query and search for a list of Insurances (our Entity) with more conditions. Assume we are looking for insurance records in the database that, besides NOT having their status set to archived, have a specific keyword (Amsterdam)in a few of its columns and get it ordered by insurance number.

So, for example, the parcel’s source or destination has this word in it or maybe the name of the client or company, basically any record that may have that piece of information.

Like in the first scenario, let’s write a Derived Query Methods to handle this case as well; why not? It can be done quickly, and one does not need to know much about the syntax of the SQL, so here we go:

public interface InsuranceRepository
    extends JpaRepository<Insurance, Long>
{

    // Some generic CRUD methods ...
Page<Insurance> findAllByStatusNotAndParcelDescriptionContainingOrClientNameContainingOrTripSourceContainingOrTripDestinationContainingorPostCompnayNameContainingOrderByInsuranceNumberDesc(Insurance.Status status, String parcelDescription,String clientName, String tripSource, String tripDestination, String postCompanyName, Pageable pageable);
}

I think it is safe to say that in the eyes of most of us, that’s ugly, unreadable, and again hard to maintain since if you change something about the way you want your query built, you need to modify the method signature and also apply it on all the other places in the code that’s being used.

So clearly, this should not be the way to go, and writing JPQL or SQL, as we already discussed, does not solve the problems of conditional querying elegantly.

Query by Example API to the Rescue! (or Not!)

As I mentioned previously, the JpaRepository interface extends two other interfaces that one of which is PagingAndSortingRepository<T, ID> (we tried it), but the other one that we are going to use here is QueryByExampleExecutor<T>

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {}

This interface provides us with a variety of different but familiar find methods that also accept an instance of an Example.

public interface QueryByExampleExecutor<T> {
     <S extends T> Optional<S> findOne(Example<S> var1);     
     <S extends T> Iterable<S> findAll(Example<S> var1);     
     <S extends T> Iterable<S> findAll(Example<S> var1, Sort var2);
     <S extends T> Page<S> findAll(Example<S> var1, Pageable var2);
     <S extends T> long count(Example<S> var1);     
     <S extends T> boolean exists(Example<S> var1); 
}

How does it work? To put it most simply: The Example is literally an example of the entity that you want to search for, built in a way that says, “Look for entity records that are similar to me!”

While using Query by Example API you can do a lot with more clear code comparing to the previous methods. There are three concepts incorporated into the API: Probe, ExampleMatcher, and Example.

The first one is the actual example of the domain object which you are querying for, populated with the desirable fields. The second one is an indication of how to compare and match records of the database in relation to the Probe and finally, the last one is the combination of the two above that is being passed to one of the find methods and create the query for us.

It is a good way to go only if you don’t have nested objects to look for (like what we did above) and alongside its simplicity, it also brings several major limitations that one must consider before deciding to use this API.

• No support for nested or grouped property constraints, such as firstname = ?0 or (firstname = ?1 and lastname = ?2).
• Only supports starts/contains/ends/regex matching for strings and exact matching for other property types.

For the first reason, we cannot utilize it to reproduce the query we already did above.

So for the simple objects (similar to the Person below), you can write something similar to this:

public class Person {    
    @Id   private String id;
    private String firstname;
    private String lastname;
    private Address address;
    // … getters and setters omitted
 }
Person person = new Person();                                person.setFirstname("Dave");                             ExampleMatcher matcher = ExampleMatcher.matching()         .withIgnorePaths("lastname")                            .withIncludeNullValues()                                .withStringMatcherEnding();                            Example<Person> example = Example.of(person, matcher);
Optional<Person> actual = repository.findOne(example);

What we did above is creating an instance of the model that we are looking for, set its firstName property to the keyword that we are looking for, and wrap up the whole example with custom ExampleMatcher which basically applies the desirable behavior while searching for similar records.

So evidently Query by Example API will not help us in more complex querying although it provides adynamic query creation without the need for writing any SQL itself which can come in handy depending on the circumstance.

Let’s Do It with “Specification”!

So what is the real alternative to the Derived Query Methods or concatenating the SLQ string to each other in our case?

Actually, there are two possible scenarios out there which I would like to explain, one is Criteria API and its Specification and the Other one is QueryDSL. The former will be the last part of this article and the latter I’ll describe in another article separately.

The Specification interface is derived from concepts introduced in Eric Evans’ Domain-Driven Design book. It defines a specification as a predicate over an entity which is exactly what the Specification interface represents.

In order to use it, you have to extend your repository interface from JpaSpecificationExecutor<T> :

public interface InsuranceRepository
    extends JpaRepository<Insurance, UUID> ,    JpaSpecificationExecutor<Insurance> {}

The idea here is that we create dynamic specs for each condition that we want to check/search for in the records of the target entity and then chain them up together with the proper logic according to our need.

passing that Object which consists of chained up single specs to the default method of the JpaSpecificationExecutor interface will do the trick for us.

public interface JpaSpecificationExecutor<T> {

   Optional<T> findOne(@Nullable Specification<T> spec);

   List<T> findAll(@Nullable Specification<T> spec);

   Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

   List<T> findAll(@Nullable Specification<T> spec, Sort sort);

   long count(@Nullable Specification<T> spec);
}

Let see how … we start by creating methods for each condition that returns a Specification object from the class of our target model.

static Specification<Insurance> withTripSource(@Nullable String source) {
return (root, cq, cb) -> title == null ? null : cb.like(cb.lower(root.join("trip", JoinType.LEFT).get("source")), cb.lower(cb.literal("%" + source + "%")));
}

It may seem complex at first glance but it actually is simpler than you think.

Here we set our method name to “withTripSource” to indicate we want to include trip.source content as one of the subjects/conditions of our query.

The next part is quite useful since it gives us the freedom to be able to dynamically ignore the result of this method in our final Specification object if we haven’t provided any source String to look for.

This behavior is the key functionality that we have and allows us to accept dynamic parameters in the coming request and create a dynamic query (behind the scene) based on that. Let’s say we may always don’t want to filter our entities based on the trip.source, passing null to this method will take care of that intention of ours.

Specification<Insurance> specs = Specification
        .where(withStatus(status))
        .and(withTripSource(source))
        .and(withTripDestination(destination))
        .and(withPostCompanyName(name));

Pageable paging = PageRequest.of(page, size);
return insuranceRepository.findAll(specs, paging);

Using the Criteria API will give you a lot of flexibility in creating runtime dynamic queries with your desirable conditions (equals, contains …).

In building up the chain from all the specs you wanted to have, besides where & and, not and or are also available.

One other thing that you can (or I believe you must do) is to add type safety to the above construct by using metadata.

Another way of providing type safety is QueryDSL which has been mentioned and there will come another article to describe that.

Good luck and enjoy coding!

refs:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.repositories