With @Query annotation we can use advance LIKE expressions. This allows to embed wildcard like % within the query while using query parameters. Let's understand that with example.

Example

Entity

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  private int salary;
    .............
}

Normal JPQL LIKE expression

Let's declare a query with @Query having normal JPQL LIKE operator. The query will find employees with name containing a certain part. While using query parameter, JPQL does not allow to use % wildcard in the query, instead it has to be embedded within parameter value:

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

  @Query("SELECT e FROM Employee e WHERE e.name LIKE ?1")
  public List<Employee> findByName(String name);
    .............
}

Let's see how we will use above method:

         repo.findByName("%ana%")

Using @Query advance LIKE expression

Above query works fine but we have to use wildcards within the parameter value. Spring @Query allows to use wildcards within the query itself while passing the parameter value without them. Let's see how to do that:

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    .............
  @Query("SELECT e FROM Employee e WHERE e.name LIKE %?1%")
  public List<Employee> findByName2(String name);
    .............
}

Calling above method:

        repo.findByName2("ana");

Using query method with 'Containing' keyword

If our query is not too complex then we can use query method without any JPQL. We need to use 'Containing' keyword which is equivalent to 'LIKE' expression.

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
    .............
  public List<Employee> findByNameContaining(String name);
}

Calling above method:

        repo.findByNameContaining("ana");

Complete Example

The repository

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

  @Query("SELECT e FROM Employee e WHERE e.name LIKE ?1")
  public List<Employee> findByName(String name);

  @Query("SELECT e FROM Employee e WHERE e.name LIKE %?1%")
  public List<Employee> findByName2(String name);

  public List<Employee> findByNameContaining(String name);
}

The example client

@Component
public class ExampleClient {

  @Autowired
  private EmployeeRepository repo;

  public void run() {
      List<Employee> employees = createEmployees();
      repo.saveAll(employees);

      System.out.println(" -- finding all employees --");
      Iterable<Employee> all = repo.findAll();
      all.forEach(System.out::println);

      System.out.println(" -- finding by name containing %ana%  --");
      List<Employee> list = repo.findByName("%ana%");
      list.forEach(System.out::println);
      System.out.println(" -- finding by name containing ana  --");
      list = repo.findByName2("ana");
      list.forEach(System.out::println);

      System.out.println(" -- finding via query method containing ana  --");
      list = repo.findByNameContaining("ana");
      list.forEach(System.out::println);
  }

  private List<Employee> createEmployees() {
      return Arrays.asList(
              Employee.create("Diana", "Admin", 2000),
              Employee.create("Mike", "Sale", 1000),
              Employee.create("Rose", "IT", 4000),
              Employee.create("Sara", "Admin", 3500),
              Employee.create("Tanaka", "Sale", 3000),
              Employee.create("Charlie", "IT", 2500)
      );
  }
}

Main class

public class ExampleMain {

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ExampleClient exampleClient = context.getBean(ExampleClient.class);
      exampleClient.run();
      EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
      emf.close();
  }
}

 -- finding all employees --
Employee{id=1, name='Diana', dept='Admin', salary=2000}
Employee{id=2, name='Mike', dept='Sale', salary=1000}
Employee{id=3, name='Rose', dept='IT', salary=4000}
Employee{id=4, name='Sara', dept='Admin', salary=3500}
Employee{id=5, name='Tanaka', dept='Sale', salary=3000}
Employee{id=6, name='Charlie', dept='IT', salary=2500}
 -- finding by name containing %ana%  --
Employee{id=1, name='Diana', dept='Admin', salary=2000}
Employee{id=5, name='Tanaka', dept='Sale', salary=3000}
 -- finding by name containing ana  --
Employee{id=1, name='Diana', dept='Admin', salary=2000}
Employee{id=5, name='Tanaka', dept='Sale', salary=3000}
 -- finding via query method containing ana  --
Employee{id=1, name='Diana', dept='Admin', salary=2000}
Employee{id=5, name='Tanaka', dept='Sale', salary=3000}

Example Project

Dependencies and Technologies Used:

• spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
  Uses org.springframework:spring-context version 5.0.6.RELEASE
• hibernate-core 5.3.1.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• h2 1.4.197: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9