This Spring Data JPA tutorial will demonstrate how to use the LIKE % expression in a Native SQL Query.
In an advanced SQL LIKE expression, the % sign represents zero, one, or multiple characters within the SQL query. Let’s take a look at a Native JPA SQL Query that employs an advanced SQL LIKE expression to select all users whose first_name or last_name in a database table called Users match the specified keyword.
Please note that this tutorial covers Native SQL Queries only. To view a JPQL code example, refer to “SQL Query with JPQL in a Spring Boot Application”.
Additionally, you may wish to read the following tutorial to learn how to make Hibernate log SQL queries for viewing purposes.
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 code 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
Additional JPA SQL Query Expressions
In addition to the % wildcard character, the LIKE expression in Native SQL queries also supports the use of two other wildcard characters – _ (underscore) and [ ] (brackets).
The underscore (_) character matches any single character, while the brackets [ ] match any single character within the brackets. Here are some examples of how to use these wildcard characters in a Native SQL query:
- Using the underscore character: Suppose you want to find all users whose first name is three characters long, and the second character is the letter “o”. You can use the underscore character to match any single character in the first and third positions, and the letter “o” in the second position. The query would look like this:
@Query(value="SELECT * FROM Users u WHERE u.first_name LIKE '_o_'", nativeQuery=true)
List<UserEntity> findUsersByFirstName(@Param("firstName") String firstName);
- Using brackets: Suppose you want to find all users whose last name starts with either “A” or “B”. You can use the brackets to match any single character within the brackets. The query would look like this:
@Query(value="SELECT * FROM Users u WHERE u.last_name LIKE '[AB]%'", nativeQuery=true)
List<UserEntity> findUsersByLastName(@Param("lastName") String lastName);
In the example above, the LIKE expression matches any last name that starts with either “A” or “B”, followed by any number of additional characters.
It’s important to note that while these wildcard characters can be useful in certain situations, they can also make queries more expensive to execute. Therefore, it’s generally a good practice to use them judiciously and only when necessary. Additionally, not all databases support these wildcard characters, so it’s important to check the documentation for your specific database to ensure compatibility.
Escaping Special Characters in Native JPA Queries with LIKE Expression
Sometimes, the search string used in a LIKE query may contain special characters that need to be escaped. For example, if the search string itself contains the % character, then it needs to be escaped so that it is not treated as a wildcard by the LIKE expression.
To escape special characters in Native JPA SQL queries, you can use the escape clause in your query. The escape clause allows you to specify an escape character that can be used to escape special characters in your search string. Here’s an example:
@Query(value="select * from Users u where u.first_name like %:keyword escape '|'", nativeQuery=true)
List<UserEntity> findUsersByKeyword(@Param("keyword") String keyword);
In the example above, we’ve added the escape clause to the query using the pipe (|) character as the escape character. Now, any special characters that appear in the search string and are preceded by the escape character will be treated as literal characters and not as part of the LIKE expression.
For example, if the search string is “Hello|%”, then the % character will be treated as a literal character and not as a wildcard. Similarly, if the search string is “Hello|_”, then the _ character will be treated as a literal character and not as a wildcard.
Note that the escape character itself also needs to be escaped with the escape character. So, if your escape character is a backslash (), then you would need to escape it as follows: ‘\’.
By using the escape clause, you can ensure that your LIKE expressions work correctly even when the search string contains special characters.
Case Sensitivity in LIKE Query
By default, most databases are case-insensitive when performing LIKE queries. This means that if you search for “john” using a LIKE query, you will also get results for “John” and “JOHN”. However, some databases (such as PostgreSQL) are case-sensitive by default.
If you want to perform a case-sensitive LIKE query in Spring Data JPA, you can use the BINARY operator in your SQL query. The BINARY operator forces a binary (case-sensitive) comparison between two strings.
Here’s an example of a Native JPA SQL query that uses the BINARY operator to perform a case-sensitive search for users whose last name starts with “Smith”:
@Query(value="SELECT * FROM Users u WHERE BINARY u.last_name LIKE :keyword%", nativeQuery=true)
List<UserEntity> findUsersByLastName(@Param("keyword") String keyword);
In this query, the BINARY operator is used to force a case-sensitive comparison between the user’s last name and the search keyword. The search keyword is passed in as a named parameter and is concatenated with the % character to perform a “starts with” search.
If you’re using a case-sensitive database, you don’t need to use the BINARY operator to perform a case-sensitive search. However, you may want to use it anyway to make your code more portable and compatible with other databases.
Keep in mind that case-sensitive LIKE queries can be slower than case-insensitive ones, especially if you’re searching through a large number of rows. If performance is a concern, you may want to consider using other types of queries (such as exact match queries) or optimizing your database schema and indexes.
Performance Considerations
Using the LIKE expression in Native JPA SQL Queries can be resource-intensive, especially when working with large datasets. As a result, it’s important to consider performance when writing queries that use LIKE expressions.
Here are some tips to optimize the performance of Native JPA SQL Queries that use the LIKE expression:
- Use indexes: When querying large datasets, indexes can significantly improve the performance of LIKE expressions. By creating an index on the column(s) that are searched with LIKE, the database can quickly locate the rows that match the search criteria.
- Limit the number of rows returned: If possible, limit the number of rows returned by the query. For example, you could add a LIMIT clause to the SQL query to only return the first few matching rows. This can help reduce the amount of time and resources required to execute the query.
- Use parameter binding: When using parameter binding with LIKE expressions, make sure to use the bind parameter instead of directly concatenating the value into the SQL query. Parameter binding helps prevent SQL injection attacks and can also improve query performance by allowing the database to cache the query execution plan.
By following these performance considerations, you can ensure that your Native JPA SQL Queries that use the LIKE expression are optimized for speed and resource usage.
Conclusion
I hope this brief tutorial on how to use the LIKE expression in Spring Data JPA Native Queries was helpful to you.
If you’re interested in learning more about Spring Data JPA and how to create RESTful Web Services with Spring Boot and Spring Data JPA, check out my other tutorial on “RESTful Web Services with Spring Boot” page. Additionally, if you prefer learning through video lessons, consider taking one of the following video courses that cover Spring Data JPA.