CoroutineCrudRepository and Reactive Support

[henkosch]

Hi! Does kotlin-jdsl support spring data reactive queries? We are using Spring Data R2DBC with Postgres with CoroutineCrudRepository to query our database asynchronously. We are considering using kotlin-jdsl and I'm wondering if it is possible. We have found that you have support for JpaRepository with KotlinJdslJpqlExecutor, but how could we use it with our CoroutineCrudRepository?

[shouwn]

Hi henkosch!

Unfortunately, Kotlin JDSL only supports JPQL in version 3.0.X. As far as I can find, R2DBC does not support JPA. Therefore, asynchronous cannot be supported.

Kotlin JDSL has the roadmap to support a native query. When native query is supported, support for CoroutineCrudRepository may be possible. However, this will be available next year at the earliest.

And in the roadmap, I only wrote support for mysql, Oracle, but it seems like a lot of people are using Postgres. It seems more beneficial to have Postgres support than Oracle support, so I'll think about Postgres support as well.

[cj848]

Whether it's correct or not aside, according to the link above https://stackoverflow.com/questions/18383946/how-to-convert-hql-to-sql-query-programmatically-without-logging (it appears to be an older version of the HQL generator. Recently It seems that it will not work in this version, so it is better to look for the code for the latest 6.x version separately) HQL queries created through JDSL can be changed to native queries if the Hibernate library and JPA entity are defined. However, you need to be careful about adding dependencies without using Hibernate. If you first create an HQL query like in the link using a JDSL query, you can create it as follows.

val context = JpqlRenderContext()

val query = jpql {
     select(
         path(Author::authorId);
     ).from(
         entity(Author::class);
         join(BookAuthor::class).on(path(Author::authorId).equal(path(BookAuthor::authorId)));
     ).groupBy(
         path(Author::authorId);
     ).orderBy(
         count(Author::authorId).desc();
     )
}

val renderer = JpqlRenderer()

val rendered = renderer.render(query, context)
val jpqlQueryString = rendered.query
val params = rendered.params

With the jpql and parameters created above, you can create native sql through hibernate's query generator. Please look for the 6.x code separately.

Again, I do not recommend the above code, but if jdsl's native sql version DSL is released in the future, it is expected that the above code can be reused in almost the same way, so I will guide you through a roundabout method.

[henkosch]

Thank you! Unfortunately I wasn't able to find a way with Hibernate 6 to translate HQL to Native SQL yet. However I found that Hibernate now has a reactive implementation which supports running HQL directly on a reactive hibernate session. See: https://hibernate.org/reactive/documentation/2.2/reference/html_single/#_queries

I'm now trying to set this up with kotlin-jdsl. Do you think it could work?

[cj848]

Of course it is. Hibernate reactive is officially supported by kotlin-jdsl. I hope you try it.

[shouwn]

I assume you are using Coroutine because you asked for the CoroutineCrudRepository.

Since Kotlin JDSL generates a JPQL, you can run that JPQL with Hibernate Reactive. Kotlin JDSL also supports the hibernate-reactive-support module for this.

However, Hibernate Reactive doesn't work well with coroutines.

JPA has a specification for lazy loading of properties. Since lazy loading is blocking, the property itself must be suspendable, which is difficult.

Therefore, Hibernate Reactive also requires a separate fetch method to be called in the context of the session object for lazy loading.

For these reasons, I don't recommend Hibernate Reactive.

If you don't use JPA Entity as a domain and limit your use to the persistence layer, I think you can make do with Hibernate Reactive because the scope of the session or transaction is limited.

[shouwn]

For these reasons, I'm not looking for a way to run JPA asynchronously, but rather how I can easily create native queries to integrate with R2DBC.