Micronaut Data

Data Repository Support for Micronaut

Version:

1 Introduction

Micronaut Data is a database access toolkit that uses Ahead of Time (AoT) compilation to pre-compute queries for repository interfaces that are then executed by a thin, lightweight runtime layer.

Micronaut Data is inspired by GORM and Spring Data, however improves on those solutions in the following ways:

  • No runtime model - Both GORM and Spring Data maintain a runtime meta-model that uses reflection to model relationships between entities. This model consumes significant memory and memory requirements grow as your application size grows. The problem is worse when combined with Hibernate which maintains its own meta-model as you end up with duplicate meta-models.

  • No query translation - Both GORM and Spring Data use regular expressions and pattern matching in combination with runtime generated proxies to translate a method definition on a Java interface into a query at runtime. No such runtime translation exists in Micronaut Data and this work is carried out by the Micronaut compiler at compilation time.

  • No Reflection or Runtime Proxies - Micronaut Data uses no reflection or runtime proxies, resulting in better performance, smaller stack traces and reduced memory consumption due to a complete lack of reflection caches (Note that the backing implementation, for example Hibernate, may use reflection).

  • Type Safety - Micronaut Data will actively check at compile time that a repository method can be implemented and fail compilation if it cannot.

Micronaut Data provides a general API for translating a compile time Query model into a query at compilation time and provides runtime support for the following backends:

  • JPA (Hibernate)

  • SQL (JDBC, R2DBC)

Further implementations for other databases are planned in the future.

The following sections will take you through the basics of querying and using Micronaut Data, if you wish to understand more detail about how Micronaut Data works check out the How Micronaut Data Works section.

At a fundamental level however what Micronaut Data does can be summed up in the following snippets. Given the following interface:

package example;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.context.annotation.Executable;
import io.micronaut.data.annotation.*;
import io.micronaut.data.model.*;
import io.micronaut.data.repository.CrudRepository;

import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.util.List;

@Repository (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Executable
    Book find(String title);
}
package example

import io.micronaut.context.annotation.Executable
import io.micronaut.data.annotation.*
import io.micronaut.data.model.*
import io.micronaut.data.repository.CrudRepository

@Repository (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Executable
    Book find(String title)
}
package example

import io.micronaut.context.annotation.Executable
import io.micronaut.data.annotation.*
import io.micronaut.data.model.*
import io.micronaut.data.repository.CrudRepository

@Repository (1)
interface BookRepository : CrudRepository<Book, Long> { (2)
    @Executable
    fun find(title: String): Book
}
1 The @Repository annotation designates BookRepository as a data repository. Since, it is is an interface, the @Repository annotation provides implementations at compilation time.
2 By extending CrudRepository you enable automatic generation of CRUD (Create, Read, Update, Delete) operations.

Micronaut Data computes the query for the find method automatically at compilation time making it available at runtime via annotation metadata:

@Inject
BeanContext beanContext;

@Test
void testAnnotationMetadata() {
    String query = beanContext.getBeanDefinition(BookRepository.class) (1)
            .getRequiredMethod("find", String.class) (2)
            .getAnnotationMetadata().stringValue(Query.class) (3)
            .orElse(null);

    assertEquals( (4)
            "SELECT book_ FROM example.Book AS book_ WHERE (book_.title = :p1)", query);

}
@Inject
BeanContext beanContext

void "test annotation metadata"() {
    given:"The value of the Query annotation"
    String query = beanContext.getBeanDefinition(BookRepository.class) (1)
            .getRequiredMethod("find", String.class) (2)
            .getAnnotationMetadata()
            .stringValue(Query.class) (3)
            .orElse(null)

    expect:"The JPA-QL query to be correct" (4)
    query == "SELECT book_ FROM example.Book AS book_ WHERE (book_.title = :p1)"
}
@Inject
lateinit var beanContext: BeanContext

@Test
fun testAnnotationMetadata() {
    val query = beanContext.getBeanDefinition(BookRepository::class.java) (1)
            .getRequiredMethod<Any>("find", String::class.java) (2)
            .annotationMetadata
            .stringValue(Query::class.java) (3)
            .orElse(null)


    assertEquals( (4)
            "SELECT book_ FROM example.Book AS book_ WHERE (book_.title = :p1)",
            query
    )

}
1 The BeanDefinition is retrieved from the BeanContext
2 The find method is retrieved
3 The value of the @Query annotation is retrieved
4 The JPA-QL query for the method is correct

1.1 What's New?

Micronaut Data 3.1

  • Kotlin’s coroutines support. New repository interface CoroutineCrudRepository.

  • Support for AttributeConverter

  • R2DBC upgraded to Arabba-SR11

  • JPA Criteria specifications

Micronaut Data 3.0

  • Micronaut 3.0

  • Hibernate optimizations

Micronaut Data 2.5.0

  • Repositories now support batch insert/update/delete even with a custom query

  • Rewritten entity mapper allows more complex mapping for JDBC/R2DBC entities

  • Support for @JoinTable and @JoinColumn annotations

Micronaut Data 2.4.0

  • Full support for immutable entities. You can use Java 16 records or Kotlin immutable data classes

  • Integrated support for R2DBC, now the data-r2dbc module is a part of the data project and shares the same code with JDBC

  • Optimistic locking for JDBC/R2DBC

1.2 Release History

For this project, you can find a list of releases (with release notes) here:

2 Quick Start

To get started with Micronaut Data and JPA add the following dependency to your annotation processor path:

annotationProcessor("io.micronaut.data:micronaut-data-processor:3.1.2-SNAPSHOT")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>3.1.2-SNAPSHOT</version>
    </path>
</annotationProcessorPaths>

For Kotlin the dependency should be in the kapt scope and for Groovy it should be in compileOnly scope.

You should then configure a runtime dependency that matches the implementation you wish to use. For example for Hibernate/JPA:

implementation("io.micronaut.data:micronaut-data-hibernate-jpa:3.1.2-SNAPSHOT")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-hibernate-jpa</artifactId>
    <version>3.1.2-SNAPSHOT</version>
</dependency>

And ensure the implementation is configured correctly.

You can then define an @Entity:

package example;

import javax.persistence.*;

@Entity
public class Book {
    @Id
    @GeneratedValue
    private Long id;
    private String title;
    private int pages;

    public Book(String title, int pages) {
        this.title = title;
        this.pages = pages;
    }

    public Book() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getPages() {
        return pages;
    }

    public void setPages(int pages) {
        this.pages = pages;
    }
}
package example

import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.Id

@Entity
class Book {
    @Id
    @GeneratedValue
    Long id
    String title
    int pages

    Book(String title, int pages) {
        this.title = title
        this.pages = pages
    }

    Book() {
    }
}
package example

import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.Id

@Entity
data class Book(@Id
                @GeneratedValue
                var id: Long,
                var title: String,
                var pages: Int = 0)

Followed by an interface that extends from CrudRepository

package example;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.context.annotation.Executable;
import io.micronaut.data.annotation.*;
import io.micronaut.data.model.*;
import io.micronaut.data.repository.CrudRepository;

import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.util.List;

@Repository (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Executable
    Book find(String title);
}
package example

import io.micronaut.context.annotation.Executable
import io.micronaut.data.annotation.*
import io.micronaut.data.model.*
import io.micronaut.data.repository.CrudRepository

@Repository (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Executable
    Book find(String title)
}
package example

import io.micronaut.context.annotation.Executable
import io.micronaut.data.annotation.*
import io.micronaut.data.model.*
import io.micronaut.data.repository.CrudRepository

@Repository (1)
interface BookRepository : CrudRepository<Book, Long> { (2)
    @Executable
    fun find(title: String): Book
}
1 The interface is annotated with @Repository
2 The CrudRepository interface take 2 generic arguments, the entity type (in this case Book) and the ID type (in this case Long)

You can now perform CRUD (Create, Read, Update, Delete) operations on the entity. The implementation of example.BookRepository is created at compilation time. To obtain a reference to it simply inject the bean:

@Inject
BookRepository bookRepository;
@Inject BookRepository bookRepository
@Inject
lateinit var bookRepository: BookRepository

Saving an Instance (Create)

To save an instance use the save method of the CrudRepository interface:

Book book = new Book();
book.setTitle("The Stand");
book.setPages(1000);
bookRepository.save(book);
Book book = new Book(title:"The Stand", pages:1000)
bookRepository.save(book)
var book = Book(0,"The Stand", 1000)
bookRepository.save(book)

Retrieving an Instance (Read)

To read a book back use findById:

book = bookRepository.findById(id).orElse(null);
book = bookRepository.findById(id).orElse(null)
book = bookRepository.findById(id).orElse(null)

Updating an Instance (Update)

To update an instance use save again:

book.setTitle("Changed");
bookRepository.save(book);
book.title = "Changed"
bookRepository.save(book)
book.title = "Changed"
bookRepository.save(book)

Deleting an Instance (Delete)

To delete an instance use deleteById:

bookRepository.deleteById(id);
bookRepository.deleteById(id)
bookRepository.deleteById(id)

Congratulations you have implemented your first Micronaut Data repository! Read on to find out more.

3 Build Configuration

Since Micronaut Data is a build time tool, it will not work correctly unless your build is configured correctly.

There are two imporant aspects to Micronaut Data:

  1. The build time annotation processors

  2. The runtime APIs

The build time processor is added by adding the micronaut-data-processor module to your annotation processor configuration in either Gradle or Maven:

annotationProcessor("io.micronaut.data:micronaut-data-processor:3.1.2-SNAPSHOT")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>3.1.2-SNAPSHOT</version>
    </path>
</annotationProcessorPaths>

Micronaut Data and Lombok

If you intend to use Lombok with Micronaut Data then you must place the Lombok annotation processor before the Micronaut processors in your build configuration since Micronaut needs to see the mutations to the AST that Lombok applies.

Lombok plugins like the Gradle plugin io.franzbecker.gradle-lombok are not supported as they place the annotation processors in an incorrect order.

Micronaut Data Runtime Dependencies

At runtime for both Micronaut Data JPA and JDBC you as a minimum need the following:

1) A SQL driver for your database in your build configuration. For example for H2:

runtime("com.h2database:h2")
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

2) A JDBC Connection Pool Module (Hikari, Tomcat JDBC or DBCP ) from the Micronaut SQL project. For example for Tomcat JDBC:

runtime("io.micronaut.sql:micronaut-jdbc-tomcat")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-tomcat</artifactId>
    <scope>runtime</scope>
</dependency>

3) A configured data source in application.yml. For example for H2:

datasources:
  default:
    url: jdbc:h2:mem:devDb
    driverClassName: org.h2.Driver
    username: sa
    password: ''
    schema-generate: CREATE_DROP
    dialect: H2

Once this is in place if you wish to use Hibernate / JPA you must include the dependency on the micronaut-data-hibernate-jpa:

implementation("io.micronaut.data:micronaut-data-hibernate-jpa")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-hibernate-jpa</artifactId>
</dependency>

And add the following configuration in application.yml

jpa:
  default:
    entity-scan:
        packages: 'example.domain'

Where jpa.default.entity-scan.packages references the root package where your @Entity classes are located.

Or if you wish to use SQL then the micronaut-data-jdbc module:

implementation("io.micronaut.data:micronaut-data-jdbc")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
</dependency>

4 Repository Interfaces

As seen in the Quick Start repositories in Micronaut Data are defined as interfaces that are annotated with the @Repository annotation.

The @Repository annotation accepts an optional string value which represents the name of the connection or datasource in a multiple datasource scenario. By default Micronaut Data will look for the default datasource.

The entity to treat as the root entity for the purposes of querying is established either from the method signature or from the generic type parameter specified to the GenericRepository interface.

If no root entity can be established then a compilation error will occur.

The following table summarizes the repository interfaces that come with Micronaut Data:

Table 1. Builtin Repository Interfaces

Interface

Description

GenericRepository

A root interface that features no methods but defines the entity type and ID type as generic arguments

CrudRepository

Extends GenericRepository and adds methods to perform CRUD

AsyncCrudRepository

Extends GenericRepository and adds methods for asynchronous CRUD execution

ReactiveStreamsCrudRepository

Extends GenericRepository and adds CRUD methods that return Publisher

ReactorCrudRepository

Extends ReactiveStreamsCrudRepository and is using Reactor return types

RxJavaCrudRepository

Extends GenericRepository and adds CRUD methods that return RxJava 2 types

CoroutineCrudRepository

Extends GenericRepository and is using Kotlin coroutines for reactive CRUD operations

PageableRepository

Extends CrudRepository and adds methods for pagination

Note that in addition to interfaces you can also define repositories as abstract classes:

package example;

import io.micronaut.data.annotation.Repository;
import io.micronaut.data.repository.CrudRepository;

import javax.persistence.EntityManager;
import java.util.List;

@Repository
public abstract class AbstractBookRepository implements CrudRepository<Book, Long> {

    private final EntityManager entityManager;

    public AbstractBookRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Book> findByTitle(String title) {
        return entityManager.createQuery("FROM Book AS book WHERE book.title = :title", Book.class)
                    .setParameter("title", title)
                    .getResultList();
    }
}
package example

import io.micronaut.data.annotation.Repository
import io.micronaut.data.repository.CrudRepository

import javax.persistence.EntityManager

@Repository
abstract class AbstractBookRepository implements CrudRepository<Book, Long> {

    private final EntityManager entityManager

    AbstractBookRepository(EntityManager entityManager) {
        this.entityManager = entityManager
    }

    List<Book> findByTitle(String title) {
        return entityManager.createQuery("FROM Book AS book WHERE book.title = :title", Book)
                .setParameter("title", title)
                .getResultList()
    }
}
package example

import io.micronaut.data.annotation.Repository
import io.micronaut.data.repository.CrudRepository

import javax.persistence.EntityManager

@Repository
abstract class AbstractBookRepository(private val entityManager: EntityManager) : CrudRepository<Book, Long> {

    fun findByTitle(title: String): List<Book> {
        return entityManager.createQuery("FROM Book AS book WHERE book.title = :title", Book::class.java)
                .setParameter("title", title)
                .resultList
    }
}

As you can see from the above example, using abstract classes can be useful as it allows you to combine custom code that interacts with JPA and repository interface code implemented automatically by Micronaut Data.

5 Writing Queries

The implementation of querying in Micronaut Data is based on the dynamic finders in GORM.

A pattern matching approach is taken at compilation time. The general pattern of query methods is:

finderpattern
Figure 1. Query Method Pattern

As shown in Figure 1, the most common query stem is find, but you can also use search, query, get, read or retrieve.

The projection and ordering parts of the query pattern are optional (more on those later). The following snippet demonstrates 3 simple queries that use a different stem but perform the same query:

Book findByTitle(String title);

Book getByTitle(String title);

Book retrieveByTitle(String title);
Book findByTitle(String title)

Book getByTitle(String title)

Book retrieveByTitle(String title)
fun findByTitle(title: String): Book

fun getByTitle(title: String): Book

fun retrieveByTitle(title: String): Book

The above examples return a single instance of an entity, the supported return types are described in the following table:

Table 1. Supported Return Types for Finder Methods

Return Type

Description

List<Book>

A java.util.List or any common Iterable type

Stream<Book>

A Java 8 java.util.stream.Stream instance

Optional<Book>

An optional value, if null is retrieved otherwise a EmptyResultException is thrown

Page<Book>

An instance of Page for pagination.

Slice<Book>

An instance of Slice for pagination.

Future<Book> or CompletableFuture<Book>

A java.util.concurrent.Future for asynchronous execution

Publisher<Book> (or 'Flowable', Single, Maybe, Flux, Mono etc.)

An Reactive Streams compatible type

Flow<Book> as an alternative to suspend functions

A Kotlin reactive type. Requires kotlinx-coroutines-reactive dependency for the proper conversion.

Primitive/Simple Types

In the case of projections primitive/basic types can be returned

Methods with Stream<Book> results need to be used with a 'try-with-resources' block and should be executed within a transaction.

In addition, to the standard findBy* pattern, a few other patterns exist that have special return type requirements.

The following table summarizes the possible alternative patterns, behaviour and expected return types:

Table 2. Method Patterns and Return Types

Method Pattern

Expected Return Type

Description

countBy*

A primitive number of an instance of java.lang.Number

Counts the number of records

existsBy*

A primitive or wrapper boolean

Checks whether a record exists

deleteBy*

A void or Number return type

Batch delete for the given finder

updateBy*

A void or Number return type

Batch update for the given finder

More details about the batch update variants of these methods is covered in the Data Updates section.

Finally, as an alternative to the By syntax you also define simple finders that use the parameter names to match properties to query. This syntax is less flexible, but is more readable in certain circumstances. For example the following can be used as an alternative to findByTitle:

@Executable
Book find(String title);
@Executable
Book find(String title)
@Executable
fun find(title: String): Book

Note that in this case if the title parameter does not exist as a property in the entity being queried or the type does not match up a compilation error will occur. Also you can specify more than one parameter to perform a logical AND.

5.1 Query Criteria

The previous example presented a simple findByTitle query which searches for all Book instances that have a title property equal to the given value.

This is the simplest type of query supported by Micronaut Data, but you can use an optional suffix on the property name to modify the type of criterion to apply.

For example the following query pattern will execute a query that finds only Book instances that have a page count greater than the given value:

List<Book> findByPagesGreaterThan(int pageCount);
List<Book> findByPagesGreaterThan(int pageCount)
fun findByPagesGreaterThan(pageCount: Int): List<Book>

The following table summarizes the possible expressions and behaviour:

Table 1. Property Criterion Expressions

Example Suffix

Description

Sample

After

Find results where the property is after the given value

findByDateCreatedAfter

Before

Find results where the property is before the given value

findByDateCreatedBefore

Contains

Find results where the property contains the given value

findByTitleContains

StartsWith or StartingWith

Find results where the property starts with the given value

findByTitleStartsWith

EndsWith or EndingWith

Find results where the property ends with the given value

findByTitleEndsWith

Equals or Equal

Find results equal to the given value

findByTitleEquals

NotEquals or NotEqual

Find results not equal to the given value

findByTitleNotEquals

GreaterThan

Find results where the property is greater than the given value

findByPagesGreaterThan

GreaterThanEquals

Find results where the property is greater than or equal to the given value

findByPagesGreaterThanEquals

LessThan

Find results where the property is less than the given value

findByPagesLessThan

LessThanEquals

Find results where the property is less than or equal to the given value

findByPagesLessThanEquals

Like

Finds string values "like" the given expression

findByTitleLike

Ilike

Case insensitive "like" query

findByTitleIlike

InList or In

Find results where the property is that are contained within the given list

findByTitleInList

Between or InRange

Find results where the property is between the given values

findByDateCreatedBetween

IsNull

Finds results where the property is null

findByAuthorIsNull

IsNotNull

Finds results where the property is not null

findByAuthorIsNotNull

IsEmpty

Finds results where the property is empty or null

findByAuthorIsEmpty

IsNotEmpty

Finds results where the property is not empty or null

findByAuthorIsNotEmpty

True

Finds results where the property is true

findByAuthorEnabledTrue

False

Finds results where the property is false

findByAuthorEnabledFalse

Any of these criterion expressions can be negated by adding the word Not before the expression (for example NotInList).

You can combine multiple criterion by separating them with And or Or logical operators. For example:

List<Book> findByPagesGreaterThanOrTitleLike(int pageCount, String title);
List<Book> findByPagesGreaterThanOrTitleLike(int pageCount, String title)
fun findByPagesGreaterThanOrTitleLike(pageCount: Int, title: String): List<Book>

The above example uses Or to express a greater than condition and a like condition.

You can also negate any of the aforementioned expressions by adding Not prior the name of the expression (example NotTrue or NotContain).

5.2 Pagination

Typically when returning multiple records you need some control over paging the data. Micronaut Data includes the ability to specify pagination requirements with the Pageable type (inspired by GORM’s PagedResultList and Spring Data’s Pageable).

In addition methods can return a Page object which includes the execution of an additional query to obtain the total number of results for a given query.

The following are some example signatures:

List<Book> findByPagesGreaterThan(int pageCount, Pageable pageable);

Page<Book> findByTitleLike(String title, Pageable pageable);

Slice<Book> list(Pageable pageable);
List<Book> findByPagesGreaterThan(int pageCount, Pageable pageable)

Page<Book> findByTitleLike(String title, Pageable pageable)

Slice<Book> list(Pageable pageable)
fun findByPagesGreaterThan(pageCount: Int, pageable: Pageable): List<Book>

fun findByTitleLike(title: String, pageable: Pageable): Page<Book>

fun list(pageable: Pageable): Slice<Book>

And some test data:

bookRepository.saveAll(Arrays.asList(new Book("The Stand", 1000), new Book("The Shining", 600),
        new Book("The Power of the Dog", 500), new Book("The Border", 700),
        new Book("Along Came a Spider", 300), new Book("Pet Cemetery", 400), new Book("A Game of Thrones", 900),
        new Book("A Clash of Kings", 1100)));
bookRepository.saveAll(Arrays.asList(
        new Book("The Stand", 1000),
        new Book("The Shining", 600),
        new Book("The Power of the Dog", 500),
        new Book("The Border", 700),
        new Book("Along Came a Spider", 300),
        new Book("Pet Cemetery", 400),
        new Book("A Game of Thrones", 900),
        new Book("A Clash of Kings", 1100)
))
bookRepository.saveAll(Arrays.asList(
        Book(0,"The Stand", 1000),
        Book(0,"The Shining", 600),
        Book(0,"The Power of the Dog", 500),
        Book(0,"The Border", 700),
        Book(0,"Along Came a Spider", 300),
        Book(0,"Pet Cemetery", 400),
        Book(0,"A Game of Thrones", 900),
        Book(0,"A Clash of Kings", 1100)
))

You can execute queries and return paginated data using the from method of Pageable and specifying an appropriate return type:

Slice<Book> slice = bookRepository.list(Pageable.from(0, 3));
List<Book> resultList = bookRepository.findByPagesGreaterThan(500, Pageable.from(0, 3));
Page<Book> page = bookRepository.findByTitleLike("The%", Pageable.from(0, 3));
Slice<Book> slice = bookRepository.list(Pageable.from(0, 3))
List<Book> resultList =
        bookRepository.findByPagesGreaterThan(500, Pageable.from(0, 3))
Page<Book> page = bookRepository.findByTitleLike("The%", Pageable.from(0, 3))
val slice = bookRepository.list(Pageable.from(0, 3))
val resultList = bookRepository.findByPagesGreaterThan(500, Pageable.from(0, 3))
val page = bookRepository.findByTitleLike("The%", Pageable.from(0, 3))

The from method accepts index and size arguments which are the page number to begin from and the number of records to return per page.

A Slice is the same as a Page but results in one less query as it excludes the total number of pages calculation.

5.3 Ordering

You can control ordering of results by appending an OrderBy* expression to the end of the method name:

List<Book> listOrderByTitle();

List<Book> listOrderByTitleDesc();
List<Book> listOrderByTitle()

List<Book> listOrderByTitleDesc()
fun listOrderByTitle(): List<Book>

fun listOrderByTitleDesc(): List<Book>

The OrderBy* expression refer to the property name to order by and can optionally be appended with either Asc or Desc to control ascending or descending order. Multiple conditions can be used by joining them with And like findByTypeOrderByNameAndDate.

5.4 Query Projections

Frequently, rather than retrieving all of the data for a particular entity, you may only want a single property or association of an entity or to perform some kind of computation and obtain just that result. This is where query projections come in.

The simplest form of projection is to retrieve a property or association. For example:

List<String> findTitleByPagesGreaterThan(int pageCount);
List<String> findTitleByPagesGreaterThan(int pageCount)
fun findTitleByPagesGreaterThan(pageCount: Int): List<String>

In the above example the findTitleByPagesGreaterThan method is resolving the title property of the Book entity and returning the data as a List of String.

If the projected property type and the return generic type do not match up then Micronaut Data will fail to compile the method.

You can also use projections on association paths, for example if an author association were present you could write findAuthorNameByPagesGreaterThan to retrieve the names of all the authors.

In addition to this, Micronaut Data also supports projection expressions. The following table summarizes the possible expressions with an example and description:

Table 1. Projection Expressions

Expression

Example

Description

Count

countTitleByPagesGreaterThan

Counts the values

CountDistinct

countDistinctTitleByPagesGreaterThan

Counts the distinct values

Distinct

findDistinctTitleByPagesGreaterThan

Finds the distinct property values

Max

findMaxPagesByTitleLike

Finds the maximum property value

Min

findMinPagesByTitleLike

Finds the minimum property value

Sum

findSumPagesByTitleLike

Finds the sum of all the property values

Avg

findAvgPagesByTitleLike

Finds the average of all the property values

You can also use top or first to limit the results returned (as a simple alternative to pagination)

List<Book> findTop3ByTitleLike(String title);
List<Book> findTop3ByTitleLike(String title)
fun findTop3ByTitleLike(title: String): List<Book>

The above query will return the first 3 results for the given query expression.

5.5 DTO Projections

Micronaut Data supports reflection-free Data Transfer Object (DTO) projections if the return type is annotated with @Introspected.

For example if you wanted to project on an entity called Book you could define a DTO as follows:

package example;

import io.micronaut.core.annotation.Introspected;

@Introspected
public class BookDTO {

    private String title;
    private int pages;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getPages() {
        return pages;
    }

    public void setPages(int pages) {
        this.pages = pages;
    }
}
package example

import io.micronaut.core.annotation.Introspected

@Introspected
class BookDTO {
    String title
    int pages
}
package example

import io.micronaut.core.annotation.Introspected

@Introspected
data class BookDTO(
    var title: String,
    var pages: Int
)

The DTO should include properties that match the property names you wish to project on (in this case title and pages). If any properties do not match then a compilation error will occur.

You can then use the DTO object as return type in query methods:

BookDTO findOne(String title);
BookDTO findOne(String title);
fun findOne(title: String): BookDTO

Micronaut Data will optimize the query to only select the necessary properties from the database.

You can use @NamingStrategy annotation to override the default naming strategy.

5.6 Join Queries

To optimize your queries you may need to alter joins to fetch exactly the data you need in the result set.

If a LazyInitializationException occurs this is not a bug in Micronaut Data or Hibernate, but instead an indication that you should alter your query joins to fetch the associated data you need to implement your use case.

Consider a Product entity:

package example;

import javax.persistence.*;

@Entity
public class Product {

    @Id
    @GeneratedValue
    private Long id;
    private String name;
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Manufacturer manufacturer;

    public Product(String name, Manufacturer manufacturer) {
        this.name = name;
        this.manufacturer = manufacturer;
    }

    public Product() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Manufacturer getManufacturer() {
        return manufacturer;
    }

    public void setManufacturer(Manufacturer manufacturer) {
        this.manufacturer = manufacturer;
    }
}
package example

import javax.persistence.*

@Entity
class Product {

    @Id
    @GeneratedValue
    Long id
    String name
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    Manufacturer manufacturer

    Product(String name, Manufacturer manufacturer) {
        this.name = name
        this.manufacturer = manufacturer
    }

    Product() {
    }
}
package example

import javax.persistence.*

@Entity
data class Product(
    @Id
    @GeneratedValue
    var id: Long?,
    var name: String,
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    var manufacturer: Manufacturer
)

That has an association to a Manufacturer entity:

package example;

import org.hibernate.annotations.BatchSize;

import javax.persistence.*;

@Entity
@BatchSize(size = 10)
public class Manufacturer {
    @Id
    @GeneratedValue
    private Long id;
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}
package example

import javax.persistence.*

@Entity
class Manufacturer {

    @Id
    @GeneratedValue
    Long id
    String name
}
package example

import javax.persistence.*

@Entity
data class Manufacturer(
    @Id
    @GeneratedValue
    var id: Long?,
    var name: String
)

In this case when you read each Product from the database an additional select is required to retrieve the Manufacturer for each Product. This leads to N + 1 queries.

To resolve this you can use the @Join annotation on your repository interface to specify that a JOIN FETCH should be executed to retrieve the associated Manufacturer.

@Repository
public interface ProductRepository extends CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}
@Repository
abstract class ProductRepository implements CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    abstract List<Product> list()
}
@Repository
abstract class ProductRepository : CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    abstract fun list(): List<Product>
}
1 The @Join is used to indicate a JOIN FETCH clause should be included.

Note that the @Join annotation is repeatable and hence can be specified multiple times for different associations. In addition, the type member of the annotation can be used to specify the join type, for example LEFT, INNER or RIGHT.

JPA 2.1 Entity Graphs

A JPA-specific alternative to specifying the joins to a query is to use JPA 2.1 entity graphs. With entity graphs you defer to the JPA implementation to pick the appropriate join type to use:

@EntityGraph(attributePaths = {"manufacturer", "title"}) (1)
List<Product> findAll();
@EntityGraph(attributePaths = ["manufacturer", "title"]) (1)
abstract List<Product> findAll()
@EntityGraph(attributePaths = ["manufacturer", "title"]) (1)
abstract override fun findAll(): List<Product>
1 The attributePaths member is used to specify the paths to include in the Entity graph.

5.7 Explicit Queries

If you want to have more control over the JPA-QL query then you can use the @Query annotation to specify an explicit query:

@Query("FROM Book b WHERE b.title = :t ORDER BY b.title")
List<Book> listBooks(String t);
@Query("FROM Book b WHERE b.title = :t ORDER BY b.title")
List<Book> listBooks(String t)
@Query("FROM Book b WHERE b.title = :t ORDER BY b.title")
fun listBooks(t: String): List<Book>

You specify named parameters using colon (:) followed by the name and these must match a parameter specified to the method otherwise a compilation error will occur.

Currently Micronaut Data does not parse the JPA-QL AST and perform any further type checking hence greater care should be taken when using explicit queries. This may change in a future version of Micronaut Data.

Note that if the method returns a Page for pagination then you must additionally specify a query that performs the equivalent count using the countQuery member of the @Query annotation.

5.8 Modifying Queries with @Where

You can use the @Where annotation to modify compile time generated query with additional query criterion.

A common use case for this is to implement soft delete. For example considering the following User entity which declares an enabled property:

package example;

import io.micronaut.data.annotation.*;

@MappedEntity
@Where("@.enabled = true") (1)
public class User {
    @GeneratedValue
    @Id
    private Long id;
    private String name;
    private boolean enabled = true; (2)

    public User(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isEnabled() {
        return enabled;
    }

    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }
}
package example

import groovy.transform.EqualsAndHashCode
import io.micronaut.data.annotation.*

@MappedEntity
@Where("@.enabled = true") (1)
@EqualsAndHashCode(includes = "name")
class User {
    @GeneratedValue
    @Id
    Long id
    String name
    boolean enabled = true (2)

    User(String name) {
        this.name = name
    }
}
package example

import io.micronaut.data.annotation.Where
import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.Id


@Entity
@Where("@.enabled = true") (1)
data class User(
    @GeneratedValue
    @Id
    var id: Long,
    val name: String,
    val enabled: Boolean (2)
)
1 The @Where annotation is used to declare that all queries should include enabled = true and @ is a placeholder for the query’s alias.
2 An enabled property exists on the entity

You can then easily modify the delete operations to instead issue an update. For example, consider the following repository implementation:

package example;

import io.micronaut.core.annotation.NonNull;
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.CrudRepository;
import javax.validation.constraints.NotNull;
import java.util.List;

@JdbcRepository(dialect = Dialect.H2)
public interface UserRepository extends CrudRepository<User, Long> { (1)

    @Override
    @Query("UPDATE user SET enabled = false WHERE id = :id") (2)
    void deleteById(@NonNull @NotNull Long id);

    @Query("SELECT * FROM user WHERE enabled = false") (3)
    List<User> findDisabled();
}
package example

import io.micronaut.core.annotation.NonNull
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.CrudRepository

import javax.validation.constraints.NotNull

@JdbcRepository(dialect = Dialect.H2)
interface UserRepository extends CrudRepository<User, Long> { (1)

    @Override
    @Query("UPDATE user SET enabled = false WHERE id = :id") (2)
    void deleteById(@NonNull @NotNull Long id)

    @Query("SELECT * FROM user WHERE enabled = false") (3)
    List<User> findDisabled()
}
package 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.CrudRepository
import javax.validation.constraints.NotNull

@JdbcRepository(dialect = Dialect.H2)
interface UserRepository : CrudRepository<User, Long> { (1)

    @Query("UPDATE user SET enabled = false WHERE id = :id") (2)
    override fun deleteById(@NotNull id: Long)

    @Query("SELECT * FROM user WHERE enabled = false") (3)
    fun findDisabled(): List<User>
}
1 The interface extends CrudRepository
2 The deleteById is overridden to perform a soft delete by setting enabled to false.
3 An additional method is added to return disabled entities if needed using an explicit query.

All other queries performed on the entity will include enabled = true in the query statement.

5.9 Native Queries

When using Micronaut Data with JPA you can execute native SQL queries by setting nativeQuery to true in the @Query annotation:

@Query(value = "select * from books b where b.title like :title limit 5",
       nativeQuery = true)
List<Book> findNativeBooks(String title);
@Query(value = "select * from books b where b.title like :title limit 5",
        nativeQuery = true)
List<Book> findNativeBooks(String title)
@Query(value = "select * from books b where b.title like :title limit 5", nativeQuery = true)
fun findNativeBooks(title: String): List<Book>

The above example will execute the raw SQL against the database.

For Pagination queries that return a Page you also need to specify a native countQuery.

5.10 Asynchronous Queries

Micronaut Data supports asynchronous query execution by defining methods that return either CompletionStage, CompletableFuture or Future.

In the case of asynchronous execution and if the backing implementation is blocking, Micronaut Data will use the Configured I/O thread pool to schedule the query execution on a different thread.

The following is an example of a couple of asynchronous methods:

@Repository
public interface ProductRepository extends CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join("manufacturer")
    CompletableFuture<Product> findByNameContains(String str);

    CompletableFuture<Long> countByManufacturerName(String name);
}
@Repository
abstract class ProductRepository implements CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join("manufacturer")
    abstract CompletableFuture<Product> findByNameContains(String str)

    abstract CompletableFuture<Long> countByManufacturerName(String name)
}
@Repository
abstract class ProductRepository : CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    @Join("manufacturer")
    abstract fun findByNameContains(str: String): CompletableFuture<Product>

    abstract fun countByManufacturerName(name: String): CompletableFuture<Long>
}

The above example defines two methods that use CompletableFuture as return type, the API for which you can use to compose query operations:

long total = productRepository.findByNameContains("o")
        .thenCompose(product -> productRepository.countByManufacturerName(product.getManufacturer().getName()))
        .get(1000, TimeUnit.SECONDS);

Assertions.assertEquals(
        2,
        total
);
when:"A result is retrieved using async composition"
long total = productRepository.findByNameContains("o")
        .thenCompose { product -> productRepository.countByManufacturerName(product.manufacturer.name) }
        .get(1000, TimeUnit.SECONDS)

then:"the result is correct"
total == 2
val total = productRepository.findByNameContains("o")
        .thenCompose { product -> productRepository.countByManufacturerName(product.manufacturer.name) }
        .get(1000, TimeUnit.SECONDS)

assertEquals(
        2,
        total
)
In the case of JPA each operation will run with its own transaction and session, hence care needs to be taken to fetch the correct data and avoid detached objects. In addition for more complex operations it may be more efficient to write custom code that uses a single session.

5.11 Reactive Queries

Micronaut Data supports reactive query execution by defining methods that return either Publisher, Reactor or a RxJava 2 type. If you use Kotlin, you can use coroutines and Flow.

In the case of reactive execution and if the backing implementation is blocking, Micronaut Data will use the Configured I/O thread pool to schedule the query execution on a different thread.

If the backing implementation natively supports reactive types at the driver level then the I/O thread pool is not used and instead it is assumed the driver will handle the query in a non-blocking manner.

The following is an example of a couple of reactive methods:

@Join("manufacturer")
Maybe<Product> queryByNameContains(String str);

Single<Long> countDistinctByManufacturerName(String name);
@Join("manufacturer")
abstract Maybe<Product> queryByNameContains(String str)

abstract Single<Long> countDistinctByManufacturerName(String name)
@Join("manufacturer")
abstract fun queryByNameContains(str: String): Maybe<Product>

abstract fun countDistinctByManufacturerName(name: String): Single<Long>

The above example defines two methods that use reactive return types from RxJava 2, the API for which you can use to compose query operations:

long total = productRepository.queryByNameContains("o")
        .flatMap(product -> productRepository.countDistinctByManufacturerName(product.getManufacturer().getName())
                                .toMaybe())
        .defaultIfEmpty(0L)
        .blockingGet();

Assertions.assertEquals(
        2,
        total
);
when:"A result is retrieved with reactive composition"
long total = productRepository.queryByNameContains("o")
        .flatMap { product -> productRepository.countDistinctByManufacturerName(product.manufacturer.name).toMaybe() }
        .defaultIfEmpty(0L)
        .blockingGet()

then:"The result is correct"
total == 2
val total = productRepository.queryByNameContains("o")
        .flatMap { product ->
            productRepository.countDistinctByManufacturerName(product.manufacturer.name)
                    .toMaybe()
        }
        .defaultIfEmpty(0L)
        .blockingGet()

assertEquals(
        2,
        total
)

In the case of JPA each operation will run with its own transaction and session, hence care needs to be taken to fetch the correct data and avoid detached objects.

In addition for more complex operations it may be more efficient to write custom code that uses a single session.

5.12 JPA specifications

Currently supported only by JPA runtime.

Based on the same concept as Spring Data, when you need to create queries dynamically by composing JPA criteria then you can implement the JpaSpecificationExecutor interface which provides multiple methods that receive an instance of Specification which can be used in combination with existing repository interfaces.

The Specification interface represents a simple Criteria-based API entry point:

public interface Specification<T> {

    @Nullable
    Predicate toPredicate(@NonNull Root<T> root,
                          @NonNull CriteriaQuery<?> query,
                          @NonNull CriteriaBuilder criteriaBuilder);

}

The following example implementation demonstrates custom entity filtering using specifications:

class Specifications {

    public static Specification<Product> nameEquals(String name) {
        return (root, query, criteriaBuilder)
                -> criteriaBuilder.equal(root.get("name"), name);
    }

    public static Specification<Product> nameEqualsCaseInsensitive(String name) {
        return (root, query, criteriaBuilder)
                -> criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase());
    }
}
static class Specifications {

    static Specification<Product> nameEquals(String name) {
        return (root, query, criteriaBuilder)
                -> criteriaBuilder.equal(root.get("name"), name)
    }

    static Specification<Product> nameEqualsCaseInsensitive(String name) {
        return (root, query, criteriaBuilder)
                -> criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase());
    }
}
object Specifications {

    fun nameEquals(name: String) = Specification<Product> { root, _, criteriaBuilder ->
        criteriaBuilder.equal(root.get<String>("name"), name)
    }

    fun nameEqualsCaseInsensitive(name: String) = Specification<Product> { root, _, criteriaBuilder ->
        criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase())
    }
}

You can create default methods in your repository class and provide dynamic implementation with a combination of multiple specifications:

@Repository
public interface ProductRepository extends CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {

    @Transactional
    default List<Product> findByName(String name, boolean caseInsensitive, boolean includeBlank) {
        Specification<Product> specification;
        if (caseInsensitive) {
            specification = Specifications.nameEqualsCaseInsensitive(name);
        } else {
            specification = Specifications.nameEquals(name);
        }
        if (includeBlank) {
            specification = specification.or(Specifications.nameEquals(""));
        }
        return findAll(specification);
    }

    class Specifications {

        public static Specification<Product> nameEquals(String name) {
            return (root, query, criteriaBuilder)
                    -> criteriaBuilder.equal(root.get("name"), name);
        }

        public static Specification<Product> nameEqualsCaseInsensitive(String name) {
            return (root, query, criteriaBuilder)
                    -> criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase());
        }
    }

}
@Repository
abstract class ProductRepository implements CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {

    @Transactional
    List<Product> findByName(String name, boolean caseInsensitive, boolean includeBlank) {
        Specification<Product> specification
        if (caseInsensitive) {
            specification = Specifications.nameEqualsCaseInsensitive(name)
        } else {
            specification = Specifications.nameEquals(name)
        }
        if (includeBlank) {
            specification = specification | Specifications.nameEquals("")
        }
        return findAll(specification)
    }

    static class Specifications {

        static Specification<Product> nameEquals(String name) {
            return (root, query, criteriaBuilder)
                    -> criteriaBuilder.equal(root.get("name"), name)
        }

        static Specification<Product> nameEqualsCaseInsensitive(String name) {
            return (root, query, criteriaBuilder)
                    -> criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase());
        }
    }

}
@Repository
abstract class ProductRepository : CrudRepository<Product, Long>, JpaSpecificationExecutor<Product> {

    @Transactional
    fun findByName(name: String, caseInsensitive: Boolean, includeBlank: Boolean): List<Product> {
        var specification = if (caseInsensitive) {
            Specifications.nameEqualsCaseInsensitive(name)
        } else {
            Specifications.nameEquals(name)
        }
        if (includeBlank) {
            specification = specification.or(Specifications.nameEquals(""))
        }
        return findAll(specification)
    }

    object Specifications {

        fun nameEquals(name: String) = Specification<Product> { root, _, criteriaBuilder ->
            criteriaBuilder.equal(root.get<String>("name"), name)
        }

        fun nameEqualsCaseInsensitive(name: String) = Specification<Product> { root, _, criteriaBuilder ->
            criteriaBuilder.equal(criteriaBuilder.lower(root.get("name")), name.toLowerCase())
        }
    }

}
In Micronaut Data, the preferred way is to have build-time generated queries. It’s recommended to use Criteria-based API only for queries that need to be generated dynamically at the runtime.

6 Inserting and modifying data

There are various ways to perform read/write operations with Micronaut Data interfaces:

6.1 Inserting

To insert data the simplest form is to define a method that accepts the type of the entity, the same way as the CrudRepository interface does:

Book save(Book entity);
Book save(Book entity)
fun save(entity: Book): Book

The method must accept a single argument that is the entity and start with either save, persist, insert or store, to persist multiple entities the method needs to accept java.lag.Iterable of the entity.

Alternatively you can also define a method that features parameter names that match the properties of the entity name:

Book persist(String title, int pages);
Book persist(String title, int pages)
fun persist(title: String, pages: Int): Book

In this case, when update of whole entity is intended, you must specify parameters for all properties other than those that are declared as @Nullable or as a @GeneratedValue, if you do not a compilation error will occur.

The insert method can have a custom query defined by @Query annotation:

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insert(String title, int pages);

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insertOne(Book book);

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insertMany(Iterable<Book> books);
@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insert(String title, int pages)

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insertOne(Book entity)

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
void insertMany(Iterable<Book> entities)
@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
fun insert(title: String, pages: Int)

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
fun insertOne(book: Book)

@Query("INSERT INTO Book(title, pages) VALUES (:title, :pages)")
fun insertMany(books: Iterable<Book>)
It is not possible to use the entity as the return type in partial updates because it would require an additional select to retrieve the additional information. A number type (int, long, etc) can be returned to indicate the number of rows updated. The updated row count should be checked in most scenarios to ensure the update actually affected the row.

6.2 Updating

To update an entity you can once again pass the entity to the update method:

Book update(Book newBook);
Book update(Book newBook)
fun update(newBook: Book): Book

However, generally it is more efficient to use batch updates to only update the properties that have actually changed.

There are a couple of ways to achieve batch updates. One way is to define a method that features an argument annotated with @Id, starts with the stem update:

void update(@Id Long id, int pages);
void update(@Id Long id, int pages)
fun update(@Id id: Long?, pages: Int)

In this case the ID of the entity will be used to query and perform an update on the entity with all the remaining arguments (in this case pages). If an argument does not match an existing property of the entity a compilation error will occur.

Another alternative is to use updateBy* (the method should again return void or a Number indicating the number of records that were updated):

void updateByTitle(String title, int pages);
void updateByTitle(String title, int pages)
fun updateByTitle(title: String, pages: Int)

In this case you can use any finder expression to query on arbitrary properties and any remaining arguments that don’t form part of the query expression are used for the update. Once again if one of the remaining arguments does not match an existing property of the entity a compilation error will occur.

You can also specify a custom query for the update methods:

@Query("UPDATE book SET title = :title where id = :id")
void updateOne(Book book);

@Query("UPDATE book SET title = :title where id = :id")
void updateMany(Iterable<Book> books);
@Query("UPDATE book SET title = :title where id = :id")
void updateOne(Book book)

@Query("UPDATE book SET title = :title where id = :id")
void updateMany(Iterable<Book> books)
@Query("UPDATE book SET title = :title where id = :id")
fun updateOne(book: Book)

@Query("UPDATE book SET title = :title where id = :id")
fun updateMany(books: Iterable<Book>)

6.3 Deleting

Deleting can be performed in a number of ways. To delete everything (use with care!) you can use deleteAll:

void deleteAll();
void deleteAll()
override fun deleteAll()
deleteAll does not cascade. Delete all foreign key references first or use delete on all individual items.

To delete by ID or by the value of a property you can specify a parameter that matches a property of an entity:

void delete(String title);
void delete(String title)
fun delete(title: String)

Finally, you can also use the deleteBy* pattern (the method must start with delete, remove, erase or eliminate) and any finder expression, for example:

void deleteByTitleLike(String title);
void deleteByTitleLike(String title)
fun deleteByTitleLike(title: String)

You can also specify A custom query for a delete method:

@Query("DELETE FROM Book WHERE title = :title")
void deleteOne(Book book);

@Query("DELETE FROM Book WHERE title = :title")
void deleteMany(Iterable<Book> books);
@Query("DELETE FROM Book WHERE title = :title")
void deleteOne(Book book)

@Query("DELETE FROM Book WHERE title = :title")
void deleteMany(Iterable<Book> books)
@Query("DELETE FROM Book WHERE title = :title")
fun deleteOne(book: Book)

@Query("DELETE FROM Book WHERE title = :title")
fun deleteMany(books: Iterable<Book>)

6.4 Entity Timestamps

It is common to want to add a field that represents the time when an entity was first persisted and the time when it was last updated.

You can annotate a property that is a date type of an entity with @DateCreated which will be automatically populated when saving entities and indicates the date a record was created.

You can also annotate a property that is a date type of an entity with @DateUpdated which will be automatically populated whenever the entity is updated either via the persist method or when using one of the batch update methods of Micronaut Data.

If you update the entity with an external SQL statement or custom logic you will need to update the underlying DateUpdated column manually.

6.5 Entity Events

Since 2.3, Micronaut Data supports defining entity event listeners for either JPA or JDBC using either annotations or by implementation the EntityEventListener interface.

The following table lists the available event annotations:

Table 1. Entity Event Listener Annotations

Annotation

Description

@PrePersist

Triggered prior to persisting an object

@PostPersist

Triggered after persisting an object

@PreRemove

Triggered prior to deleting an object (note: doesn’t apply to batch deletes)

@PostRemove

Triggered after to deleting an object (note: doesn’t apply to batch deletes)

@PreUpdate

Triggered prior to updating an object (note: doesn’t apply to batch updates)

@PostUpdate

Triggered after updating an object (note: doesn’t apply to batch updates)

You can also use the JPA annotations in the javax.persistence package if you prefer.

Each event listener annotation can be applied to an instance method of an entity class (a JPA entity or a class annotated with ann:data.annotation.MappedEntity) in which case the method must return void and have zero arguments for example:

package example;

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.PrePersist;
import java.nio.charset.StandardCharsets;
import java.time.MonthDay;
import java.util.Base64;

@Entity
public class Account {
    @GeneratedValue
    @Id
    private Long id;
    private String username;
    private String password;
    @Column(columnDefinition = "date")
    @Convert(converter = MonthDayDateAttributeConverter.class)
    private MonthDay paymentDay;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public MonthDay getPaymentDay() {
        return paymentDay;
    }

    public void setPaymentDay(MonthDay paymentDay) {
        this.paymentDay = paymentDay;
    }

    @PrePersist
    void encodePassword() {
        this.password = Base64.getEncoder()
                .encodeToString(this.password.getBytes(StandardCharsets.UTF_8));
    }
}
package example

import javax.persistence.*
import java.nio.charset.StandardCharsets

@Entity
class Account {
    @GeneratedValue
    @Id
    Long id
    String username
    String password

    @PrePersist
    void encodePassword() {
        this.password = Base64.encoder
                .encodeToString(this.password.getBytes(StandardCharsets.UTF_8))
    }
}
package example

import java.nio.charset.StandardCharsets
import java.util.*
import javax.persistence.*

@Entity
data class Account(@GeneratedValue @Id
                   var id: Long? = null,
                   val username: String,
                   var password: String) {

    @PrePersist
    fun encodePassword() {
        password = Base64.getEncoder()
            .encodeToString(password.toByteArray(StandardCharsets.UTF_8))
    }
}

The above example defines a @PrePersist listener that encodes the password (in a not very secure base64 format, clearly not recommended!) prior to inserting into the database.

In addition, the annotations can be applied to any instance method of a Micronaut bean, in which case the method must return void and have a single argument that is the entity type (note the type can be Object to listener for all events). For example:

package example;

import io.micronaut.data.annotation.event.PrePersist;

import jakarta.inject.Singleton;

@Singleton
public class AccountUsernameValidator {
    @PrePersist
    void validateUsername(Account account) {
        final String username = account.getUsername();
        if (username == null || !username.matches("[a-z0-9]+")) {
            throw new IllegalArgumentException("Invalid username");
        }
    }
}
package example

import io.micronaut.data.annotation.event.PrePersist

import jakarta.inject.Singleton

@Singleton
class AccountUsernameValidator {
    @PrePersist
    void validateUsername(Account account) {
        final String username = account.username
        if (!username || !(username ==~ /[a-z0-9]+/)) {
            throw new IllegalArgumentException("Invalid username")
        }
    }
}
package example

import io.micronaut.data.annotation.event.PrePersist
import jakarta.inject.Singleton

@Singleton
class AccountUsernameValidator {
    @PrePersist
    fun validateUsername(account: Account) {
        val username: String = account.username
        require(username.matches("[a-z0-9]+".toRegex())) { "Invalid username" }
    }
}

The above listener serves to validate the account username prior to any insert.

Finally, it is also possible to define a Micronaut bean that implements the EntityEventListener interface or one of the functional interfaces that are sub-interfaces of the EntityEventListener listed in the following table:

Table 2. Entity Event Listener Interfaces

Interface

Description

PrePersistListener

Triggered prior to persisting an object

@PostPersistListener

Triggered after persisting an object

@PreRemoveListener

Triggered prior to deleting an object (note: doesn’t apply to batch deletes)

@PostRemoveListener

Triggered after to deleting an object (note: doesn’t apply to batch deletes)

@PreUpdateListener

Triggered prior to updating an object (note: doesn’t apply to batch updates)

@PostUpdateListener

Triggered after updating an object (note: doesn’t apply to batch updates)

For example the following Micronaut factory bean defines listeners that are executed before and after the Book entity is persisted:

package example;

import io.micronaut.context.annotation.Factory;
import io.micronaut.data.event.listeners.PostPersistEventListener;
import io.micronaut.data.event.listeners.PrePersistEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jakarta.inject.Singleton;

@Factory
public class BookListeners {
    private static final Logger LOG = LoggerFactory.getLogger(BookListeners.class);

    @Singleton
    PrePersistEventListener<Book> beforeBookPersist() { (1)
        return (book) -> {
            LOG.debug("Inserting book: {}", book.getTitle() );
            return true; (2)
        };
    }

    @Singleton
    PostPersistEventListener<Book> afterBookPersist() { (3)
        return (book) -> LOG.debug("Book inserted: {}", book.getTitle() );
    }
}
package example

import io.micronaut.context.annotation.Factory
import io.micronaut.data.event.listeners.PostPersistEventListener
import io.micronaut.data.event.listeners.PrePersistEventListener
import org.slf4j.Logger
import org.slf4j.LoggerFactory

import jakarta.inject.Singleton

@Factory
class BookListeners {
    private static final Logger LOG = LoggerFactory.getLogger(BookListeners)

    @Singleton
    PrePersistEventListener<Book> beforeBookPersist() { (1)
        return (book) -> {
            LOG.debug "Inserting book: ${book.title}"
            return true (2)
        }
    }

    @Singleton
    PostPersistEventListener<Book> afterBookPersist() { (3)
        return (book) -> LOG.debug("Book inserted: ${book.title}")
    }
}
package example

import io.micronaut.context.annotation.Factory
import io.micronaut.data.event.listeners.PostPersistEventListener
import io.micronaut.data.event.listeners.PrePersistEventListener
import org.slf4j.LoggerFactory
import jakarta.inject.Singleton

@Factory
class BookListeners {
    @Singleton
    fun beforeBookPersist(): PrePersistEventListener<Book> { (1)
        return PrePersistEventListener { book: Book ->
            LOG.debug("Inserting book: ${book.title}")
            true (2)
        }
    }

    @Singleton
    fun afterBookPersist(): PostPersistEventListener<Book> { (3)
        return PostPersistEventListener { book: Book ->
            LOG.debug("Book inserted: ${book.title}")
        }
    }

    companion object {
        private val LOG = LoggerFactory.getLogger(BookListeners::class.java)
    }
}
1 The factory returns a bean of type PrePersistListener that includes Book as the generic argument
2 The PrePersistListener can return false if the operation should not proceed, if this case true is returned
3 An additional @PostPersistListener event listener is defined

6.6 Transactions

Micronaut Data will automatically manage transactions for you. You can simply declare a method as transactional with the javax.transaction.Transactional annotation.

If you prefer Spring-managed transactions you can add the micronaut-data-spring dependency and Spring-managed transactions will be used instead. See the section on Spring Support for more information.

Micronaut Data maps the declared transaction annotation to the correct underlying semantics and compilation time.

Micronaut Data will also automatically apply read-only transactional semantics to query methods and write transaction semantics to write operations.

6.6.1 Programmatic Transactions

You can use the SynchronousTransactionManager API to perform programmatic transactions.

The following demonstrates an example:

package example;

import io.micronaut.transaction.SynchronousTransactionManager;
import jakarta.inject.Singleton;
import javax.persistence.EntityManager;
import java.sql.Connection;

@Singleton
public class ProductManager {

    private final EntityManager entityManager;
    private final SynchronousTransactionManager<Connection> transactionManager;

    public ProductManager(
            EntityManager entityManager,
            SynchronousTransactionManager<Connection> transactionManager) { (1)
        this.entityManager = entityManager;
        this.transactionManager = transactionManager;
    }

    Product save(String name, Manufacturer manufacturer) {
        return transactionManager.executeWrite(status -> { (2)
            final Product product = new Product(name, manufacturer);
            entityManager.persist(product);
            return product;
        });
    }

    Product find(String name) {
        return transactionManager.executeRead(status -> (3)
                entityManager.createQuery("from Product p where p.name = :name", Product.class)
                    .setParameter("name", name)
                    .getSingleResult()
        );
    }
}
package example

import io.micronaut.transaction.SynchronousTransactionManager
import jakarta.inject.Singleton
import javax.persistence.EntityManager
import java.sql.Connection

@Singleton
class ProductManager {

    private final EntityManager entityManager
    private final SynchronousTransactionManager<Connection> transactionManager

    ProductManager(
            EntityManager entityManager,
            SynchronousTransactionManager<Connection> transactionManager) { (1)
        this.entityManager = entityManager
        this.transactionManager = transactionManager
    }

    Product save(String name, Manufacturer manufacturer) {
        return transactionManager.executeWrite { (2)
            final product = new Product(name, manufacturer)
            entityManager.persist(product)
            return product
        }
    }

    Product find(String name) {
        return transactionManager.executeRead { (3)
            entityManager.createQuery("from Product p where p.name = :name", Product)
                    .setParameter("name", name)
                    .singleResult
        }
    }
}
package example

import io.micronaut.transaction.SynchronousTransactionManager
import java.sql.Connection
import jakarta.inject.Singleton
import javax.persistence.EntityManager

@Singleton
class ProductManager(
        private val entityManager: EntityManager,
        private val transactionManager: SynchronousTransactionManager<Connection>) (1)
{

    fun save(name: String, manufacturer: Manufacturer): Product {
        return transactionManager.executeWrite { (2)
            val product = Product(0, name, manufacturer)
            entityManager.persist(product)
            product
        }
    }

    fun find(name: String): Product {
        return transactionManager.executeRead {  (3)
            entityManager.createQuery("from Product p where p.name = :name", Product::class.java)
                    .setParameter("name", name)
                    .singleResult
        }
    }
}
1 The constructor is injected with the SynchronousTransactionManager and a transaction-aware EntityManager
2 The save method uses the executeWrite method to execute a write transaction within the context of the passed lambda.
3 The find method uses the executeRead method to execute a read-only transaction within the context of the passed lambda.

Note that if you are using Micronaut Data JDBC then instead of an EntityManager you should inject a transaction-aware JDBC Connection object.

The following presents an example:

package example;

import io.micronaut.transaction.SynchronousTransactionManager;
import jakarta.inject.Singleton;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@Singleton
public class ProductManager {

    private final Connection connection;
    private final SynchronousTransactionManager<Connection> transactionManager;

    public ProductManager(
            Connection connection,
            SynchronousTransactionManager<Connection> transactionManager) { (1)
        this.connection = connection;
        this.transactionManager = transactionManager;
    }

    Product save(String name, Manufacturer manufacturer) {
        return transactionManager.executeWrite(status -> { (2)
            final Product product = new Product(name, manufacturer);
            try (PreparedStatement ps =
                         connection.prepareStatement("insert into product (name, manufacturer_id) values (?, ?)")) {
                ps.setString(1, name);
                ps.setLong(2, manufacturer.getId());
                ps.execute();
            }
            return product;
        });
    }

    Product find(String name) {
        return transactionManager.executeRead(status -> { (3)
            try (PreparedStatement ps = connection
                    .prepareStatement("select * from product p where p.name = ?")) {
                ps.setString(1, name);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) {
                        return new Product(rs.getString("name"), null);
                    }
                    return null;
                }
            }
        });
    }
}
package example

import io.micronaut.transaction.SynchronousTransactionManager
import jakarta.inject.Singleton
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet

@Singleton
class ProductManager {

    private final Connection connection
    private final SynchronousTransactionManager<Connection> transactionManager

    ProductManager(
            Connection connection,
            SynchronousTransactionManager<Connection> transactionManager) { (1)
        this.connection = connection
        this.transactionManager = transactionManager
    }

    Product save(String name, Manufacturer manufacturer) {
        return transactionManager.executeWrite { (2)
            final Product product = new Product(name, manufacturer)
            connection.prepareStatement("insert into product (name, manufacturer_id) values (?, ?)")
                .withCloseable { PreparedStatement ps ->
                    ps.setString(1, name)
                    ps.setLong(2, manufacturer.getId())
                    ps.execute()
                }
            return product
        }
    }

    Product find(String name) {
        return transactionManager.executeRead{ (3)
            connection
                .prepareStatement("select * from product p where p.name = ?").withCloseable {
                    PreparedStatement ps ->
                ps.setString(1, name)
                ps.executeQuery().withCloseable { ResultSet rs ->
                    if (rs.next()) {
                        return new Product(rs.getString("name"), null)
                    }
                    return null
                }
            }
        }
    }
}
package example

import io.micronaut.transaction.SynchronousTransactionManager
import java.sql.Connection
import jakarta.inject.Singleton

@Singleton
class ProductManager(
        private val connection: Connection,
        private val transactionManager: SynchronousTransactionManager<Connection>) (1)
{

    fun save(name: String, manufacturer: Manufacturer): Product {
        return transactionManager.executeWrite { (2)
            val product = Product(0, name, manufacturer)
            connection.prepareStatement("insert into product (name, manufacturer_id) values (?, ?)").use { ps ->
                ps.setString(1, name)
                ps.setLong(2, manufacturer.id!!)
                ps.execute()
            }
            product
        }
    }

    fun find(name: String): Product? {
        return transactionManager.executeRead { (3)
            connection
                    .prepareStatement("select * from product p where p.name = ?").use { ps ->
                        ps.setString(1, name)
                        ps.executeQuery().use { rs ->
                            if (rs.next()) {
                                return@executeRead Product(
                                        rs.getLong("id"),
                                        rs.getString("name"),
                                        null)
                            }
                            return@executeRead null
                        }
                    }
        }
    }
}
1 The constructor is injected with the SynchronousTransactionManager and a transaction-aware Connection
2 The save method uses the executeWrite method to execute a write transaction within the context of the passed lambda.
3 The find method uses the executeRead method to execute a read-only transaction within the context of the passed lambda.

Note that it is important that you always use the injected connection as Micronaut Data makes available a transaction-aware implementation that uses the connection associated with the underlying transaction.

If a transaction is not active when using this connection then a NoTransactionException will be thrown indicating you should either provide a programmatic transaction or use @Transactional.

6.6.2 Transactional Events

You can write event listeners that are transaction aware using the @TransactionalEventListener annotation.

The following demonstrates an example:

package example;

import io.micronaut.context.event.ApplicationEventPublisher;
import io.micronaut.transaction.annotation.TransactionalEventListener;
import jakarta.inject.Singleton;
import javax.transaction.Transactional;

@Singleton
public class BookManager {
    private final BookRepository bookRepository;
    private final ApplicationEventPublisher<NewBookEvent> eventPublisher;

    public BookManager(BookRepository bookRepository, ApplicationEventPublisher<NewBookEvent> eventPublisher) { (1)
        this.bookRepository = bookRepository;
        this.eventPublisher = eventPublisher;
    }

    @Transactional
    void saveBook(String title, int pages) {
        final Book book = new Book(title, pages);
        bookRepository.save(book);
        eventPublisher.publishEvent(new NewBookEvent(book)); (2)
    }

    @TransactionalEventListener
    void onNewBook(NewBookEvent event) {
        System.out.println("book = " + event.book); (3)
    }

    static class NewBookEvent {
        final Book book;

        public NewBookEvent(Book book) {
            this.book = book;
        }
    }
}
package example

import io.micronaut.context.event.ApplicationEventPublisher
import io.micronaut.transaction.annotation.TransactionalEventListener
import jakarta.inject.Singleton
import javax.transaction.Transactional

@Singleton
class BookManager {
    private final BookRepository bookRepository
    private final ApplicationEventPublisher<NewBookEvent> eventPublisher

    BookManager(BookRepository bookRepository, ApplicationEventPublisher<NewBookEvent> eventPublisher) { (1)
        this.bookRepository = bookRepository
        this.eventPublisher = eventPublisher
    }

    @Transactional
    void saveBook(String title, int pages) {
        final Book book = new Book(title, pages)
        bookRepository.save(book)
        eventPublisher.publishEvent(new NewBookEvent(book)) (2)
    }

    @TransactionalEventListener
    void onNewBook(NewBookEvent event) {
        println("book = $event.book") (3)
    }

    static class NewBookEvent {
        final Book book

        NewBookEvent(Book book) {
            this.book = book
        }
    }
}
package example

import io.micronaut.context.event.ApplicationEventPublisher
import io.micronaut.transaction.annotation.TransactionalEventListener
import jakarta.inject.Singleton
import javax.transaction.Transactional

@Singleton
open class BookManager(
        private val bookRepository: BookRepository, private val eventPublisher: ApplicationEventPublisher<NewBookEvent>) { (1)

    @Transactional
    open fun saveBook(title: String, pages: Int) {
        val book = Book(0, title, pages)
        bookRepository.save(book)
        eventPublisher.publishEvent(NewBookEvent(book)) (2)
    }

    @TransactionalEventListener
    open fun onNewBook(event: NewBookEvent) {
        println("book = ${event.book}") (3)
    }

    class NewBookEvent(val book: Book)
}
1 The BookManager class receives an instance of ApplicationEventPublisher.
2 When the event is published if there is a running transaction then it will only trigger the listener once the transaction is committed.
3 The listener itself is annotated with @TransactionalEventListener
You can set the value of the @TransactionalEventListener annotation to bind the listener to a particular transaction phase.

7 How Micronaut Data Works

Micronaut Data uses two key features of Micronaut: The TypeElementVisitor API and Introduction Advice.

Micronaut Data defines a RepositoryTypeElementVisitor that at compilation time visits all interfaces in the source tree that are annotated with the @Repository annotation.

The RepositoryTypeElementVisitor uses service loader to load all available MethodCandidate implementations and iterate over them.

You can add additional method candidates by creating a library that depends on micronaut-data-processor and defining the META-INF/services definition for the method candidate. The new library should be added to your annotation processor path.

The MethodCandidate interface features a isMethodMatch method which allows matching a MethodElement. Once a MethodElement has been matched the buildMatchInfo method of the MethodCandidate is invoked which returns an instance of MethodMatchInfo.

The constructor for MethodMatchInfo allows specifying the runtime DataInterceptor to execute, which typically differs based on the return type and behaviour required and an optional Query instance which represents the query model of the query to be executed.

The RepositoryTypeElementVisitor takes the MethodMatchInfo and converts the Query instance into the equivalent String-based query (such as JPA-QL) using the QueryBuilder that is configured by the @Repository annotation.

A binding between runtime method parameters and named query parameters is also created.

The visited MethodElement is then dynamically annotated with the following information:

  • The constructed string-based query (for example JPA-QL)

  • The parameter binding (A map containing the named parameter in the query as key and the name of the method argument as a value)

  • The runtime DataInterceptor to execute.

At runtime all the DataInterceptor has to do is retrieve the query, read the method parameter values using the parameter binding and execute the query.

8 Micronaut Data with SQL

In addition to JPA, Micronaut Data supports the generation of repositories that use native SQL. The implementation is general enough that any transport can use be used for executing the SQL queries, as of this writing JDBC and R2DBC are supported implementations for executing SQL queries.

8.1 Micronaut Data JDBC / R2DBC

Micronaut Data JDBC / R2DBC is an implementation that pre-computes native SQL queries (given a particular database dialect) and provides a repository implementation that is a simple data mapper between a native result set and an an entity.

Micronaut Data JDBC / R2DBC supports all of the features of Micronaut Data for JPA including dynamic finders, pagination, projections, Data Transfer Objects (DTO), Batch Updates, Optimistic locking and so on.

However, Micronaut Data JDBC / R2DBC is not a Object Relational Mapping (ORM) implementation and does not and will not include any of the following concepts:

  • Lazy Loading or Proxying of Associations

  • Dirty Checking

  • Persistence Contexts / Sessions

  • First Level Caching and Entity Proxies

Micronaut Data JDBC / R2DBC is designed for users who prefer a lower-level experience and working directly with SQL.

Micronaut Data JDBC / R2DBC is useful for implementing the majority of the simple SQL queries that exist in a typical application and does not include any runtime query building DSLs. For more complex queries Micronaut Data JDBC / R2DBC can be paired with one of the many great existing Java SQL DSLs out there like JOOQ, QueryDSL, Requery or even JPA.

8.1.1 Quick Start

The quickest way to get started is to create a new Micronaut application with Micronaut Launch and choose the data-jdbc or data-r2dbc, a database driver and a database migration framework features. This can also be done via the Micronaut 2.2 and above CLI:

Creating an application with the CLI
# For Maven add: --build maven
$ mn create-app --lang java example --features data-jdbc,flyway,mysql

Or via curl:

Creating an application with curl
# For Maven add to the URL: &build=maven
$ curl https://launch.micronaut.io/demo.zip?lang=java&features=data-jdbc,flyway,mysql -o demo.zip && unzip demo.zip -d demo && cd demo

8.1.1.1 JDBC

To get started with Micronaut Data JDBC add the following dependency to your annotation processor path:

annotationProcessor("io.micronaut.data:micronaut-data-processor:3.1.2-SNAPSHOT")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>3.1.2-SNAPSHOT</version>
    </path>
</annotationProcessorPaths>

For Kotlin the dependency should be in the kapt scope and for Groovy it should be in compileOnly scope.

You should then configure a compile scoped dependency on the micronaut-data-jdbc module:

implementation("io.micronaut.data:micronaut-data-jdbc:3.1.2-SNAPSHOT")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
    <version>3.1.2-SNAPSHOT</version>
</dependency>

You should also ensure you have the JDBC driver and connection pool dependencies configured. For example for H2 in-memory database driver:

runtime("com.h2database:h2")
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

And Hikari connection pool:

runtime("io.micronaut.sql:micronaut-jdbc-hikari")
<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-hikari</artifactId>
    <scope>runtime</scope>
</dependency>

Next up you need to configure at least one data source. The following snippet from application.yml is an example of configuring the default JDBC data source:

Example YAML configuration
datasources:
  default:
    url: jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    driverClassName: org.h2.Driver
    username: sa
    password: ''
    schema-generate: CREATE_DROP
    dialect: H2
The schema-generate setting is only useful for demos and testing trivial examples, for production usage it is recommended you pair Micronaut Data with a SQL migration tool such as Flyway or Liquibase.

To retrieve objects from the database you need to define a class annotated with @MappedEntity. Note that this is a meta annotation and in fact if you prefer you can use JPA annotations (only a subset are supported, more on that later). If you wish to use JPA annotations include the following compileOnly scoped dependency:

compileOnly("jakarta.persistence:jakarta.persistence-api:2.2.2")
<dependency>
    <groupId>jakarta.persistence</groupId>
    <artifactId>jakarta.persistence-api</artifactId>
    <version>2.2.2</version>
    <scope>provided</scope>
</dependency>

As above since only the annotations are used the dependency can be included only for compilation and not at runtime so you don’t drag along the rest of the API, reducing your JAR file size.

You can then define an @Entity:

package example;

import javax.persistence.*;

@Entity
public class Book {
    @Id
    @GeneratedValue
    private Long id;
    private String title;
    private int pages;

    public Book(String title, int pages) {
        this.title = title;
        this.pages = pages;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public int getPages() {
        return pages;
    }
}
package example

import javax.persistence.*

@Entity
class Book {
    @Id
    @GeneratedValue
    Long id
    private String title
    private int pages

    Book(String title, int pages) {
        this.title = title
        this.pages = pages
    }

    String getTitle() {
        return title
    }

    int getPages() {
        return pages
    }
}
package example

import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.Id

@Entity
data class Book(@Id
                @GeneratedValue
                var id: Long,
                var title: String,
                var pages: Int = 0)

Followed by an interface that extends from CrudRepository

package example;

import io.micronaut.context.annotation.Executable;
import io.micronaut.data.annotation.*;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.*;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;
import java.util.List;


@JdbcRepository(dialect = Dialect.H2)        (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    @Executable
    Book find(String title);
}
package example

import io.micronaut.data.annotation.*
import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.model.*
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.repository.CrudRepository
import java.util.List


@JdbcRepository(dialect = Dialect.H2)        (1)
interface BookRepository extends CrudRepository<Book, Long> { (2)
    Book find(String title);
}
package example

import io.micronaut.context.annotation.Executable
import io.micronaut.data.annotation.*
import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.model.*
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.repository.CrudRepository

@JdbcRepository(dialect = Dialect.H2) (1)
interface BookRepository : CrudRepository<Book, Long> { (2)
    @Executable
    fun find(title: String): Book
}
1 The interface is annotated with @JdbcRepository and specifies a dialect of H2 used to generate queries
2 The CrudRepository interface take 2 generic arguments, the entity type (in this case Book) and the ID type (in this case Long)

You can now perform CRUD (Create, Read, Update, Delete) operations on the entity. The implementation of example.BookRepository is created at compilation time. To obtain a reference to it simply inject the bean:

@Inject BookRepository bookRepository;
@Inject @Shared BookRepository bookRepository
@Inject
lateinit var bookRepository: BookRepository

Saving an Instance (Create)

To save an instance use the save method of the CrudRepository interface:

Book book = new Book("The Stand", 1000);
bookRepository.save(book);
Book book = new Book("The Stand", 1000)
bookRepository.save(book)
var book = Book(0,"The Stand", 1000)
bookRepository.save(book)
Unlike the JPA implementation there is no dirty checking so save always performs a SQL INSERT. For batch updates use an update method (see following section).

Retrieving an Instance (Read)

To read a book back use findById:

book = bookRepository.findById(id).orElse(null);
book = bookRepository.findById(id).orElse(null)
book = bookRepository.findById(id).orElse(null)

Updating an Instance (Update)

With Micronaut Data JDBC, you must manually implement an update method since the JDBC implementation doesn’t include any dirty checking or persistence session notion. So you have to define explicit update methods for updates in your repository. For example:

void update(@Id Long id, int pages);

void update(@Id Long id, String title);
void update(@Id Long id, int pages);

void update(@Id Long id, String title);
fun update(@Id id: Long?, pages: Int)

fun update(@Id id: Long?, title: String)

Which can then be called like so:

bookRepository.update(book.getId(), "Changed");
bookRepository.update(book.getId(), "Changed")
bookRepository.update(book.id, "Changed")

Deleting an Instance (Delete)

To delete an instance use deleteById:

bookRepository.deleteById(id);
bookRepository.deleteById(id)
bookRepository.deleteById(id)

Congratulations you have implemented your first Micronaut Data JDBC repository! Read on to find out more.

8.1.1.2 R2DBC

Let’s generate a new Micronaut application with Micronaut Launch and choose the data-r2dbc, mysql and flyway features.

The generated application will use MySQL since we passed the mysql feature adding dependency on the R2DBC driver for MySQL:

runtimeOnly("dev.miku:r2dbc-mysql")
<dependency>
    <groupId>dev.miku</groupId>
    <artifactId>r2dbc-mysql</artifactId>
    <scope>runtime</scope>
</dependency>

And for flyway the JDBC driver:

runtimeOnly("mysql:mysql-connector-java")
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

To create configurations for other drivers you can select the appropriate feature: oracle, postgres, sqlserver, h2 or mariadb.

Now define a SQL script that creates your initial schema in src/main/resources/db/migration. For example:

Example V1__create-schema.sql
CREATE TABLE book(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), pages INT, author_id BIGINT NOT NULL);
CREATE TABLE author(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));

You can now configure your application to connect to the database using src/main/resources/application.yml which contains the application configuration:

Example application.yml
flyway: (1)
  datasources:
    default:
      enabled: true
datasources:
  default: (2)
    url: jdbc:mysql://localhost:3306/mydatabase
r2dbc:
  datasources:
    default: (3)
      url: r2dbc:mysql:///mydatabase
1 The Flyway configuration ensures the schema migration is applied. See Micronaut Flyway for more information.
2 The Flyway configuration needs a JDBC datasource configured, this setting configures one. See datasource configuration for more information.
3 The property r2dbc.datasources.default.url is used to configure the default R2DBC ConnectionFactory
The R2DBC ConnectionFactory object can be injected anywhere in your code with dependency injection.

Now define a @MappedEntity that maps to the author table defined in the schema:

package example;

import io.micronaut.data.annotation.*;

@MappedEntity
public class Author {
    @GeneratedValue
    @Id
    private Long id;
    private final String name;

    public Author(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}
package example

import io.micronaut.data.annotation.*

@MappedEntity
class Author {
    @GeneratedValue
    @Id
    Long id
    final String name

    Author(String name) {
        this.name = name
    }
}
package example

import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity

@MappedEntity
data class Author(val name: String) {
    @GeneratedValue
    @Id
    var id: Long? = null
}

And a repository interface to access the database that extends from ReactiveStreamsRepository:

package example;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.r2dbc.annotation.R2dbcRepository;
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import javax.validation.constraints.NotNull;

@R2dbcRepository(dialect = Dialect.MYSQL) (1)
public interface AuthorRepository extends ReactiveStreamsCrudRepository<Author, Long> {
    @NonNull
    @Override
    Mono<Author> findById(@NonNull @NotNull Long aLong); (2)

    @NonNull
    @Override
    Flux<Author> findAll();
}
package example

import io.micronaut.core.annotation.NonNull
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.r2dbc.annotation.R2dbcRepository
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono

import javax.validation.constraints.NotNull

@R2dbcRepository(dialect = Dialect.MYSQL) (1)
interface AuthorRepository extends ReactiveStreamsCrudRepository<Author, Long> {
    @NonNull
    @Override
    Mono<Author> findById(@NonNull @NotNull Long aLong) (2)

    @NonNull
    @Override
    Flux<Author> findAll()
}
package example

import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.r2dbc.annotation.R2dbcRepository
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono
import javax.validation.constraints.NotNull

@R2dbcRepository(dialect = Dialect.MYSQL) (1)
interface AuthorRepository : ReactiveStreamsCrudRepository<Author, Long> {
    override fun findById(id: @NotNull Long): Mono<Author> (2)
    override fun findAll(): Flux<Author>
}
1 The @R2dbcRepository annotation can be used to specify the datasource and dialect
2 You can override methods from the super interface to specialize the default Publisher return type with a concrete implementation

You can now inject this interface into controllers and use it to perform R2DBC queries:

1 By returning a reactive type that emits many items you can stream data (either Flowable or Flux)
2 By returning a reactive type that emits a single item you return the entire response (either Single or Mono)

8.1.2 Configuration

JDBC driver

Micronaut Data JDBC requires that an appropriate java.sql.DataSource bean is configured.

You can either do this manually or use the Micronaut JDBC module which provides out-of-the-box support for configuring connection pooling with either Tomcat JDBC, Hikari, Commons DBCP or Oracle UCP.

R2DBC driver

Micronaut Data R2DBC requires driver configuration using Micronaut R2DBC

Table 1. The following drivers are available as of this writing.

Database

Dependency

The H2 database

io.r2dbc:r2dbc-h2

MySQL

org.mariadb:r2dbc-mariadb

MariaDB

dev.miku:r2dbc-mysql

Postgres

io.r2dbc:r2dbc-postgresql

SQL Server

io.r2dbc:r2dbc-mssql

Oracle

com.oracle.database.r2dbc:oracle-r2dbc

SQL Logging

You can enable SQL logging by enabling trace logging for the io.micronaut.data.query logger. For example in logback.xml:

Enabling SQL Query Logging
<logger name="io.micronaut.data.query" level="trace" />

Creating the Schema

To create the database schema it is recommended you pair Micronaut Data with a SQL migration tool such as Flyway or Liquibase.

SQL migration tools provide more complete support for creating and evolving your schema across a range of databases.

If you want to quickly test out Micronaut Data then you can set the schema-generate option of the data source to create-drop as well as the appropriate schema name:

Most of the database migration tools use JDBC driver to make DB changes. If you use R2DBC you would need to separately configure JDBC data source.
Using schema-generate
datasources:
  default:
    url: jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    driverClassName: org.h2.Driver
    username: sa
    password: ''
    schema-generate: CREATE_DROP
    dialect: H2

The schema-generate option is currently only recommended for simple applications, testing and demos and is not considered production-ready. The dialect set in configuration is the dialect that will be used to generate the schema.

Setting the Dialect

As seen in the YAML above you should also configure the dialect. Although queries are precomputed in the repository some cases (like pagination) still require the dialect to specified. The following table summarizes the supported dialects:

Table 2. Supported JDBC / R2DBC Dialects

Dialect

Description

H2

The H2 database (typically used for in-memory testing)

MYSQL

MySQL 5.5 or above

POSTGRES

Postgres 9.5 or above

SQL_SERVER

SQL Server 2012 or above

ORACLE

Oracle 12c or above

The dialect setting in configuration does not replace the need to ensure the correct dialect is set at the repository. If the dialect is H2 in configuration, the repository should have @JdbcRepository(dialect = Dialect.H2) / @R2dbcRepository(dialect = Dialect.H2). Because repositories are computed at compile time, the configuration value is not known at that time.

8.1.3 Repositories

As seen in the Quick Start JDBC / R2DBC repositories in Micronaut Data are defined as interfaces that are annotated with the @JdbcRepository annotation, @R2dbcRepository accordingly.

In multiple datasource scenario, the @io.micronaut.data.annotation.Repository annotation can be used to specify the datasource configuration to use. By default Micronaut Data will look for the default datasource.

For example:

@Repository(value = "inventoryDataSource") (1)
@JdbcRepository(dialect = Dialect.ORACLE) (2)
public interface PhoneRepository extends CrudRepository<Phone, Integer> {
    Optional<Phone> findByAssetId(@NotNull Integer assetId);
}
1 @Repository annotaiton, pointing to data source configuration 'inventoryDataSource'
2 @JdbcRepository with a specific dialect.

The entity to treat as the root entity for the purposes of querying is established either from the method signature or from the generic type parameter specified to the GenericRepository interface.

If no root entity can be established then a compilation error will occur.

The same interfaces supported by the JPA implementation are supported by JDBC.

Note that because queries are computed at compilation time the dialect you use must be specified on the repository.

It is recommended you test against your target dialect. The Test Containers project is a great solution for this. If you must test against another dialect (like H2) then you can define a subinterface that @Replaces the repository with a different dialect for the scope of testing.

Note that in addition to interfaces you can also define repositories as abstract classes:

package example;

import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.jdbc.runtime.JdbcOperations;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import javax.transaction.Transactional;
import java.sql.ResultSet;
import java.util.List;
import java.util.stream.Collectors;

@JdbcRepository(dialect = Dialect.H2)
public abstract class AbstractBookRepository implements CrudRepository<Book, Long> {

    private final JdbcOperations jdbcOperations;

    public AbstractBookRepository(JdbcOperations jdbcOperations) {
        this.jdbcOperations = jdbcOperations;
    }

    @Transactional
    public List<Book> findByTitle(String title) {
        String sql = "SELECT * FROM Book AS book WHERE book.title = ?";
        return jdbcOperations.prepareStatement(sql, statement -> {
            statement.setString(1, title);
            ResultSet resultSet = statement.executeQuery();
            return jdbcOperations.entityStream(resultSet, Book.class).collect(Collectors.toList());
        });
    }
}
package example

import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.jdbc.runtime.JdbcOperations
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.repository.CrudRepository

import javax.transaction.Transactional
import java.sql.ResultSet
import java.util.List
import java.util.stream.Collectors

@JdbcRepository(dialect = Dialect.H2)
abstract class AbstractBookRepository implements CrudRepository<Book, Long> {

    private final JdbcOperations jdbcOperations

    AbstractBookRepository(JdbcOperations jdbcOperations) {
        this.jdbcOperations = jdbcOperations
    }

    @Transactional
    List<Book> findByTitle(String title) {
        String sql = "SELECT * FROM Book AS book WHERE book.title = ?"
        return jdbcOperations.prepareStatement(sql,  { statement ->
            statement.setString(1, title)
            ResultSet resultSet = statement.executeQuery()
            return jdbcOperations.entityStream(resultSet, Book.class)
                    .collect(Collectors.toList())
        })
    }
}
package example

import io.micronaut.data.annotation.Repository
import io.micronaut.data.jdbc.runtime.JdbcOperations
import io.micronaut.data.repository.CrudRepository
import java.util.stream.Collectors

import javax.transaction.Transactional
import kotlin.streams.toList

@Repository
abstract class AbstractBookRepository(private val jdbcOperations: JdbcOperations) : CrudRepository<Book, Long> {

    @Transactional
    fun findByTitle(title: String): List<Book> {
        val sql = "SELECT * FROM Book AS book WHERE book.title = ?"
        return jdbcOperations.prepareStatement(sql) { statement ->
            statement.setString(1, title)
            val resultSet = statement.executeQuery()
            jdbcOperations.entityStream(resultSet, Book::class.java)
                    .toList()
        }
    }
}

As you can see from the above example, using abstract classes can be useful as it allows you to combine custom code that performs your own SQL queries.

The example above uses the JdbcOperations interface which simplifies executing JDBC queries within the context of transactions.

You could also inject whichever other tool you wish to use to handle more complex queries, such as QueryDSL, JOOQ, Spring JdbcTemplate etc.

8.1.3.1 Inserts and Updates

Unlike JPA/Hibernate, Micronaut Data JDBC / R2DBC is stateless and has no notion of a persistence session that requires state management.

Since there is no session, features like dirty checking are not supported. This has implications when defining repository methods for inserts and updates.

By default when saving an entity with a method like save(MyEntity) a SQL INSERT is always performed since Micronaut Data has no way to know whether the entity is associated to a particular session.

If you wish to update an entity you should instead either use update(MyEntity) or even better define an appropriate update method to update only the data you want to update, for example:

void update(@Id Long id, int pages);

void update(@Id Long id, String title);
void update(@Id Long id, int pages);

void update(@Id Long id, String title);
fun update(@Id id: Long?, pages: Int)

fun update(@Id id: Long?, title: String)

By being explicit in defining the method as an update method Micronaut Data knows to execute an UPDATE.

8.1.3.2 Optimistic locking

Optimistic locking is a strategy where you note the actual record state’s version and modify the record only when the version is the same.

To enable optimistic locking for your entity add @Version annotated field with one of the types:

  • java.lang.Integer

  • java.lang.Long

  • java.lang.Short

  • Date-time type extending java.time.Temporal

The field is going to be incremented (for number types) or replaced (for date types) on an update operation.

Micronaut Data will generate UPDATE/DELETE SQL queries with a version match: …​ WHERE rec.version = :currentVersion …​ and if the update/delete doesn’t produce any result OptimisticLockException will be thrown.

@Entity
public class Student {

    @Id
    @GeneratedValue
    private Long id;
    @Version
    private Long version;
@Entity
class Student {

    @Id
    @GeneratedValue
    Long id
    @Version
    Long version
@Entity
data class Student(
        @Id @GeneratedValue
        var id: Long?,
        @Version
        val version: Long,

It’s possible to use @Version in a partial update or a delete method, in this case the version needs to match the version of the stored record.

@Repository
public interface StudentRepository extends CrudRepository<Student, Long> {

    void update(@Id Long id, @Version Long version, String name);

    void delete(@Id Long id, @Version Long version);
}
@Repository
interface StudentRepository extends CrudRepository<Student, Long> {

    void update(@Id Long id, @Version Long version, String name)

    void delete(@Id Long id, @Version Long version)
}
@Repository
interface StudentRepository : CrudRepository<Student, Long> {

    fun update(@Id id: Long, @Version version: Long, name: String)

    fun delete(@Id id: Long, @Version version: Long)

}

8.1.3.3 Pessimistic Locking

Pessimistic locking is supported through the use of find*ForUpdate methods.

@Repository
@JdbcRepository(dialect = Dialect.POSTGRES)
public interface AccountBalanceRepository extends CrudRepository<AccountBalance, Long> {

    AccountBalance findByIdForUpdate(Long id); (1)

    @Transactional (2)
    void addToBalance(Long id, BigInteger amount) {
        AccountBalance accountBalance = findByIdForUpdate(id); (3)
        accountBalance.addAmount(amount);
        update(accountBalance); (4)
    }
}
1 The ForUpdate suffix indicates that the selected record should be locked.
2 Both read and write operations are wrapped in a single transaction.
3 A locking read is performed, preventing other queries from accessing the record.
4 The record is updated safely.

All find methods can be declared as ForUpdate:

@Repository
@JdbcRepository(dialect = Dialect.POSTGRES)
public interface BookRepository extends CrudRepository<Book, Long> {

    @Join("author")
    Optional<Book> findByIdForUpdate(Long id);

    List<Book> findAllOrderByTotalPagesForUpdate();

    List<Book> findByTitleForUpdate(String title);
}

The queries generated for these methods make use of the FOR UPDATE SQL clause or the UPDLOCK and ROWLOCK query hints in the case of SQL Server.

The semantics of the FOR UPDATE clause may vary depending on the database. Make sure to check the relevant documentation for your engine.

8.1.4 Mapping Entities

As mentioned in the Quick Start section, if you need to customize how entities map to the table and column names of the database you can use JPA annotations to do so or Micronaut Datas own annotations in the io.micronaut.data.annotation package.

An important aspect of Micronaut Data JDBC / R2DBC is that regardless whether you use JPA annotations or Micronaut Data annotations the entity classes must be compiled with Micronaut Data.

This is because Micronaut Data pre-computes the persistence model (the relationships between entities, the class/property name to table/column name mappings) at compilation time, which is one of the reasons Micronaut Data JDBC can startup so fast.

An example of mapping with Micronaut Data annotations can be seen below:

Micronaut Data Annotation Mapping Example
/*
 * Copyright 2017-2020 original authors
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package io.micronaut.data.tck.entities;

import io.micronaut.data.annotation.*;

import java.util.Set;
import java.util.UUID;

@MappedEntity
public class Country {

    @Id
    @AutoPopulated
    private UUID uuid;
    private String name;

    @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "country")
    private Set<CountryRegion> regions;

    public Country(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public UUID getUuid() {
        return uuid;
    }

    public void setUuid(UUID uuid) {
        this.uuid = uuid;
    }

    public Set<CountryRegion> getRegions() {
        return regions;
    }

    public void setRegions(Set<CountryRegion> regions) {
        this.regions = regions;
    }
}

8.1.4.1 SQL Annotations

The following table summarizes the different annotations and what they enable. If you are familiar with and prefer the JPA annotations then feel free to skip to the next section:

Table 1. Micronaut Data Annotations

Annotation

Description

@AutoPopulated

Meta annotation for a value that should be auto-populated by Micronaut Data (such as time stamps and UUIDs)

@DateCreated

Allows assigning a data created value (such as a java.time.Instant) prior to an insert

@DateUpdated

Allows assigning a last updated value (such as a java.time.Instant) prior to an insert

@Embeddable

Specifies that the bean is embeddable

@EmbeddedId

Specifies an embedded ID of an entity

@GeneratedValue

Specifies that the property value is generated by the database and not included in inserts

@JoinTable

Specifies a join table association

@JoinColumn

Specifies a join column mapping

@Id

Specifies the ID of an entity

@MappedEntity

Specifies the entity is mapped to the database. If your table name differs from the entity name, pass the name as the value. For example: @MappedEntity( value = "TABLE_NAME" ).

@MappedProperty

Used to customize the column name, definition and data type

@Relation

Used to specify a relationship (one-to-one, one-to-many, etc.)

@Transient

Used to specify a property is transient

@TypeDef

Used to specify the property’s data type and custom converter

@Version

Specifies the version field of an entity, enables optimistic locking

In the case of using JPA only a subset of annotations are supported including the following:

  • Basic: @Table @Id @Version @Column @Transient @Enumerated

  • Embedded definition: @Embedded @EmbeddedId @Embeddable

  • Relationship mapping: @OneToMany @OneToOne @ManyToOne @ManyToMany

  • Join specification: @JoinTable @JoinColumn

  • Type converters: @Convert @Converter and AttributeConverter interface

Micronaut Data supports both javax.persistence and jakarta.persistence packages.

Again Micronaut Data JDBC / R2DBC is not an ORM, but instead a simple data mapper so many of the concepts in JPA simply don’t apply, however for users familiar with these annotations it is handy being able to use them.

8.1.4.2 ID Generation

The default ID generation expects the database to populate a value for the ID such as an IDENTITY column.

You can remove the @GeneratedValue annotation and in this case the expectation is that you will assign an ID before calling save().

If you wish to use sequences for the ID you should invoke the SQL that generates the sequence value and assign it prior to calling save().

Automatically assigned UUIDs are also supported by adding a property annotated with @Id and @AutoPopulated.

8.1.4.3 Composite Primary Keys

Composite primary keys can be defined using either JPA or Micronaut Data annotations. A composite ID requires an additional class to represent the key. The class should define fields that correspond to the columns making up the composite key. For example:

package example;

import javax.persistence.Embeddable;
import java.util.Objects;

@Embeddable
public class ProjectId {
    private final int departmentId;
    private final int projectId;

    public ProjectId(int departmentId, int projectId) {
        this.departmentId = departmentId;
        this.projectId = projectId;
    }

    public int getDepartmentId() {
        return departmentId;
    }

    public int getProjectId() {
        return projectId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        ProjectId projectId1 = (ProjectId) o;
        return departmentId == projectId1.departmentId &&
                projectId == projectId1.projectId;
    }

    @Override
    public int hashCode() {
        return Objects.hash(departmentId, projectId);
    }
}
package example

import groovy.transform.EqualsAndHashCode
import javax.persistence.Embeddable

@EqualsAndHashCode
@Embeddable
class ProjectId {
    final int departmentId
    final int projectId

    ProjectId(int departmentId, int projectId) {
        this.departmentId = departmentId
        this.projectId = projectId
    }
}
package example

import javax.persistence.Embeddable

@Embeddable
data class ProjectId(val departmentId: Int, val projectId: Int)
It is recommended that the ID class be immutable and implement equals/hashCode. TIP: When using Java, be sure to define getters for the fields making up your composite key.

You can then declare the id property of the entity using either JPA’s @EmbeddedId or @EmbeddedId:

package example;

import javax.persistence.EmbeddedId;
import javax.persistence.Entity;

@Entity
public class Project {
    @EmbeddedId
    private ProjectId projectId;
    private String name;

    public Project(ProjectId projectId, String name) {
        this.projectId = projectId;
        this.name = name;
    }

    public ProjectId getProjectId() {
        return projectId;
    }

    public String getName() {
        return name;
    }
}
package example

import javax.persistence.EmbeddedId
import javax.persistence.Entity

@Entity
class Project {
    @EmbeddedId
    private ProjectId projectId
    private String name

    Project(ProjectId projectId, String name) {
        this.projectId = projectId
        this.name = name
    }

    ProjectId getProjectId() {
        return projectId
    }

    String getName() {
        return name
    }
}
package example

import javax.persistence.EmbeddedId
import javax.persistence.Entity

@Entity
class Project(
    @EmbeddedId val projectId: ProjectId,
    val name: String
)
To alter the column mappings for the ID, you may use the @Column annotation on the fields within the ProjectId class

8.1.4.4 Constructor Arguments

Micronaut Data JDBC / R2DBC also allows the definition of immutable objects using constructor arguments instead of getters/setters. If you define multiple constructors then the one used to create the object from the database should be annotated with io.micronaut.core.annotation.Creator.

For example:

package example;

import io.micronaut.core.annotation.Creator;

import javax.persistence.*;

@Entity
public class Manufacturer {
    @Id
    @GeneratedValue
    private Long id;
    private String name;

    @Creator
    public Manufacturer(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

}
package example

import io.micronaut.core.annotation.Creator

import javax.persistence.*

@Entity
class Manufacturer {
    @Id
    @GeneratedValue
    Long id
    final String name

    @Creator
    Manufacturer(String name) {
        this.name = name
    }
}
package example

import javax.persistence.*

@Entity
data class Manufacturer(
    @Id
    @GeneratedValue
    var id: Long?,
    val name: String
)

As you can see from the example above, the ID of the object should however include a setter since this has to be assigned from the database generated value.

8.1.4.5 SQL Naming Strategies

The default naming strategy when converting camel case class and property names to database tables and columns is to use underscore separated lower case. In other words FooBar becomes foo_bar.

If this is not satisfactory then you can customize this by setting the namingStrategy member of the @MappedEntity annotation on the entity:

Micronaut Data Naming Strategy
@MappedEntity(namingStrategy = NamingStrategies.Raw.class)
public class CountryRegion {
    ...
}

Few important things to note. Since Micronaut Data pre-computes the table and column name mappings at compilation time the specified NamingStrategy implementation must be on the annotation processor classpath (annotationProcessor scope for Java or kapt for Kotlin).

In addition if you don’t want to repeat the above annotation definition on every entity it is handy to define a meta-annotation where the above annotation definition is applied to another annotation that you add to your class.

Escaping Table/Column Name Identifiers

In some cases it may be necessary to escape table and/or column names if characters are used within the names that are invalid without the presence of escaping.

In this case you should set the escape member of the @MappedEntity annotation to true:

@MappedEntity(escape=true)

Micronaut Data will generate SQL statements that escape table and column names within queries using the escape character that is appropriate for the configured SQL dialect.

Overriding default query alias

The default query alias is the table name followed by an underscore. If you want to change it, specify it in the @MappedEntity annotation:

@MappedEntity(alias="my_table_")

8.1.4.6 Association Mapping

To specify a relation between two entities you need to use @Relation annotation. The relation kind is specified using enum @Kind value attribute which is similar to JPA relations annotation names (@OneToMany, @OneToOne etc.)

Table 1. Micronaut Data supported relations:

Kind

Description

Kind.ONE_TO_MANY

One to many association

Kind.ONE_TO_ONE

One to one association

Kind.MANY_TO_MANY

Many to many association

Kind.MANY_TO_ONE

Many to one association

Kind.EMBEDDED

Embedded association

Use 'mappedBy' to specify inverse property that this relation is mapped by.

Table 2. Micronaut Data supported association cascade types:

Type

Description

Cascade.PERSIST

Associated entity or entities are going to be persisted when owning entity is saved

Cascade.UPDATE

Associated entity or entities are going to be updated when owning entity is updated

Cascade.NONE

(Default) No operation is cascaded

Cascade.ALL

All (Cascade.PERSIST and Cascade.UPDATE) operations are cascaded

You can use JPA’s equivalent annotations @JoinTable and @JoinColumn to specify more complex mapping definition.

8.1.4.7 Association Fetching

Micronaut Data is a simple data mapper, hence it will not fetch any associations for you using techniques like lazy loading of entity proxies for single-ended associations.

You must instead specify ahead of time what data you want to fetch. You cannot map an association as being eager or lazy. The reason for this design choice is simple, even in the JPA world accessing lazy associations or lazy initialization collections is considered bad practice due to the N+1 query issue and the recommendation is always to write an optimized join query.

Micronaut Data JDBC / R2DBC takes this a step further by simply not supporting those features considered bad practice anyway. However, it does impact how you may model an association. For example, if you define an association in a constructor argument such as the following entity:

package example;

import javax.persistence.*;

@Entity
public class Product {

    @Id
    @GeneratedValue
    private Long id;
    private String name;
    @ManyToOne
    private Manufacturer manufacturer;

    public Product(String name, Manufacturer manufacturer) {
        this.name = name;
        this.manufacturer = manufacturer;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public Manufacturer getManufacturer() {
        return manufacturer;
    }
}
package example

import javax.persistence.*

@Entity
class Product {

    @Id
    @GeneratedValue
    Long id
    private String name
    @ManyToOne
    private Manufacturer manufacturer

    Product(String name, Manufacturer manufacturer) {
        this.name = name
        this.manufacturer = manufacturer
    }

    String getName() {
        return name
    }

    Manufacturer getManufacturer() {
        return manufacturer
    }
}
package example

import javax.persistence.*

@Entity
data class Product(
    @Id
    @GeneratedValue
    var id: Long?,
    var name: String,
    @ManyToOne
    var manufacturer: Manufacturer?
)

Then attempt to read the Product entity back without specifying a join an exception will occur since the manufacturer association is not Nullable.

There are few ways around this, one way is to declare at the repository level to always fetch manufacturer, another is declare the @Nullable annotation on the manufacturer argument to allow it to be declared null (or in Kotlin add ? to the end of the constructor argument name). Which approach you choose is dependent on the design of the application.

The following section provides more coverage on handling joins.

8.1.4.8 Using @ColumnTransformer

Inspired by the similar annotation in Hibernate, you can apply a transformation when either reading or writing a column from or to the database using the @ColumnTransformer annotation.

This feature can be used to encrypt/decrypt values or invoke any arbitrary database function. To define a read transformation use the read member. For example:

Applying a read transformation
@ColumnTransformer(read = "UPPER(@.name)")
private String name;
@ is a query alias placeholder and will be replaced with one if the query specifies it. Example: "UPPER(@.name) is going to become UPPER(project_.name).

To apply a write transformation you should use the write member and include exactly one ? placeholder:

Apply a write transformation
@ColumnTransformer(write = "UPPER(?)")
private String name;

With this any place any INSERT or UPDATE statement generated will include the above write entry.

8.1.4.9 JSON Column Support

You can declare a field of a class as a JSON type using the @TypeDef annotation as follows:

@TypeDef(type = DataType.JSON)
private Map<String, String> data;

The above will map to a column called data. Depending on the underling database the column type will be adjusted. For example for Postgres which features native JSON support the column type will be JSONB.

To allow JSON to be serialized and deserialized in entity properties you must have Jackson and the micronaut-runtime module your classpath.

8.1.4.10 Support for Java 16 Records

Since 2.3.0, Micronaut Data JDBC / R2DBC has support for using Java 16 records to model entities.

The following record class demonstrates this capability:

package example;

import io.micronaut.core.annotation.Nullable;
import io.micronaut.data.annotation.*;
import java.util.Date;

@MappedEntity (1)
record Book(
        @Id @GeneratedValue @Nullable Long id, (2)
        @DateCreated @Nullable Date dateCreated,
        String title,
        int pages) {
}
1 The @MappedEntity annotation is used on the record
2 The database identifier is annotated with @Id and @GeneratedValue plus marked as @Nullable

Since records are immutable constructor arguments that are generated values need to be marked as @Nullable and you should pass null for those arguments. The following presents an example:

Book book = new Book(null,null, "The Stand", 1000);
book = bookRepository.save(book);

It is important to note that the returned instance is not the same as the instance passed to the save method. When a write operation is performed Micronaut Data will use a copy-constructor approach to populate the database identifier and return a new instance from the save method.

8.1.4.11 Support for Kotlin immutable data classes

Micronaut Data JDBC / R2DBC supports using immutable Kotlin data classes as model entities. The implementation is the same as for Java 16 records: to modify an entity a copy-constructor will be used and every modification means a new entity instance.

src/main/kotlin/example/Student.kt
package example

import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.data.annotation.Relation

@MappedEntity
data class Student(
        @field:Id @GeneratedValue
        val id: Long?,
        val name: String,
        @Relation(value = Relation.Kind.MANY_TO_MANY, cascade = [Relation.Cascade.PERSIST])
        val courses: List<Course>,
        @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "student")
        val ratings: List<CourseRating>
) {
    constructor(name: String, items: List<Course>) : this(null, name, items, emptyList())
}
Generated values and relations that cannot be created during the entity initialization should be declared as nullable.

8.1.5 Data Types

Micronaut Data JDBC / R2DBC supports most common Java data types. The following properties types are supported by default:

  • All primitive types and their wrappers (int, java.lang.Integer etc.)

  • CharSequence, String etc.

  • Date types like java.util.Date, java.time.LocalDate etc.

  • Enum types (by name only)

  • Entity References. In the case of @ManyToOne the foreign key column name is computed to be the name of the association plus a suffix of _id. You can alter this with either @Column(name="..") or by providing a NamingStrategy.mappedName(..) implementation.

  • Collections of Entity. In the case of @OneToMany and if mappedBy is specified then it is expected that the inverse property exists defining the column, otherwise a join table mapping is created.

If you wish to define a custom data type then you can do so by defining a class that is annotated with @TypeDef.

8.1.6 Using Attribute Converter

There are cases where you would like to represent the attribute differently in the database than in the entity.

Consider the following example entity:

package example;

import javax.persistence.*;

@Entity
public class Sale {

    @ManyToOne
    private final Product product;
    private final Quantity quantity;

    @Id
    @GeneratedValue
    private Long id;

    public Sale(Product product, Quantity quantity) {
        this.product = product;
        this.quantity = quantity;
    }

    public Product getProduct() {
        return product;
    }

    public Quantity getQuantity() {
        return quantity;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}
package example

import javax.persistence.Id
import javax.persistence.Entity
import javax.persistence.ManyToOne
import javax.persistence.GeneratedValue

@Entity
class Sale {

    @ManyToOne
    final Product product

    final Quantity quantity

    @Id
    @GeneratedValue
    Long id

    Sale(Product product, Quantity quantity) {
        this.product = product
        this.quantity = quantity
    }
}
package example

import javax.persistence.*

@Entity
data class Sale(
    @Id
    @GeneratedValue
    var id: Long?,
    @ManyToOne
    val product: Product,
    val quantity: Quantity
)

The Sale class has a reference to a type Quantity. The Quantity type is defined as:

package example;

import io.micronaut.data.annotation.TypeDef;
import io.micronaut.data.model.DataType;

@TypeDef(type = DataType.INTEGER, converter = QuantityAttributeConverter.class)
public class Quantity {

    private final int amount;

    private Quantity(int amount) {
        this.amount = amount;
    }

    public int getAmount() {
        return amount;
    }

    public static Quantity valueOf(int amount) {
        return new Quantity(amount);
    }
}
package example

import groovy.transform.Immutable
import io.micronaut.data.annotation.TypeDef
import io.micronaut.data.model.DataType

@TypeDef(type = DataType.INTEGER, converter = QuantityAttributeConverter.class)
@Immutable
class Quantity {
    int amount
}
package example

import io.micronaut.data.annotation.TypeDef
import io.micronaut.data.model.DataType

@TypeDef(type = DataType.INTEGER, converter = QuantityAttributeConverter::class)
data class Quantity(val amount: Int)

As you can see @TypeDef is used to define the Quantity type as an INTEGER using the DataType enum.

If you cannot declare @TypeDef directly on the type then you can declare it on the field where the type is used.

The last step is to add custom attribute conversion so that Micronaut Data knows how to read and write the type from an Integer:

package example;

import io.micronaut.core.convert.ConversionContext;
import io.micronaut.data.model.runtime.convert.AttributeConverter;
import jakarta.inject.Singleton;

@Singleton (1)
public class QuantityAttributeConverter implements AttributeConverter<Quantity, Integer> {

    @Override (2)
    public Integer convertToPersistedValue(Quantity quantity, ConversionContext context) {
        return quantity == null ? null : quantity.getAmount();
    }

    @Override (3)
    public Quantity convertToEntityValue(Integer value, ConversionContext context) {
        return value == null ? null : Quantity.valueOf(value);
    }

}
package example

import groovy.transform.CompileStatic
import io.micronaut.core.convert.ConversionContext
import io.micronaut.data.model.runtime.convert.AttributeConverter
import jakarta.inject.Singleton

@Singleton (1)
@CompileStatic
class QuantityAttributeConverter implements AttributeConverter<Quantity, Integer> {

    @Override (2)
    Integer convertToPersistedValue(Quantity quantity, ConversionContext context) {
        return quantity == null ? null : quantity.getAmount()
    }

    @Override (3)
    Quantity convertToEntityValue(Integer value, ConversionContext context) {
        return value == null ? null : new Quantity(value)
    }

}
package example

import io.micronaut.core.convert.ConversionContext
import io.micronaut.data.model.runtime.convert.AttributeConverter
import jakarta.inject.Singleton

@Singleton (1)
class QuantityAttributeConverter : AttributeConverter<Quantity?, Int?> {

    (2)
    override fun convertToPersistedValue(quantity: Quantity?, context: ConversionContext): Int? {
        return quantity?.amount
    }

    (3)
    override fun convertToEntityValue(value: Int?, context: ConversionContext): Quantity? {
        return if (value == null) null else Quantity(value)
    }

}
1 The attribute converter implements @AttributeConverter and must be a bean
2 A converter from Quantity to Integer
3 A converter from Integer to Quantity
It’s possible to define the converter using @MappedProperty: @MappedProperty(converter = QuantityTypeConverter.class), in this case the data type will be detected automatically.

8.1.7 Join Queries

As discussed in the previous section, Micronaut Data JDBC doesn’t support associations in the traditional ORM sense. There is no lazy loading or support for proxies.

Consider a Product entity from the previous section that has an association to a Manufacturer entity:

package example;

import io.micronaut.core.annotation.Creator;

import javax.persistence.*;

@Entity
public class Manufacturer {
    @Id
    @GeneratedValue
    private Long id;
    private String name;

    @Creator
    public Manufacturer(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

}
package example

import io.micronaut.core.annotation.Creator

import javax.persistence.*

@Entity
class Manufacturer {
    @Id
    @GeneratedValue
    Long id
    final String name

    @Creator
    Manufacturer(String name) {
        this.name = name
    }
}
package example

import javax.persistence.*

@Entity
data class Manufacturer(
    @Id
    @GeneratedValue
    var id: Long?,
    val name: String
)

Say you query for Product instances, what happens is that by default Micronaut Data JDBC will only query for and fetch the simple properties. In the case of single ended associations like the above Micronaut Data will only retrieve the ID and assign it if is possible (In the case of entities that require constructor arguments this is not even possible).

If you need to fetch the association too then you can use the @Join annotation on your repository interface to specify that a INNER JOIN (or whichever join types is more appropriate) should be executed to retrieve the associated Manufacturer.

@JdbcRepository(dialect = Dialect.H2)
public interface ProductRepository extends CrudRepository<Product, Long> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}
@JdbcRepository(dialect = Dialect.H2)
public interface ProductRepository extends CrudRepository<Product, Long> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}
@JdbcRepository(dialect = Dialect.H2)
interface ProductRepository : CrudRepository<Product, Long> {
    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    fun list(): List<Product>
}
1 The @Join is used to indicate a INNER JOIN clause should be included.

Note that the @Join annotation is repeatable and hence can be specified multiple time for different associations. In addition, the type member of the annotation can be used to specify the join type, for example LEFT, INNER or RIGHT.

Finally, by default Micronaut Data will generate aliases to use for selecting columns in joins and querying. However, if at any point you experience a conflict you can specify an alias for a particular join using the alias member of the @Join annotation. You can override the default entity alias using the alias member of the @MappedEntity annotation.

Some databases like Oracle limit the length of alias names in SQL queries so another reason you may want to set custom aliases is to avoid exceeding the alias name length restriction in Oracle.

If you need to do anything more complex than the join options Micronaut Data has to offer then you may need a native query.

8.1.8 Explicit Queries

When using Micronaut Data with JDBC you can execute native SQL queries using the @Query annotation:

@Query("select * from book b where b.title like :title limit 5")
List<Book> findBooks(String title);
@Query("select * from book b where b.title like :title limit 5")
List<Book> findBooks(String title);
@Query("select * from book b where b.title like :title limit 5")
fun findBooks(title: String): List<Book>

The above example will execute the raw SQL against the database.

For Pagination queries that return a Page you also need to specify a native countQuery.

Explicit Queries and Joins

When writing an explicit SQL query if you specify any joins within the query you may want the resulting data bound to the returned entity. Micronaut Data will not automatically do this, instead you need to specify the associated @Join annotation.

For example:

    @Query("SELECT *, m_.name as m_name, m_.id as m_id FROM product p INNER JOIN manufacturer m_ ON p.manufacturer_id = m_.id WHERE p.name like :name limit 5")
    @Join(value = "manufacturer", alias = "m_")
    List<Product> searchProducts(String name);
    @Query("""SELECT *, m_.name as m_name, m_.id as m_id
              FROM product p
              INNER JOIN manufacturer m_ ON p.manufacturer_id = m_.id
              WHERE p.name like :name limit 5""")
    @Join(value = "manufacturer", alias = "m_")
    List<Product> searchProducts(String name);
    @Query("""SELECT *, m_.name as m_name, m_.id as m_id
                    FROM product p
                    INNER JOIN manufacturer m_ ON p.manufacturer_id = m_.id
                    WHERE p.name like :name limit 5""")
    @Join(value = "manufacturer", alias = "m_")
    fun searchProducts(name: String): List<Product>

In the above example the query uses an alias called m_ to query the manufacturer table via an INNER JOIN. Since the returned Product entity features a manufacturer association it may be nice to materialize this object as well. The alias member of the @Join annotation is used to specify which alias to materialize the Manufacturer instance from.

It is necessary to use the "logical name" of the field in the @Join (the name used in the @Entity class) and not the name used in the native query itself. In the previous example, if the name in the class were myManufacturer, then you would need to use Join(value = "myManufacturer", alias = "m_") without modifying anything on the native sql query.

8.1.9 R2DBC specifics

The R2DBC implementation is fully reactive and bring some differences comparing to JPA or Micronaut Data JDBC.

8.1.9.1 Reactive repositories

The following table summarizes the reactive repository interfaces that come with Micronaut Data and are recommended to be used with R2DBC:

Table 1. Builtin Reactive Repository Interfaces

Interface

Description

ReactiveStreamsCrudRepository

Extends GenericRepository and adds CRUD methods that return Publisher

ReactorCrudRepository

Extends ReactiveStreamsCrudRepository and is using Reactor return types

RxJavaCrudRepository

Extends GenericRepository and adds CRUD methods that return RxJava 2 types

CoroutineCrudRepository

Extends GenericRepository and is using Kotlin coroutines for reactive CRUD operations

8.1.9.2 Transactions

Micronaut Data R2DBC features Reactive transaction management support whereby you can declare javax.transaction.Transactional on your methods and a reactive transaction will be initiated, for example:

package example;

import reactor.core.publisher.Mono;

import jakarta.inject.Singleton;
import javax.transaction.Transactional;
import java.util.Arrays;

@Singleton
public class AuthorService {
    private final AuthorRepository authorRepository;
    private final BookRepository bookRepository;

    public AuthorService(AuthorRepository authorRepository, BookRepository bookRepository) { (1)
        this.authorRepository = authorRepository;
        this.bookRepository = bookRepository;
    }

    @Transactional (2)
    Mono<Void> setupData() {
        return Mono.from(authorRepository.save(new Author("Stephen King")))
                .flatMapMany((author -> bookRepository.saveAll(Arrays.asList(
                        new Book("The Stand", 1000, author),
                        new Book("The Shining", 400, author)
                ))))
                .then(Mono.from(authorRepository.save(new Author("James Patterson"))))
                .flatMapMany((author ->
                        bookRepository.save(new Book("Along Came a Spider", 300, author))
                )).then();
    }
}
package example

import reactor.core.publisher.Mono

import jakarta.inject.Singleton
import javax.transaction.Transactional

@Singleton
class AuthorService {
    private final AuthorRepository authorRepository
    private final BookRepository bookRepository

    AuthorService(AuthorRepository authorRepository, BookRepository bookRepository) { (1)
        this.authorRepository = authorRepository
        this.bookRepository = bookRepository
    }

    @Transactional (2)
    Mono<Void> setupData() {
        return Mono.from(authorRepository.save(new Author("Stephen King")))
                .flatMapMany((author -> bookRepository.saveAll([
                        new Book("The Stand", 1000, author),
                        new Book("The Shining", 400, author)
                ])))
                .then(Mono.from(authorRepository.save(new Author("James Patterson"))))
                .flatMapMany((author ->
                        bookRepository.save(new Book("Along Came a Spider", 300, author))
                )).then()
    }
}
package example

import reactor.core.publisher.Mono
import jakarta.inject.Singleton
import javax.transaction.Transactional

@Singleton
open class AuthorService(
        private val authorRepository: AuthorRepository,
        private val bookRepository: BookReactiveRepository) { (1)

    @Transactional (2)
    open fun setupData(): Mono<Void> {
        return Mono.from(authorRepository.save(Author("Stephen King")))
                .flatMapMany { author: Author ->
                    bookRepository.saveAll(listOf(
                            Book("The Stand", 1000, author),
                            Book("The Shining", 400, author)
                    ))
                }
                .then(Mono.from(authorRepository.save(Author("James Patterson"))))
                .flatMapMany { author: Author ->
                    bookRepository.save(Book("Along Came a Spider", 300, author))
                }.then()
    }
}
1 Supporting repositories are injected
2 @Transactional is used to declare a transaction

This same declarative logic can be done programmatically as well by injecting the R2dbcOperations interface:

Mono.from(operations.withTransaction(status ->
    Flux.from(authorRepository.save(new Author("Stephen King")))
            .flatMap((author -> bookRepository.saveAll(Arrays.asList(
                    new Book("The Stand", 1000, author),
                    new Book("The Shining", 400, author)
            ))))
    .thenMany(Flux.from(authorRepository.save(new Author("James Patterson"))))
        .flatMap((author ->
                bookRepository.save(new Book("Along Came a Spider", 300, author))
    )).then()
)).block();
Mono.from(operations.withTransaction(status ->
        Flux.from(authorRepository.save(new Author("Stephen King")))
                .flatMap((author -> bookRepository.saveAll([
                        new Book("The Stand", 1000, author),
                        new Book("The Shining", 400, author)
                ])))
                .thenMany(Flux.from(authorRepository.save(new Author("James Patterson"))))
                .flatMap((author ->
                        bookRepository.save(new Book("Along Came a Spider", 300, author))
                )).then()
)).block()
Mono.from(operations.withTransaction {
    Flux.from(authorRepository.save(Author("Stephen King")))
            .flatMap { author: Author ->
                bookRepository.saveAll(listOf(
                        Book("The Stand", 1000, author),
                        Book("The Shining", 400, author)
                ))
            }
            .thenMany(Flux.from(authorRepository.save(Author("James Patterson"))))
            .flatMap { author: Author -> bookRepository.save(Book("Along Came a Spider", 300, author)) }.then()
}).block()

In the above case the withTransaction method is used to initiate a transaction.

Note however, that transaction management is possibly one of the most challenging areas to get right in reactive programming since you need to propagate the transaction across the reactive flow.

Most R2DBC drivers are implemented in Project Reactor which has the ability to propagate a context across reactive operators and Micronaut Data R2DBC will populate this context and ensure the transaction is re-used if it is found within it.

However, it is still pretty easy for the context to be lost since different libraries that implement Reactive Streams don’t propagate contexts between each other so if you include RxJava or any other reactive operator library it is likely the context will be lost.

To ensure this doesn’t happen it is recommended that you annotate write operations that participate within a transaction as MANDATORY which ensures it is not possible to run these methods without a surrounding transaction present so that if the transaction is somehow lost within the reactive flow it doesn’t cause operations to be run in separate transactions:

@NonNull
@Override
@Transactional(Transactional.TxType.MANDATORY)
<S extends Book> Publisher<S> save(@NonNull @Valid @NotNull S entity);

@NonNull
@Override
@Transactional(Transactional.TxType.MANDATORY)
<S extends Book> Publisher<S> saveAll(@NonNull @Valid @NotNull Iterable<S> entities);
@NonNull
@Override
@Transactional(Transactional.TxType.MANDATORY)
<S extends Book> Publisher<S> save(@NonNull @Valid @NotNull S entity);

@NonNull
@Override
@Transactional(Transactional.TxType.MANDATORY)
<S extends Book> Publisher<S> saveAll(@NonNull @Valid @NotNull Iterable<S> entities);
@Transactional(Transactional.TxType.MANDATORY)
override suspend fun <S : Book> save(entity: S): S

@Transactional(Transactional.TxType.MANDATORY)
override fun <S : Book> saveAll(entities: Iterable<S>): Flow<S>

If the transaction is somehow lost during the reactive flow there are a couple of ways you can solve the problem. One way is to use the withTransaction method of the R2dbcOperations interface to obtain the current ReactiveTransactionStatus, you can then pass this instance into another execution of the withTransaction method or pass it directly as the last argument to any method declared on the repository itself.

An example of the former approach is presented below, using RxJava 2 this time which will cause propagation loss:

Flux.from(operations.withTransaction(status -> (1)
        Flux.from(authorRepository.save(new Author("Michael Crichton")))
                .flatMap((author -> operations.withTransaction(status, (s) -> (2)
                        bookRepository.saveAll(Arrays.asList(
                                new Book("Jurassic Park", 300, author),
                                new Book("Disclosure", 400, author)
                        )))))
)).collectList().block();
Flux.from(operations.withTransaction(status -> (1)
        Flux.from(authorRepository.save(new Author("Michael Crichton")))
                .flatMap((author -> operations.withTransaction(status, (s) -> (2)
                        bookRepository.saveAll([
                                new Book("Jurassic Park", 300, author),
                                new Book("Disclosure", 400, author)
                        ]))))
)).collectList().block()
Flux.from(operations.withTransaction { status: ReactiveTransactionStatus<Connection> ->  (1)
    Flux.from(authorRepository.save(Author("Michael Crichton")))
            .flatMap { author: Author ->
                operations.withTransaction(status) {   (2)
                    bookRepository.saveAll(listOf(
                            Book("Jurassic Park", 300, author),
                            Book("Disclosure", 400, author)
                    ))
                }
            }
}).collectList().block()
1 An outer withTransaction call starts the transaction
2 An inner call ensures the existing transaction is propagated

8.1.9.3 Reactive Entity Events

Micronaut Data R2DBC supports persistence events introduced in Micronaut Data 2.3 and above however it should be noted that these should not block and should only perform operations that don’t incur any network I/O and if they do a new thread should execute this logic.

Note that persistence events are most commonly used to pre-populate database properties prior to performing an insert (for example encoding a password etc.) these types of operations typically don’t involve blocking I/O and are safe to perform.

9 Going Native with GraalVM

Micronaut Data supports GraalVM native images for both the JPA and JDBC implementations.

The currently supported databases are:

  • H2

  • Postgres

  • Oracle

  • MariaDB

  • MS SQLServer

Micronaut Data will automatically detect the driver and configure the driver correctly for each database as appropriate.

10 Spring Data Support

Micronaut Data features general Spring support that is provided through the micronaut-data-spring dependency:

implementation("io.micronaut.data:micronaut-data-spring:3.1.2-SNAPSHOT")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-spring</artifactId>
    <version>3.1.2-SNAPSHOT</version>
</dependency>

In addition to this dependency you will need either spring-orm (for Hibernate) or spring-jdbc (for JDBC) on your classpath to enable support for Spring-based transaction management:

implementation("org.springframework:spring-orm:5.2.0.RELEASE")
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.2.0.RELEASE</version>
</dependency>

You can then compile existing Spring Data repository interfaces and use Spring annotations such as org.springframework.transaction.annotation.Transactional in your application.

You can extend from existing Spring Data interfaces such as CrudRepository, PagingAndSortingRepository and so on.

The following Spring Data types are also supported:

Spring Data JPA Specification Support

To obtain additional support for Spring Data JPA Specifications when using Hibernate and JPA you should add the following dependency to your classpath:

implementation("io.micronaut.data:micronaut-data-spring-jpa:3.1.2-SNAPSHOT")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-spring-jpa</artifactId>
    <version>3.1.2-SNAPSHOT</version>
</dependency>

You can then implement the JpaSpecificationExecutor (the generic argument to the interface should be a domain class) interface as per the Spring Data JPA documentation.