The Exists Query in Spring Data

1. Introduction

In many data-centric applications, situations can arise where we need to check whether a particular object already exists.

In this tutorial, we’ll discuss several ways to achieve this using Spring Data and JPA.

2. Sample Entity

To set the stage for our examples, we’ll create an entity Car with two properties, model and power:

@Entity
public class Car {

    @Id
    @GeneratedValue
    private int id;

    private Integer power;
    private String model;
    
    // getters, setters, ...
}

3. Searching by ID

The JpaRepository interface exposes the existsById method, which checks if an entity with the given id exists in the database:

int searchId = 2; // ID of the Car
boolean exists = repository.existsById(searchId)

Let’s assume that searchId is the id of a Car we created during test setup. For the sake of test repeatability, we should never use a hard-coded number (for example “2”) because the id property of a Car is likely auto-generated and could change over time. The existsById query is the easiest, but least flexible way of checking for an object’s existence.

4. Using a Derived Query Method

We can also use Spring’s derived query method feature to formulate our query. In our example, we want to check if a Car with a given model name exists; therefore, we devise the following query method:

boolean existsCarByModel(String model);

It’s important to note that the naming of the method isn’t arbitrary, it must follow certain rules. Spring will then generate the proxy for the repository such that it can derive the SQL query from the name of the method. Modern IDEs, like IntelliJ IDEA, will provide syntax completion.

When queries get more complex, such as incorporating ordering, limiting results, and including several query criteria, these method names can get quite long, right up to the point of illegibility. Derived query methods also might seem a bit magical because of their implicit and “by convention” nature.

Nevertheless, they can come in handy when clean and uncluttered code is important, and when developers want to rely on a well-tested framework.

5. Searching by Example

An Example is a very powerful way of checking for existence because it uses ExampleMatchers to dynamically build the query. So whenever we require dynamicity, this is a good way to do it. A comprehensive explanation of Spring’s ExampleMatchers and how to use them can be found in our Spring Data Query article.

5.1. The Matcher

Suppose we want to search for model names in a case-insensitive way. We’ll start by creating our ExampleMatcher:

ExampleMatcher modelMatcher = ExampleMatcher.matching()
  .withIgnorePaths("id") 
  .withMatcher("model", ignoreCase());

Note that we must explicitly ignore the id path because id is the primary key, and those are picked up automatically by default.

5.2. The Probe

Next, we need to define a so-called “probe,” which is an instance of the class we want to look up. It has all the search-relevant properties set. We then connect it to our nameMatcher, and execute the query:

Car probe = new Car();
probe.setModel("bmw");
Example<Car> example = Example.of(probe, modelMatcher);
boolean exists = repository.exists(example);

With great flexibility comes great complexity, and as powerful as the ExampleMatcher API may be, using it will produce quite a few lines of extra code. We suggest using it in dynamic queries, or if no other method fits our needs.

6. Writing a Custom JPQL Query With Exists Semantics

The last method we’ll examine uses JPQL (Java Persistence Query Language) to implement a custom query with exists–semantics:

@Query("select case when count(c)> 0 then true else false end from Car c where lower(c.model) like lower(:model)")
boolean existsCarLikeCustomQuery(@Param("model") String model);

The idea is to execute a case-insensitive count query based on the model property, evaluate the return value, and map the result to a Java boolean. Again, most IDEs have pretty good support for JPQL statements.

Custom JPQL queries can be seen as an alternative to derived methods, and are often a good choice when we’re comfortable with SQL-like statements and don’t mind the additional @Query annotations.

7. Conclusion

In this article, we learned how to check if an object exists in a database using Spring Data and JPA. There’s no hard and fast rule when to use each method because it largely depends on the use case at hand and personal preference.

As a good rule of thumb though, given a choice, developers should always lean toward the more straightforward method for reasons of robustness, performance, and code clarity. Also, once decided on either derived queries or custom JPQL queries, it’s a good idea to stick with that choice for as long as possible to ensure a consistent coding style.