mn create-app example.micronaut.micronautguide \
--features=data-jdbc,flyway,jdbc-hikari,mysql,serialization-jackson \
--build=gradle
--lang=groovy
Access a database with Micronaut Data JDBC
Learn how to access a database with Micronaut JDBC repositories.
Authors: Sergio del Amo, John Shingler
Micronaut Version: 3.9.2
1. Getting Started
In this guide, we will create a Micronaut application written in Groovy.
The application exposes some REST endpoints and stores data in a MySQL 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 -
Docker installed to run MySQL and to run tests using Testcontainers.
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.
-
Download and unzip the source
4. Writing the Application
Create an application using the Micronaut Command Line Interface or with Micronaut Launch.
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
.
If you use Micronaut Launch, select Micronaut Application as application type and add data-jdbc
, flyway
, jdbc-hikari
, mysql
, and serialization-jackson
features.
If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features and apply those changes to your application. |
4.1. Data Source configuration
Define the datasource in src/main/resources/application.yml
.
datasources:
default:
dialect: MYSQL
driverClassName: com.mysql.cj.jdbc.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. |
4.2. Database Migration with Flyway
We need a way to create the database schema. For that, we use Micronaut integration with Flyway.
Flyway automates schema changes, significantly simplifying schema management tasks, such as migrating, rolling back, and reproducing in multiple environments.
Add the following snippet to include the necessary dependencies:
implementation("io.micronaut.flyway:micronaut-flyway")
runtimeOnly("org.flywaydb:flyway-mysql")
We will enable Flyway in application.yml
and configure it to perform migrations on one of the defined data sources.
flyway:
datasources:
default:
enabled: true (1)
1 | Enable Flyway for the default datasource. |
Configuring multiple data sources is as simple as enabling Flyway for each one. You can also specify directories that will be used for migrating each data source. Review the Micronaut Flyway documentation for additional details. |
Flyway migration will be automatically triggered before your Micronaut application starts. Flyway will read migration commands in the resources/db/migration/
directory, execute them if necessary, and verify that the configured data source is consistent with them.
Create the following migration files with the database schema creation:
DROP TABLE IF EXISTS genre;
CREATE TABLE genre (
id BIGINT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);
During application startup, Flyway will execute the SQL file and create the schema needed for the application.
4.3. Domain
Create the domain entities:
package example.micronaut.domain
import groovy.transform.CompileStatic
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 javax.validation.constraints.NotNull
@CompileStatic
@Serdeable
@MappedEntity
class Genre {
@Id
@GeneratedValue(GeneratedValue.Type.AUTO)
Long id
@NotNull
String name
String toString() {
"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.4. Repository Access
Next, create a repository interface to define the operations to access the database. Micronaut Data will implement the interface at compilation time:
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.MYSQL) (1)
abstract class GenreRepository implements PageableRepository<Genre, Long> { (2)
abstract Genre save(@NonNull @NotBlank String name)
@Transactional
Genre saveWithException(@NonNull @NotBlank String name) {
save(name)
throw new DataAccessException('test exception')
}
abstract long 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 PageableRepository
→ CrudRepository
→ GenericRepository
.
Repository | Description |
---|---|
|
A repository that supports pagination. It provides |
|
A repository interface for performing CRUD (Create, Read, Update, Delete). It provides methods such as |
|
A root interface that features no methods but defines the entity type and ID type as generic arguments. |
4.5. 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:
package example.micronaut
import groovy.transform.CompileStatic
import io.micronaut.serde.annotation.Serdeable
import javax.validation.constraints.NotBlank
import javax.validation.constraints.NotNull
@CompileStatic
@Serdeable (1)
class GenreUpdateCommand {
@NotNull
final Long id
@NotBlank
final String name
GenreUpdateCommand(Long id, String name) {
this.id = id
this.name = name
}
}
1 | Declare the @Serdeable annotation at the type level in your source code to allow the type to be serialized or deserialized. |
Create GenreController
, a controller that exposes a resource with the common CRUD operations:
package example.micronaut
import example.micronaut.domain.Genre
import groovy.transform.CompileStatic
import io.micronaut.data.exceptions.DataAccessException
import io.micronaut.data.model.Pageable
import io.micronaut.http.HttpHeaders
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
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 javax.validation.Valid
import javax.validation.constraints.NotBlank
@CompileStatic
@ExecuteOn(TaskExecutors.IO) (1)
@Controller('/genres') (2)
class GenreController {
protected final GenreRepository genreRepository
GenreController(GenreRepository genreRepository) { (3)
this.genreRepository = genreRepository
}
@Get('/{id}') (4)
Optional<Genre> show(Long id) {
genreRepository
.findById(id) (5)
}
@Put (6)
HttpResponse update(@Body @Valid GenreUpdateCommand command) { (7)
genreRepository.update(command.id, command.name)
HttpResponse
.noContent()
.header(HttpHeaders.LOCATION, location(command.id).path) (8)
}
@Get('/list') (9)
List<Genre> list(@Valid Pageable pageable) { (10)
genreRepository.findAll(pageable).content
}
@Post (11)
HttpResponse<Genre> save(@Body('name') @NotBlank String name) {
Genre genre = genreRepository.save(name)
HttpResponse.created(genre)
.headers(headers -> headers.location(location(genre)))
}
@Post('/ex') (12)
HttpResponse<Genre> saveExceptions(@Body @NotBlank String name) {
try {
Genre genre = genreRepository.saveWithException(name)
HttpResponse.created(genre)
.headers(headers -> headers.location(location(genre)))
} catch(DataAccessException ex) {
return HttpResponse.noContent()
}
}
@Delete('/{id}') (13)
@Status(HttpStatus.NO_CONTENT)
void delete(Long id) {
genreRepository.deleteById(id)
}
private static URI location(Long id) { URI.create("/genres/$id") }
private static URI location(Genre genre) { location(genre.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 | 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/list , 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.6. Writing Tests
Create a test to verify the CRUD operations:
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.http.uri.UriBuilder
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import jakarta.inject.Inject
import spock.lang.Specification
import spock.lang.Unroll
@MicronautTest (1)
class GenreControllerSpec extends Specification {
@Inject
@Client("/")
HttpClient client; (2)
void "find non-existing genre should return 404"() {
when:
client.toBlocking().exchange(HttpRequest.GET('/genres/99'))
then:
HttpClientResponseException exception = thrown()
exception.response
exception.status == HttpStatus.NOT_FOUND
}
@Unroll
void "genre CRUD operations"() {
given: "verify post/create operation - adding #entries.value"
HttpRequest<?> request = HttpRequest.POST('/genres', [name: 'DevOps']) (3)
when:
HttpResponse<?> response = client.toBlocking().exchange(request)
then:
response.status == HttpStatus.CREATED
when:
String location = response.header(HttpHeaders.LOCATION)
then:
location.startsWith("/genres/")
when:
Long devOpsId = Long.valueOf(location.substring("/genres/".length()))
then:
noExceptionThrown()
when:
request = HttpRequest.POST('/genres', [name: 'Microservices']) (3)
response = client.toBlocking().exchange(request)
then:
response.status == HttpStatus.CREATED
when:
location = response.header(HttpHeaders.LOCATION)
then:
location.startsWith("/genres/")
when:
Long microservicesId = Long.valueOf(location.substring("/genres/".length()))
then:
noExceptionThrown()
when: "verify get/read operation"
URI microservicesUri = UriBuilder.of("/genres").path(""+microservicesId).build()
Genre genre = client.toBlocking().retrieve(microservicesUri.toString(), Genre) (4)
then:
genre.name == 'Microservices'
when: "verify put/update operation"
request = HttpRequest.PUT('/genres', new GenreUpdateCommand(microservicesId, 'Micro-services'))
response = client.toBlocking().exchange(request) (5)
then:
response.status == HttpStatus.NO_CONTENT
when:
genre = client.toBlocking().retrieve(microservicesUri.toString(), Genre)
then:
genre.name == 'Micro-services'
when: "verify list operation"
request = HttpRequest.GET('/genres/list')
List<Genre> genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))
then:
genres.size() == 2
when: "verify transaction execption does not add genre"
request = HttpRequest.POST('/genres/ex', [name: 'Microservices']) (3)
response = client.toBlocking().exchange(request)
then:
response.status == HttpStatus.NO_CONTENT
and:
client.toBlocking().retrieve(HttpRequest.GET('/genres/list'), Argument.listOf(Genre)).size() == 2
when: "verify pageable operation"
request = HttpRequest.GET(UriBuilder.of("/genres")
.path("list")
.queryParam("size", 1)
.build())
genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))
then:
genres.size() == 1
genres[0].name == 'DevOps'
when: "verify pagable sort operation"
request = HttpRequest.GET(UriBuilder.of("/genres")
.path("list")
.queryParam("size", 1)
.queryParam("sort", "name,desc")
.build())
genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))
then:
genres.size() == 1
genres[0].name == 'Micro-services'
when: "verify pageable empty page"
request = HttpRequest.GET(UriBuilder.of("/genres")
.path("list")
.queryParam("size", 1)
.queryParam("page", 2)
.build())
genres = client.toBlocking().retrieve(request, Argument.listOf(Genre))
then:
genres.size() == 0
when: "verify delete operation - id: #id"
request = HttpRequest.DELETE(UriBuilder.of("/genres").path("" + microservicesId).build().toString())
response = client.toBlocking().exchange(request)
then:
response.status == HttpStatus.NO_CONTENT
when:
request = HttpRequest.DELETE(UriBuilder.of("/genres").path("" + devOpsId).build().toString())
response = client.toBlocking().exchange(request)
then:
response.status == HttpStatus.NO_CONTENT
}
}
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. Testing Running API
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" }'
8. Test Resources
When the application is started locally — either under test or by running the application — resolution of the datasource URL is detected and the Test Resources service will start a local MySQL docker container, and inject the properties required to use this as the datasource.
For more information, see the JDBC section or R2DBC section of the Test Resources documentation.
9. Connecting to a MySQL database
Previously, we connected to a MySQL database, which Micronaut Test Resources started for us.
However, it is easy to connect to an already existing database. Let’s start a database and connect to it.
Execute 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
If you are using macOS on Apple Silicon – e.g. M1, M1 Pro, etc. – Docker might fail to pull an image for mysql:8 . In that case substitute mysql:oracle .
|
Export several environment variables:
export DATASOURCES_DEFAULT_URL=jdbc:mysql://localhost:3306/db
export DATASOURCES_DEFAULT_USERNAME=sherlock
export DATASOURCES_DEFAULT_PASSWORD=elementary
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.
You can run the application and test the API as it was described in the previous sections. However, when you run the application, Micronaut Test Resources does not start a MySQL container because you have provided values for datasources.default.*
properties.
10. Next steps
Read more about Micronaut Data.
11. Help with the Micronaut Framework
The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.