Spring Data ‘LIKE’ Query with JdbcClient or JpaRepository

Learn to write SQL queries with ‘LIKE’ keyword using Spring Data JPA module in a Spring Boot application. In this tutorial, We will learn to:

• Write ‘LIKE‘ queries with JdbcClient or JdbcTemplate
• Write ‘LIKE‘ queries using custom query methods in JpaRepository or @Query

1. Initial Setup

1.1. Entity and Initial Data

We have created a Spring boot data application that configures JdbcClient and JpaRepository based accesses. For demo purposes, we have created a JPa entity Person with the fields: id, firstName, lastName and createdAt.

@Entity
//...
public class Person {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  Long id;
  String firstName;
  String lastName;
  Instant createdAt;
}

We will apply the LIKE queries on the ‘firstName‘ field. The examples can be applied on any type of @Entity and any VARCHAR type field.

The initial data is pushed through an SQL file that gets executed at the start of unit tests.

truncate table person;
insert into person(first_name, last_name, created_at) values
    ('Alex','Kolen', CURRENT_TIMESTAMP),
    ('Brian','Schultz', CURRENT_TIMESTAMP),
    ('Charles','Dave', CURRENT_TIMESTAMP),
    ('Charlie','Brown', CURRENT_TIMESTAMP),
    ('David','Freezer', CURRENT_TIMESTAMP),
    ('Edvin','Shaw', CURRENT_TIMESTAMP),
    ('Elger','Shaun', CURRENT_TIMESTAMP);

1.2. Enable SQL Logging

Addtionally, we have added the ‘com.github.gavlyukovskiy:datasource-proxy-spring-boot-starter‘ dependency that adds ability to intercept and log sql queries.

<dependency>
  <groupId>com.github.gavlyukovskiy</groupId>
  <artifactId>datasource-proxy-spring-boot-starter</artifactId>
  <version>1.9.1</version>
</dependency>

And add debug logging in the application.properties file:

logging.level.net.ttddyy.dsproxy.listener=debug

2. Write LIKE queries with JdbcClient and JdbcTemplate

The one thing common between JdbcClient, JdbcTemplate and @Query annotation is that we have write native SQL query that will be executed in the database. So the solution discussed in this section applies to all 3 methods for executing the native SQL queries.

2.1. Using concat() Function in SQL Queries

In this solution, we can write the LIKE keyword directly in the SQL statement as folanywhere in thelows. This query finds all people in the database whose first name contains the specified ‘searchTerm‘ in the beginning, at the end, or anywhere in the middle.

String sql = "select id, first_name, last_name, created_at \
    from person \
    where first_name like concat('%', :searchTerm, '%')";

We can use futher customize the concat() function (joins two or more strings together to form a single string) to create conditions for startWith and endsWith like conditions.

2.2. Demo

For example, we can write a function for containing operation using Spring 6 JdbcClient as follows:

@Repository
public class PersonRepository {

  private final JdbcClient jdbcClient;
  public PersonRowMapper personRowMapper = PersonRowMapper.getInstance();

  public PersonJdbcClientRepository(DataSource dataSource) {
    this.jdbcClient = JdbcClient.create(dataSource);
  }

  //...

  public List<Person> findAllByFirstNameContaining(String searchTerm) {

    String sql = """
        select id, first_name, last_name, created_at \
        from person \
        where first_name like concat('%', :searchTerm,'%')""";

    return jdbcClient.sql(sql)
        .param("searchTerm", searchTerm)
        .query(personRowMapper)
        .list();
  }
}

Similarily, we can use the ‘LIKE’ query with JdbcTemplate as follows:

@Repository
public class PersonRepository {

  private NamedParameterJdbcTemplate jdbcTemplate;
  public PersonRowMapper personRowMapper = PersonRowMapper.getInstance();

  public PersonRepository(NamedParameterJdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

  public List<Person> findAllByFirstNameContaining(String searchTerm) {

     String sql = """
        select id, first_name, last_name, created_at \
        from person \
        where first_name like concat('%', :searchTerm,'%')""";

      return jdbcTemplate.query(sql, new MapSqlParameterSource("searchTerm", searchTerm), personRowMapper);
  }
}

3. Write ‘LIKE‘ queries using custom query methods in JpaRepository

The JpaRepository interface provides a set of default methods for common CRUD operations. We can also define custom methods that are automatically implemented by Spring Data JPA based on method naming conventions. It also allows to write JPQL, native SQL, or SpEL expressions using the @Query annotation on the custom methods.

The following methods can be used to perform query operations with LIKE keyword using JpaRepository interface using the keywords Containing, StartsWith, EndsWith, IgnoreCase etc.

A similar keyword LIKE also can be used but the catch is that in the generated SQL query, it does not append the wildcard characters. So we must pass the wildcard characters, appended in search term, while calling the method.

List<Person> findByFirstNameLike(String searchTerm);  //LIKE Operation

List<Person> findByFirstNameNotLike(String searchTerm);   // NOT LIKE Operation

We can define these methods as following:

import com.howtodoinjava.jpaRepository.demo.model.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface PersonJpaRepository extends JpaRepository<Person, Long> {
  
  //Containing, Contains, IsContaining
  List<Person> findByFirstNameContaining(String searchTerm);
  List<Person> findByFirstNameIsContaining(String searchTerm);
  List<Person> findByFirstNameContains(String searchTerm);

  //Like
  List<Person> findByFirstNameLike(String searchTerm);

  //StartsWith, EndsWith
  List<Person> findByFirstNameStartsWith(String searchTerm);
  List<Person> findByFirstNameEndsWith(String searchTerm);

  //Not
  List<Person> findByFirstNameNotContaining(String searchTerm);
  List<Person> findByFirstNameNotLike(String searchTerm);
  
  //IgnoreCase
  List<Person> findByFirstNameLikeIgnoreCase(String searchTerm);
  List<Person> findByFirstNameStartsWithIgnoreCase(String searchTerm);
  List<Person> findByFirstNameEndsWithIgnoreCase(String searchTerm);
}

Next, we can test these methods in unit tests with @DataJpaTest as follows:

@DataJpaTest
@Sql("/test-data.sql")
public class PersonJpaRepositoryTest {

  @Autowired
  PersonJpaRepository repository;

  @Test
  void findAllPersonsFirstNameContaining_should_pass() {

    List<Person> persons = repository.findByFirstNameContaining("Ale");
    assertThat(persons).isNotEmpty();
    assertThat(persons).hasSize(1);
  }

  @Test
  void findAllPersonsFirstNameLike_should_pass() {

    List<Person> persons = repository.findByFirstNameLike("%Ale%");
    assertThat(persons).isNotEmpty();
    assertThat(persons).hasSize(1);
  }
}

4. Conclusion

This short Spring Data JPA tutorial discussed different ways to execute SQL queries with LIKE keyword. We learned to write native SQL queries and execute them with JdbcTemplate and JdbcClient objects. Next, we learned to write the custom methods in JpaRepository interface that generate the required SQL queries with LIKE keyword.

Happy Learning !!

Source Code on Github