Repository queries

The CrudRepository methods are enough to work with one record by id. Searching needs your own queries. In Micronaut Data you usually do not write them, you name them: the processor parses the method name findByTitleContains at compile time and turns it into SQL with WHERE title LIKE. A typo in a field name shows up right away during the build, not in front of a user.

Queries from method names

Extend the article repository:

// src/main/java/com/example/ArticleRepository.java
package com.example;
 
import io.micronaut.data.annotation.Query;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.PageableRepository;
 
import java.util.List;
 
@JdbcRepository(dialect = Dialect.H2)
public interface ArticleRepository extends PageableRepository<Article, Long> {
 
    List<Article> findByTitleContains(String text);
 
    List<Article> findByTitleContainsOrderByIdDesc(String text);
 
    long countByTitleContains(String text);
 
    @Query("SELECT * FROM article WHERE LENGTH(body) > :minLength")
    List<Article> findLong(int minLength);
}

For searching with ?q=, ArticleService gets a search(text) method that calls findByTitleContains. The log shows what the name turned into:

SELECT article_.`id`,article_.`title`,article_.`body` FROM `article` article_ WHERE (article_.`title` LIKE CONCAT('%',?,'%'))

How a name is built

Part	Example	SQL
`findBy` + field	`findByTitle`	`WHERE title = ?`
`Contains`	`findByTitleContains`	`LIKE '%…%'`
`GreaterThan`, `LessThan`	`findByIdGreaterThan`	`>`, `<`
`And`, `Or`	`findByTitleAndBody`	`AND`, `OR`
`OrderBy` + field + `Desc`	`…OrderByIdDesc`	`ORDER BY id DESC`
`countBy`, `existsBy`, `deleteBy`	`countByTitleContains`	`COUNT`, a check, `DELETE`

If a query cannot be expressed as a name, write SQL in @Query. The :minLength parameter binds to the method argument by name.

Pagination

PageableRepository adds findAll(Pageable). The controller takes a Pageable parameter, and Micronaut builds it from page, size and sort in the address:

// src/main/java/com/example/ArticleController.java
@Get("/page")
public Page<Article> page(Pageable pageable) {
    return articleService.findPage(pageable);
}

In the service, findPage simply calls articleRepository.findAll(pageable). With three articles, curl "http://localhost:8080/articles/page?page=0&size=2" returns:

{"content":[{"id":1,"title":"Hello, Micronaut","body":"First article"},{"id":2,"title":"Records in Java","body":"Second article"}],"pageable":{"size":2,"number":0,"sort":{},"mode":"OFFSET"},"totalSize":3}

Page numbers start at zero, and totalSize is the total number of records. Sorting looks like this: ?page=1&size=2&sort=title.

Comments

A comment stores the number of its article:

// src/main/java/com/example/Comment.java
@Serdeable
@MappedEntity
public record Comment(
        @Id @GeneratedValue @Nullable Long id,
        Long articleId,
        String author,
        String text) {
}

// src/main/java/com/example/CommentRepository.java
@JdbcRepository(dialect = Dialect.H2)
public interface CommentRepository extends CrudRepository<Comment, Long> {
 
    List<Comment> findByArticleId(Long articleId);
 
    long countByArticleId(Long articleId);
}

The articleId field is stored in the article_id column. A CommentController at /articles/{articleId}/comments returns findByArticleId(articleId) on GET and saves a comment on POST. Check it:

curl -X POST http://localhost:8080/articles/1/comments \
  -H "Content-Type: application/json" -d '{"author":"Oleg","text":"Great article"}'
curl http://localhost:8080/articles/1/comments

[{"id":1,"articleId":1,"author":"Oleg","text":"Great article"}]

Common mistake

A typo in a field name breaks the build, not the running application. A findByHeadline method in the article repository gives this at compile time:

[ERROR] ArticleRepository.java:[19,19] Unable to implement Repository method: com.example.ArticleRepository.findByHeadline(String headline).
Cannot query entity [Article] on non-existent property: Headline [title, body]

In square brackets Micronaut lists the fields the entity actually has, so pick the right one.

Thanks for reading!

Queries from method names

How a name is built

Comments

Common mistake

Thanks for reading!