Flexible Application of LIKE Operator in Spring JPA @Query: Multiple Approaches for Implementing Fuzzy Queries

Introduction and Problem Context

In enterprise application development based on the Spring framework, Spring Data JPA serves as a core component of the data access layer, significantly simplifying database operations. However, developers often encounter challenging issues when dealing with complex query logic. This article focuses on a typical scenario: how to leverage the @Query annotation and LIKE operator to implement fuzzy matching queries on entity attributes (e.g., usernames), particularly for retrieving records that contain a specific substring (rather than only starting or ending with it).

Problem Analysis and Common Pitfalls

In the original problem, the developer attempted to define a method findUserByUsernameLike in a CrudRepository interface, aiming to retrieve all users whose usernames contain the input parameter. The initial implementation was as follows:

@Repository
public interface UserRepository extends CrudRepository<User, Long> {
    @Query("select u from user u where u.username like '%username%'")
    List<User> findUserByUsernameLike(@Param("username") String username);
}

This code has two critical issues. First, JPQL (Java Persistence Query Language) requires entity names (here, User) to start with an uppercase letter, but the code uses lowercase user, which causes deployment failure. Second, the username in the query string '%username%' is misinterpreted as a literal rather than a method parameter, preventing dynamic binding of input values and thus failing to achieve the intended fuzzy query functionality.

Core Solution: Constructing Dynamic Queries with CONCAT Function

To address these issues, the best practice is to use the CONCAT function to dynamically concatenate wildcard % and parameter values. The following code demonstrates the corrected implementation:

@Query("SELECT u FROM User u WHERE u.username LIKE CONCAT('%', :username, '%')")
List<User> findUsersByUsernameContaining(@Param("username") String username);

In this query, CONCAT('%', :username, '%') wraps the input parameter :username between two % wildcards, generating a pattern like %alice% to match any username containing the substring alice. Key points include:

• The entity name User is correctly capitalized, adhering to JPQL standards.
• Parameter binding via :username ensures query dynamism and security, mitigating SQL injection risks.
• The combination of LIKE operator and CONCAT function provides flexible string matching capabilities.

This method not only resolves deployment issues but also precisely meets the requirement for "containing" queries, serving as a standard approach for such scenarios.

Alternative Approach: Queries Based on Method Naming Conventions

Beyond custom @Query annotations, Spring Data JPA supports automatic query generation through method naming, which can simplify code and improve readability. For fuzzy queries, Spring offers a range of keywords:

• findByUsernameLike(String username): Generates where x.username like ?1, with no wildcards added by default, requiring manual handling.
• findByUsernameStartingWith(String username): Generates where x.username like ?1%, matching strings starting with the parameter.
• findByUsernameEndingWith(String username): Generates where x.username like %?1, matching strings ending with the parameter.
• findByUsernameContaining(String username): Generates where x.username like %?1%, directly implementing "containing" queries without additional configuration.

Thus, for the original problem, using findByUsernameContaining is the most concise alternative, avoiding the complexity of manually writing JPQL while maintaining code clarity. For example:

List<User> findByUsernameContaining(String username);

This method relies on Spring Data JPA's query derivation mechanism, automatically parsing method names into corresponding SQL queries. It is suitable for simple query scenarios but may be less flexible than @Query for complex logic.

Technical Details and Best Practices

When implementing fuzzy queries, the following technical details should be considered:

1. JPQL vs. SQL Differences: JPQL is an object-oriented query language that operates on entities rather than database tables. Therefore, in @Query, entity class names (e.g., User) and property names (e.g., username) should be used, not table or column names.
2. Parameter Binding Security: Using @Param annotations or positional parameters (e.g., ?1) prevents SQL injection, as Spring escapes parameters. Avoid directly concatenating user input into query strings.
3. Performance Considerations: LIKE queries, especially patterns starting with % (e.g., %value%), may lead to full table scans, impacting performance. For large datasets, consider adding indexes or optimizing with full-text search engines.
4. Wildcard Handling: If input parameters might contain wildcards (e.g., % or _), use the ESCAPE clause for escaping, e.g., LIKE CONCAT('%', :username, '%') ESCAPE '\', to ensure query accuracy.

In practical projects, it is recommended to choose solutions based on query complexity: for simple "containing" queries, prefer findByUsernameContaining; for queries requiring custom logic or complex joins, use the @Query annotation.

Conclusion

Through a specific case study, this article systematically explains two main methods for implementing fuzzy queries in Spring Data JPA: using the @Query annotation with the CONCAT function, and leveraging method naming conventions. The core solution—LIKE CONCAT('%', :username, '%')—not only corrects syntax errors but also provides dynamic and secure query capabilities. Simultaneously, the Containing keyword based on method naming offers a more concise alternative. Developers should flexibly apply these techniques according to project needs to build efficient and maintainable data access layers. In the future, with the ongoing evolution of Spring Data JPA, further exploration of query optimization features (e.g., pagination support, native query integration) is warranted.