Your own queries: @Query

A query from a method name is fine while the condition is simple. Searching for a word in both the title and the body would turn into findByTitleContainingIgnoreCaseOrBodyContainingIgnoreCase — hard to read. In this case you write the query yourself in the @Query annotation and give the method any name you like.

JPQL

// src/main/java/com/example/blog/PostRepository.java
public interface PostRepository extends JpaRepository<Post, Long> {
 
    @Query("""
            select p from Post p
            where lower(p.title) like lower(concat('%', :word, '%'))
               or lower(p.body) like lower(concat('%', :word, '%'))
            """)
    List<Post> search(@Param("word") String word);
}

This is JPQL — the JPA query language. It looks like SQL but works with entities and their fields: Post is a class name, not a table, and p.title is a field, not a column. :word is a named parameter whose value comes from the argument marked @Param("word"). The """ text block lets you write the query over several lines.

Hibernate translates JPQL into SQL for your database (output shortened):

Hibernate: select ... from posts p1_0 where lower(p1_0.title) like lower(('%'||?||'%')) escape '' or lower(p1_0.body) like lower(('%'||?||'%')) escape ''

Using it

In the service the search method now calls the new query:

// src/main/java/com/example/blog/PostService.java
public List<PostResponse> search(String word) {
    return posts.search(word).stream().map(PostResponse::from).toList();
}

The request curl "http://localhost:8080/api/posts/search?q=repositories" finds the "Spring Data JPA" post even though the word is only in its body.

Plain SQL

When you need a feature of a particular database, you can write real SQL with nativeQuery = true:

// src/main/java/com/example/blog/PostRepository.java
@Query(value = "select * from posts order by created_at desc limit :count", nativeQuery = true)
List<Post> findLatest(@Param("count") int count);

Here we use table and column names: posts, created_at. Hibernate does not translate such a query, so it may need rewriting if you switch databases.

What to choose

Modifying queries

For update and delete, add @Modifying to @Query, and the calling service method must run in a transaction (transactions come two lessons later):

@Modifying
@Query("delete from Post p where p.createdAt < :before")
int deleteOlderThan(@Param("before") Instant before);

The method returns the number of deleted rows.

Common mistake

JPQL uses class and field names. If out of habit you write the table name — select p from posts p — the application does not start: Hibernate checks queries at startup and reports that it knows no entity called posts. JPQL needs Post, nativeQuery needs posts.