Create and Connect a Micronaut Application to a MySQL Database

This guide describes how to create a database application using the Graal Development Kit for Micronaut (GDK). The application presents REST endpoints and stores data in a MySQL database using Micronaut^® Data.

Micronaut Data is a database access toolkit that uses ahead-of-time compilation to precompute queries for repository interfaces that are then executed by a thin, lightweight runtime layer. Micronaut Data supports the following back ends: JPA (Hibernate and Hibernate Reactive); SQL (JDBC, R2DBC); and MongoDB.

Prerequisites #

• JDK 17 or higher. See Setting up Your Desktop.
• A Docker-API compatible container runtime such as Rancher Desktop or Docker installed to run MySQL and to run tests using Testcontainers.
• The GDK CLI. See Setting up Your Desktop. (Optional.)

Follow the steps below to create the application from scratch. However, you can also download the completed example:

A note regarding your development environment

Consider using Visual Studio Code, which provides native support for developing applications with the Graal Development Kit extension.

Note: If you use IntelliJ IDEA, enable annotation processing.

Windows platform: The GDK guides are compatible with Gradle only. Maven support is coming soon.

1. Create the Application #

Create an application using the GDK Launcher.

1. Open the GDK Launcher in advanced mode.

2. Create a new project using the following selections.
   • Project Type: Application (Default)
   • Project Name: db-demo
   • Base Package: com.example (Default)
   • Clouds: None
   • Build Tool: Gradle (Groovy) or Maven
   • Language: Java (Default)
   • Test Framework: JUnit (Default)
   • Java Version: 17 (Default)
   • Micronaut Version: (Default)
   • Cloud Services: Database
   • Features: GraalVM Native Image (Default)
   • Sample Code: Yes (Default)

3. Switch to the Cloud Services tab and make sure the Database service is selected. Deselect the other services. The Database service bundles all necessary features for a Micronaut database application: Micronaut Data JDBC, Hikari JDBC Connection Pool, MySQL driver and default config, Flyway Database Migration.

4. Switch to Selected tab to verify the selection. You should see Database and the GraalVM Native Image packaging feature (selected by default) selected.

5. Click Generate Project, then click Download Zip. The GDK Launcher creates an application with the default package com.example in a directory named db-demo. The application ZIP file will be downloaded to your default downloads directory. Unzip it, open it in your code editor, and proceed to the next steps.

Alternatively, use the GDK CLI as follows:

gdk create-app com.example.db-demo \
    --services=database \
    --features=graalvm \
    --build=gradle \
    --jdk=17 \
    --lang=java

gdk create-app com.example.db-demo \
    --services=database \
    --features=graalvm \
    --build=maven \
    --jdk=17 \
    --lang=java

If you enable sample code generation, the GDK Launcher creates the main controller, repository interface, entity, service classes, and tests for you. In the micronaut-cli.yml file you can find all features packaged with the application:

features: [app-name, data, data-jdbc, flyway, gdk-bom, gdk-database, gdk-license, gdk-platform-independent, graalvm, http-client-test, java, java-application, jdbc-hikari, junit, logback, maven, maven-enforcer-plugin, micronaut-aot, mysql, netty-server, properties, readme, serialization-jackson, shade, test-resources, validation]

Let’s examine the project more closely.

1.1. Configure Datasource #

The default datasource is defined in the src/main/resources/application.properties file:

datasources.default.dialect=MYSQL
datasources.default.db-type=mysql
datasources.default.driver-class-name=com.mysql.cj.jdbc.Driver

If you deploy to, for example, Oracle MySQL Database, substitute the driver-class-name value with the Oracle Database Server (see Create and Connect a Micronaut Application to the Oracle Cloud Infrastructure MySQL HeatWave Service).

1.2. Database Migration with Flyway #

The GDK Launcher included Flyway for database migrations. It uses the Micronaut integration with Flyway that automates schema changes, significantly simplifies schema management tasks, such as migrating, rolling back, and reproducing in multiple environments. The GDK Launcher enables Flyway in the src/main/resources/application.properties file and configures it to perform migrations on the default datasources.

flyway.datasources.default.enabled=true

Note: Flyway migrations are not compatible with the default automatic schema generation that is configured in src/main/resources/application.properties. If schema-generate is active, it will conflict with Flyway. So edit src/main/resources/application.properties and either delete the datasources.default.schema-generate=CREATE_DROP line or change that line to datasources.default.schema-generate=NONE to ensure that only Flyway manages your schema.

Configuring multiple datasources is as simple as enabling Flyway for each one. You can also specify directories that will be used for migrating each datasource. For more information, see Micronaut integration with Flyway.

Flyway migration is automatically triggered before your application starts. Flyway reads migration file(s) in the src/main/resources/db/migration/ directory. The migration file with the database schema, src/main/resources/db/migration/V1__schema.sql, was also created for you by the GDK Launcher.

DROP TABLE IF EXISTS genre;

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

During application startup, Flyway runs the commands in the SQL file and creates the schema needed for the application.

1.3. Domain Entity #

The GDK Launcher created the sample domain entity in the src/main/java/com/example/domain/Genre.java file:

package com.example.domain;

import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.serde.annotation.Serdeable;

import jakarta.validation.constraints.NotNull;

@Serdeable
@MappedEntity
public class Genre {

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

1.4. Repository Interface #

A repository interface defines the operations to access the database. Micronaut Data implements the interface at compilation time. A sample repository interface was created for you in src/main/java/com/example/repository/GenreRepository.java:

package com.example.repository;

import com.example.domain.Genre;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.repository.PageableRepository;

import jakarta.validation.constraints.NotBlank;

import static io.micronaut.data.model.query.builder.sql.Dialect.MYSQL;

@JdbcRepository(dialect = MYSQL) // <1>
public interface GenreRepository extends PageableRepository<Genre, Long> { // <2>

    Genre save(@NonNull @NotBlank String name);

    long update(@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 PageableRepository → CrudRepository → GenericRepository.

1.5. Controller #

Hibernate Validator is a reference implementation of the Validation API. Micronaut has built-in support for validation of beans that use jakarta.validation annotations. The necessary dependencies are included by default when creating a project.

The GDK Launcher created the main controller that exposes a resource with the common CRUD operations for you in src/main/java/com/example/controller/GenreController.java:

package com.example.controller;

import com.example.domain.Genre;
import com.example.service.GenreService;
import io.micronaut.data.model.Pageable;
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.scheduling.TaskExecutors;
import io.micronaut.scheduling.annotation.ExecuteOn;

import jakarta.validation.Valid;
import jakarta.validation.constraints.NotBlank;
import java.net.URI;
import java.util.List;
import java.util.Optional;

import static io.micronaut.http.HttpHeaders.LOCATION;
import static io.micronaut.http.HttpStatus.NO_CONTENT;

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

    private final GenreService genreService;

    GenreController(GenreService genreService) { // <3>
        this.genreService = genreService;
    }

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

    @Put("/{id}/{name}") // <5>
    public HttpResponse<?> update(long id, String name) {
        genreService.update(id, name);
        return HttpResponse
                .noContent()
                .header(LOCATION, URI.create("/genres/" + id).getPath());
    }

    @Get("/list") // <6>
    public List<Genre> list(@Valid Pageable pageable) {
        return genreService.list(pageable);
    }

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

        return HttpResponse
                .created(genre)
                .headers(headers -> headers.location(URI.create("/genres/" + genre.getId())));
    }

    @Delete("/{id}") // <8>
    @Status(NO_CONTENT)
    public void delete(Long id) {
        genreService.delete(id);
    }
}

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 Uses 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 (id).

5 Maps a PUT request to /genres/{id}/{name}, which attempts to update a genre. This illustrates the use of URL path variables (id and name).

6 Maps a GET request to /genres/list, which returns a list of genres. This mapping illustrates URL parameters being mapped to a single POJO.

7 Maps a POST request to /genres, which attempts to create a new genre.

8 Maps a DELETE request to /genres/{id}, which attempts to remove a genre. This illustrates the use of a URL path variable (id).

1.6. Service #

A service contains business logic and facilitates communication between the controller and the repository. The domain is used to communicate between the controller and service layers.

The GDK Launcher created a sample service class, src/main/java/com/example/service/GenreService.java, for you:

package com.example.service;

import com.example.domain.Genre;
import com.example.repository.GenreRepository;
import io.micronaut.data.model.Pageable;
import jakarta.inject.Singleton;

import jakarta.transaction.Transactional;
import java.util.List;
import java.util.Optional;

@Singleton
public class GenreService {

    private final GenreRepository genreRepository;

    GenreService(GenreRepository genreRepository) {
        this.genreRepository = genreRepository;
    }

    public Optional<Genre> findById(Long id) {
        return genreRepository.findById(id);
    }

    @Transactional
    public long update(long id, String name) {
        return genreRepository.update(id, name);
    }

    public List<Genre> list(Pageable pageable) {
        return genreRepository.findAll(pageable).getContent();
    }

    @Transactional
    public Genre save(String name) {
        return genreRepository.save(name);
    }

    @Transactional
    public void delete(long id) {
        genreRepository.deleteById(id);
    }
}

1.7. Tests #

The GDK Launcher wrote tests for you, in src/test/java/com/example/GenreControllerTest.java, to verify the CRUD operations:

package com.example;

import com.example.domain.Genre;
import com.example.repository.GenreRepository;
import io.micronaut.core.type.Argument;
import io.micronaut.context.env.Environment;
import io.micronaut.http.HttpRequest;
import io.micronaut.http.HttpResponse;
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 jakarta.inject.Inject;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Test;

import java.util.Collections;
import java.util.List;

import static io.micronaut.http.HttpHeaders.LOCATION;
import static io.micronaut.http.HttpStatus.CREATED;
import static io.micronaut.http.HttpStatus.NOT_FOUND;
import static io.micronaut.http.HttpStatus.NO_CONTENT;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertThrows;

@MicronautTest
class GenreControllerTest {

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

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

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

    @Test
    void testGenreCrudOperations() {

        HttpResponse<?> response = client.toBlocking().exchange(
                HttpRequest.POST("/genres", Collections.singletonMap("name", "DevOps")));
        assertEquals(CREATED, response.getStatus());

        response = client.toBlocking().exchange(
                HttpRequest.POST("/genres", Collections.singletonMap("name", "Microservices")));
        assertEquals(CREATED, response.getStatus());

        Long id = entityId(response);

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

        response = client.toBlocking().exchange(
                HttpRequest.PUT("/genres/" + id + "/Micro-services", null));
        assertEquals(NO_CONTENT, response.getStatus());

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

        List<Genre> genres = client.toBlocking().retrieve(
                HttpRequest.GET("/genres/list"), Argument.listOf(Genre.class));
        assertEquals(2, genres.size());

        genres = client.toBlocking().retrieve(
                HttpRequest.GET("/genres/list?size=1"), Argument.listOf(Genre.class));
        assertEquals(1, genres.size());
        assertEquals("DevOps", genres.get(0).getName());

        genres = client.toBlocking().retrieve(
                HttpRequest.GET("/genres/list?size=1&sort=name,desc"), Argument.listOf(Genre.class));
        assertEquals(1, genres.size());
        assertEquals("Micro-services", genres.get(0).getName());

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

        response = client.toBlocking().exchange(
                HttpRequest.DELETE("/genres/" + id));
        assertEquals(NO_CONTENT, response.getStatus());
    }

    private Long entityId(HttpResponse<?> response) {
        String value = response.header(LOCATION);
        if (value == null) {
            return null;
        }
        String path = "/genres/";
        int index = value.indexOf(path);
        return index == -1 ? null : Long.valueOf(value.substring(index + path.length()));
    }

    @Inject
    GenreRepository genreRepository;

    @AfterEach
    void cleanup() {
        genreRepository.deleteAll();
    }
}

2. Test the Application #

To run the tests, use the following command:

./gradlew test

./mvnw test

When the application is started locally—either under test or by running the application—resolution of the datasources URL is detected, the Test Resources service will start a local MySQL container, and inject the properties required to use this as the datasources.

For more information, see the JDBC section of the Test Resources documentation.

3. Generate a Native Executable Using GraalVM #

The GDK supports compiling Java applications ahead-of-time into native executables using GraalVM Native Image. You can use the Gradle plugin for GraalVM Native Image building/Maven plugin for GraalVM Native Image building. Packaged as a native executable, it significantly reduces application startup time and memory footprint.

Prerequisites: Make sure you have installed a GraalVM JDK. The easiest way to get started is with SDKMAN!. For other installation options, visit the Downloads section.

To generate a native executable, use the following command:

./gradlew nativeCompile

./mvnw package -Dpackaging=native-image

Before running this native executable, you need to start and then connect to a MySQL database.

4. Connect to a MySQL Database #

Start and connect to an existing database. Then define the database driver URL, username, and password via environment variables.

Use the following command to run a MySQL container:

docker run -it --rm \
    -p 3306:3306 \
    -e MYSQL_DATABASE=db \
    -e MYSQL_USER=sherlock \
    -e MYSQL_PASSWORD=elementary \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=true \
    mysql:8

Note: If you are using macOS on Apple Silicon (M1, M1 Pro), Docker might fail to pull a container image for mysql:8. In that case, substitute with mysql:oracle.

Define the database driver URL, username, and password via environment variables:

export DATASOURCES_DEFAULT_URL=jdbc:mysql://localhost:3306/db
export DATASOURCES_DEFAULT_USERNAME=sherlock
export DATASOURCES_DEFAULT_PASSWORD=elementary

set DATASOURCES_DEFAULT_URL=jdbc:mysql://localhost:3306/db
set DATASOURCES_DEFAULT_USERNAME=sherlock
set DATASOURCES_DEFAULT_PASSWORD=elementary

$ENV:DATASOURCES_DEFAULT_URL = "jdbc:mysql://localhost:3306/db"
$ENV:DATASOURCES_DEFAULT_USERNAME = "sherlock"
$ENV:DATASOURCES_DEFAULT_PASSWORD = "elementary"

The Micronaut framework populates the properties datasources.default.url, datasources.default.username and datasources.default.password with those environment variables’ values. Learn more about JDBC Connection Pools.

5. Run the Application #

Run the application from the native executable which starts the application on port 8080:

build/native/nativeCompile/db-demo

target/db-demo

Save one genre and your genre database table will now contain an entry:

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

Access the genres endpoint exposed by the application:

curl localhost:8080/genres/list

When you run the application, Micronaut Test Resources do not start a MySQL container because you have provided values for datasources.default.* properties

Summary #

This guide demonstrated how to use the GDK to create a Micronaut database application that stores data in a MySQL database. You also learned how to package and run this application as a native executable.

Related Documentation #

• Micronaut Data
• Native Build Tools
• GraalVM Native Image