How to Use LIKE % Expression in JPA SQL Query

This short Spring Data JPA tutorial will teach you 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 matches the provided keyword.

This tutorial is about Native SQL Queries. For the JPQL code example, read “SQL Query with JPQL in a Spring Boot Application“.

You can also read the following tutorial to learn how to make Hibernate log SQL queries so you can see them.

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 that uses advanced LIKE expression and Named Parameters. In the 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 want 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 Boot. And if you enjoy learning by following a step-by-step video lesson, check out the video courses below that teach Spring Data JPA.

Leave a Reply

Your email address will not be published.