Spring JPA Like Query Example

1. Overview

In this article, We will learn Spring JPA Like Query Example, While working with contains criteria like the query are used, for example when we want to search for specific terms inside the column value at that time like query is used.

We will cover Spring JPA Like query using method name and @Query annotation, We also explain here contains (like), not contains (not like), starting with and ending with including the case-insensitive query.

We have written the separate article for spring JPA dynamic query which also contains an example of Spring JPA dynamic like query.

2. Spring JPA Like Query Example

2.1 Like or Contains JPA Query

Using method name

As we all know spring JPA support to return data without writing the Query, Spring JPA support retrieve data using the method name. Using Containing we can retrieve containing data.

List<Employee> findByEmployeeNameContaining(String name); // fetch list of employee containing name 

Using @Query annotation

Here we have written query using @Query and used like operator to match the result. Here is a document for like operator in MySQL

@Query("select e from Employee e where e.employeeName like %:name%")
List<Employee> findByEmployeeNameContaining(@Param("name") String name); // fetch list of employee containing name

Output

Example: findByEmployeeNameContaining("Jone");

Query: SELECT * FROM employee WHERE employeeName like ?

Log: binding parameter [1] as [VARCHAR] – [%Jone%]

2.2 Like or Contains with ignoring case JPA Query 

Using IgnoreCase in the method name, we can ignore case while fetching data.

Using method name

List<Employee> findByEmployeeNameContainingIgnoreCase(@Param("name")String name); // fetch list of employee containing name with ignore case

Using @Query annotation

To check case-insensitive lower function is useful, here we have lower([column_name]) and parameter has been lower while before passing to query.

@Query("select e from Employee e where lower(e.employeeName) like %:name%")
List<Employee> findByEmployeeNameContaining(@Param("name") String name); // fetch list of employee containing name with ignore case

Output

Example: findByEmployeeNameContaining("Jone".toUpperCase());

Query: SELECT * FROM employee WHERE lower(employeeName) like ?

Log: binding parameter [1] as [VARCHAR] – [%jone%]

2.3 Start with JPA Query

Using Method name

In method name, StartingWith inside method name we can retrieve start with the result.

List<Employee> findByEmployeeNameStartingWith(String name); // fetch list of employee start with name

Using @Query annotation

@Query("select e from Employee e where e.employeeName like :name%")
List<Employee> findByEmployeeNameStartsWith(@Param("name")String name); // fetch list of employee start with name

Output

Example: findByEmployeeNameStartsWith("Jone");

Query: SELECT * FROM employee WHERE employeeName like ?

Log: binding parameter [1] as [VARCHAR] – [jone%]

2.4 Ends with JPA Query

In method name, endingWith inside method name we can retrieve start with the result.

List<Employee> findByEmployeeNameEndingWith(String name); // fetch list of employee end with name

Using @Query annotation

@Query("select e from Employee e where e.employeeName like %:name")
List<Employee> findByEmployeeNameEndingWith(@Param("name")String name); // fetch list of employee end with name

Output

Example: findByEmployeeNameEndingWith("Jone");

Query: SELECT * FROM employee WHERE employeeName like ?

Log: binding parameter [1] as [VARCHAR] – [%Jone]

2.5  Not Like or Not Containing JPA Query

Using Method name

List<Employee> findByEmployeeNameNotContaining(@Param("name") String name); // fetch list of Employee which not like or not containing name

Using @Query annotation

@Query("select e from Employee e where lower(e.employeeName) not like %:name%")
List<Employee> findByEmployeeNameNotContaining(@Param("name") String name); // fetch list of Employee which not like or not containing name

3. Conclusion

In this article, We learned like or containing query example using spring JPA, Spring JPA support two ways to fire query, One is using method name and other is using @Query annotation, using those both ways we learned like query, start with containing, end with containing and not like query.

4. References

• Spring JPA documentation