Nicolas Fränkel - Jul 13 '23 - A couple of years ago, Reactive Programming was all the rage, but it had one big issue: reactive stopped as soon as you accessed a SQL database. You had a nice reactive chain up to the database, defeating the whole purpose. Given the prevalence of SQL databases in existing and new apps, one couldn't enjoy the full benefits of Reactive Programming but still pay the full price of complexity.
Since then, the landscape has changed tremendously. Most importantly, it offers many reactive drivers over popular databases: PostgreSQL, MariaDB and MySQL, Microsoft SQL Server, Oracle, you name it! Even better, some frameworks provide a reactive API over them.
Even though I'm not providing consulting services regularly, I wanted to keep up-to-date on accessing data reactively. In this post, I'll describe Hibernate Reactive, Spring Data R2DBC, and jOOQ in no particular order.
The base application uses Project Reactor and its types - Flux and Mono. For an added twist, I use Kotlin (without coroutines). Most code snippets have unnecessary type hints for better understanding.
I don't want a complicated demo model, but I don't want it to be too simple. I'll use a single many-to-many relationship and a field with LocalDate:
As far as I remember, the Spring ecosystem was the first to offer a reactive database access API. At first, it was limited to H2 - not very useful in production. However, new reactive drivers were easy to integrate.
Spring Data RDBC builds upon the widespread Spring Data JPA. The biggest difference is that there's a single required annotation for entities, @Id.
Here's the code for the person table:
data class Person(
@Id val id: Long,
val firstName: String,
val lastName: String,
val birthdate: LocalDate?,
@Transient
val addresses: MutableSet<Address> = mutableSetOf()
)
interface PersonRepository : ReactiveCrudRepository<Person, Long>
R2DBC repositories look similar to regular Spring Data repositories with one big difference. They integrate Project Reactor's reactive types, Mono and Flux. Note that it's easy to use Kotlin's coroutines with an additional bridge dependency.
Now comes the hard problem: mapping the many-to-many relationship with the Address.
First, we must tell Spring Data R2DBC to use a specific constructor with an empty set of addresses.
data class Person(
@Id val id: Long,
val firstName: String,
val lastName: String,
val birthdate: LocalDate?,
@Transient
val addresses: MutableSet<Address> = mutableSetOf()
) {
@PersistenceCreator
constructor(
id: Long,
firstName: String,
lastName: String,
birthdate: LocalDate? = null
) : this(id, firstName, lastName, birthdate, mutableSetOf())
}
We also need to define the Address repository, as well as a query to list all addresses of a person:
interface AddressRepository : ReactiveCrudRepository<Address, Long> {
@Query("SELECT * FROM ADDRESS WHERE ID IN (SELECT ADDRESS_ID FROM PERSON_ADDRESS WHERE PERSON_ID = :id)")
fun findAddressForPersonById(id: Long): Flux<Address>
}
Now comes the least tasteful part: Spring Data R2DBC doesn't support many-to-many relationships at the moment. We need a hook that queries the addresses after loading a person.
class PersonLoadOfficeListener(@Lazy private val repo: AddressRepository) //1
: AfterConvertCallback<Person> {
override fun onAfterConvert(person: Person, table: SqlIdentifier) =
repo.findAddressForPersonById(person.id) //2
.mapNotNull {
person.addresses.add(it) //3
person
}.takeLast(1) //4
.single(person) //5
}
@Lazy to avoid running into circular dependencies exception during injectionPerson
As far as I can understand, Spring Data R2DBC still needs to execute additional queries, thus leading to the (in)famous N+1 query problem.
One configures database access via all available Spring alternatives: properties, YAML, Spring profiles, environment variables, etc. Here's a YAML example:
spring.r2dbc:
url: r2dbc:postgresql://localhost:5432/postgres?currentSchema=people
username: postgres
password: root
If you're familiar with regular Hibernate, you'll feel right at home with Hibernate Reactive. The mapping is the same in both cases:
@Entity
@Table(name = "person", schema = "people") //1
class Person(
@Id var id: Long?,
@Column(name = "first_name") //2
var firstName: String?,
@Column(name = "last_name") //2
var lastName: String?,
var birthdate: LocalDate?,
@ManyToMany
@JoinTable( //3
name = "person_address",
schema = "people",
joinColumns = [ JoinColumn(name = "person_id") ],
inverseJoinColumns = [ JoinColumn(name = "address_id") ]
)
val addresses: MutableSet<Address> = mutableSetOf()
) {
internal constructor() : this(null, null, null, null) //4
}
We also need to configure the database. Hibernate Reactive uses the traditional XML-based JPA approach:
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
version="3.0">
<persistence-unit name="postgresql">
<provider>org.hibernate.reactive.provider.ReactivePersistenceProvider</provider> <!--1-->
<properties>
<property name="jakarta.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/postgres?currentSchema=people" />
<property name="jakarta.persistence.jdbc.user" value="postgres" />
<property name="jakarta.persistence.jdbc.password" value="root" />
<property name="jakarta.persistence.schema-generation.database.action" value="validate" />
</properties>
</persistence-unit>
</persistence>
Here's the source for the query itself:
val emf = Persistence.createEntityManagerFactory("postgresql") //1
val sessionFactory: Mutiny.SessionFactory = emf.unwrap(Mutiny.SessionFactory::class.java) //2
val people: Mono<MutableList<Person>> = sessionFactory
.withSession {
it.createQuery<Person>("SELECT p FROM Person p LEFT JOIN FETCH p.addresses a").resultList
}.convert().with(UniReactorConverters.toMono()) //3
EntityManagerFactory
ReactivePersistenceProvider in the persistence.xml, it's a Mutiny.SessionFactory
Note that Hibernate Reactive is the only library among the three to return a Mono<List<T>> instead of a Flux<T>. In layman's terms, it means you get the whole list at once instead of getting the elements one by one and being able to do something on each one individually.
As for the two above frameworks, jOOQ Reactive is similar to its non-reactive version. You first generate the code from the database schema, then use it.
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId> <!--1-->
<executions>
<execution>
<id>jooq-codegen</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.postgresql</groupId> <!--2-->
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
</dependencies>
<configuration>
<generator>
<name>org.jooq.codegen.KotlinGenerator</name> <!--3-->
<database>
<inputSchema>people</inputSchema> <!--4-->
</database>
<target>
<packageName>ch.frankel.blog.reactivedata.jooq</packageName>
</target>
</generator>
<jdbc> <!--4-->
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost:5432/postgres</url>
<user>postgres</user>
<password>root</password>
</jdbc>
</configuration>
</plugin>
Once you've generated the code, you can create your data class and design the query. jOOQ class hierarchy integrates with Java's collections, Java's Reactive Streams, and Project Reactor.
The code may look complex if you're neither a SQL nor a jOOQ expert.
Remember that variable types are unnecessary, but added for documentation purposes:
fun findAll(): Flux<PersonWithAddresses> { //1
val people: SelectJoinStep<Record5<Long?, String?, String?, LocalDate?, MutableList<Address>>> = //2
ctx.select(
PERSON.ID,
PERSON.FIRST_NAME,
PERSON.LAST_NAME,
PERSON.BIRTHDATE,
DSL.multiset( //2
DSL.select(
PERSON_ADDRESS.ADDRESS_ID,
PERSON_ADDRESS.address.FIRST_LINE,
PERSON_ADDRESS.address.SECOND_LINE,
PERSON_ADDRESS.address.ZIP,
PERSON_ADDRESS.address.CITY,
PERSON_ADDRESS.address.STATE,
PERSON_ADDRESS.address.COUNTRY,
).from(PERSON_ADDRESS)
.where(PERSON_ADDRESS.PERSON_ID.eq(PERSON.ID))
).convertFrom { it.map(addressMapper) } //3
).from(PERSON)
return Flux.from(people) //4
.map(personWithAddressesMapper) //3
}
Flux
multiset, see below.Flux for people is a Project Reactor Publisher
Let's dive a bit into multiset from the point of view of a non-jOOQ expert - me. Initially, I tried to execute a regular SQL query with results I tried to flatten with Project Reactor's API. I failed miserably because of my lack of knowledge of the API, but even if I had succeeded, it would have been the wrong approach.
After hours of research, I found multiset via a post from Vlad Mihalcea:
The
MULTISETvalue constructor is one of jOOQ's and standard SQL's most powerful features. It allows for collecting the results of a non scalar subquery into a single nested collection value withMULTISETsemantics.
In the above query, we first select all addresses of a person, map each row to an object, and flatten them in a list on the same result row as the person. The second mapper maps the row, including the address list, to a dedicated person with an addresses list.
I'm not a SQL master, so multiset is hard at first glance. However, I confirm that it's a powerful feature indeed.
Note that nested collections are fetched eagerly on a per-record basis, whereas top-level records are streamed reactively.
We have browsed the surface of the main three reactive database access: Spring Data R2DBC, Hibernate, and jOOQ. So, which one should one choose?
The main deciding factor is whether you already use one of their non-reactive flavors. Use the framework you're familiar with since both reactive and non-reactive usages are similar.
I think that jOOQ is extremely powerful but requires a familiarity I currently lack. If you have complex queries that don't map easily to other approaches, it's the way to go. Besides that, I've no strong opinion, though I find Hibernate Reactive's configuration too limited by JPA and its Mono<List<T>> return type puzzling.
Thanks for Lukas Eder and Mark Paluch for their reviews on their respective sections of expertise.
The complete source code for this post can be found on GitHub:
To test the app, you can start a PostgreSQL containerized database in the project’s directory:
docker run -it --rm -e POSTGRES_PASSWORD=root -p5432:5432 -v ./data:/docker-entrypoint-initdb.d postgres:15-alpine
Run the app and try the different endpoints:
curl localhost:8080/hibernate
curl localhost:8080/spring
curl localhost:8080/jooq
To go further:
Originally published at A Java Geek on July 9th, 2023
