Create and Connect a Micronaut Application to a MySQL Database

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

flyway.datasources.default.enabled=true

DROP TABLE IF EXISTS genre;

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

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 + "'}";
    }
}

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);
}

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);
    }
}

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);
    }
}

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