Spring JPA @Query with LIKE

Devising custom queries with LIKE and @Query

While mastering the LIKE operator, being aware of some pivotal concepts allows you to deliver robust and resilient queries:

1. Dynamic patterns via JPQL CONCAT

A well-thought CONCAT usage can help to dynamically assemble your query patterns:

// Concatenate and confound cyberspace!
@Query("SELECT u FROM User u WHERE u.username LIKE CONCAT('%', :username, '%')")
List<User> findByUsernameContaining(@Param("username") String username);


2. JPQL naming conventions: The Capitalization Rule

In JPQL, tables take the stage under the names of their respective entity classes, and they must start with a capital letter:

// Humpty Dumpty sat on a User...
@Query("SELECT u FROM User u WHERE u.name LIKE %?1%")
List<User> findByNamePattern(String name);


3. Harnessing Spring JPA method signature wizardry

Here's a glimpse of Spring JPA's chameleonic capability in method names, capable of transforming into query constructs:

// Three innocent method names walked into a bar...
List<User> findUserByUsernameStartingWith(String prefix);
List<User> findUserByUsernameEndingWith(String suffix);
List<User> findUserByUsernameContainingIgnoreCase(String snippet);


4. Match-Making 101: The art of finding the one (... or many)

Sometimes, you just want to match a username anywhere in the value:

// Matchmaker, matchmaker, make me a match...
@Query("SELECT u FROM User u WHERE u.username LIKE %?1%")
List<User> findUserBySimilarUsername(String username);


5. When less is more: Simplifying with method names

For less complex queries, trade-in @Query annotations for a more verbal style: method names that convey your exact intentions:

// Why toil with @Query for a simple fetch, eh?
List<User> findUserByUsernameContaining(String substring);


6. Testing, the unsung hero

Because we are humans and code can be a wild beast:

// Test today, save a headache tomorrow.
assertEquals(2, userRepository.findUserBySimilarUsername("smith").size());


7. If you like it then you shoulda put a case on it

Need a case-insensitive search? Spring JPA has got you covered:

// Because we don't judge on uppercase...
List<User> findUserByUsernameContainingIgnoreCase(String substring);


Handling common yet tricky scenarios

There are times when standard, by-the-book queries aren't enough. Here come the specialties:

1. Casting a wide net

When you need to match at any point in a value:

// Where did I keep my fishing net?
@Query("SELECT p FROM Post p WHERE p.title LIKE %:phrase%")
List<Post> searchByPhrase(@Param("phrase") String phrase);


2. Dealing with wildcard characters

To handle the morose % and _ characters that users might input:

// It's not you underscore, it's me...
@Query("SELECT c FROM Comment c WHERE c.text LIKE ?1 escape '!'")
List<Comment> findByTextContainingSpecialCharacter(String text);


3. Crafting complex patterns

Complex patterns require the exact assembling of your characters:

// The most complex jigsaw puzzle ever!
@Query("SELECT a FROM Article a WHERE a.content LIKE CONCAT(CONCAT('Intro: ', :keyword),'%')")
List<Article> findByIntroKeyword(@Param("keyword") String keyword);
