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)

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

1.1 Release History

1.0.0.RC1

  • Support for Spring Data JPA specifications

  • Bug Fixes and Improvements

1.0.0.M5

  • Support for explicit entity updates with the update method

  • Support for cascading inserts in JDBC

1.0.0.M4

  • Support for Soft Delete and query customization with @Where.

  • Support for Sequences for ID generation in Oracle, Postgres and SQL Server

  • Automatic quoting/escaping of identifiers for generated queries

1.0.0.M3

  • Spring for Transaction Management Now Optional

  • Support for @TransactionalEventListener

  • Support for javax.transaction.Transactional usage without Spring

  • Bug Fixes and Improvements

1.0.0.M2

  • Bug Fixes and Improvements

1.0.0.M1

  • Initial Milestone Release

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.2")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>1.0.2</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:1.0.2")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-hibernate-jpa</artifactId>
    <version>1.0.2</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 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:1.0.2")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>1.0.2</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.configuration:micronaut-jdbc-tomcat")
<dependency>
    <groupId>io.micronaut.configuration</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 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.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 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.

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.

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.

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

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.
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 edu.umd.cs.findbugs.annotations.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 edu.umd.cs.findbugs.annotations.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> {
    @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.

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

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

For partial updates, you must define a method that features a parameter annotated with @Id and parameters for the properties to be updated.

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

6.1 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.1.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 javax.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 javax.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 javax.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 javax.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 javax.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 javax.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.1.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 javax.inject.Singleton;
import javax.transaction.Transactional;

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

    public BookManager(BookRepository bookRepository, ApplicationEventPublisher 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 javax.inject.Singleton
import javax.transaction.Transactional

@Singleton
class BookManager {
    private final BookRepository bookRepository
    private final ApplicationEventPublisher eventPublisher

    BookManager(BookRepository bookRepository, ApplicationEventPublisher 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 javax.inject.Singleton
import javax.transaction.Transactional

@Singleton
open class BookManager(
        private val bookRepository: BookRepository, private val eventPublisher: ApplicationEventPublisher) { (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.

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

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

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

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 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.2")
<annotationProcessorPaths>
    <path>
        <groupId>io.micronaut.data</groupId>
        <artifactId>micronaut-data-processor</artifactId>
        <version>1.0.2</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:1.0.2")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-jdbc</artifactId>
    <version>1.0.2</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)

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.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 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.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
/*
 * Copyright 2017-2019 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

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

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.

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

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

9 Going Native with GraalVM

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

Before you can build a native image however there are a few changes you need to make to your project to allow a native build.

The following sections describe the changes you need to make.

9.1 JDBC and Native Builds

Getting your project ready for a native GraalVM build is JDBC driver dependent.

GraalVM substrate remains an evolving and experimental feature of GraalVM, hence support for GraalVM substrate in Micronaut Data is also regarded as experimental and evolving.

Generally you need to add a @TypeHint(…​) declaration to your Application class that tells GraalVM what is the class that represents the JDBC driver and you need to add the driver to the class that are initialized at build time with the --initialize-at-build-time=DRIVER_CLASS_NAME flag which you can add to the native-image.properties file found in src/main/resources/META-INF/native-image for a project that was created with mn create-app my-app --features graal-native-image.

MySQL is as of this time unsupported in native image due to this issue.

Configuration for H2

The changes for H2 are:

@TypeHint Declaration in Application Class
@TypeHint(typeNames = {"org.h2.Driver", "org.h2.mvstore.db.MVTableEngine"})
GraalVM Flags
--report-unsupported-elements-at-runtime --initialize-at-build-time=org.h2.Driver
The --report-unsupported-elements-at-runtime flag is needed for H2 if you use the embedded H2 server otherwise can be omitted.

Configuration for Postgres

The changes for Postgres JDBC Driver (org.postgresql:postgresql:42.2.8 or above) are:

@TypeHint Declaration in Application Class
@TypeHint(org.postgresql.Driver.class)
GraalVM Flags
--initialize-at-build-time=org.postgresql.Driver,org.postgresql.util.SharedTimer

Configuration for Oracle

The changes for Oracle JDBC driver (com.oracle.ojdbc:ojdbc8:19.3.0.0 or above) are quite extensive and are documented in this Github issue.

Configuration for Microsoft SQL Server

The changes for SQL Server JDBC Driver (com.microsoft.sqlserver:mssql-jdbc:7.2.2.jre8 or above) are:

@TypeHint Declaration in Application Class
@TypeHint(typeNames = "com.microsoft.sqlserver.jdbc.SQLServerDriver")
GraalVM Flags
-H:IncludeResourceBundles=com.microsoft.sqlserver.jdbc.SQLServerResource \
--initialize-at-build-time=com.microsoft.sqlserver.jdbc.SQLServerResource \
--initialize-at-run-time=java.sql.DriverManager

Configuration for MariaDB

The changes for MariaDB driver (org.mariadb.jdbc:mariadb-java-client:2.5.2 or above) are:

@TypeHint Declaration in Application Class
@TypeHint(value = {
        org.mariadb.jdbc.util.Options.class,
        org.mariadb.jdbc.Driver.class
},
accessType = TypeHint.AccessType.ALL_PUBLIC)
GraalVM Flags
--initialize-at-build-time=org.mariadb \
--initialize-at-run-time=org.mariadb.jdbc.credential.aws,org.mariadb.jdbc.internal.failover.impl.MastersSlavesListener,org.mariadb.jdbc.internal.com.send.authentication.SendPamAuthPacket

9.2 JPA and Native Builds

If you are using JPA then in addition to the above configuration you should add modify your @TypeHint declaration to include a reference to the Hibernate dialect associated with the database you are using.

For example for Postgres:

Postgres Dialect Config
import org.hibernate.dialect.PostgreSQL10Dialect;
import org.postgresql.Driver;

...

@TypeHint( {
    PostgreSQL10Dialect.class,
    Driver.class
})

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:1.0.2")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-spring</artifactId>
    <version>1.0.2</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:1.0.2")
<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-spring-jpa</artifactId>
    <version>1.0.2</version>
</dependency>

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