Accessing a database with JDBC Predator and Micronaut

Accessing a database with JDBC Predator and Micronaut

Micronaut Predator was just unveiled 2 days ago (July 18, 2019)  and it was very exciting. Micronaut Predator is Micronaut's version of Spring Data or Grail's GORM. In essence, it will allow you to do something like this


@JdbcRepository
public interface GenreRepository extends CrudRepository<Genre, Long> {

    @NonNull
    Optional<Genre> findById(@Id @NotNull @NonNull Long id);

    default Genre save(@NotBlank String name) {
        return save(name, emptySet());
    }

    Genre save(@NotBlank String name, @NotNull Set<Book> books);

    void deleteById(@Id @NonNull @NotNull Long id);

    Page<Genre> findAll(@NotNull Pageable pageable);

    void update(@Id Long id, @NotBlank String name);

}

And without writing any implementation of that interface, you can inject it into your other beans and use it directly, and microanut will automagically translate those method calls to sql queries.

@Validated
@Controller("/genres")
public class GenreController {

    private final GenreRepository genreRepository;

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

So what's the big deal then?

Why create another Spring Data or GORM library? - well this one works with the same principle of Micronaut - it uses Ahead of Time compilation instead of reflection to achieve the same functionality, but without slowing down startup time and heavily increasing the memory consumption.

Sounds good and all, but how can you try it out?

I've played around with it this past couple of days, and the result can be found here in this github project - https://github.com/franz-see/micronaut-predator-jdbc-example

This is project is similar to the official micronaut guide for Hibernate/JPA and official micornaut guide for MyBatis, except that the underlying database access library uses micronaut-predator-jdbc

Note: there are several flavors of micronaut-predator, there's a

  • micronaut-predator spring-data,
  • micronaut-predator hibernate,
  • micronaut-predator GORM, and then there's
  • micronaut-predator-jdbc.

The micronaut-predator-jdbc is its most direct database approach, and this also uses the least amount of reflection and offers the most performance gain.

Building and running the application

Once you have the source code, y0u can build it with ./gradlew clean build and run it with java -jar build/libs/mn-predator-jdbc-0.1.jar

Alternatively, you can build the docker image with ./gradlew clean build && sh docker-build.sh. and then execute with docker run -p 8080:8080 mn-predator-jdbc.

CURL Testing

To create a new Genre

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

To list all genres

curl http://localhost:8080/genres/list

To view a single genre

curl http://localhost:8080/genres/1

Deleting a Genre

curl -X DELETE  http://localhost:8080/genres/1

Creating the micronaut predator jdbc REST application from scratch

If you want to create one from scratch, make you sure have java 8+ and micronaut installed in your machine (see installation instruction here)

Once you have the pre-requisites, create a new micronaut application

mn create-app ph.net.see.mn-predator-jdbc

The format above creates a micronaut application with group ph.net.see and artifact mn-predator-jdbc.

From there, add the following dependencies

repositories {
    ...
    // add my maven repo to download a copy of micronaut-predator libraries
    maven { url "https://dl.bintray.com/franz-see/maven-repo" }
}

dependencies {
    compileOnly 'org.projectlombok:lombok:1.18.8'
    annotationProcessor "org.projectlombok:lombok:1.18.8"
    ...
    annotationProcessor 'ph.net.see.io.micronaut.data:micronaut-predator-processor:1.0.0-unofficial-1'
    ...
    compile "javax.annotation:javax.annotation-api"
    compile 'ph.net.see.io.micronaut.data:micronaut-predator-jdbc:1.0.0-unofficial-1'
    compile 'io.micronaut.configuration:micronaut-jdbc-hikari'
    ...
    runtime "com.h2database:h2"
    ...
}

These would add project lombok (nice to have, but not required), micronaut-predator-jdbc (what we really want), and h2 database (a sample database to use)

Note: micronaut-predator does not have an official release yet as of this writing. So what I did was I forked micronaut-predator, and released an unofficial build it into my public bintray maven repo.

And then, we add the Application class which basically starts everything up

package ph.net.see;

import io.micronaut.runtime.Micronaut;

public class Application {

    public static void main(String[] args) {
        Micronaut.run(Application.class);
    }
}

Domain Models

Let's create the classic Micronaut sample domain models - Book and Genre. (Genre has one or more Genres)

Some notes on the code below:

  • @MappedEntity symbolizes that this class is an entity that is mapped to a table in the database.
  • @Id , @GeneratedValue - similar to a JPA annotation to mark that this field is the id and its value is auto-generated
  • @Relation - defines relationships amongst tables/entities.
  • No lombok @Data and @Value - micronaut-predator-jdbc's domain models does not play well with lombok's @Data and @Value. You can still use those two annotations elsewhere though, or use lombok's @EqualAndHashCode and @ToString within the domain models.
package ph.net.see.model;

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 lombok.EqualsAndHashCode;
import lombok.ToString;

import java.util.LinkedHashSet;
import java.util.Set;

@MappedEntity
@EqualsAndHashCode
@ToString
public class Genre {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "country")
    private Set<Book> books = new LinkedHashSet<>();

    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 Set<Book> getBooks() {
        return books;
    }

    public void setBooks(Set<Book> books) {
        this.books = books;
    }
}
package ph.net.see.model;

import io.micronaut.data.annotation.*;
import lombok.EqualsAndHashCode;
import lombok.ToString;

@MappedEntity
@EqualsAndHashCode
@ToString
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    private String isbn;

    @Relation(Relation.Kind.MANY_TO_ONE)
    private Genre genre;

    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 String getIsbn() {
        return isbn;
    }

    public void setIsbn(String isbn) {
        this.isbn = isbn;
    }

    public Genre getGenre() {
        return genre;
    }

    public void setGenre(Genre genre) {
        this.genre = genre;
    }
}

Repository

Unlike the Micronaut MyBatis or Hibernate/JPA implementation, there is no longer a need to implement the repository interface - just like how you'd do it with Spring Data.

Some notes:

  • @JdbcRepository - to mark that it's using micronaut-predator-jdbc
  • Pageable - is a micronaut-predator interface for pagination. And a result of which can be of type Page
package ph.net.see.repository;

import edu.umd.cs.findbugs.annotations.NonNull;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.Page;
import io.micronaut.data.model.Pageable;
import io.micronaut.data.repository.CrudRepository;
import ph.net.see.model.Book;
import ph.net.see.model.Genre;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.List;
import java.util.Optional;
import java.util.Set;

import static java.util.Collections.emptySet;

@JdbcRepository
public interface GenreRepository extends CrudRepository<Genre, Long> {

    @NonNull
    Optional<Genre> findById(@Id @NotNull @NonNull Long id);

    default Genre save(@NotBlank String name) {
        return save(name, emptySet());
    }

    Genre save(@NotBlank String name, @NotNull Set<Book> books);

    void deleteById(@Id @NonNull @NotNull Long id);

    Page<Genre> findAll(@NotNull Pageable pageable);

    void update(@Id Long id, @NotBlank String name);

}
...

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}
    schema-generate: CREATE_DROP
src/main/resources/application.yml

Controller

Now we create our REST controller. The REST controller would look very similar to that of the Micronaut's guide on Hibernate/JPA and MyBatis.

  • @Controller - just like Spring, this marks a Controller class
  • Lombok's @Data and @Value - both can now be used in the Controller's DTO. But in order for jackson to properly work well with Lombok, we need to create lombok.config file under src/main. Technically, we could put the lombok.config file under the root directory ./ but by putting it in src/main, we are making it easier for IntelliJ to find it
package ph.net.see.controller;

import io.micronaut.data.model.Page;
import io.micronaut.data.model.Pageable;
import io.micronaut.http.HttpHeaders;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.annotation.*;
import io.micronaut.validation.Validated;
import ph.net.see.model.Genre;
import ph.net.see.repository.GenreRepository;

import javax.validation.Valid;
import java.net.URI;

@Validated
@Controller("/genres")
@SuppressWarnings("unused")
public class GenreController {

    private final GenreRepository genreRepository;

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

    @Get("/{id}")
    public Genre show(Long id) {
        return genreRepository
                .findById(id)
                .orElse(null);
    }

    @Put()
    public HttpResponse<Void> update(@Body @Valid GenreUpdateCommand command) {
        genreRepository.update(command.getId(), command.getName());

        return HttpResponse
                .<Void>noContent()
                .header(HttpHeaders.LOCATION, location(command.getId()).getPath());
    }

    @Get(value = "/list{?pageable*}")
    public Page<Genre> list(@Valid Pageable pageable) {
        Page<Genre> result = genreRepository.findAll(pageable);
        return result;
    }

    @Post()
    public HttpResponse<Genre> save(@Body @Valid GenreSaveCommand cmd) {
        Genre genre = genreRepository.save(cmd.getName());

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

    @Delete("/{id}")
    public HttpResponse<Void> delete(Long id) {
        genreRepository.deleteById(id);
        return HttpResponse.<Void>noContent();
    }

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

}
package ph.net.see.controller;

import lombok.Value;

import javax.validation.constraints.NotBlank;

@Value
public class GenreSaveCommand {

    @NotBlank
    private String name;

}
package ph.net.see.controller;

import lombok.Value;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

@Value
public class GenreUpdateCommand {

    @NotNull
    private Long id;

    @NotNull
    private String name;

}
lombok.anyConstructor.addConstructorProperties=true
src/main/lombok.config

Controller Test

  • @MicronautTest - similar to Spring' @SpringBootTest, this will run this test as an integration test. But since a micronaut application's cold startup time is really fast, you can run several integration tests for the cost of a few seconds only instead of minutes in a Spring application's integration tesing.
  • @Property(name = "datasources.default.schema-generate", value = "CREATE_DROP") - resets the database for each test case execution
  • Technically speaking, we do not need to modify the default Jackson configuration, but since I wanted to convert the JSON REST response back into models inside the test, I had to tell Jackson how to convert those JSON back into objects. So, I declared some mixins to instruct jackson how to deserialize micronaut-predator's classes - namely Page, Pageable, Sort, and Sort.Order.
package ph.net.see.controller;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.micronaut.context.annotation.Property;
import io.micronaut.core.type.Argument;
import io.micronaut.data.model.Page;
import io.micronaut.data.model.Pageable;
import io.micronaut.data.model.Sort;
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.BlockingHttpClient;
import io.micronaut.http.client.RxHttpClient;
import io.micronaut.http.client.annotation.Client;
import io.micronaut.http.client.exceptions.HttpClientResponseException;
import io.micronaut.http.uri.UriBuilder;
import io.micronaut.http.uri.UriTemplate;
import io.micronaut.test.annotation.MicronautTest;
import org.junit.jupiter.api.Test;
import ph.net.see.Application;
import ph.net.see.model.Genre;

import javax.inject.Inject;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

import static java.lang.String.format;
import static java.util.Collections.singletonList;
import static org.junit.jupiter.api.Assertions.*;

@MicronautTest(transactional = false, application = Application.class)
@Property(name = "datasources.default.schema-generate", value = "CREATE_DROP")
class GenreControllerTest {

    @Inject
    @Client("/")
    private RxHttpClient rxHttpClient;

    @Inject
    private ObjectMapper objectMapper;

    BlockingHttpClient getClient() {
        return rxHttpClient.toBlocking();
    }

    @Test
    void supplyAnInvalidOrderTriggersValidationFailure() {
        assertThrows(HttpClientResponseException.class, () ->
                getClient().retrieve(HttpRequest.GET("/genres/list?sort=foo"), Argument.of(List.class, Genre.class)));
    }

    @Test
    void testFindNonExistingGenreReturns404() {
        assertThrows(HttpClientResponseException.class, () ->
                getClient().retrieve(HttpRequest.GET("/genres/99"), Argument.of(Genre.class)));
    }

    private HttpResponse<Genre> saveGenre(String genre) {
        HttpRequest<GenreSaveCommand> request = HttpRequest.POST("/genres", new GenreSaveCommand(genre));
        return getClient().exchange(request);
    }

    @Test
    void testGenreCrudOperations() throws IOException {
        List<Long> genreIds = new ArrayList<>();
        HttpResponse response = saveGenre("DevOps");
        genreIds.add(entityId(response));
        assertEquals(HttpStatus.CREATED, response.getStatus());

        response = saveGenre("Microservices");
        assertEquals(HttpStatus.CREATED, response.getStatus());

        Long id = entityId(response);
        genreIds.add(id);
        Genre genre = show(id);
        assertEquals("Microservices", genre.getName());

        response = update(id, "Micro-services");
        assertEquals(HttpStatus.NO_CONTENT, response.getStatus());

        genre = show(id);
        assertEquals("Micro-services", genre.getName());

        Page<Genre> genres = listGenres(null);
        assertEquals(2, genres.getContent().size());

        genres = listGenres(Pageable.from(0, 1));
        assertEquals(1, genres.getContent().size());
        assertEquals("DevOps", genres.getContent().get(0).getName());

        genres = listGenres(Pageable.from(0, 1,
                Sort.of(singletonList(new Sort.Order("name", Sort.Order.Direction.DESC, false)))));
        assertEquals(1, genres.getContent().size());
        assertEquals("Micro-services", genres.getContent().get(0).getName());

        genres = listGenres(Pageable.from(10, 1));
        assertEquals(0, genres.getContent().size());

        // cleanup:
        for (Long genreId : genreIds) {
            response = delete(genreId);
            assertEquals(HttpStatus.NO_CONTENT, response.getStatus());
        }
    }

    private Page<Genre> listGenres(Pageable pageable) throws IOException {
        String uri = "/genres/list";
        if (pageable != null) {
            UriBuilder uriBuilder = UriBuilder.of(uri)
                    .queryParam("size", pageable.getSize())
                    .queryParam("page", pageable.getNumber());

            pageable.getSort().getOrderBy().forEach(order ->
                    uriBuilder.queryParam("sort", format("%s,%s", order.getProperty(), order.getDirection())));

            uri = uriBuilder.build().toString();
        }
        HttpRequest<Object> request = HttpRequest.GET(uri);
        Argument<Page<Genre>> argument = Argument.of((Class<Page<Genre>>) ((Class) Page.class), Genre.class);

        String jsonResponse = getClient().exchange(request, String.class).getBody().get();

        Page<Genre> pagedGenre = objectMapper.readValue(jsonResponse, new TypeReference<Page<Genre>>() {
        });
        return getClient().retrieve(request, argument);
    }

    private Genre show(Long id) {
        String uri = UriTemplate.of("/genres/{id}").expand(Collections.singletonMap("id", id));
        HttpRequest request = HttpRequest.GET(uri);
        @SuppressWarnings("unchecked") Genre result = getClient().retrieve(request, Genre.class);
        return result;
    }

    private HttpResponse update(Long id, String name) {
        HttpRequest request = HttpRequest.PUT("/genres", new GenreUpdateCommand(id, name));
        @SuppressWarnings("unchecked") HttpResponse result = getClient().exchange(request);
        return result;
    }

    private HttpResponse delete(Long id) {
        HttpRequest request = HttpRequest.DELETE("/genres/" + id);
        @SuppressWarnings("unchecked") HttpResponse result = getClient().exchange(request);
        return result;
    }

    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;
    }
}
package ph.net.see.config;

import com.fasterxml.jackson.core.Version;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.module.SimpleAbstractTypeResolver;
import com.fasterxml.jackson.databind.module.SimpleModule;
import io.micronaut.context.annotation.Context;
import io.micronaut.data.model.Page;
import io.micronaut.data.model.Pageable;
import io.micronaut.data.model.Sort;
import ph.net.see.config.jackson.DefaultPageMixin;
import ph.net.see.config.jackson.OrderMixin;
import ph.net.see.config.jackson.PageableMixin;
import ph.net.see.config.jackson.SortMixin;

import javax.inject.Inject;

@Context
public class JacksonConfig {

    @Inject
    public void configureObjectMapper(ObjectMapper objectMapper) throws ClassNotFoundException {
        Class<?> defaultPageClass = Class.forName("io.micronaut.data.model.DefaultPage");

        SimpleAbstractTypeResolver resolver = new SimpleAbstractTypeResolver();
        resolver.addMapping(Page.class, (Class) defaultPageClass);

        SimpleModule module = new SimpleModule("CustomModel", Version.unknownVersion());
        module.setAbstractTypes(resolver);

        objectMapper.registerModule(module);

        // Note: We cannot use Page.of(..) as @JsonCreator because of
        // https://github.com/FasterXML/jackson-databind/issues/2384
        objectMapper.addMixIn(defaultPageClass, DefaultPageMixin.class);
        objectMapper.addMixIn(Pageable.class, PageableMixin.class);
        objectMapper.addMixIn(Sort.class, SortMixin.class);
        objectMapper.addMixIn(Sort.Order.class, OrderMixin.class);
    }
}
package ph.net.see.config.jackson;

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.data.model.Pageable;

import java.util.List;

@JsonIgnoreProperties(ignoreUnknown = true)
public abstract class DefaultPageMixin<T> {

    @JsonCreator
    DefaultPageMixin(
            @JsonProperty("content") List<T> content,
            @JsonProperty("pageable") Pageable pageable,
            @JsonProperty("totalSize") long totalSize) {
    }
}
package ph.net.see.config.jackson;

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.data.model.Sort;

@JsonIgnoreProperties(ignoreUnknown = true)
public class OrderMixin {

    @SuppressWarnings("unused")
    @JsonCreator
    public OrderMixin(
            @JsonProperty("property") String property,
            @JsonProperty("direction") Sort.Order.Direction direction,
            @JsonProperty("ignoreCase") boolean ignoreCase) {
    }

}
package ph.net.see.config.jackson;

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import edu.umd.cs.findbugs.annotations.Nullable;
import io.micronaut.data.model.Pageable;
import io.micronaut.data.model.Sort;

import java.util.List;

@JsonIgnoreProperties(ignoreUnknown = true)
public abstract class PageableMixin {

    @SuppressWarnings("unused")
    @JsonCreator
    static Pageable from(
            @JsonProperty("index") int number,
            @JsonProperty("size") int size,
            @JsonProperty("sort") @Nullable Sort sort) {
        return null;
    }

    @JsonIgnore
    public List<Sort.Order> getOrderBy() {
        return null;
    }
}
package ph.net.see.config.jackson;

import com.fasterxml.jackson.annotation.JsonCreator;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.micronaut.data.model.Sort;

import java.util.List;

@JsonIgnoreProperties(ignoreUnknown = true)
public class SortMixin {

    @SuppressWarnings("unused")
    @JsonCreator
    static Sort of(@JsonProperty("orderBy") List<Sort.Order> ignore) {
        return null;
    }
}

Conclusion

In this post, we just learned how to create a REST application powered by micronaut-predator-jdbc

Disclaimer

As of this writing, Micronaut Predator has no official releases yet. So I forked Micronaut Predator's repo, and created an unofficial release. APIs may change once Micronaut creates an official release.