Access a database with Micronaut Data JDBC

Learn how to access a database with Micronaut JDBC repositories.

Authors: Sergio del Amo

Micronaut Version: 3.2.7

1. Getting Started

In this guide, we will create a Micronaut application written in Java.

The application exposes some REST endpoints and stores data in a database using Micronaut Data JDBC.

2. What you will need

To complete this guide, you will need the following:

  • Some time on your hands

  • A decent text editor or IDE

  • JDK 1.8 or greater installed with JAVA_HOME configured appropriately

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide --build=gradle --lang=java
If you don’t specify the --build argument, Gradle is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

4.1. Enable annotation Processing

If you use Java or Kotlin and IntelliJ IDEA, make sure to enable annotation processing.

annotationprocessorsintellij

4.2. Data Source configuration

Add the following dependencies:

build.gradle
annotationProcessor("io.micronaut.data:micronaut-data-processor") (1)
implementation("io.micronaut.data:micronaut-data-jdbc") (2)
implementation("io.micronaut.sql:micronaut-jdbc-hikari") (3)
runtimeOnly("com.h2database:h2") (4)
1 Add Micronaut Data annotation processor
2 Configures a compile-scoped dependency on the micronaut-data-jdbc
3 Configures SQL DataSource instances using Hikari Connection Pool
4 Add dependency to in-memory H2 Database

Define the datasource in src/main/resources/application.yml.

src/main/resources/application.yml
datasources:
  default:
    url: ${JDBC_URL:`jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE`}
    username: ${JDBC_USER:sa}
    password: ${JDBC_PASSWORD:""}
    driverClassName: ${JDBC_DRIVER:org.h2.Driver}
This way of defining the datasource properties enables us to externalize the configuration, for example for production environment, and also provide a default value for development. If the environment variables are not defined, the Micronaut framework will use the default values.
Also keep in mind that it is necessary to escape the : in the connection URL using backticks `.

4.3. DB Schema

We need a way to create the database schema. For that we will use Micronaut integration with Flyway.

Add the following snippet to include the necessary dependencies:

build.gradle
implementation("io.micronaut.flyway:micronaut-flyway")

Configure the database migrations directory for Flyway in application.yml.

src/main/resources/application.yml
flyway:
  datasources:
    default:
      enabled: true

Create the file V1__schema.sql with the database schema creation:

src/main/resources/db/migration/V1__schema.sql
DROP TABLE IF EXISTS GENRE;

CREATE TABLE GENRE (
  id    BIGINT SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(255)              NOT NULL UNIQUE
);

During application startup, Flyway will execute the SQL file and create the schema needed for the application.

4.4. Domain

Create the domain entities:

src/main/java/example/micronaut/domain/Genre.java
package example.micronaut.domain;

import com.fasterxml.jackson.annotation.JsonIgnore;
import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.data.annotation.Relation;
import javax.validation.constraints.NotNull;
import java.util.Set;

@MappedEntity
public class Genre {

    @Id
    @GeneratedValue(GeneratedValue.Type.AUTO)
    private Long id;

    @NotNull
    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;
    }

    @Override
    public String toString() {
        return "Genre{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}
You could use a subset of supported JPA annotations instead by including the following compileOnly scoped dependency: jakarta.persistence:jakarta.persistence-api.

4.5. Repository Access

Next, create a repository interface to define the operations to access the database. Micronaut Data will implement the interface at compilation time:

src/main/java/example/micronaut/GenreRepository.java
package example.micronaut;

import example.micronaut.domain.Genre;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.exceptions.DataAccessException;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.PageableRepository;
import javax.transaction.Transactional;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

@JdbcRepository(dialect = Dialect.H2) (1)
public interface GenreRepository extends PageableRepository<Genre, Long> { (2)

    Genre save(@NonNull @NotBlank String name);

    @Transactional
    default Genre saveWithException(@NonNull @NotBlank String name) {
        save(name);
        throw new DataAccessException("test exception");
    }

    int update(@NonNull @NotNull @Id Long id, @NonNull @NotBlank String name);
}
1 @JdbcRepository with a specific dialect.
2 Genre, 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.

The repository extends from PageableRepository. It inherits the hierarchy PageableRepositoryCrudRepositoryGenericRepository.

Repository Description

PageableRepository

A repository that supports pagination. It provides findAll(Pageable) and findAll(Sort).

CrudRepository

A repository interface for performing CRUD (Create, Read, Update, Delete). It provides methods such as findAll(), save(Genre), deleteById(Long), and findById(Long).

GenericRepository

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

4.6. Controller

Micronaut validation is built on the standard framework – JSR 380, also known as Bean Validation 2.0.

Hibernate Validator is a reference implementation of the validation API. Micronaut has built-in support for validation of beans that are annotated with javax.validation annotations.

The necessary dependencies are included by default when creating a new application, so you don’t need to add anything else.

Create a class to encapsulate the Update operations:

src/main/java/example/micronaut/GenreUpdateCommand.java
package example.micronaut;

import io.micronaut.core.annotation.Introspected;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

@Introspected (1)
public class GenreUpdateCommand {
    @NotNull
    private final Long id;

    @NotBlank
    private final String name;

    public GenreUpdateCommand(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

}
1 Annotate the class with @Introspected to generate BeanIntrospection metadata at compilation time. This information can be used, for example, to the render the POJO as JSON using Jackson without using reflection.

Create GenreController, a controller that exposes a resource with the common CRUD operations:

src/main/java/example/micronaut/GenreController.java
package example.micronaut;

import example.micronaut.domain.Genre;
import io.micronaut.data.model.Pageable;
import io.micronaut.data.model.Sort;
import io.micronaut.http.HttpHeaders;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.annotation.Body;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Delete;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.Post;
import io.micronaut.http.annotation.Put;
import io.micronaut.http.annotation.Status;
import io.micronaut.http.HttpStatus;
import javax.validation.constraints.NotBlank;
import io.micronaut.scheduling.TaskExecutors;
import io.micronaut.scheduling.annotation.ExecuteOn;
import io.micronaut.data.exceptions.DataAccessException;
import javax.validation.Valid;
import java.net.URI;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.StreamSupport;

@ExecuteOn(TaskExecutors.IO)  (1)
@Controller("/genres")  (2)
public class GenreController {

    protected final GenreRepository genreRepository;

    public GenreController(GenreRepository genreRepository) { (3)
        this.genreRepository = genreRepository;
    }

    @Get("/{id}") (4)
    public Optional<Genre> show(Long id) {
        return genreRepository
                .findById(id); (5)
    }

    @Put (6)
    public HttpResponse update(@Body @Valid GenreUpdateCommand command) { (7)
        genreRepository.update(command.getId(), command.getName());
        return HttpResponse
                .noContent()
                .header(HttpHeaders.LOCATION, location(command.getId()).getPath()); (8)
    }

    @Get(value = "/list") (9)
    public List<Genre> list(@Valid Pageable pageable) { (10)
        return genreRepository.findAll(pageable).getContent();
    }

    @Post (11)
    public HttpResponse<Genre> save(@Body("name") @NotBlank String name) {
        Genre genre = genreRepository.save(name);

        return HttpResponse
                .created(genre)
                .headers(headers -> headers.location(location(genre.getId())));
    }

    @Post("/ex") (12)
    public HttpResponse<Genre> saveExceptions(@Body @NotBlank String name) {
        try {
            Genre genre = genreRepository.saveWithException(name);
            return HttpResponse
                    .created(genre)
                    .headers(headers -> headers.location(location(genre.getId())));
        } catch(DataAccessException e) {
            return HttpResponse.noContent();
        }
    }

    @Delete("/{id}") (13)
    @Status(HttpStatus.NO_CONTENT)
    public void delete(Long id) {
        genreRepository.deleteById(id);
    }

    protected URI location(Long id) {
        return URI.create("/genres/" + id);
    }

    protected URI location(Genre genre) {
        return location(genre.getId());
    }
}
1 It is critical that any blocking I/O operations (such as fetching the data from the database) are offloaded to a separate thread pool that does not block the Event loop.
2 The class is defined as a controller with the @Controller annotation mapped to the path /genres
3 Use constructor injection to inject a bean of type GenreRepository
4 Maps a GET request to /genres/{id}, which attempts to show a genre. This illustrates the use of a URL path variable.
5 Returning an empty optional when the genre doesn’t exist makes the Micronaut framework respond with 404 (not found).
6 Maps a PUT request to /genres, which attempts to update a genre.
7 Adds @Valid to any method parameter that requires validation. Use a POJO supplied as a JSON payload in the request to populate command.
8 It is easy to add custom headers to the response.
9 Maps a GET request to /genres, which returns a list of genres. This mapping illustrates URL parameters being mapped to a single POJO.
10 You can bind Pageable as a controller method argument. Check the examples in the following test section and read the Pageable configuration options. For example, you can configure the default page size with the configuration property micronaut.data.pageable.default-page-size.
11 Maps a POST request to /genres, which attempts to save a genre.
12 Maps a POST request to /ex, which generates an exception.
13 Maps a DELETE request to /genres/{id}, which attempts to remove a genre. This illustrates the use of a URL path variable.

4.7. Writing Tests

Create a test to verify the CRUD operations:

src/test/java/example/micronaut/GenreControllerTest.java
package example.micronaut;

import example.micronaut.domain.Genre;
import io.micronaut.core.type.Argument;
import io.micronaut.http.HttpHeaders;
import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.client.HttpClient;
import io.micronaut.http.client.annotation.Client;
import io.micronaut.http.client.exceptions.HttpClientResponseException;
import io.micronaut.test.extensions.junit5.annotation.MicronautTest;
import org.junit.jupiter.api.Test;

import jakarta.inject.Inject;
import java.util.ArrayList;
import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertThrows;
import java.util.Collections;
@MicronautTest (1)
public class GenreControllerTest {

    @Inject
    @Client("/")
    HttpClient client; (2)

    @Test
    public void testFindNonExistingGenreReturns404() {
        HttpClientResponseException thrown = assertThrows(HttpClientResponseException.class, () -> {
            client.toBlocking().exchange(HttpRequest.GET("/genres/99"));
        });

        assertNotNull(thrown.getResponse());
        assertEquals(HttpStatus.NOT_FOUND, thrown.getStatus());
    }

    @Test
    public void testGenreCrudOperations() {

        List<Long> genreIds = new ArrayList<>();

        HttpRequest request = HttpRequest.POST("/genres", Collections.singletonMap("name", "DevOps")); (3)
        HttpResponse response = client.toBlocking().exchange(request);
        genreIds.add(entityId(response));

        assertEquals(HttpStatus.CREATED, response.getStatus());

        request = HttpRequest.POST("/genres", Collections.singletonMap("name", "Microservices")); (3)
        response = client.toBlocking().exchange(request);

        assertEquals(HttpStatus.CREATED, response.getStatus());

        Long id = entityId(response);
        genreIds.add(id);
        request = HttpRequest.GET("/genres/" + id);

        Genre genre = client.toBlocking().retrieve(request, Genre.class); (4)

        assertEquals("Microservices", genre.getName());

        request = HttpRequest.PUT("/genres", new GenreUpdateCommand(id, "Micro-services"));
        response = client.toBlocking().exchange(request);  (5)

        assertEquals(HttpStatus.NO_CONTENT, response.getStatus());

        request = HttpRequest.GET("/genres/" + id);
        genre = client.toBlocking().retrieve(request, Genre.class);
        assertEquals("Micro-services", genre.getName());

        request = HttpRequest.GET("/genres/list");
        List<Genre> genres = client.toBlocking().retrieve(request, Argument.of(List.class, Genre.class));

        assertEquals(2, genres.size());

        request = HttpRequest.POST("/genres/ex", Collections.singletonMap("name", "Microservices")); (3)
        response = client.toBlocking().exchange(request);

        assertEquals(HttpStatus.NO_CONTENT, response.getStatus());

        request = HttpRequest.GET("/genres/list");
        genres = client.toBlocking().retrieve(request, Argument.of(List.class, Genre.class));

        assertEquals(2, genres.size());

        request = HttpRequest.GET("/genres/list?size=1");
        genres = client.toBlocking().retrieve(request, Argument.of(List.class, Genre.class));

        assertEquals(1, genres.size());
        assertEquals("DevOps", genres.get(0).getName());

        request = HttpRequest.GET("/genres/list?size=1&sort=name,desc");
        genres = client.toBlocking().retrieve(request, Argument.of(List.class, Genre.class));

        assertEquals(1, genres.size());
        assertEquals("Micro-services", genres.get(0).getName());

        request = HttpRequest.GET("/genres/list?size=1&page=2");
        genres = client.toBlocking().retrieve(request, Argument.of(List.class, Genre.class));

        assertEquals(0, genres.size());

        // cleanup:
        for (Long genreId : genreIds) {
            request = HttpRequest.DELETE("/genres/" + genreId);
            response = client.toBlocking().exchange(request);
            assertEquals(HttpStatus.NO_CONTENT, response.getStatus());
        }
    }

    protected Long entityId(HttpResponse response) {
        String path = "/genres/";
        String value = response.header(HttpHeaders.LOCATION);
        if (value == null) {
            return null;
        }
        int index = value.indexOf(path);
        if (index != -1) {
            return Long.valueOf(value.substring(index + path.length()));
        }
        return null;
    }
}
1 Annotate the class with @MicronautTest so the Micronaut framework will initialize the application context and the embedded server. More info.
2 Inject the HttpClient bean and point it to the embedded server.
3 Creating HTTP Requests is easy thanks to the Micronaut framework fluid API.
4 If you care just about the object in the response, use retrieve.
5 Sometimes, receiving just the object is not enough, and you need information about the response. In this case, instead of retrieve, use the exchange method.

5. Testing the Application

To run the tests:

./gradlew test

Then open build/reports/tests/test/index.html in a browser to see the results.

6. Running the Application

To run the application, use the ./gradlew run command which starts the application on port 8080.

7. Generate a Micronaut Application Native Image with GraalVM

We will use GraalVM, the polyglot embeddable virtual machine, to generate a native image of our Micronaut application.

Compiling native images ahead of time with GraalVM improves startup time and reduces the memory footprint of JVM-based applications.

Only Java and Kotlin projects support using GraalVM’s native-image tool. Groovy relies heavily on reflection, which is only partially supported by GraalVM.

7.1. Native image generation

The easiest way to install GraalVM on Linux or Mac is to use SDKMan.io.

Java 11
$ sdk install java 21.3.0.r11-grl
If you still use Java 8, use the JDK11 version of GraalVM.
Java 17
$ sdk install java 21.3.0.r17-grl

For installation on Windows, or for manual installation on Linux or Mac, see the GraalVM Getting Started documentation.

After installing GraalVM, install the native-image component, which is not installed by default:

gu install native-image

To generate a native image using Gradle, run:

./gradlew nativeCompile

The native image is created in build/native/nativeCompile directory and can be run with build/native/nativeCompile/application.

It is possible to customize the name of the native image or pass additional parameters to GraalVM:

build.gradle
graalvmNative {
    binaries {
        main {
            imageName.set('mn-graalvm-application') (1)
            buildArgs.add('--verbose') (2)
        }
    }
}
1 The native image name will now be mn-graalvm-application
2 It is possible to pass extra arguments to build the native image

You can execute the genres endpoints exposed by the native image, for example:

curl localhost:8080/genres/list

8. Using PostgreSQL

When running on production, you want to use a real database instead of using H2. Let’s explain how to use PostgreSQL.

After installing Docker, execute the following command to run a PostgreSQL container:

docker run -it --rm \
    -p 5432:5432 \
    -e POSTGRES_USER=dbuser \
    -e POSTGRES_PASSWORD=theSecretPassword \
    -e POSTGRES_DB=micronaut \
    postgres:11.5-alpine

Add PostgreSQL driver dependency:

build.gradle
runtimeOnly("org.postgresql:postgresql")
Change the dialect in the GenreRepository @JdbcRepository annotation.

To use PostgreSQL, set up several environment variables that match those defined in application.yml:

$ export JDBC_URL=jdbc:postgresql://localhost:5432/micronaut
$ export JDBC_USER=dbuser
$ export JDBC_PASSWORD=theSecretPassword
$ export JDBC_DRIVER=org.postgresql.Driver

Run the application again. If you look at the output, you can see that the application uses PostgreSQL.

Connect to your PostgreSQL database, and you will see both genre and book tables.

datagrip

Save one genre, and your genre table will now contain an entry.

curl -X "POST" "http://localhost:8080/genres" \
     -H 'Content-Type: application/json; charset=utf-8' \
     -d $'{ "name": "music" }'

9. Next steps

Read more about Micronaut Data.

10. Help with the Micronaut Framework

Object Computing, Inc. (OCI) sponsored the creation of this Guide. A variety of consulting and support services are available.