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)

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.data.annotation.*;
import io.micronaut.data.model.*;
import io.micronaut.data.repository.CrudRepository;
import java.util.List;

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

package example

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)
    Book find(String title)
}

package example

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

@Repository (1)
interface BookRepository : CrudRepository<Book, Long> { (2)
    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

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:1.0.0.M1'

<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>1.0.0.M1</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:

compile 'io.micronaut.data:micronaut-data-hibernate-jpa:1.0.0.M1'

<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-hibernate-jpa</artifactId>
    <version>1.0.0.M1</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.data.annotation.*;
import io.micronaut.data.model.*;
import io.micronaut.data.repository.CrudRepository;
import java.util.List;

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

package example

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)
    Book find(String title)
}

package example

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

@Repository (1)
interface BookRepository : CrudRepository<Book, Long> { (2)
    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 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 methods for reactive CRUD execution
RxJavaCrudRepository	Extends GenericRepository and adds methods for reactive CRUD execution using RxJava 2
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.configuration.hibernate.jpa.scope.CurrentSession;
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(@CurrentSession 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.configuration.hibernate.jpa.scope.CurrentSession
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(@CurrentSession 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.configuration.hibernate.jpa.scope.CurrentSession
import io.micronaut.data.annotation.Repository
import io.micronaut.data.repository.CrudRepository

import javax.persistence.EntityManager

@Repository
abstract class AbstractBookRepository(@param:CurrentSession 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.

4 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:

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 `Single`, `Maybe`, `Flux`, `Mono` etc.)	An Reactive Streams compatible type
Primitive/Simple Types	In the case of projections primitive/basic types can be returned

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:

Book find(String title);

Book find(String title)

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.

4.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).

4.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.

4.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.

4.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.

4.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.

4.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 javax.persistence.*;

@Entity
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> {
    Manufacturer saveManufacturer(String name);

    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}

@Repository
interface ProductRepository extends CrudRepository<Product, Long> {
    Manufacturer saveManufacturer(String name)

    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list()
}

@Repository
interface ProductRepository : CrudRepository<Product, Long> {
    fun save(manufacturer: Manufacturer) : Manufacturer

    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    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)
List<Product> findAll()

@EntityGraph(attributePaths = ["manufacturer", "title"]) (1)
override fun findAll(): List<Product>

1	The `attributePaths` member is used to specify the paths to include in the Entity graph.

4.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.

4.8 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.

4.9 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> {
    @Join("manufacturer")
    CompletableFuture<Product> findByNameContains(String str);

    CompletableFuture<Long> countByManufacturerName(String name);
}

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

    CompletableFuture<Long> countByManufacturerName(String name)
}

@Repository
interface ProductRepository : CrudRepository<Product, Long> {
    @Join("manufacturer")
    fun findByNameContains(str: String): CompletableFuture<Product>

    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)

Assertions.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.

4.10 Reactive Queries

Micronaut Data supports reactive query execution by defining methods that return either Publisher or a RxJava 2 type.

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")
Maybe<Product> queryByNameContains(String str)

Single<Long> countDistinctByManufacturerName(String name)

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

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()

Assertions.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 Updating Data

There are various ways to perform write operations with Micronaut Data interfaces.

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 persist(Book entity);

Book persist(Book entity)

fun persist(entity: Book): Book

The method must accept a single argument that is the entity and start with either save, persist, insert or store.

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 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.

5.1 Transactions

Micronaut Data will automatically manage transactions for you. The underlying implementation delegates to the Spring transaction manager, however you can use any of the following annotations to declare your transaction semantics without directly depending on Spring APIs:

javax.transaction.Transactional
org.springframework.transaction.annotation.Transactional
io.micronaut.spring.tx.annotation.Transactional

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.

5.2 Batch Updates

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

Book persist(Book entity);

Book persist(Book entity)

fun persist(entity: 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 and returns void:

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.

5.3 Batch Deletes

Batch deletes 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()

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)

5.4 Entity Timestamps

It is common to want to add a field that represents the time when an entity is 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 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.

7 Spring Data Support

Micronaut Data is able to compile existing Spring Data repository interfaces if you add the spring-data-commons dependency to your compilation classpath:

compile 'org.springframework.data:spring-data-commons:2.1.8.RELEASE'

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-commons</artifactId>
    <version>2.1.8.RELEASE</version>
</dependency>

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

The following Spring Data types are also supported:

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 is the only supported implementation for executing SQL queries, however this may change in the future.

8.1 Micronaut Data JDBC

Micronaut Data JDBC 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 JDBC ResultSet and an object.

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

However, Micronaut Data JDBC 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
Optimistic Locking

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

Micronaut Data JDBC 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 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 JDBC Quick Start

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

annotationProcessor 'io.micronaut.data:micronaut-data-processor:1.0.0.M1'

<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>1.0.0.M1</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:

compile 'io.micronaut.data:micronaut-data-jdbc:1.0.0.M1'

<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
    <version>1.0.0.M1</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.configuration:micronaut-jdbc-hikari'

<dependency>
    <groupId>io.micronaut.configuration</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;MVCC=TRUE;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.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.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.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)
    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`)

@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.2 JDBC Configuration

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 or Commons DBCP.

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:

Using schema-generate

datasources:
  default:
    url: jdbc:h2:mem:devDb;MVCC=TRUE;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.

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 1. Supported JDBC 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

8.1.3 JDBC Repositories

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

The @JdbcRepository 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 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 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 define an appropriate update method, 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.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 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

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
@GeneratedValue	Specifies that the property value is generated by the database and not included in inserts
@Id	Specifies the ID of an entity
@EmbeddedId	Specifies an embedded ID of an entity
@MappedEntity	Specifies the entity is mapped to the database
@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

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

@Table
@Id
@Column
@Transient
@JoinTable
@OneToMany
@OneToOne
@ManyToOne
@ManyToMany
@Embedded
@Embeddable

Again Micronaut Data JDBC 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

You can define a composite primary key using either JPA or Micronaut Data annotations.

A composite ID requires an additional class 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.

You should then declare the id property of the entity with 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 use @Column in the ProjectId class

8.1.4.4 Constructor Arguments

Micronaut Data JDBC 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.

8.1.4.6 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 practise due to the N+1 query issue and the recommendation is always to write an optimized join query.

Micronaut Data JDBC takes this a step further by simply not supporting those features considered bad practise 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.7 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;

You may need to use the alias before the column name which is the table name followed by underscore. Example: 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.5 JDBC 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> {
    Manufacturer saveManufacturer(String name);

    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}

@JdbcRepository(dialect = Dialect.H2)
public interface ProductRepository extends CrudRepository<Product, Long> {
    Manufacturer saveManufacturer(String name);

    @Join(value = "manufacturer", type = Join.Type.FETCH) (1)
    List<Product> list();
}

@JdbcRepository(dialect = Dialect.H2)
interface ProductRepository : CrudRepository<Product, Long> {
    fun save(manufacturer: Manufacturer) : Manufacturer

    @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.

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.6 JDBC Data Types

Micronaut Data JDBC 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.

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 io.micronaut.data.annotation.TypeDef
import io.micronaut.data.model.DataType

import javax.persistence.*

@Entity
class Sale {

    @ManyToOne
    final Product product
    @TypeDef(type = DataType.INTEGER)
    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)
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)
@Immutable
class Quantity {
    int amount
}

package example

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

@TypeDef(type = DataType.INTEGER)
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 type conversion so that Micronaut Data knows how to read and write the type from an Integer:

package example;

import io.micronaut.context.annotation.Factory;
import io.micronaut.core.convert.TypeConverter;

import javax.inject.Singleton;
import java.util.Optional;

@Factory (1)
public class QuantityConverters {

    @Singleton (2)
    TypeConverter<Quantity, Integer> quantityIntegerTypeConverter() {
        return (object, targetType, context) -> Optional.of(object.getAmount());
    }

    @Singleton (3)
    TypeConverter<Integer, Quantity> integerQuantityTypeConverter() {
        return (object, targetType, context) -> Optional.of(Quantity.valueOf(object));
    }
}

package example

import groovy.transform.CompileStatic
import io.micronaut.context.annotation.Factory
import io.micronaut.core.convert.ConversionContext
import io.micronaut.core.convert.TypeConverter

import javax.inject.Singleton

@Factory (1)
@CompileStatic
class QuantityConverters {

    @Singleton (2)
    TypeConverter<Quantity, Integer> quantityIntegerTypeConverter() {
        return { Quantity quantity, Class targetType, ConversionContext context ->
            Optional.of(quantity.amount)
        } as TypeConverter<Quantity, Integer>
    }

    @Singleton (3)
    TypeConverter<Integer, Quantity> integerQuantityTypeConverter() {
        return { Integer integer, Class targetType, ConversionContext context ->
            Optional.of(new Quantity(integer))
        } as TypeConverter<Integer, Quantity>
    }
}

package example

import io.micronaut.context.annotation.Factory
import io.micronaut.core.convert.TypeConverter

import javax.inject.Singleton
import java.util.Optional

@Factory (1)
class QuantityConverters {

    @Singleton (2)
    fun quantityIntegerTypeConverter(): TypeConverter<Quantity, Int> {
        return TypeConverter { quantity, targetType, context -> Optional.of<Int>(quantity.amount) }
    }

    @Singleton (3)
    fun integerQuantityTypeConverter(): TypeConverter<Int, Quantity> {
        return TypeConverter { integer, targetType, context -> Optional.of<Quantity>(Quantity(integer)) }
    }
}

1	A `@Factory` bean is created to define the converters
2	A converter from `Quantity` to `Integer`
3	A converter from `Integer` to `Quantity`

8.1.7 Explicit JDBC 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.

Table of Contents

Micronaut Data

1 Introduction

2 Quick Start

Saving an Instance (Create)

Retrieving an Instance (Read)

Updating an Instance (Update)

Deleting an Instance (Delete)

3 Repository Interfaces

4 Writing Queries

4.1 Query Criteria

4.2 Pagination

4.3 Ordering

4.4 Query Projections

4.5 DTO Projections

4.6 Join Queries

JPA 2.1 Entity Graphs

4.7 Explicit Queries

4.8 Native Queries

4.9 Asynchronous Queries

4.10 Reactive Queries

5 Updating Data

5.1 Transactions

5.2 Batch Updates

5.3 Batch Deletes

5.4 Entity Timestamps

6 How Micronaut Data Works

7 Spring Data Support

8 Micronaut Data with SQL

8.1 Micronaut Data JDBC

8.1.1 JDBC Quick Start

Saving an Instance (Create)

Retrieving an Instance (Read)

Updating an Instance (Update)

Deleting an Instance (Delete)

8.1.2 JDBC Configuration

Creating the Schema

Setting the Dialect

8.1.3 JDBC Repositories

8.1.3.1 Inserts and Updates

8.1.4 Mapping Entities

8.1.4.1 SQL Annotations

8.1.4.2 ID Generation

8.1.4.3 Composite Primary Keys

8.1.4.4 Constructor Arguments

8.1.4.5 SQL Naming Strategies

8.1.4.6 Association Fetching

8.1.4.7 Using @ColumnTransformer

8.1.5 JDBC Join Queries

8.1.6 JDBC Data Types

8.1.7 Explicit JDBC Queries

Explicit Queries and Joins