How to Use LIKE % Expression in JPA SQL Query

In this short Spring Data JPA tutorial, you will learn how to use the LIKE % expression in a Native SQL Query.

When using advanced SQL LIKE expression the % sign in the SQL query would represent zero, one or multiple characters. Let’s have a look at the Native JPA SQL Query that uses advanced SQL LIKE expression to select all users whose first_name or last_name in a database table called Users match the provided keyword.

SQL LIKE % Expression in Native JPA SQL Query

@Query(value="select * from Users u where u.first_name like %:keyword% or u.last_name like %:keyword%", nativeQuery=true)
List<UserEntity> findUsersByKeyword(@Param("keyword") String keyword);

In the example above I am creating a Native JPA SQL Query which uses advanced LIKE expression together with Named Parameters. In SQL query above:

  • The :keyword is a named parameter which will be replaced with a value passed in as a method argument annotated with a @Param annotation,
  • The named parameter :keyword is surrounded with which means that the last_name can start with any String, and it can also end with any String but it must contain the value with which the named parameter :keyword will be replaced.

Some examples of using LIKE % expression:

  • %:keyword  – ends with a value that the named parameter “keyword” holds,
  • :keyword% – begins with a value that the named parameter “keyword” holds,
  • %:keyword% – contains the value that the named parameter “keyword” holds,

I hope this short tutorial on how to use the LIKE expression in Spring Data JPA Native Queries was helpful to you.

If you are interested to learn more about Spring Data JPA and how to build RESTful Web Services with Spring Boot and Spring Data JPA check out my other tutorial on page RESTful Web Services with Spring MVC. And if you enjoy learning by following a step-by-step video lesson then check out the below video courses that teach Spring Data JPA.

Leave a Reply

Your email address will not be published.