LIKE Queries in Spring JPA Repositories

1. Overview

In this quick tutorial, we’re going to cover different ways of creating LIKE queries in Spring JPA Repositories.

We’ll start by looking at the various keywords we can use while creating query methods. Then we’ll cover the @Query annotation with named and ordered parameters.

2. Setup

For our example, we’ll be querying a movie table.

Let’s define our Movie entity:

@Entity
public class Movie {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private String title;
    private String director;
    private String rating;
    private int duration;

    // standard getters and setters
}

With our Movie entity defined, let’s create some sample insert statements:

INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(1, 'Godzilla: King of the Monsters', ' Michael Dougherty', 'PG-13', 132);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(2, 'Avengers: Endgame', 'Anthony Russo', 'PG-13', 181);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(3, 'Captain Marvel', 'Anna Boden', 'PG-13', 123);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(4, 'Dumbo', 'Tim Burton', 'PG', 112);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(5, 'Booksmart', 'Olivia Wilde', 'R', 102);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(6, 'Aladdin', 'Guy Ritchie', 'PG', 128);
INSERT INTO movie(id, title, director, rating, duration) 
    VALUES(7, 'The Sun Is Also a Star', 'Ry Russo-Young', 'PG-13', 100);

3. LIKE Query Methods

For many simple LIKE query scenarios, we can take advantage of a variety of keywords to create query methods in our repositories.

Let’s explore them now.

3.1. Containing, Contains, IsContaining and Like

Let’s look at how we can perform the following LIKE query with a query method:

SELECT * FROM movie WHERE title LIKE '%in%';

First, let’s define query methods using Containing, Contains and IsContaining:

List<Movie> findByTitleContaining(String title);
List<Movie> findByTitleContains(String title);
List<Movie> findByTitleIsContaining(String title);

Let’s call our query methods with the partial title in:

List<Movie> results = movieRepository.findByTitleContaining("in");
assertEquals(3, results.size());

results = movieRepository.findByTitleIsContaining("in");
assertEquals(3, results.size());

results = movieRepository.findByTitleContains("in");
assertEquals(3, results.size());

We can expect each of the three methods to return the same results.

Spring also provides us with a Like keyword, but it behaves slightly differently in that we’re required to provide the wildcard character with our search parameter.

Let’s define a LIKE query method:

List<Movie> findByTitleLike(String title);

Now we’ll call our findByTitleLike method with the same value we used before but including the wildcard characters:

results = movieRepository.findByTitleLike("%in%");
assertEquals(3, results.size());

3.2. StartsWith

Let’s look at the following query:

SELECT * FROM Movie WHERE Rating LIKE 'PG%';

We’ll use the StartsWith keyword to create a query method:

List<Movie> findByRatingStartsWith(String rating);

With our method defined, let’s call it with the value PG:

List<Movie> results = movieRepository.findByRatingStartsWith("PG");
assertEquals(6, results.size());

3.3. EndsWith

Spring provides us with the opposite functionality with the EndsWith keyword.

Let’s consider this query:

SELECT * FROM Movie WHERE director LIKE '%Burton';

Now we’ll define an EndsWith query method:

List<Movie> findByDirectorEndsWith(String director);

Once we’ve defined our method, let’s call it with the Burton parameter:

List<Movie> results = movieRepository.findByDirectorEndsWith("Burton");
assertEquals(1, results.size());

3.4. Case Insensitivity

We often want to find all the records containing a certain string regardless of the case. In SQL, we might accomplish this by forcing the column to all capital or lowercase letters and providing the same with values we’re querying.

With Spring JPA, we can use the IgnoreCase keyword combined with one of our other keywords:

List<Movie> findByTitleContainingIgnoreCase(String title);

Now we can call the method with the and expect to get results containing both lowercase and uppercase results:

List<Movie> results = movieRepository.findByTitleContainingIgnoreCase("the");
assertEquals(2, results.size());

3.5. Not

Sometimes we want to find all the records that don’t contain a particular string. We can use the NotContains, NotContaining and NotLike keywords to do that.

Let’s define a query using NotContaining to find movies with ratings that don’t contain PG:

List<Movie> findByRatingNotContaining(String rating);

Now let’s call our newly defined method:

List<Movie> results = movieRepository.findByRatingNotContaining("PG");
assertEquals(1, results.size());

To achieve functionality that finds records where the director’s name doesn’t start with a particular string, we’ll use the NotLike keyword to retain control over our wildcard placement:

List<Movie> findByDirectorNotLike(String director);

Finally, let’s call the method to find all the movies where the director’s name starts with something other than An:

List<Movie> results = movieRepository.findByDirectorNotLike("An%");
assertEquals(5, results.size());

We can use NotLike in a similar way to accomplish a Not combined with the EndsWith kind of functionality.

4. Using @Query

Sometimes we need to create queries that are too complicated for Query Methods or would result in absurdly long method names. In those cases, we can use the @Query annotation to query our database.

4.1. Named Parameters

For comparison purposes, we’ll create a query that’s equivalent to the findByTitleContaining method we defined earlier:

@Query("SELECT m FROM Movie m WHERE m.title LIKE %:title%")
List<Movie> searchByTitleLike(@Param("title") String title);

We include our wildcards in the query we supply. The @Param annotation is important here because we’re using a named parameter.

4.2. Ordered Parameters

In addition to named parameters, we can use ordered parameters in our queries:

@Query("SELECT m FROM Movie m WHERE m.rating LIKE ?1%")
List<Movie> searchByRatingStartsWith(String rating);

We have control of our wildcards, so this query is the equivalent of the findByRatingStartsWith query method.

Let’s find all the movies with a rating starting with PG:

List<Movie> results = movieRepository.searchByRatingStartsWith("PG");
assertEquals(6, results.size());

When we use ordered parameters in LIKE queries with untrusted data, we should escape incoming search values.

If we’re using Spring Boot 2.4.1 or later, we can use the SpEL escape method:

@Query("SELECT m FROM Movie m WHERE m.director LIKE %?#{escape([0])} escape ?#{escapeCharacter()}")
List<Movie> searchByDirectorEndsWith(String director);

Now let’s call our method with the value Burton:

List<Movie> results = movieRepository.searchByDirectorEndsWith("Burton");
assertEquals(1, results.size());

5. Conclusion

In this short article, we learned how to create LIKE queries in Spring JPA Repositories.

First, we learned how to use the provided keywords to create query methods.

Then we learned how to accomplish the same tasks using the @Query parameter with both named and ordered parameters.