Reactive MySQL with Spring Boot

A working project with Java and R2DBC.

Data is getting complicated (source: wallpapertag.com)

I recently explored reactive Java and wanted to play with MySQL data. There are various articles on the internet that attempt to explain how to use MySQL reactively using R2DBC but I kept getting tripped up by dated or non-working code.

It was a tedious task looking for a beginning-to-end Spring Boot example that wasn’t a single file calling a direct connection with hard-coded properties and a basic Mono query. So, rather than procrastinate about the state of the internet I decided to turn out my own example.

The project lives here: https://github.com/robincakeellis/sqlrx

Executive Summary

I want a working Java project that uses Spring Boot to return data reactively from a MySQL 8 database. It must be configurable with application properties and have build-time tests.

The Data

Examples on the web tend to simple: an Animal table with dog and cat. That did not seem enough for me to see how things work.

I chose to use data from the game EVE Online because I am familiar with it having built a website to help me play (links at the end). Tables range from relatively small (27 rows) to relatively large (~11K rows) and generally come with nice integer primary keys. The data is free (though copyright applies of course).

There is a simple SQL script containing test data in the Github project. A link to various flavours of the full static data is at the end.

Tech Stack

• Java: I am using OpenJDK 17. I also built and ran with OpenJDK 8 all good.
• Spring Boot: I am using 2.7.x. I also built and ran with 2.5.0 no problemo.
• MySQL 8: I have this installed along with MySQL Workbench. I did not test with MySQL 5 nor did I use a Docker container.
• R2DBC: the reactive gateway to SQL databases. It is included with Spring Boot.
• Junit: I am using v5 but v4 should work fine, possibly with mild annotation editing.
• Apache Maven: I assemble with maven. I did not test with Gradle.

The POM

A review of the assembly POM: what is in it and what is not. The list applies to Gradle.

• the Java version is specified here. Remember to edit it from 17 to whatever version is appropriate for you. Similarly, the Spring Boot version is specified as the parent.
• spring-boot-starter-webflux will give Mono, Flux and the usual controller annotations.
• spring-boot-starter-data-r2dbc will front the reactive calls to the database.
• dev.miku:r2dbc-mysql is the driver that sits between R2DBC and MySQL. Another option for MySQL is com.github.jasync-sql:jasync-r2dbc-mysqy and for other databases such as H2 you can check the link at the end.
• spring-boot-starter-test for testing.
• io.projectreactor:reactor:test for testing reactive results.

My optional dependencies are Lombok and Spring Boot Devtools which are not essential but I think they are nice to have.

And what is not in the POM:

• spring-boot-starter-web: not necessary. Webflux provides what is needed.
• anything JPA or JDBC related: no. I am not going to enable JPA repositories and I do not need JDBC.
• mysql-connector-java: no. This is not needed for reactive work. It is not even a transitive dependency. Let go.
• anything HikariCP: no. R2DBC has its own connection pool.
• anything Tomcat, Jetty or Undertow related: no. Netty supports reactive work. It might be possible to use an alternative but that is outside the scope of this article.

The Shell

The bare-bones project will have the POM, typical folder structure with empty application.properties (or .yaml), and two classes:

• the first class in main source is the basic Spring Boot application starter. It will have a static main method, and the single annotation @SpringBootApplication on the class
• the second class in test source is the application-start test. It will have a single method — probably called contextLoads() — annotated with @Test and the class itself is annotated with @SpringBootTest

I can run the test in an IDE. I can build and test with Maven and start my app from the command line to see logging such as Bootstrapping Spring Data R2DBC repositories in DEFAULT mode and Netty started on port 8080. Lovely.

Data Access — Repository

I need a repository interface that extends R2dbcRepository with two types: bean class and primary key class. My interface will have the @Repository annotation (though it seems optional).

Data Access — Bean

I need a bean that the repository will work with. This can have an @Table annotation with the name of the table. The simple bean name will used (mostly) if no table name is provided.

Hibernate power users may have experienced the table name being renamed to suit the platform. For example, agtAgents would be renamed by Hibernate to agt_types when using MySQL. This does not happen with R2DBC — what you specify in @Table is what you get in queries.

Having said that (!) if you do not specify the name then a strategy is applied. For example, the bean is called AgentRow and so — without a specific table name — R2DBC tries to use agent_row as the table name.

Properties in the bean will be mapped to matching columns in the table. Case may or may not be important for your SQL provider so you can specify an exact column name to use per property. For example, I have a bean property called agentTypeId which is mapped to the column agentTypeID and I have a bean boolean called locator mapped to the column isLocator.

Primary key is noted using @Id. I have no experience of composite primary keys with R2DBC.

Data Access — Properties

I need to specify where my data lives so it is time to update application.properties (or .yaml). The critical properties are spring.r2dbc.url and spring.r2dbc.username and spring.r2dbc.password

The standard spring.datasource.* properties are not relevant for R2DBC. Use spring.r2dbc.* — the full set of known properties may appear in auto-complete in your IDE or you can find them on the massive Spring Boot property page (linked at the end).

An important change is that you must move from jdbc: prefixed URLs to r2dbc:

Pooling is enabled by default but I believe you need to specify :pool in the connection URL to actually use the connection pool. I am open to corrections!

An R2DBC URL: spring.r2dbc.url=r2dbc:pool:mysql://localhost:3306/SQL_RX_TEST?zeroDateTimeBehavior=convertToNull&useSSL=false&useServerPrepareStatement=true

While you are here and if you are updating an existing project, remember that Hibernate or HikariCP related properties are not needed.

Integration Tests

I need tests for my repository. I pick two standard repository methods to begin with: count() and findAll(). The repository test class will be annotated with @SpringBootTest.

You may have use @DataJpaTest in the past but we are not using JPA. DataJpaTest creates an in-memory database for tests. It might be possible to use reactive embedded H2 for integration tests and reactive MySQL for non test work but I have not investigated that.

The test methods are annotated with @Test as normal.

The classic approach is to call the repository and perform asserts on the returned value. This is possible but the repo result will be reactive so block() must be called to get a final assertable value. For example:

final long count = repo.count().block();
assertEquals(10871L, count);

The preferred approach is to use StepVerifier. It can check sequences of data, check if data matches some expected content and check whether the response is terminated the correct way — powerful!

StepVerifier
    .create(repo.count())
    .expectNext(10871L)
    .expectComplete()
    .verify();
        
StepVerifier
    .create(repo.findAll().collectList())
    .expectNextMatches(list -> list.size() == 10871)
    .expectComplete()
    .verify();

I can run the tests and see logging such as Scanning for R2DBC repositories in packages XXX. The next line notes a repository was found: Identified candidate component class: file [YYY].

Expanding The Repository

R2DBC supports methods with planned names that follow rules to generate the final SQL. @Query can also be used. The test data has an index on the agents table by corporation id and another index by location id so I create a method for each of those.

Flux<AgentRow> findAllByCorporationId(int corpId);
Flux<AgentRow> findAllByLocationId(int locationId);

More tests are required. I confirmed what the numbers should be using MySQL Workbench.

StepVerifier
    .create(repo.findAllByCorporationId(1000120).collectList())
    .expectNextMatches(list -> list.size() == 144)
    .expectComplete()
    .verify();
StepVerifier
    .create(repo.findAllByLocationId(60008368).collectList())
    .expectNextMatches(list -> list.size() == 18)
    .expectComplete()
    .verify();

Log My Queries

I can enable logging using application.properties to see the executed queries— handy.

logging.level.org.springframework.data.repository=DEBUG
logging.level.org.springframework.r2dbc.core=DEBUG

Service

I need a service to hide all of that unused repo functionality. The service methods could perform some transformation or decoration but for this project the service is a proxy. Anything Flux or Mono from the repo is returned as-is to the service caller.

I could write tests for the service but — as it is a proxy — I skip these tests. Shocking.

Controller

Controller endpoints have the typical mapping related annotations. The controller calls the service and responds to the caller with whatever the service returns — reactive all the way.

@GetMapping("/corp/{id}")
public Flux<AgentRow> getForCorp(@PathVariable int id) {
    return service.getForCorp(id);
}

One issue might occur if you annotate your controller with @Controller: calling a reactive endpoint can lead to an exception with a message such as Multi-value reactive types not supported in view resolution. This can be fixed by annotating the method with @ResponseBody. Alternatively, annotate the controller with @RestController.

I want to test this controller. The non-reactive approach is to annotate the test class with @WebMvcTest, wire in a MockMvc instance, mock underlying instances (such as the service), call the controller and check the response including content with jsonPath().

We have no idea when data will arrive in ReactiveLand so we follow a different route.

I use @WebFluxText (instead of WebMvcTest) and specify the controller class I am testing. I wire in a WebTestClient (instead of MockMvc). I still need to feed data into the mocked service so the endpoints can find something. I can call endpoints using the web test client and test the response.

A cool thing I learned was you can check the status and headers of the response and then pass the body to StepVerifier for thorough reactive tests.

final ResponseSpec response = webTestClient
   .get().uri("http://localhost:8080/agents/ids")
   .exchange();
final Flux<Integer> flux = response
   .expectStatus().isOk()
   .returnResult(Integer.class)
   .getResponseBody();
StepVerifier.create(flux.collectList())
   .expectNextMatches(list -> list.size() == 2)
   .verifyComplete();

Summary

I enjoyed learning during this short project and especially how to test reactive results. I have barely touched the surface of reactive development but StepVerifier is proper handy.

I hope this helps you move forward with reactive development as much as it helped me!

Links

• EVE Online static data in various formats: https://www.fuzzwork.co.uk/dump/ Be kind to this site — the provider hosts it for free
• R2DBC driver list: https://spring.io/projects/spring-data-r2dbc
• Spring Boot application properties: https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html
• Nice WebTestClient reference: https://spring.getdocs.org/en-US/spring-framework-docs/docs/testing/integration-testing/webtestclient.html
• If you play EVE Online, an agent explorer: https://evemissioneer.com
• If you do not play EVE Online: https://www.eveonline.com/