%like% Query in spring JpaRepository

java

prompt-engineering

best-practices

performance

byAlex Kataev·Aug 24, 2024

In Spring Data repository, use the Containing keyword to execute %LIKE% queries. Here's an example:

interface MyRepository extends JpaRepository<MyEntity, Long> {
    List<MyEntity> findByNameContaining(String name);
}

To retrieve entities where the name field contains "term", invoke findByNameContaining("term"), which is similar to SQL's LIKE '%term%'.

For case-insensitive searches, leverage findBy<Attribute>IgnoreCaseContaining method:

List<MyEntity> findByNameIgnoreCaseContaining(String name);

This method ignores the case of the name parameter while searching.

Digging deeper: Custom Queries

If standard methodology is not sufficient, @Query can be used for custom input:

@Query("SELECT m FROM MyEntity m WHERE m.name LIKE %:name%")
List<MyEntity> searchByName(@Param("name") String name);

Here, ensure the correct use of spaces and parameter binding with :name to avoid syntax errors.

Advanced Searches

Custom @Query annotations provide flexibility in complex scenarios:

Complex search patterns: When the search logic is too complicated for simple method names.
Performance optimizations: Additional tweaking of actual SQL might be required for large datasets.
Database-specific features: Utilizing functions or operations unique to your database.

Ensure correct usage of wildcard placeholders:

@Query("SELECT m FROM MyEntity m WHERE m.description LIKE CONCAT('%',:term,'%')")
List<MyEntity> searchByDescription(@Param("term") String term);

Even deeper: Native Queries

Use native queries when standard methods seem insufficient:

@Query(value = "SELECT * FROM my_entity WHERE name LIKE %?1%", nativeQuery = true)
List<MyEntity> searchNative(String term);

Heads up, native queries are like raw seafood 🍣 - consuming directly without the proper precaution might cause problems - a.k.a SQL injection vulnerabilities!

The science behind Naming Conventions

With Spring Data JPA, using the correct naming conventions can solve complex queries. Keywords like findByPlaceStartingWith, findByPlaceEndingWith, findByPlaceContaining, if used correctly, can handle most simple pattern searches without any need for @Query.

For edge cases requiring full SQL potential (field concatenation, use subqueries, etc.), opt for custom @Query. The choice depends on your use case complexity and optimization needs.

Taming the Wildcards

If you are building search patterns programmatically, concatenate wildcards with the search term:

String searchTerm = "%" + name + "%";
List<MyEntity> results = myRepository.searchByName(searchTerm);
// Now who's afraid of the big bad wildcards, huh? :)

Make sure the field name in your method matches the entity field to avoid errors.

Expert advice

While the Containing keyword simplifies things, optimizing performance often requires the precision of a custom query, especially with large datasets. It also allows more complex operations, like joining tables that aren't directly related through an entity's relationships.

Best practices at a glance

Choose standard methods over @Query whenever possible for simplicity and maintainability.
Rely on custom @Query for complex or database-specific tasks.
Always verify syntax and placeholders in custom queries.
Master JpaRepository conventions for efficient querying.

Common Pitfalls

These are some typical errors and how to avoid them:

Typo errors: Can cause @Query to malfunction. Double-check your entity definition.
Incorrect placeholders: Wrong use of : or missing % can lead to unexpected behavior.
Misusing naming conventions: Using Containing when StartingWith or EndingWith would be more appropriate. Choose wisely.