Wildcard Queries with Spring Data JPA

Examples of Wildcard Queries with Spring Data JPA for example, LIKE, starts with, ends with, contains, not like.

Overview

In this tutorial we are going to practice wildcard queries on a String column by passing a search value. We will match the records where the column contains, does not contain, starts with, or ends with the given search value. First, we will use @Query annotation to write wildcard queries using standard SQL operators. Then we we see how Spring Data JPA Query methods support various wildcard queries.

If you are looking for using Spring Data JPA in a Spring Boot project, please read: Spring Boot with Spring Data JPA.

Entity Bean (Dog.java)

Consider that we have a Dog table and we want to find dogs where the name matches to the given patterns. The Entity bean for the Dog will look like this.

@Entity
public class Dog {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String name;
    private int age;

    public Dog(){}

    public Dog(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString(){
        return "id: " + this.id + ", name: " + this.name + ", age: "+this.age;
    }
}Code language: Java (java)

Named Queries (@Query Annotation)

We can add @Query annotation on Spring Data JPA Query methods to provide SQL queries. These queries are executed when the respective method of the repository is invoked. We can also pass arguments to these query methods, which can be used as variables in the provided SQL.

Contains

@Query("FROM Dog d WHERE d.name LIKE %:name%")
List<Dog> findByNameLike(@Param("name") String name);Code language: Java (java)

Here, we are using @Query annotation to provide SQL query to the query method. The query method accepts a named parameter of name. The SQL contains LIKE operator on the column with a pair of % surrounding the named parameter. The % operator denotes zero or more number of characters. Hence the LIKE expression evaluates to any number of characters before the string as well as after the string.

The method returns list of Dog where the name contains the given String.

Does not contain

@Query("FROM Dog d WHERE d.name NOT LIKE %:name%")
List<Dog> findByNameNotLike(@Param("name") String name);Code language: Java (java)

Similarly, we can use NOT LIKE operator to return list of Dog where the name does not contain the given String.

Starts With

We have already seen % denotes zero or more number of characters. To prepare a starts with expression we need to use the % at the end of the string.

@Query("FROM Dog d WHERE d.name LIKE :name%")
List<Dog> findByNameStartsWith(@Param("name") String name);Code language: Java (java)

The above method will return list of Dog objects where name starts with the given string.

Ends With

Similar to the previous example, the % operator can be used at the beginning of the expression to specify spring that ends with given string.

@Query("FROM Dog d WHERE d.name LIKE %:name")
List<Dog> findByNameEndsWith(@Param("name") String name);Code language: Java (java)

This method will return list of Dog objects where name ends with the given string.

In this section, we have seen how to use Named Queries (@Query annotation) with SQL expressions to perform wildcard searches. In the next section we will learn a few auto generated query methods, which can be used in wildcard queries.

Query Methods

Instead of using Named Queries and passing SQL statement to it, we can easily use query methods to perform the wildcard searches.

Contains

This query method uses Containing to perform the wildcard search. It accepts the search string as method parameter.

List<Dog> findByNameContaining(String name);Code language: Java (java)

Alternatively, IsContaining and Contains can be used to perform the same search.

List<Dog> findByNameIsContaining(String name);
List<Dog> findByNameContains(String name);Code language: Java (java)

All three query methods have the same behaviour and can be used alternatively.

Does not contain

To performa a NOT LIKE query, NotContaining can be used in the query method name.

List<Dog> findByNameNotContaining(String name);Code language: Java (java)

This method returns the Dog objects where name does not contain given String.

Starts With And Ends With

To perform a Starts with search, we can add StartsWith to the query method.

List<Dog> findByNameStartsWith(String name);Code language: Java (java)

Similarly, adding EndsWith we can conduct an ends with search.

List<Dog> findByNameEndsWith(String name);Code language: Java (java)

The above two query methods will return Dog objects where name starts with and ends with the given string respectively.

Summary

In this quick tutorial, we have learned how to perform wildcard searches in Spring Data JPA. We performed contains, not contains, starts with, and ends with searches by using both Named Queries (@Query annotation) as well as query methods.

For full source code of the examples used here, please visit our Github Repository.

---